Mariabackup backup - Store history in database
Colleagues hello to all.
In today's article, we'll talk about how you save the result of a Mariabackup backup in MariaDB. Performing a backup is good, but we will not always be able to know at what point we may start having problems with performing a backup, since the backup will be performed automatically. Saving the result of the execution is very useful, we can then display these results in some monitoring system, for example Grafana.
Related articles:
After the backup is completed, a file called xtrabackup_info is created in the same directory with the backup and the result of the execution is written to it.
But that doesn't really work for us, I want to store these results in the MariaDB database itself in a separate schema.
To be able to save results of MariaDB database backup using Mariabackup, then we need our user which we created in previous articles to add the necessary rights.
The first right is to add the ability to create a new schema in the database called PERCONA_SCHEMA. In this scheme, Mariabackup will create a table in which it will store all the results of the backup.
mariadb> GRANT CREATE ON PERCONA_SCHEMA.* TO 'mariabackup'@'localhost';
The second right is to add the ability to write data to this schema for the user mariabackup.
mariadb> GRANT INSERT ON PERCONA_SCHEMA.* TO 'mariabackup'@'localhost';
Now you can try backing up the MariaDB database. We must run the mariabackup program with parameters for authorization and the storage location of the most backup copies, but the most important thing is not to forget to specify the --history parameter, which is responsible for saving the result of the backup in the PERCONA_SCHEMA database in the xtrabackup_history.
$. mariabackup --backup --history=backup_all --target-dir=/app/mariabackup/19.07.2022/full --user=mariabackup --password=Qwerty123 --socket=/app/mysql/data/socket.sock
You can specify any value in the --history parameter, and it does not affect anything. For example, I indicated that I would have a full backup.
After the backup is complete, mariabackup will create the PERCONA_SCHEMA schema and an empty xtrabackup_history table.
Now we can see what the result is, for this we need to query the PERCONA_SCHEMA database:
mariadb> SELECT * FROM PERCONA_SCHEMA.xtrabackup_history;
As you can see, we have data when the backup started, the time when the backup ended, and much more.
Thank you all, I hope that my article helped you in some way.
