Since the Oracle's acquisition of MySQL, a lot of MySQL developers and users moved away from MySQL due to Oracle's more closed-door stance on MySQL development and maintenance. The community-driven outcome of such movement is a fork of MySQL, called MariaDB. Led by original MySQL developers, the development of MariaDB follows the open-source philosophy and makes sure of its binary compatibility with MySQL. The Linux distributions such as Red Hat families (Fedora, CentOS, RHEL), Ubuntu and Mint, openSUSE and Debian already started to use and support MariaDB as a drop-in replacement of MySQL.
If you want to migrate your database from MySQL to MariaDB, this article is what you are looking for. Fortunately, due to their binary compatibility, MySQL-to-MariaDB migration process is pretty much straightforward. If you follow the steps below, the migration from MySQL to MariaDB will most likely be painless.
Prepare a MySQL Database and a Table
For demonstration purpose, let's create a test MySQL database and one table in the database before doing the migration. Skip this step if you already have existing MySQL database(s) to migrate to MariaDB. Otherwise proceed as follows.
Log in into MySQL from a terminal by typing your MySQL root user password.
Create a database and a table.
mysql> use test01;
mysql> create table pet(name varchar(30), owner varchar(30), species varchar(20), sex char(1));
Add some records to the table.
Then quit the MySQL database.
Backup the MySQL Database
The next step is to back up existing MySQL database(s). Use the following mysqldump command to export all existing databases to a file. Before running this command, make sure that binary logging is enabled in your MySQL server. If you don't know how to enable binary logging, see the instructions toward the end of the tutorial.
Now create a backup of my.cnf file somewhere in your system before uninstalling MySQL. This step is optional.
Uninstall MySQL Package
First, you need to stop the MySQL service.
Then go ahead and remove MySQL packages and configurations as follows.
On RPM based system (e.g., CentOS, Fedora or RHEL):
$ sudo rm -rf /var/lib/mysql
On Debian based system (e.g., Debian, Ubuntu or Mint):
$ sudo apt-get autoremove
$ sudo apt-get autoclean
$ sudo deluser mysql
$ sudo rm -rf /var/lib/mysql
Install MariaDB Package
The latest CentOS/RHEL 7 and Ubuntu (14.04 or later) contain MariaDB packages in their official repositories. In Fedora, MariaDB has become a replacement of MySQL since version 19. If you are using an old version or LTS type like Ubuntu 13.10 or earlier, you still can install MariaDB by adding its official repository.
MariaDB website provide an online tool to help you add MariaDB's official repository according to your Linux distribution. This tool provides steps to add the MariaDB repository for openSUSE, Arch Linux, Mageia, Fedora, CentOS, RedHat, Mint, Ubuntu, and Debian.
As an example, let's use the Ubuntu 14.04 distribution and CentOS 7 to configure the MariaDB repository.
$ 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'
$ sudo apt-get update
$ sudo apt-get install mariadb-server
Create a custom yum repository file for MariaDB as follows.
[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/5.5/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
After all necessary packages are installed, you may be asked to type a new password for root user account. After setting the root password, don't forget to recover my.cnf backup file.
Now start MariaDB service as follows.
Importing MySQL Database(s)
Finally, we have to import the previously exported database(s) back to MariaDB server as follows.
Enter your MariaDB's root password, and the database import process will start. When the import process is finished, it will return to a command prompt.
To check whether or not the import process is completed successfully, log in into MariaDB server and perform some sample queries.
MariaDB [(none)]> use test01;
MariaDB [test01]> select * from pet;
As you can see in this tutorial, MySQL-to-MariaDB migration is not difficult. MariaDB has a lot of new features than MySQL, that you should know about. As far as configuration is concerned, in my test case, I simply used my old MySQL configuration file (my.cnf) as a MariaDB configuration file, and the import process was completed fine without any issue. My suggestion for the configuration is that you read the documentation on MariaDB configuration options carefully before the migration, especially if you are using specific MySQL configurations.
If you are running more complex setup with tons of tables and databases including clustering or master-slave replication, take a look at the more detailed guide by the Mozilla IT and Operations team, or the official MariaDB documentation.
1. While running mysqldump command to back up databases, you are getting the following error.
mysqldump: Error: Binlogging on server not active
By using "--master-data", you are trying to include binary log information in the exported output, which is useful for database replication and recovery. However, binary logging is not enabled in MySQL server. To fix this error, modify your my.cnf file, and add the following option under [mysqld] section.
Save my.cnf file, and restart the MySQL service:
Subscribe to Xmodulo
Do you want to receive Linux FAQs, detailed tutorials and tips published at Xmodulo? Enter your email address below, and we will deliver our Linux posts straight to your email box, for free. Delivery powered by Google Feedburner.
Did you find this tutorial helpful? Then please be generous and support Xmodulo!