Tutorial Backup and Restore a MariaDB Database

MariaDB is one of the most popular open-source relational databases. This database was created by the original MySQL developers and is guaranteed to remain Open Source. MariaDB is a DBMS or Database Management System of MySQL branches and uses the MariaDB Storage Engine. In this article, we are going to teach you How to Backup and Restore a MariaDB Database. You can visit the packages available in Eldernode if you wish to purchase a VPS server.

How to Backup and Restore a MariaDB Database

What is MariaDB Database?

MariaDB is one of the most popular database servers in the world. Important users of this service include Google, Wikipedia, and Facebook. As mentioned, MariaDB has been developed as an open-source and as relational database that provides a kind of SQL interface for accessing data. The latest versions of MariaDB also include GIS and JSON features.

This database actually converts data into structured information over a wide range of applications, from websites to banking applications. MariaDB is known as a viable alternative to MySql‌. Because this service is very fast and scalable and powerful. It can also use for a wide range of topics by providing a powerful ecosystem of plugins, storage engines, and other tools.

MariaDB features include the following:

1. Popular customers of this database

Popular and large customers can give the most credit to a brand. MariaDB has well-known clients such as Google, Wikipedia, and WordPress, which have increased the credibility of this database.

2. Storage engines

Storage Engines have been well developed in MariaDB and can provide new features to users. Among the storage, engines are XtraDB and Aria.

3. Database Security

Although MariaDB is known as a subset of the MYSQL database, it seems that the principles of database security have received more attention in its development. So do not worry about security issues when using MariaDB.

Backup a MariaDB Database

Backup of the MariaDB database can be done in two ways. Logical backup and physical backup. In the rest of this article, we’re going to teach you both ways to backup MariaDB. Please join us.

How to make a Logical Backup of MariaDB

In this section, we are going to teach you how to make a logical backup of MariaDB. To do this, you must follow the steps below in order. First, logical backup can be done using the following command:

mysqldump -u root -p inventory > /backup/inventory.dump

The parameters of the above command are explaining as follows:

root: This parameter displays the username to connect to MariaDB for backup.

-p: The password request for this user is specified using this parameter.

inventory: This parameter shows the database selected for backup.

/backup/inventory.dump: Specifies the path of the backup file.

One thing to keep in mind is that to backup all databases logically, you must use the -all-databases option, as in the following command:

mysqldump -u root -p --all-databases > /backup/mariadb.dump

Note: It is interesting to know that the output of a logical backup is a series of SQL statements.

How to make a Physical Backup of MariaDB

In this section, we want to teach you how to make a physical backup of MariaDB. To do this, you must follow the steps below in order. It should be noted that several tools are available for physical backup. In the following, we are going to show you the process of backing up MariaDB using LVM.

First, verify the MariaDB file storage using the following command:

mysqladmin variables | grep datadir

Output:

| datadir | /var/lib/mysql/ |

Then you need to run the following command to check which logical volume is hosting this place:

df /var/lib/mysql

You can now confirm the amount of space available for a snapshot by running the following command:

vgdisplay vg0 | grep Free

You need to connect to MariaDB by running the following command, drag the tables on the disk and lock them:

mysql -u root -p

In the next step and in another terminal session, you need to create the LVM snapshot using the following command:

lvcreate -L20G -s -n mariadb-backup /dev/vg0/mariadb

The important thing to know is that the LVM snapshot must be large enough to change from the moment the database is unlocked until the database backup is complete. Because when the storage space is full and the backup fails, the snapshot becomes invalid.

Now open the tables using the following commands in the main MariaDB session:

MariaDB [(none)]> UNLOCK TABLES;

Pay attention to the following commands and run them to install the snapshot in the desired location:

mkdir /mnt/snapshot

mount /dev/vg0/mariadb-backup /mnt/snapshot

In the last step, the backup snapshot should be deleted using the following commands after backup:

umount /mnt/snapshot

lvremove /dev/vg0/mariadb-backup

How to Restore a MariaDB Database

As we told you in the MariaDB backup section, there are two physical and logical ways to do this. There are two logical and physical methods in restoring a backup. Stay tuned for the rest of this article.

You can easily use the following command to perform a logical restore:

mysql -u root -p inventory < /backup/mariadb.dump

You must also follow the steps below to perform a physical recovery. First, you need to run the following command to verify the storage location of MariaDB files:

mysqladmin variables | grep datadir

Output:

| datadir | /var/lib/mysql/ |

The important point here is that to perform a physical recovery, you must stop using the MariaDB subroutine command:

systemctl stop mariadb

Finally, you need to delete the actual content using the following command:

rm -rf /var/lib/mysql/*

Conclusion

The core of MariaDB is the Mysql version 5, with some differences in engine performance. In fact, the two most important security issues and tuning MariaDB have been worked hard and its productivity is much better than Mysql. In this article, we tried to teach you How to Backup and Restore a MariaDB Database. You can refer to the article Tutorial MariaDB installation on Ubuntu if you wish.