How to migrate MySQL to MariaDB in Linux

If you want to migrate the MySQL database to MariaDB, this article is what you expect. Fortunately, due to their binary compatibility, MySQL-to-Maria

If you want to migrate the MySQL database to MariaDB, this article is what you expect. Fortunately, due to their binary compatibility, MySQL-to-Maria

Since the acquisition of MySQL by Oracle, many MySQL developers and users have abandoned MySQL because Oracle tends to be closed-doors for MySQL development and maintenance. Driven by the community, more people are moved to another branch named MariaDB in MySQL. Under the guidance of the original MySQL developers, MariaDB follows the open-source concept and ensures its binary format is compatible with MySQL. Linux releases, such as the Red Hat family (Fedora, CentOS, RHEL), Ubuntu, Mint, openSUSE, and Debian, are available and support MariaDB as a direct replacement product for MySQL.

If you want to migrate the MySQL database to MariaDB, this article is what you expect. Fortunately, 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.

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

Create a database and table.

Add some data to the table.

Log out of the MySQL database.

Back up a 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 binary logs are enabled on your MySQL server. If you do not know how to enable binary logs, see the Tutorial at the end.

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

Uninstall MySQL

First, stop the MySQL service.

Or:

Or:

Next, run the following command to remove MySQL and the configuration file.

On RPM-based systems (such as CentOS, Fedora, or RHEL ):

On Debian-based systems (such as Debian, Ubuntu, or Mint ):

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 MariaDB by adding its official repository.

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.

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

Ubuntu 14.04

CentOS 7

Create a custom yum repository file for MariaDB.

After installing all necessary software packages, 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.

Start the MariaDB service.

Or:

Or:

Import MySQL database

Finally, import the exported database to the MariaDB server.

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.

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 a complex configuration of a large number of tables, including clusters or master-slave databases, take a look at the more detailed guide from the Mozilla IT and Operations teams, or the official MariaDB documentation.

Troubleshooting

1. the following error occurs when running the mysqldump command to back up the database.

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 .)

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

Or:

Or:

Linux Tutorial: How to check the MariaDB server version

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

How to migrate MySQL 5.5 database to MariaDB 10 on Ubuntu

Install MariaDB on the Ubuntu 14.04 (Trusty) Server

MariaDB details: click here
MariaDB's: click here

Via:

Author: Kristophorus Hadiono Translator: strugglingyouth Proofreader: wxy

This article was originally translated by LCTT and launched with the Linux honor in China

This article permanently updates the link address: