How to migrate MySQL to MariaDB, mysqlmariadb

How to migrate MySQL to MariaDB, mysqlmariadb

Since Oracle's acquisition of MySQL, many MySQL developers and users have abandoned it because Oracle's Development and Maintenance of MySQL tends to be closed-doors. Driven by the community, more people are moved to another branch of MySQL, MariaDB. Under the guidance of the original MySQL developers, MariaDB's development follows the open-source concept, and ensure that its binary format is compatible with MySQL. Many Linux distributions, such as Red Hat, support MariaDB as a direct replacement for MySQL.

If you want to migrate the MySQL database to MariaDB, you are very lucky. Due to their binary compatibility, the MySQL-to-MariaDB migration process is very simple. If you follow the steps below, it is painless to migrate MySQL to MariaDB.

Prepare MySQL Databases and tables

For demonstration purpose, we will create a MySQL database and table for test in the database before migration. Skip this step if you already have a database to be migrated to MariaDB in MySQL. Otherwise, follow these steps.

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

$ mysql -u root -p

2. Create a database and table.

mysql> create database test01;mysql> use test01;mysql> create table pet(name varchar(30), owner varchar(30), species varchar(20), sex char(1));

3. Add some data to the table.

mysql> insert into pet values('brandon','Jack','puddle','m'),('dixie','Danny','chihuahua','f');

4. log out of the MySQL database.

Back up a MySQL database

1. Back up the existing MySQL database and use the mysqldump command below to export the existing database to the file. Before running this command, make sure that 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. Run either of the following three commands:

$ sudo service mysql stop  //RHEL6$ sudo systemctl stop mysql //RHEL7$ sudo /etc/init.d/mysql stop //RHEL6

2. Run the following command to remove MySQL and configuration files.
On RPM-based systems (such as CentOS, Fedora, or RHEL ):

$ sudo yum remove mysql* mysql-server mysql-devel mysql-libs$ sudo rm -rf /var/lib/mysql

On Debian-based systems (such as 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

Install MariaDB

On CentOS/RHEL 7 and Ubuntu (14.04 or later), the latest MariaDB is included in its official source. On Fedora, MariaDB has replaced MySQL since version 19. If you are using an old version or an LTS type such as Ubuntu 13.10 or earlier, you can still install it by adding its official repository, on the MariaDB official website, an online tool is provided to help you add the official repository of MariaDB based on your Linux release. This tool provides the official MariaDB repository 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: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

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 // install MariaDB

2. After all necessary software packages are installed, you may be asked to create a new password for the root user of MariaDB. After setting the root password, do not forget to restore the backup my. cnf file.

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

3. Start the MariaDB service now. Choose one of the following three commands you can run.

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

Migrate Mysql to MariaDB

Import the previously exported Mysql database to the MariaDB server.

$ mysql -u root -p < backupdb.sql

Enter the root password of MariaDB. The database import process starts. After the import process is complete, return to the command prompt.

To check whether the import process is complete, log on to the MariaDB server and check some samples.

$ Mysql-u root-p ................................. ..... // The following is the SQL command MariaDB [(none)]> show databases; MariaDB [(none)]> use test01; MariaDB [test01]> select * from pet;

Conclusion

As you can see in this tutorial, MySQL-to-MariaDB migration is not difficult. You should know that MariaDB has many new functions compared with MySQL. In terms of configuration, I only used my. cnf as the configuration file of MariaDB in my tests. There was no problem during the import process. For the configuration file, I suggest you carefully read the MariaDB configuration option file before migration, especially if you are using specific MySQL configurations.

If you are running complex configurations for a large number of tables, including clusters or master-slave databases, take a look at more detailed instructions from the Mozilla IT and Operations teams, or MariaDB official documentation.

Troubleshooting
The following error occurs when you run the mysqldump command to back up the database.

Mysqldump: Error: Binlogging on server not active

By using "-- master-data", you can include binary log information in the exported output, which is useful for database replication and recovery. However, binary logs are not enabled on the MySQL server. To solve this error, modify the my. cnf file and add the following options in the [mysqld] section. (LCTT: in fact, if you have not enabled binary logs, cancel "-- master-data .)

log-bin=mysql-bin

Save the my. cnf file and restart the MySQL service.

This article Reprinted from: http://www.linuxprobe.com/how-migrate-mysql-to-mariadb/
Free to provide the latest Linux technology tutorial books, for open source technology enthusiasts to do more and better: http://www.linuxprobe.com/