Open In App

Simple Steps Migration From MySQL To MariaDB On Linux

Improve
Improve
Like Article
Like
Save
Share
Report

MySQL and MariaDB are two different Relational Database Management Systems (RDBMS). Superficially, both of these software look and act in the same way. But there are many technical differences between them. So, before diving into the steps for migration, let’s first understand what is MySQL and MariaDB as well as their key features. 

What is MySQL?

MySQL is a Relational Database Management System (RDBMS) developed by Oracle. It is based on Structured Query Language (SQL). It is one of the most popular software out there in the market for maintaining databases. 

Key Features of MySQL

  • Speed – MySQL runs very fast in optimal server hardware.
  • Ease of use – MySQL is a relatively simple-to-use software. The introduction of graphical interfaces such as MySQL workbench has reduced its complexity.
  • Cost – MySQL is available free of cost. But the enterprise version of the same is not free.
  • Portability – MySQL is a cross-platform software and it is tested to work on different compilers. 
  • Data type – MySQL supports fixed as well as variable length records. 
  • Security – MySQL allows host-based security. Password encryption is also available. 
  • Scalability – MySQL can support colossal databases. Some existing databases contain around 50 million records in them.
  • Connectivity – MySQL supports a wide range of connection protocols which makes networking, a smooth task.
  • Localization – Error messages in MySQL can be found in many different languages thus enhancing localization.
  • Availability of tools – MySQL has many client and utility programs which include MySQL administrator MySQL query browser, etc. 

What is MariaDB?

MariaDB is also an RDBMS that was developed based on MySQL, by Michael “Monty” Widenius. It is a drop-in replacement for MySQL. It is a fork of the MySQL project and is community-developed. It was originally forked to focus more on enterprise features rather than web/internet applications. Thus MariaDB is a successor of MySQL.  

Key Features of MariaDB

  • Open-source – MariaDB is meant to remain open-source under the GNU general public license.
  • Support for SQL – MariaDB has SQL support. Hence, if you know how to work with MySQL, then it is easy to learn MariaDB.
  • JSON and GIS – New versions of MariaDB has JavaScript Object Notation and Geographical Information System features. This ensures compatibility with various GIS tools.
  • Robust – MariaDB is fast, scalable, and has a robust design.
  • Support for big data – MariaDB is ideal for big data applications.
  • Languages – MariaDB is written in C, C++, Pearl, and bash.
  • Storage engines – Apart from standard storage engines, MariaDB comes with additional engines that include ColumnStore, MyRocks, Spider, Cassandra, and many others.
  • Security – Plugins like the unix_socket authentication plugin help in keeping the databases secure.      

 Why should we use MariaDB instead of MySQL?

  • MariaDB is a lightweight software which means it consumes less memory in the system.
  • MariaDB provides more transparency. It has a public bug tracker and email support system.
  • The size of  MariaDB’s community has been increasing rapidly and is getting larger than that of MySQL. A large community will help you in solving technical issues easily.
  • Many speed improvements have been accommodated by MariaDB such as the FLUSH SSL command which lets you reload an SSL certificate without restarting the system. 
  • There are fewer warnings and bugs in MariaDB when compared to MySQL.
  • MariaDB provides better testing facilities such as the removal of invalid tests.
  • Most important of all, MariaDB is truly open-source software that doesn’t have any closed-source modules like the ones that can be seen in MySQL Enterprise Edition. 

Thus, if you want to work faster with a lighter and open-source RDBMS, you might need to migrate to MariaDB.

Steps To Migrate

Process of Migration from MySQL to MariaDB

Process of Migration from MySQL to MariaDB

Since we are about to learn how to migrate from MySQL to MariaDB, we are assuming that the MySQL database software is already installed in the system. We are also explaining the steps using a Debian-based Linux distribution – Kali Linux. Some commands such as those involving package management may be different for different Linux distributions. However, the underlying logic will remain the same.
 

As we are using Linux, using a terminal is the best way to learn this migration process. You can use the hotkey ‘ctrl+alt+t’ or click the terminal icon in the GUI (Graphical User Interface) to open the terminal. Let us guide you through an example. Let us assume we have a MySQL installed system. 

Simple Steps Migration From MySQL To MariaDB On Linux

Step1 : Backup 

When you remove or purge a MySQL package from your system, all the associated databases will also be deleted. Thus it is necessary to take backups of necessary files and other data associated with our MySQL databases. So that we will be able to recover them after installing MariaDB. This step is crucial and must be done before uninstalling MySQL.

Backup Databases

To back up a database, we need to have an existing database. So let’s create one using the MySQL command interpreter. Enter the following commands in the terminal to log in to MySQL monitor. After the ‘-u’ option, enter the user name. In our case, it is the root. 

$ mysql -u root -p 

Once you hit enter, you will be prompted to enter the password to log in. After successful login, you can now enter commands in the MySQL command interpreter. Now type the below command to create a new database.

$ create database GFG_db;

Here, GFG_db is the name of the database. Now you can see a list of available databases using the following command.

$ show databases;

MySQL  Monitor:

Creating a new database in MySQL Monitor

Creating a new database in MySQL Monitor

enter the below command to quit MySQL monitor. 

\q;

