How To Migrate From MySQL To MariaDB On Linux

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:

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 testdb1;
Query OK, 1 row affected (0.00 sec)

mysql> create database testdb2;
Query OK, 1 row affected (0.00 sec)

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

mysql> show databases;

Sample output:

 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | testdb1            |
 | testdb2            |
 +--------------------+
 5 rows in set (0.00 sec)

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).

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

Oops! I got the following error.

mysqldump: Error: Binlogging on server not active

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

Edit my.cnf file:

On Debian/Ubuntu:

sudo vi /etc/mysql/my.cnf

On RPM based systems:

sudo vi /etc/my.cnf

Under [mysqld] section, add the following parameter.

log-bin=mysql-bin

Sample output of my my.cnf file.

 [...]
 [mysqld]
 #
 # * Basic Settings
 #
 user            = mysql
 pid-file        = /var/run/mysqld/mysqld.pid
 socket          = /var/run/mysqld/mysqld.sock
 port            = 3306
 basedir         = /usr
 datadir         = /var/lib/mysql
 tmpdir          = /tmp
 lc-messages-dir = /usr/share/mysql
 skip-external-locking
 log-bin=mysql-bin
 [...]

Save and exit file. Restart mysql server.

On RPM based systems:

sudo /etc/init.d/mysqld restart

On Debian based systems:

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

Sample output:

Enter password:
 -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

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:

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

On Deb systems:

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

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

On RPM based systems:

sudo /etc/init.d/mysqld stop

On Debian based systems:

sudo /etc/init.d/mysql stop

Then, remove mysql packages.

On RPM systems:

sudo yum remove mysql* mysql-server mysql-devel mysql-libs

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:

sudo apt-get remove mysql-server mysql-client

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,

vi /etc/yum.repos.d/mariadb.repo

Add the following lines:

# MariaDB 5.5 CentOS repository list - created 2014-12-09 08:11 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Update the repositories and install mariadb using the following commands:

sudo yum update 
sudo yum install MariaDB-server MariaDB-client

Start mysql service using the following command:

sudo /etc/init.d/mysqld start
sudo chkconfig mysqld on

Or,

sudo systemctl start mysqld
sudo systemctl enable mysqld

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

mysql_secure_installation

Install MariaDB On Ubuntu 14.04:

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://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://mirror.mephi.ru/mariadb/repo/5.5/ubuntu trusty main'

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

sudo apt-get update
sudo apt-get install mariadb-server

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:

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

On DEB systems:

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

5. Import Databases

Finally, let us import the old databases which we’ve created in the first step using 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, log in to mysql prompt using command:

mysql -u root -p

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

MariaDB [(none)]> show databases;

Sample output:

 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | testdb1            |
 | testdb2            |
 +--------------------+
 5 rows in set (0.00 sec)

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

That’s all. Start using MariaDb.

Disclaimer: Be careful while using these steps in production environment. Also, refer the MariaDB official documentations for further clarifications. If you don’t know what you’re doing, please ask a Database administrator help. Don’t lose your precious database backup.

Good luck!