MariaDB, Mariabackup backup and restore, part 2

Colleagues hello to all.

In the last article, I showed you how to install Mariabackup. In today's article, I will show you how to create full backups and restore a database. It will be interesting!

Article content:

1. Creating a backup user.
2. Creating a directory for backups.
3. Backing up the database.
4. Preparing a backup for restoration.
5. Restoring the database.
6. Assign rights.
7. Launching the database.
8. Total

1. Create a backup user.

Mariabackup needs to log in to the database server while it runs the backup operation. Of course, you can do everything under the root account, but we will do everything in the right way without violating the fundamental principles of database security.

In MariaDB 10.5 and later, the following account privileges are required: RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR

Before version 10.5 MariaDB required account privileges: RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT

Let's create a user who will be responsible for the backup.

mariadb> CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'Qwerty123#';

mariadb> GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariabackup'@'localhost';

2. Creating a directory for backups.

Now let's decide where we will store the backups. I usually store backup copies not far from the database itself, it's just more convenient for me. Let's create directories.

$. mkdir -p /mariadb/backup

$. chown -R mysql:mysql /mariadb/backup

We've created a  backup directory next to the database itself and assigned the owner of the directory to a user and a mysql group. IMPORTANT! The directory where the first backup will be performed must be allowed!!!

3. Backing up the database.

Now that we have everything prepared and now we can start the process of creating the first backup. To start, you need to run the command mariabackup --backup with parameters.

• --backup - indicates that we are doing a backup.
• --target-dir - target directory where backups will be stored.
• --user - account under which the backup process will be started.
• --password - account password.

Launching the backup.

$. mariabackup --backup --target-dir=/mariadb/backup/ --user=mariabackup --password='Qwerty123#' --socket=/mariadb/datafiles/mysql.sock

I also have the parameter  --socket=/mariadb/datafiles/mysql.sock in the command because I moved the database to a different directory. But, if you did not change anything after installing the DBMS, then you do not need to specify this parameter.

When finished, you should see an entry at the end completed OK!

At the end of the backup process, my database files appear in the  /mariadb/backup directory.

4. Preparing a backup for restoration.

Once you have created a backup and want to restore it, you first need to prepare the backup because the backup files are not consistent at a specific point in time, given that the data files are copied at different times. If you try restore data from these files, InnoDB will notice the inconsistencies and throw an error to protect you from data corruption.

The mariabackup command also takes care of preparing a backup before restoring, but with the --prepare parameter.

$. mariabackup --prepare --target-dir=/mariadb/backup/ --socket=/mariadb/datafiles/mysql.sock

At the end, it should say completed OK!

5. Restoring the database.

After the backup is complete and the backup is ready to be restored, you can now try restoring the backup.

5.1. The first thing is to stop the database.

$. service mariadb stop

5.2. The datadir directory where you store all the database files should be empty.

There are two ways to restore:

• --copy-back - allows you to keep the original backup files
• --move-back - actually moves the backup files to the directory where you have all the database files, so the original backup files are lost.

I recommend performing restores with  --copy-back

Restore command:

$. mariabackup --copy-back --target-dir=/mariadb/backup/ --socket=/mariadb/datafiles/mysql.sock

At the end of the process, you should have completed OK!

6. Assigning permissions.

If you made a backup under a different user, then it will restore all files with rights not from mysql, for this we will reassign the rights to the directory where we have all the database files.

$. chown -R mysql:mysql /mariadb/datafiles

7. Starting the database.

If you did everything right, you can start the database.

$. service mariadb start

8. Total.

And so today we successfully tested how the backup and restore process works for us. In the next article, I will tell you how to make an incremental backup.

Thank you all, I hope that my article helped you in some way.