Shulou(Shulou.com)06/01 Report--

This article introduces the knowledge about "how to migrate MySQL to MariaDB in Linux". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

If you want to migrate databases from MySQL to MariaDB, this article is what you expect. Fortunately, due to their binary compatibility, the process of migrating MySQL to MariaDB will be very simple if you follow the steps below.

Preparing MySQL databases and tables

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

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

$ mysql -u root -p

Create a database and tables.

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

Add some data to the table.

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

Exit MySQL database.

Backup 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 binary logging is enabled on your MySQL server. If you don't know how to enable binary logging, see the tutorial instructions at the end.

$ 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

Uninstalling 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 and remove MySQL and configuration files using the following command.

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 (version 14.04 or later), MariaDB *** is already included in its official sources. MariaDB has replaced MySQL on Fedora since version 19. If you are using an older version or LTS type such as Ubuntu 13.10 or earlier, you can still install MariaDB by adding its official repository.

The MariaDB website provides an online tool to help you add MariaDB's official repository depending on your Linux distribution. This tool provides the official repository for MariaDB for openSUSE, Arch Linux, Mageia, Fedora, CentOS, RedHat, Mint, Ubuntu and Debian.

In the example below, we configured MariaDB library using Ubuntu 14.04 release and CentOS 7.

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 creates a custom yum repository file for MariaDB.

$ sudo vi /etc/yum.repos.d/MariaDB.repo[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

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

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

Now start MariaDB service.

$ sudo service mariadb start

Or:

$ sudo systemctl start mariadb

Or:

$ sudo /etc/init.d/mariadb start

Import MySQL database

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

$ mysql -u root -p

show databases;MariaDB [(none)]> use test01;MariaDB [test01]> select * from pet;

conclusion

As you can see in this tutorial, migrating MySQL to MariaDB is not difficult. You should know that MariaDB has many new features compared to MySQL. As far as configuration is concerned, in my test case, I simply used my old MySQL configuration file (my.cnf) as MariaDB's configuration file, and the import process went on without any problems at all. For configuration files, I recommend that you read the MariaDB configuration options file carefully before migrating, especially if you are using MySQL specific configurations.

If you're running a complex configuration with a large number of tables, including clustered or master-slave replicated databases, take a look at the Mozilla IT and Operations team's more detailed guide, or the official MariaDB documentation.

troubleshooting

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

$ mysqldump --all-databases --user=root --password --master-data > backupdb.sqlmysqldump: 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 logging is not enabled on MySQL servers. To fix this error, modify the my.cnf file and add the following option in the [mysqld] section. (In fact, if you don't have binary logging enabled, just cancel "--master-data.")

log-bin=mysql-bin

Save my.cnf file and restart MySQL service:

$ sudo service mysql restart

Or:

$ sudo systemctl restart mysql

Or:

$ sudo /etc/init.d/mysql restart"How to migrate MySQL to MariaDB in Linux" is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.