MariaDB is a high performance, open source database that helps the world’s busiest websites deliver more content, faster. It has been created by the developers of MySQL with the help of the FOSS community and is a fork of MySQL. It offers various features and enhancements like alternate storage engines, server optimisations and patches.
The lead developer of MariaDB is Michael Monty Widenius, who is also the founder of MySQL and Monty Program AB.
No single person or company nurtures MariaDB/MySQL development. The guardian of the MariaDB community, the MariaDB Foundation, drives it. It states that it has the trademark of the MariaDB server and owns mariadb.org, which ensures that the official MariaDB development tree is always open to the developer community. The MariaDB Foundation assures the community that all the patches, as well as MySQL source code, are merged into MariaDB. The Foundation also provides a lot of documentation. MariaDB is a registered trademark of SkySQL Corporation and is used by the MariaDB Foundation with permission. It is a good choice for database professionals looking for the best and most robust SQL server.
In 2008, Sun Microsystems bought MySQL for US$ 1 billion. But the original developer, Monty Widenius, was quite disappointed with the way things were run at Sun and founded his own new company and his own fork of MySQL – MariaDB. It is named after Monty’s younger daughter, Maria. Later, when Oracle announced the acquisition of Sun, most of the MySQL developers jumped to its forks: MariaDB and Drizzle.
MariaDB version numbers follow MySQL numbers till 5.5. Thus, all the features in MySQL are available in MariaDB. After MariaDB 5.5, its developers started a new branch numbered MariaDB 10.0, which is the development version of MariaDB. This was done to make it clear that MariaDB 10.0 will not import all the features from MySQL 5.6. Also, at times, some of these features do not seem to be solid enough for MariaDBs standards. Since new specific features have been developed in MariaDB, the team decided to go for a major version number. The currently used version, MariaDB 10.0, is built on the MariaDB 5.5 series and has back ported features from MySQL 5.6 along with entirely new developments.
Why MariaDB is better than MySQL
When comparing MariaDB and MySQL, we are comparing different development cultures, features and performance. The patches developed by MariaDB focus on bug fixing and performance. By supporting the features of MySQL, MariaDB implements more improvements and delivers better performance without restrictions on compatibility with MySQL. It also provides more storage engines than MySQL. What makes MariaDB different from MySQL is better testing, fewer bugs and fewer warnings. The goal of MariaDB is to be a drop-in replacement for MySQL, with better developments.
Navicat is a strong and powerful MariaDB administration and development tool. It is graphic database management and development software produced by PremiumSoft CyberTech Ltd. It provides a native environment for MariaDB database management and supports the extra features like new storage engines, microsecond and virtual columns.
It is easy to convert from MySQL to MariaDB, as we need not convert any data and all our old connectors to other languages work unchanged. As of now MariaDB is capable of handling data in terabytes, but more needs to be done for it to handle data in petabytes.
Here is a list of features that MariaDB provides:
- Since it has been released under the GPL version 2, it is free.
- It is completely open source.
- Open contributions and suggestions are encouraged.
- MariaDB is one of the fastest databases available.
- Its syntax is pretty simple, flexible and easy to manage.
- It can be easily imported or exported from CSV and XML.
- It is useful for both small as well as large databases, containing billions of records and terabytes of data in hundreds of thousands of tables.
- MariaDB includes pre-installed storage engines like Aria, XtraDB, PBXT, FederatedX and SphinxSE.
- The use of the Aria storage engine makes complex queries faster. Aria is usually faster since it caches row data in memory and normally doesn’t have to write the temporary rows to disk.
- Some storage engines and plugins are pre-installed in MariaDB.
- It has a very strong community.
Now lets look at how MariaDB is installed.
Step 1: First, make sure that the required packages are installed along with the apt-get key for the MariaDB repository, by using the following commands:
$ sudo apt-get install software-properties-common $ sudo apt-key recv-keys keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
Now, add the apt-get repository as per your Ubuntu version.
For Ubuntu 13.10
$ sudo add-apt-repository 'deb http://ftp.kaist.ac.kr/mariadb/repo/5.5/ubuntu saucy main'
For Ubuntu 13.04
$ sudo add-apt-repository 'deb http://ftp.kaist.ac.kr/mariadb/repo/5.5/ubuntu raring main'
For Ubuntu 12.10
$ sudo add-apt-repository 'deb http://ftp.kaist.ac.kr/mariadb/repo/5.5/ubuntu quantal main'
For Ubuntu 12.04 LTS
$ sudo add-apt-repository 'deb http://ftp.kaist.ac.kr/mariadb/repo/5.5/ubuntu precise main'
Step 2: Install MariaDB using the following commands:
$ sudo apt-get update $ sudo apt-get install mariadb-server
Provide the root account password as shown in Figure 1.
Step 3: Log in to MariaDB using the following command, after installation:
mysql -u root -p
Creating a database in MariaDB
When entering the account administrator password set up during installation, you will be given a MariaDB prompt.
Create a database on students by using the following command:
CREATE DATABASE students;
Switch to the new database using the following command (this is to make sure that you are currently working on this database):
Now that the database has been created, create a table:
CREATE TABLE details(student_id int(5) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, age int(3) DEFAULT NULL, marks int(5) DEFAULT NULL, PRIMARY KEY(student_i)d );
To see what we have done, use the following command:
show columns in details;
Each column in the table creation command is separated by a comma and is in the following format:
Column_Name Data_Type[(size_of_data)] [NULL or NOT NULL] [DEFAULT default_value] [AUTO_INCREMENT]
These columns can be defined as:
- Column Name: Describes the attribute being assigned.
- Data Type: Specifies the type of data in the column.
- Null: Defines whether null is a valid value for that field - it can be nullor not null.
- Default Value: Sets the initial value of all newly created records that do not specify a value.
- auto_increment: MySQL will handle the sequential numbering of any column marked with this option, internally, in order to provide a unique value for each record.
Ultimately, before closing the table definition, we need to use the primary key by typing PRIMARY KEY(column name). It guarantees that this column will serve as a unique field.
Inserting data into a MariaDB table
To insert data into a MariaDB table, use the following commands:
INSERT INTO details(name,age,marks) values ("anu",15,450); INSERT INTO details(name,age,marks) VALUES("Bob",15,400);
The output will be as shown in Figure 4.
We need not add values in student_id. It is automatically incremented. All other values are given in quotes.
Deleting a table
To delete a table, type the following command:
DROP TABLE table_name;
Once the table is deleted, the data inside it cannot be recovered.
We can view the current table using the show tables command, which gives all the tables inside the database:
After deleting the table, use the following commands:
DROP TABLE details; Query OK, 0 rows affected (0.02 sec) SHOW tables;
The output will be:
Empty set (0.00 sec)
Google waves goodbye to MySQL
Google has now switched to MariaDB and dumped MySQL. For the Web community, Googles big move might be a paradigm shift in the DBMS ecosystem, said a Google engineer. Major Linux distributions, like Red Hat and SUSE, and well-known websites such as Wikipedia, have also switched from MySQL to MariaDB. This is a great blow to MySQL.
Google has migrated applications that were previously running on MySQL on to MariaDB without changing the application code. There are five Google technicians working part-time on MariaDB patches and bug fixes, and Google continues to maintain its internal branch of MySQL to have complete control over the improvement. Google running thousands of MariaDB servers can only be good news for those who feel more comfortable with a non-Oracle future for MySQL.
Though multinational corporations like Google have switched to MariaDB, it does have a few shortcomings. MariaDBs performance is slightly better in multi-core machines, but one suspects that MySQL could be tweaked to match the performance. All it requires is for Oracle to improve MySQL by adding some new features that are not present in MariaDB, yet. And then it will be difficult to switch back to the previous database.
MariaDB has the advantage of being bigger in terms of the number of users, than its forks and clones. MySQL took a lot of time and effort before emerging as the choice of many companies. So, it is a little hard to introduce MariaDB in the commercial field. Being a new open source standard, we can only hope that MariaDB will overtake other databases in a short span of time.