Simple Steps for migrating from MySQL to MariaDB on Linux

Simple Steps for migrating from MySQL to MariaDB on Linux

Hello everyone! This is a tutorial on how to migrate from MySQL to MariaDB on a server or PC. You may ask why we want to migrate database management from MySQL to MariaDB. Let's look at why we did this.

 

Why use MariaDB instead of MySQL?

MariaDB is a branch of MySQL Community Development and an enhanced alternative. It was developed by a foundation led by MySQL developers and used exactly the same as MySQL. Since Oracle bought MySQL, it is no longer free to open source, but MariaDB is still free to open source. Some top-level websites such as Google, Wikipedia, LinkedIn, and Mozilla have been migrated to MariaDB. Where are its advantages:

• Backward compatible with MySQL
• Always open source
• Maintained by MySQL creators
• More advanced functions
• More storage engines
• Large websites have switched to MariaDB

Now, let's migrate to MariaDB!

Let's create a sample database named linoxidedb for testing.

Run the following command to log on to MySQL with the root account:

1. $ mysql -u root -p

After entering the mysql root User Password, you will enter the mysql Command Line

Create a test database:

On the mysql command line, enter the following command to create a test database.

1. mysql> create database linoxidedb;

To view available databases, enter the following command:

1. mysql> show databases;

As you can see, we have a total of five databases in the new linoxidedb.

1. mysql> quit

Now, we will migrate the newly created database from MySQL to MariaDB.

Note: you do not need to refer to this tutorial for the Fedora-based linux releases such as CentOS, because they will automatically replace MySQL when installing MariaDB and do not need to back up existing databases, you only need to update mysql to get mariadb.

 

1. Back up existing databases

The first important step is to back up the existing database. Enter the following command in the terminal (not the MySQL Command Line) to complete the backup.

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

Wow! We encountered some trouble. Don't worry. We can do it.

1. $ 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 the my. cnf file:

1. $ sudo nano /etc/mysql/my.cnf

Add the following parameters in the [mysqld] section.

Log-bin = mysql-bin

Okay. After saving and closing the file, we need to restart the mysql service. Run the following command to restart:

1. $ sudo /etc/init.d/mysql restart

Now, run the mysqldump command again to back up all the databases.

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

Dumping databases

The preceding command backs up all databases and stores them in the backupdatabase. SQL file in the current directory.

 

2. uninstall MySQL

First, we need to move the my. cnf file to a safe place.

Note: The my. cnf file will not be automatically deleted when you uninstall the MySQL package. This is just in case. When MariaDB is installed, it will ask if we want to keep the existing my. cnf file or use the self-contained version in the package (that is, the new my. cnf file ).

Run the following command in shell or terminal to back up the my. cnf file:

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

Run the following command to terminate the mysql service:

1. $ sudo /etc/init.d/mysql stop

Then remove the mysql package:

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

 

3. Install MariaDB

Here is the command to install MariaDB in Ubuntu:

1. $ sudo apt-get install software-properties-common
2. $ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
3. # sudo add-apt-repository 'deb http://mirror.mephi.ru/mariadb/repo/5.5/ubuntu trusty main'

After the key value is imported and the repository is added, run the following command to install MariaDB:

1. $ sudo apt-get update
2. $ sudo apt-get install mariadb-server

We should not forget whether to use the existing my. cnf file or the self-contained version in the package when installing MariaDB. You can use my. cnf or self-contained in the package. Even if you want to directly use the new my. cnf file, you can restore the previous backup content later (don't forget we have copied it to a safe place ). Therefore, the default option "N" is selected ". To install other versions, see the official MariaDB repository.

 

4. Restore the configuration file

To restore the content in my. cnf. bak to my. cnf, enter the following command in the terminal. Because the my. cnf. bak file is in the current directory, you only need to simply execute the following command:

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

 

5. Import the database

Finally, let's import the database we created earlier! Run the command to complete the import.

1. $ mysql -u root -p < backupdatabase.sql

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

Let's log on to the mysql command line and check whether the database has been imported:

1. $ mysql -u root -p

To check whether the database has been migrated to MariaDB, enter "show databases;" in the MariaDB command line without entering (""), as shown below:

1. mariaDB> show databases;

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

 

Summary

Finally, we have successfully migrated from MySQL to the MariaDB database management system. MariaDB is better than MySQL. Although MySQL is faster than MySQL in terms of performance, MariaDB has the advantage of its additional features and supported licenses. This ensures that it is both open-source and permanently open-source. In contrast, MySQL has many additional plug-ins, some cannot use code freely, some are not open-source development processes, and some will not be open-source in the near future. If you have any questions, comments, or feedback, do not hesitate to leave your comments directly in the comment area. Thank you for watching this tutorial. I hope you like MariaDB.

Install LAMP (Apache with MariaDB and PHP) in CentOS/RHEL/Scientific Linux 6)

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