Automated MariaDB restore tests

Nearly everybody does backups. But nobody needs backups! What everybody wants and needs is a working restore not a working backup...

So how to make sure that your backup is working for the restore? There are a few things you can do already during your backup:

• Check that your backup was running fine. For example by checking the return code of your backup.
• Check the runtime of your backup. If the runtime of your backup significantly changed, it is worth to have a closer look at the backup.
• Check the size of your backup. If the size of your backup significantly changed, it is worth to have a closer look at your backup.
• And finally make your monitoring system aware if the backup was NOT running at all and if you are sure your backup is really triggered...

Backup test with FromDual Enterprise Tools

All this functionality is integrated in the newest releases of FromDual Backup and Recovery Manager for MariaDB and MySQL (brman) and the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) in combination with the great monitoring solution Zabbix.

You have to run Backup Manager with the options --fpmmm-hostname and --fpmmm-cache-file:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=full --policy=daily \
  --fpmmm-hostname=mariadb-106 --fpmmm-cache-file=/var/cache/fpmmm/fpmmm.FromDual.mariadb-106.cache

then Backup Manager knows that it has to collect the metrics: return code, backup time and backup size and deposits them for the next Performance Monitor run. The next fpmmm run then will automatically gather these data and send it to the Zabbix Server.

In the Zabbix Monitor then you can see how your backup behaves:

and you get alerts if backups failed or did not even happen:

Restore Tests with FromDual Enterprise Tools

If your backup was running fine, returned with a zero return code, had the usual size and was running in a normal amount of time it does not necessarily mean that you also can do a restore with this backup!

For the sissies among us we also should do a restore test. To justify restore tests we have worked out 2 different concepts:

Staging systems restore test

Many of us have different stages of systems: Production, Quality Assurance, Integration, Testing and Development. All those systems need to be provisioned from time to time with new and fresh data from production. These systems would be ideal candidates for your daily restore tests. Also here the FromDual Enterprise Tools can help you: The backup, done with Backup Manager is shipped automatically to the other system where the Recovery Manager is restoring the database. Booth tools report their results to the Performance Monitor which sends the results to Zabbix. And Zabbix sends complains to the administrators if the backup or the restore did not happen or failed:

Spider system restore test

If you have many systems to do restore test or if you do not want to expose your precious data to other stages than production we recommend you the restore spider concept: Every production database does its daily backup on its local backup store. And additionally you have a centralized restore system which has access to the backup of each database. So this central spider restore system grabs now the backup of each database one after the other and does a restore on its restore system. The result will be reported to the Performance Monitor again and you get notice by Zabbix is the Restore did not happen, not work or was talking longer than a predefined time: