Restoring MySQL backups is a near-daily task of almost every MySQL DBA. In this blog post we will discuss how using Backup Ninja can make your life easier in the MySQL space. Bear in mind that Backup Ninja can be also used to restore MariaDB Server and Percona Server too so if you’re using those platforms this blog post will be useful too.

Restoring MySQL Backups - Why and When

Before we actually tell you how to restore your MySQL, MariaDB or Percona Server backups using Backup Ninja, we should probably tell you why and when would you need to restore backups in the first place. In general, most MySQL DBAs would need to restore their MySQL backups as soon as disaster strikes (for example, some of your data gets lost, corrupted, hard drives fail etc.) - restoring your MySQL backups can help minimize the downtime of your MySQL-based databases and keep you sleeping soundly at night.

There are a couple of ways to restore MySQL backups:

1. You can restore your MySQL-based backups manually. This way of recovering your backups is one of the oldest and well known ways to restore your backups, however, the downside of such an approach is that it might consume a lot of time.
2. You can restore your MySQL-based backups automatically using tools like Backup Ninja or the like. This is what we are going to look into now.

Restoring MySQL Backups with Backup Ninja

In general, Backup Ninja allows you to restore your MySQL-based backups - doesn’t matter if they are encrypted or not. If your backups are encrypted:

1. Decrypt the backup (it is a good idea to disable the command history when doing so - this way your encryption key will not be observed):
   $ bartender decrypt -k [your encryption key] -f /your/backup.tar.gz -o backup.tar
   If your backup is also compressed, add an -x flag before specifying the output file.
2. Restore the backup using mbstream for MariaDB or xbstream for Percona Server:
   $ cat backup.tar.decompressed | xbstream -x -C /path/to/datadir
3. Apply the InnoDB log files:
   $ innobackupex --apply-log /path/to/datadir
4. Now, make sure that the user / group MySQL has the ownership:
   $ chown -rf mysql:mysql /path/to/datadir
5. Restart your database server.

If your backups are not encrypted, follow these steps:

1. Decompress your backup:
   $ cat /your/backup.tar.gz | gunzip > backup.tar.decompressed
2. Restore your backup:
   $ cat backup.tar.decompressed | mysql -h127.0.0.1 -uroot -p
   Keep in mind that it’s probably a good idea to not write your password in the console because that way it can be observed by system status programs such as ps. Instead, consider putting your password into my.cnf and logging in - that way you will make the login process more secure.

Backups Causing Headaches? Backup Ninja to the Rescue!

It is worth noting that while restoring MySQL backups with Backup Ninja is very beneficial, it is far from the only features Backup Ninja actually provides. Backup Ninja can also help you with other things including, but not limited to the ability to monitor your database backups, retention planning and, of course, Backup Ninja can also help with ensuring that your database backups stay secure whatever happens. Here’s a sneak peek of what Backup Ninja can do for you. First, we have the Backup Ninja dashboard that depicts the success ratio of your backups, how many backups completed, how many backups are running, how many failed according to certain criteria (e.g last 24 hours, last 7 days or last 30 days, custom dates can also be specified), also how many servers are running or inactive, what’s the backup size according to criteria etc.:

As we have already mentioned, Backup Ninja can also help with retention planning - you can select your retention policy type, how many days do you want to store backups etc.:

We also mentioned security - that’s where Backup Ninja can make your life significantly easier as well - Backup Ninja uses advanced TLS 1.3 encryption which is in transit during all backup operations:

Summary

To summarize, Backup Ninja can be very helpful in a very wide range of database backup questions ranging from restoring the backups of your MySQL or other types of databases to monitoring your backups, retention planning and security. If you are trying to find a reliable backup solution for your business needs, be sure to give Backup Ninja a try.