How to migrate MySQL to MariaDB on Linux

Since Oracle acquired MySQL, many MySQL developers and users have given up on MySQL because of the more open-door stance of Oracle's development and maintenance of MySQL. Driven by the community, more people are moved to another branch of MySQL called MariaDB. Led by the original MySQL developer, MariaDB's development follows the idea of open source and ensures that its binary format is compatible with MySQL. Linux distributions such as the Red Hat family (Fedora,centos,rhel), Ubuntu and Mint,opensuse and Debian are already in use and support MariaDB as a direct replacement for MySQL.

If you want to migrate a database in MySQL to MariaDB, this article is what you expect. Fortunately, because of their binary compatibility, the MYSQL-TO-MARIADB migration process is very simple. If you follow the steps below, migrating MySQL to MariaDB will be painless.

Preparing MySQL Database and tables

For demonstration purposes, we created a test MySQL database and table in the database before doing the migration. If you already have a database in MySQL that you want to migrate to MariaDB, skip this step. Otherwise, follow these steps.

Enter the root password on the terminal to log in to MySQL.

$ mysql-u Root-p

Create a database and a table.

mysql> CREATE DATABASE test01;

mysql> use test01;

Mysql> CREATE TABLE Pet (name varchar (), owner varchar (+), species varchar (+), sex char (1));

Add some data to the table.

Mysql> INSERT into pet values (' Brandon ', ' Jack ', ' Puddle ', ' m '), (' Dixie ', ' Danny ', ' Chihuahua ', ' f ');

Exit the MySQL database.

Back up the MySQL database

The next step is to back up the existing MySQL database. Use the following mysqldump command to export an existing database to a file. Before running this command, make sure that the binary logs are enabled on your MySQL server.

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

Now, back up the my.cnf file on your system before uninstalling MySQL. This step is optional.

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

Uninstall MySQL

First, stop the MySQL service.

$ sudo service MySQL stop

Or:

$ sudo systemctl stop MySQL

Or:

$ sudo/etc/init.d/mysql Stop

Then proceed to the next step by using the following command to remove the MySQL and configuration files.

On an RPM-based system (for example, CentOS, Fedora, or RHEL):

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

$ sudo rm-rf/var/lib/mysql

On a Debian-based system (for example, Debian, Ubuntu, or Mint):

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

$ sudo apt-get autoremove

$ sudo apt-get AutoClean

$ sudo deluser mysql

$ sudo rm-rf/var/lib/mysql

Installing MariaDB

On Centos/rhel 7 and Ubuntu (14.04 or later), the latest MariaDB has been included in its official source. On Fedora, MariaDB has replaced MySQL since version 19. If you are using an older version or an LTS type such as Ubuntu 13.10 or earlier, you can still install MariaDB by adding its official warehouse.

In the following example, we use the Ubuntu 14.04 release and the CentOS 7 configuration MariaDB Library.

Ubuntu 14.04

$ 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 '

$ sudo apt-get update

$ sudo apt-get install Mariadb-server

CentOS 7

The following is a custom Yum repository file created for MariaDB.

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

[MARIADB]

Name = MariaDB

BaseURL = Http://yum.mariadb.org/5.5/centos7-amd64

Gpgkey=https://yum.mariadb.org/rpm-gpg-key-mariadb

Gpgcheck=1

$ sudo yum install mariadb-server mariadb-client

Once you have installed all the necessary packages, you may be asked to create a new password for the root user of MariaDB. After setting the root password, don't forget to restore the backup my.cnf file.

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

Start the MariaDB service now.

$ sudo service mariadb start

Or:

$ sudo systemctl start mariadb

Or:

$ sudo/etc/init.d/mariadb Start

Import a MySQL database

Finally, we import the previously exported database into the MariaDB server.

$ mysql-u Root-p < Backupdb.sql

Enter your MariaDB root password and the database import process will begin. When the import process is complete, it is returned to the command prompt.

To check that the import process is completely successful, log on to the MariaDB server and look at some samples to check.

$ mysql-u Root-p

MariaDB [(None)]> show databases;

MariaDB [(None)]> use test01;

MariaDB [test01]> select * from pet;

Conclusion

As you can see in this tutorial, the migration of mysql-to-mariadb is not difficult. You should know that MariaDB has a lot of new features compared to MySQL. As for the configuration aspect, in my test case, I just used my old MySQL configuration file (my.cnf) as the MariaDB profile, and the import process was completely free of any problems. For configuration files, I recommend that you carefully read the MariaDB configuration options file before migrating, especially if you are using MySQL's specific configuration.

Trouble shooting

The following error occurred while running the mysqldump command to back up the database.

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

Mysqldump:Error:Binlogging on server not active

Using "--master-data", you can include binary log information in the exported output, which is useful for database replication and recovery. However, the binary logs are not enabled on the MySQL server. To resolve this error, modify the My.cnf file and add the following options in the [Mysqld] section. LCTT: In fact, if you do not enable binary logging, then Cancel "--master-data". ）

Log-bin=mysql-bin

Save the My.cnf file and restart the MySQL service:

$ sudo service MySQL restart

Or:

$ sudo systemctl restart MySQL

Or:

$ sudo/etc/init.d/mysql Restart

brother Lian it education original linux OPS engineer / In detail linux tutorials, more information on the official website customer service: http://www.lampbrother.net/linux/

or hooking up with Q2430675018.

Welcome to the Linux Communication Group 478068715

How to migrate MySQL to MariaDB on Linux