Now let’s take a backup of all the databases associated with MySQL by entering the following command in the Linux terminal.

mysqldump –all-databases –user=root –password –master-data > GFG_backup_db.sql

Here we are dumping all databases to a file that we have created as ‘GFG_backup_db.sql’. ‘mysqldump’ is a database backup program. We are redirecting the output of this backup program to a backup file. We are dumping all tables in all databases by specifying the ‘–all-databases’ option. Provide a suitable user name after the ‘–user’ option. The ‘–master-data’ option writes the binary log file and positions the output. ‘–password’ is given to prompt for a password. 

Note: The backup file can be given any name of your choice. But it should have a file extension of .sql since it is a database. 

Troubleshooting Binlogging Error

Binlogging error while trying to use mysqldump

 

You might expect a binlogging error while attempting to use mysqldump. This is because binlogging is not active by default in the MySQL server. We can easily solve this problem by editing the configuration file /etc/mysql/my.cnf. Use a text editor to edit this file. We are going to use the vim editor here. The command is as follows.

$ vim /etc/mysql/my.cnf

Press ‘i’ to insert text into the file. Add the following lines of code to the file.

[mysqld]
log-bin=mysql-bin 

Now press ‘esc’ key. Then type ‘wq’ and hit ‘enter’ to save and quit.

Note: The procedure for editing a file varies for different text editors.

Editing my.cnf configuration file

Editing my.cnf configuration file

Restart the MySQL service for the changes made to take effect. This can be done using the following command.

$ systemctl restart mysql.service

Enter the mysqldump command again. This time, you won’t get any errors. The dump will be successful. The backup file will get saved in the present working directory.

Backup Configuration File

Backing up only the databases is not sufficient to completely recover all MySQL data. We need to back up the necessary configuration files too to perform a safe migration. For this purpose, let us copy the configuration that we have recently edited to any location of your choice. The command for copying the file is provided below.

$ cp /etc/mysql/my.cnf my.cnf.copy
Backed up configuration file and databases

Backed up the configuration files and databases

Now we are ready to move to the next step.

Step 2: Uninstall MySQL

We can’t just uninstall software straight away that has a process running in the background. We need to stop the MySQL service for safe uninstallation. Let’s do this by typing the following command.

$ systemctl stop mysql.service

It is now safe to remove the MySQL package from our system. Type the following command to accomplish the task.

$ apt-get remove mysql-server

Now we have successfully removed MySQL software from our Linux system.

Step 3: Install MariaDB

It is time to install our new RDBMS which is going to be MariaDB. Before installing a package in Linux, it is advised to update the repository to fetch the latest versions of packages. Let’s update the repository using the below command.

$ apt-get update

Now type the following command to install MariaDB.

$ apt-get install mariadb-server

select ‘yes’ to the notification that arises. Installation of MariaDB in Linux is that simple!

Step 4: Restore

The next step is to restore all the backed-up data and files so that we could use them in our newly installed MariaDB server.

Restore Configuration File

Restoring configuration files is essential for the proper functioning of MariaDB. The details of the configuration files will be used by the engine of the MariaDB server. It is just like the settings of an application. To restore the configuration file, enter the following command.

$ cp my.cnf.copy /etc/mysql/my.cnf

It is indeed the reverse of what we have done while backing up the configuration file. You might have noticed it. 

Import Databases

After restoring the configuration files, it’s time to import our backed-up databases to the MariaDB server. To do so, we need to log in to MariaDB. To log in for the first time, we need to use the below command.

$ mysql_secure_installation

This will prompt you to set a root password for your server. Press ‘y’ for all other prompts. 

Secure installation of MariaDB server

Secure installation of MariaDB server

Note: We have already set a root password in the above screenshot. If this is not the case, you will be prompted to set a new root password.

To import the backed-up databases, let’s use the following command.

$ mysql -u root -p < backup_db.sql

To check if the migration has been done successfully, let’s log in to the MariaDB command interpreter or shell. For this, we are going to use the same command again.

$ mysql -u root -p 

Type the password that you entered during MySQL secure installation. Use the already-known command to view the list of available databases.

$ show databases;

MariaDB Monitor:

Viewing the imported database- GFG_db in MariaDB monitor.

Viewing the imported database- GFG_db in MariaDB monitor.

Here you can see GFG_db which we have imported from MySQL.

Hence, we have successfully migrated from MySQL to MariaDB without any data loss.

Note: Use ‘sudo’ whenever you are denied from executing a command that we have mentioned.

Compatibility Issues

While migrating between database management systems, it is important to check the versions before starting the migration process. Because not all the versions of MySQL are compatible with every version of MariaDB. There exist some restrictions. We have provided a table of compatibility for you to refer to before migrating. 

Table To Check Version Compatibility

MySQL MariaDB
5.1 5.1, 5.2, 5.3
5.5 5.5
5.6 10.0, 10.1
5.7 10.2, 10.3, 10.4, 10.5
8.0

Conclusion

It is clear that one may need to migrate from MySQL to MariaDB for various reasons ranging from seeking community support to going, lightweight. Anyways, the migration process is that simple. All you have to do is to take a backup of your data before removing MySQL and restore the data after installing MariaDB. Wishing you good luck for a safe migration!  



Last Updated : 23 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads