Migrating From MySQL To MariaDB

Why should I migrate from MySQL to MariaDB?

MariaDb is an enhanced drop-in replacement and community-developed fork of the MySQL database system. It was developed by MariaDB foundation, and is being led by original developers of MySQL. Working with MariaDB is entirely same as MySQL. After Oracle bought MySQL, it is not free and open source anymore, but MariaDB is still free and open source.

According to themukt ,

What Oracle is doing wrong:

  • New ‘enterprise’ extensions in MySQL are closed source:
  • The bugs database is not public anymore
  • The MySQL public repositories are not anymore actively updated.
  • Security problems are not communicated nor addressed quickly (This is making Linux distributions very annoyed with Oracle)
  • Instead of fixing bugs, Oracle is removing features:
  • New code in MySQL 5.5 doesn’t have test cases anymore.
  • Some of the new code is surprisingly good by Oracle, but unfortunately the quality varies and a notable part needs to be rewritten before we can include it in MariaDB

Well, are you done with MySQL? Come on, let us migrate from MySQL to MariaDB. The migration method is much simpler than you think.

Let’s Migrate

For the testing purpose, let us create two sample databases called testdb1 , and testdb2 .

Log in to MySQL as root user using the following command:

Enter the mysql root user password. You’ll be redirected to the mysql prompt.

Create test databases:

Enter the following commands from mysql prompt to create test databases.

To see the list of available databases, enter the following command:

As see above, we have totally 5 databases including the newly created databases testdb1 and testdb2.

1. Backup existing databases

The first important step is to take backup of existing databases. To do that, enter the following command from the Terminal (not from MySQL prompt).

Oops! I got the following error.

To fix this error, we have to do a small modification in my.cnf file.

Edit my.cnf file:

On Debian/Ubuntu:

On RPM based systems:

Under [mysqld] section, add the following parameter.

Sample output of my my.cnf file.

Save and exit file. Restart mysql server.

On RPM based systems:

On Debian based systems:

Now, re-run the mysqldump command to backup all databases.

Sample output:

The above command will backup all databases, and stores them in backupdatabase.sql in the current directory.

2. Stop MySQL services and remove all mysql packages

First, backup the my.cnf fie to a safe location.

Note: The my.cnf file will not be deleted when uninstalling MySQL packages. We do it for the precaution. During MariaDB installation, the installer will ask you to keep the existing my.cnf(old backup) file or to use the package containers version (i.e new one).

On RPM systems:

On Deb systems:

To stop mysql service, enter the following command from your Terminal.

On RPM based systems:

On Debian based systems:

Then, remove mysql packages.

On RPM systems:

The above command will take the backup of your current MySQL config file /etc/my.cnf to /etc/my.cnf.rpmsave .

On Debian based systems:

3. Install MariaDB

The latest CentOS 7 and Ubuntu 14.10, 14.04 contains MariaDB packages in their official repositories. If you want to use the most recent version MariaDb, add the MariaDB official repositories depending upon your OS version.

Install MariaDB On RHEL 7 based systems:

Create a file /etc/yum.repos.d/mariadb.repo ,

Add the following lines:

Update the repositories and install mariadb using the following commands:

Start mysql service using the following command:


After installing MariaDB, you should set the database root user password. To do that, execute the following command from your Terminal.

Install MariaDB On Ubuntu 14.04:

Here are the commands to run to install MariaDB on your Ubuntu system:

Once the key is imported and the repository added you can install MariaDB with:

Keep in mind that during MariaDB installation, the installer will ask you either to use the existing my.cnf (old backup) file, or use the package containers version (i.e new one). You can either use the old my.cnf file or the package containers version. If you want to use the new my.cnf version, you can restore the contents of older my.cnf (We already have copied this file to safe location before) later.

For other versions, please refer the MariaDB official repositories page .

4. Copy contents of my.cnf.bak(old backup) file to new my.cnf file

To do that, enter the following command in Terminal. I have the old my.cnf.bak file in my current directory, so I simply copied the file using the following command:

On RPM systems:

On DEB systems:

5. Import Databases

Finally, let us import the old databases which we’ve created in the first step using the following command.

That’s it. We have successfully imported the old databases.

Let us check if the databases are really imported. To do that, log in to mysql prompt using command:

Run show databases; command from mysql prompt to check for the databases.

Sample output:

As you see in the above result all old databases including testdb1 and testdb2 have been successfully imported.

That’s all. Start using MariaDb.