How to migrate MySQL to MariaDB

Since Oracle acquired MySQL, many MySQL developers and users have given up on MySQL because Oracle has tended to be more closed-door in its development and maintenance. Driven by the community, another branch called MariaDB, led by more people to MySQL, was developed by the original MySQL developer to follow the idea of open source and to ensure that its binary format was compatible with MySQL. Many Linux distributions, such as Red Hat, support MariaDB as a direct replacement for MySQL.

If you want to migrate the databases in MySQL to MariaDB, then 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.

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

$ mysql-u Root-p

2. Create a database and a table.

Mysql> CREATE DATABASE test01;mysql> use test01;mysql> CREATE TABLE PET (name varchar (), owner varchar (+), spec IES varchar (+), sex char (1));

3. Add some data to the table.

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

4. Exit the MySQL database.

Back up the MySQL database

1. Back up the existing MySQL database and export the existing database to the file using the mysqldump command below. 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

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

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

Uninstall MySQL

1. First, stop the MySQL service, choose one of the following three commands to execute

$ sudo service mysql stop  //rhel6$ sudo systemctl stop mysql//rhel7$ sudo/etc/init.d/mysql stop//rhel6

2. Use the following command to remove 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 deluse R mysql$ Sudo rm-rf/var/lib/mysql

install 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 it by adding its official repository, and on the MARIADB website you will find an online tool to help you add MARIADB's official repository based on your Linux distribution. This tool provides MariaDB's official warehouse for OpenSUSE, Arch Linux, Mageia, Fedora, CentOS, RedHat, Mint, Ubuntu, and Debian.

1. In the following example, we use the Ubuntu 14.04 release and CentOS 7 to configure the MariaDB library.

Ubuntu 14.04

$ sudo apt-get install software-properties-common$ sudo apt-key adv--recv-keys--keyserver hkp://keyserver.ubuntu.com : 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

CentOS7

$ sudo vi/etc/yum.repos.d/mariadb.repo  //Create a custom yum source ...... ..... ..... .................  The following is the file content [mariadb]name = Mariadbbaseurl = http://yum.mariadb.org/5.5/centos7-amd64gpgkey=https://yum.mariadb.org/ rpm-gpg-key-mariadbgpgcheck=1.......................................$ sudo yum install Mariadb-server mariadb-client//installation MariaDB

2. 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/

3. Now start the MariaDB service, choose one of the following three commands you can run.

$ sudo service mariadb start$ sudo systemctl start mariadb$ sudo/etc/init.d/mariadb start

migrating MySQL to Mariadb

We imported the previously exported MySQL database to 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......................................//The following is the SQL command MARIADB [(none)]> show databases; MariaDB [(None)]> use test01; MariaDB [test01]> select * from pet;

Conclusion

If you're running a complex configuration of a massive list of tables, including clustered or master-slave databases, take a look at the   in the Mozilla IT and Operations team for a more detailed guide to Span class= "Apple-converted-space" > , or  mariadb official documentation.

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

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.

Free to provide the latest Linux technology tutorials Books, for open-source technology enthusiasts to do more and better: http://www.linuxprobe.com/

How to migrate MySQL to MariaDB