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

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.

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

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. 

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;

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.