Simple Steps Migration From MySQL To MariaDB On Linux

Hi all, this tutorial is all gonna be about how to migrate from MySQL to MariaDB on Linux Server or PC. So, you may ask why should we really migrate from MySQL to MariaDB for our database management. Here, below are the reasons why you should really need to migrate your database management system from MySQL to MariaDB.

Why should I use MariaDB instead of MySQL?

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. Top Websites like Google, Wikipedia, Linkedin, Mozilla and many more migrated to MariaDB. Its features are

  • Backwards compatible with MySQL
  • Forever open source
  • Maintained by MySQL's creator
  • More cutting edge features
  • More storage engines
  • Large websites have switched

Now, lets migrate to MariaDB.

For the testing purpose, let us create a sample database called linoxidedb .

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

$ mysql -u root -p

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.

mysql> create database linoxidedb;

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

mysql> show databases;
creating test databases

As see above, we have totally 5 databases including the newly created database linoxidedb .

mysql> quit

Now, we'll migrate the created databases from MySQL to MariaDB.

Note: This tutorial is not necessary for CentOS, fedora based distribution of Linux because MariaDB is automatically installed instead of MySQL which requires no need to backup the existing databases, you just need to update mysql which will give you mariadb.

1. Backup existing databases

Our first important step is to create a backup of existing databases. To do that, we'll enter the following command from the Terminal (not from MySQL prompt).

$ mysqldump --all-databases --user=root --password --master-data > backupdatabase.sql

Oops! We encountered an error. No worries, it can be fixed.

$ mysqldump: Error: Binlogging on server not active
mysqldump error

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

Edit my.cnf file:

$ sudo nano /etc/mysql/my.cnf

Under [mysqld] section, add the following parameter.

configuring my.cnf

Now, after done save and exit the file. Then, we'll need to restart mysql server. To do that please execute the below commands.

$ sudo /etc/init.d/mysql restart

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

$ mysqldump --all-databases --user=root --password --master-data > backupdatabase.sql
dumping databases

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

2. Uninstalling MySQL

First of all, we'll want to backup the my.cnf file 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 us to keep the existing my.cnf(old backup) file or to use the package containers version (i.e new one).

To backup the my.cnf file, please enter the following commands in a shell or terminal.

$ sudo cp /etc/mysql/my.cnf my.cnf.bak

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

$ sudo /etc/init.d/mysql stop

Then, remove mysql packages.

$ sudo apt-get remove mysql-server mysql-client

uninstalling mysql

3. Installing MariaDB

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

$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp:// 0xcbcb082a1bb943db
# sudo add-apt-repository 'deb trusty main'
adding mariadb repo

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

$ sudo apt-get update
$ sudo apt-get install mariadb-server
installing mariadb
my.conf configuration prompt

We should remember 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 ie my.cnf.bak . So, I will go for default which is N, we'll press N then. For other versions, please refer the MariaDB official repositories page.

4. Restoring Config File

To restore my.cnf from my.cnf.bak, enter the following command in Terminal. We have the old as my.cnf.bak file in our current directory, so we can simply copy the file using the following command:

$ sudo cp my.cnf.bak /etc/mysql/my.cnf

5. Importing Databases

Finally, lets import the old databases that we created before. To do that, we'll need to run the following command.

$ mysql -u root -p < backupdatabase.sql

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

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

$ mysql -u root -p
importing database

Now, to check whether the databases are migrated to MariaDB please run "show databases;" command inside the MarianDB prompt without quotes("") as

mariaDB> show databases;
mysql to mariadb database migrated

As you see in the above result all old databases including our very linoxidedb has been successfully migrated.


Finally, we have successfully migrated our databases from MySQL to MariaDB Database Management System. MariaDB is far more better than MySQL. Though MySQL is still faster than MariaDB in performance but MariaDB is far more better because of its additional features and license. MariaDB is a Free and Open Source Software (FOSS) and will be FOSS forever but MySQL has many additional plugins, etc non-free and there is no proper public roadmap and won't be FOSS in future. If you have any questions, comments, feedback to us, please don't hesitate to write on the comment box below. Thank You ! And Enjoy MariaDB.

2 Comments... add one

  1. Hello. Just sharing, not necessarilty asking for help. I went from MySQL 5.5 to MariaDB 10.5.5. [[In TEST mode thankfully]] I uninstalled MYSQL and installed MariaDB 10.5.5. As I did not remove existing database, MariaDB instance seemed to recognize existing TEST database. lets call it "TESTexisingMySQL". I needed to get a fresh replicated copy of Production, so I restored from a production database. [[I wanted to create a new database shell first and restore to that but I was talked out of it]]. We actually then did an application schema update. When I went to run backups using AutoMwSQLbackup, it said to run MariaDB-Uupgrade which I did. On second run it came up clean and said I was already upgraded. Then I added --single-transaction because when running backup again go error Table 'global_variables' was not locked with LOCK TABLES (1100). Now I see an error Couldn't execute 'SHOW FIELDS FROM `metrics`': View 'sys.metrics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356). I think I need to go back and check compatibility Matrix. Maybe I did horizontal migration and verticle upgrade and it was too much for the database to bear.

    MySQL is 5.7 not 5.5. I see it is compatible to upgrade to Maria 10.3 but I do not see Maria 10.5 in the matrix here.


Leave a Comment