[Graphic tutorial] for migrating MySQL to MariaDB on Linux]

The reason for mariadb to replace MySQL

MARIADB is used exactly as MySQL. Since Oracle bought MySQL, it is no longer free to open source, but MARIADB is still free to open source. Some of the top sites, such as Google, Wikipedia, LinkedIn, and Mozilla, have migrated to MARIADB. Where is the advantage:

Backward compatibility with MySQL

Open Source Forever

Maintained by the MySQL creator

More sophisticated features

More storage engines

Large websites have turned to mariadb

Now, let's move to MARIADB!

Let's create a sample database called Linoxidedb for testing.

Use the following command to login to MySQL with the root account:

$ mysql-u Root-p

After entering the MySQL root user password, you will enter the MySQL command line

To create a test database:

Enter the following command at the MySQL command line to create the test database.

mysql> CREATE DATABASE Linoxidedb;

To view the available databases, enter the following command:

mysql> show databases;

As you can see, we have a total of 5 databases in the newly built linoxidedb.

Mysql> quit

Now, we will migrate the database we just created from MySQL to mariadb.

Note: A fedora-based Linux distribution using CentOS does not need to refer to this tutorial because they automatically replace MySQL when installing MARIADB, without backing up the existing database, you only need to update MySQL to get mariadb.

1. Back up the existing database

Our first important step is to back up the existing database. We complete the backup by entering the following command in the terminal (not the MySQL command line).

$ mysqldump--all-databases--user=root--password--master-data > Backupdatabase.sql

Wow, we're in a bit of trouble. Don't worry, we can handle it.

$ mysqldump:Error:Binlogging on server not active

Mysqldump Error

To fix this error, we need to make some minor changes to the My.cnf file.

Edit My.cnf File:

$ sudo nano/etc/mysql/my.cnf

Add the following parameters to the [mysqld] section.

Log-bin=mysql-bin

Well, after we save and close the file, we need to restart the MySQL service. Run the following command to reboot:

$ sudo/etc/init.d/mysql Restart

Now rerun the mysqldump command to back up all the databases.

$ mysqldump--all-databases--user=root--password--master-data > Backupdatabase.sql

Dumping databases

The above command backs up all the databases and stores them in the Backupdatabase.sql file in the current directory.

2. Uninstall MySQL

First of all, we have to move the my.cnf file to a safe place.

Note: When you uninstall the MySQL package will not automatically delete the my.cnf file, we do this just in case. When MARIADB is installed, it asks us whether to keep the existing my.cnf file or to use the version that comes with the package (that is, the new my.cnf file).

Type the following command in the shell or terminal to back up the My.cnf file:

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

To terminate the MySQL service by running the command:

$ sudo/etc/init.d/mysql Stop

Then remove the MySQL package:

$ sudo apt-get remove mysql-server mysql-client

3. Install MARIADB

This is the command to install MARIADB in the Ubuntu system:

$ 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 '

After the key value is imported and the warehouse is added, you can install the MARIADB with the following command:

$ sudo apt-get update

$ sudo apt-get install Mariadb-server

We should not forget that when MARIADB is installed, it asks whether you are using an existing MY.CNF file or a version that comes with it in the package. You can use the previous my.cnf or you can bring it in your bag. Even if you want to use the new my.cnf file directly, you can still later restore the previous backup content (remember that we have copied it to a safe place). So, we chose the default option "N" directly. If you need to install additional versions, refer to the MARIADB official warehouse.

4. Restore Configuration file

To restore the contents of the My.cnf.bak to MY.CNF, enter the following command in the terminal. Because the My.cnf.bak file is in the current directory, we simply execute the following command:

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

5. Import Database

Finally, let's import the database we created before! You can complete the import by running the command.

$ mysql-u Root-p < Backupdatabase.sql

In this way, we have successfully imported the previous database.

Here, let's log in to the MySQL command line and check to see if the database is actually imported:

$ mysql-u Root-p

To check whether the database is migrated to MARIADB, enter "show Databases" on the MARIADB command line. Do not enter (""), as follows:

mariadb> show databases;

As you can see, linoxidedb and all the databases have been successfully migrated.

Summarize

We have successfully migrated from MySQL to the MARIADB database management system. MARIADB is better than MySQL, although MySQL is still faster in terms of performance, the advantage of MARIADB is its additional features and supported licenses. This ensures that it is free to open source (FOSS) and is permanently free of open source, in contrast to MySQL has many additional plug-ins, some are not free to use code, some open development process, some in the near future will not be free open source.