run-mariabackup makeover

On our busy server, we were recently hitting the roof using our previous mysqldump/bzip2 based MariaDB backup strategy.

So, I started looking for alternatives and quickly found Percona XtraBackup and its descendant mariabackup mentioned. Being physical instead of logical backup approaches which basically copy the server storage instead of dumping the database content into replicating SQL statements, they follow a very different strategy. The big advantage is much shorter database locking caused by the backup process. Also, they both offer incremental backups which further shortens execution time on successive runs.

Of course, any backup strategy is only as good as the corresponding recovery process. With logical mysqldump backups, the process is straight-forward and the data can even be inspected before being restored. With physical backups, the standard approach is to restore the full set of databases served by the server instance as a whole. Single-database recovery is described but surprisingly complicated and for example involves dropping foreign-key constraints before importing backup data and reestablishing them afterwards.

Of course, all these operations should be prepared and at hand before you adopt a new backup strategy. After more searching, I came about a script package called run-mariabackup which exists in multiple evolution steps maintained by different people. While it already looked promising as a starting point, the current state left multiple features to be desired. In particular a single-database restoration script.

Before I tell you the link to the repository, please be warned: This is very new code with little testing! So, treat it as experimental code which means that you need to thoroughly review it and test it on your own before putting it into any kind of production environment. Do independent backups if your data has any worth to you! Make sure that backup and recovery works before relying on it!

So, this is the result of a few hours of bash scripting and trying various scenarios:

tvogel / run-mariabackup

Fork and rewrite of YoSiJo/run-mariabackup

README

• forked from YoSiJo/run-mariabackup
  • forked from omegazeng/run-mariabackup
    • forked from jmfederico/run-xtrabackup.sh

Note: I have tested this on openSUSE Tumbleweed 20230512 with MariaDB 10.11.2.

Fat Warning

This new fork (as of 2023-05-15) has seen only very limited testing and use. In particular, only with MyISAM and InnoDB tables In these limited cases, it worked fine but there is no guarantee at all that it will fulfill any purpose in your specific environment. As it is acting on complete database server setups with possibly many databases and complexities which I have never seen before, there is a high probability that this script will break in your scenario and can possibly have catastrophic effects. Just as a non-exclusive single example, I have not tried it on databases with stored procedures or triggers.

So, use these scripts only after your own educated review and on your own risk!

Also, please share any concerns and observations…

View on GitHub

Backup

After adjusting some settings to fit your environment in a config.sh file, backing up is as easy as:

# ~/git/run-mariabackup/backup.sh

which will run full base-backups at specified intervals and incremental backups in between. You can also request a new full base-backup by placing a base-backup.request file into your backup target directory which is convenient if you want to trigger a full base-backup from cron every Sunday night.

The command will also discard of backups that exceed a specified age.

Restore

In order to restore all databases, you go to the backup data directory representing the state which you want to restore and:

# ~/git/run-mariabackup/restore.sh

This will move your old data-directory out of the way (and keep it) and move the complete backup back in place.

If you are interested in only one database, use:

# ~/git/run-mariabackup/restore.sh <original> <restored>

In this case, the restored data is not directly put back into place but instead into a new database named <restored> which must not exist before. After that, you can use a simple mysqldump/mysql pipe to put the data back into place after inspection. This can also be used to restore only individual tables (if this makes sense at all).

Cron

I am a big fan of https://habilis.net/cronic/, so I would recommend something like this for your /etc/crontab:

# run base mariabackup once a week (Sunday night) and incremental every 12 hours                                                                                                                                                                                              
54 00    * * 1  root  cronic touch /var/backup/mariabackup/base-backup.request                                                                                                                                                                                             
55 00,12 * * *  root  cronic /root/git/run-mariabackup/backup.sh                                                                                                                                                                                                            

At this point in time, I am interested in reviews and feedback. So, I am glad to hear back from you!