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