Testing Your MySQL Database Backups

Backup testing should be a part of any backup strategy. The testing of your backups allows you to see how long it takes to get the data derived from database backups up and running again.

What is Backup Testing?

Backup testing is the process of assessing the effectiveness of your data backups for security and reliability. In short, backup testing should help you ensure that your backups will work the way they are supposed to work after a disaster strikes – if an efficient backup testing strategy is in use, you will be able to make sure that you will be able to recover your data whatever happens.

Setting Up a Backup Strategy

In general, you would need to recover your data after a crash occurs: it may be an operating system crash, a power failure, a file system crash or a hardware problem. Whatever it is, it’s important to remember that for your backups to actually work (that is, for your backups to be able to be restored and thus tested), you would need to set up a backup strategy. Your backup strategy might be as simple as setting a cronjob to run a mysqldump every Sunday at, say, 10PM (replace text where needed) and ensuring that the backup completes successfully and can be used when needed:

0 0 * * * mysqldump -u 'root' -p'password' database > /root/backup_`date +\%Y\%m\%d_\%H\%M\%S`.sql

Also keep in mind that it might not be a very good idea to write passwords in the terminal because the command can be observed – consider putting your username and password in your my.cnf instead for a more secure approach.

Your backup strategy might also be more complex, but in general, it all comes down to periodically making periodic full or incremental backups. Running the MySQL server with the –log-bin option (the option enables binary logging – in that case the server logs all statements that change data to the binary log) might also help in this scenario.

Testing Your Backups in MySQL

Thankfully, testing your backups in MySQL is rather simple. Here’s what you need to do:

• Take a backup of your data.
• Restore the backup data on a different server and run MySQL on the new data directory.
• Consider executing SHOW statements to verify the database and table structures to verify further information (to verify the table structure, for example, navigate to a database using USE database_name;, then execute a SHOW [FULL] TABLES; statement).

The steps above should help you verify that your database backups are in good shape. If the content of your table structures is incorrect, make sure to back up your data again using a different backup strategy and then test your backups again.

Restoring Backups using Backup Ninja

Before you restore your database backup using Backup Ninja, keep in mind the following:

• The backup must exist on the target node. If it does not, download the backup to the server first.
• To restore a physical backup, you must have access to the server as a super-user.

To restore backups using Backup Ninja, select a backup under Backups, expand the row, select Actions and click Restore.

To learn more about restoring your backups using Backup Ninja, see the documentation.

Summary

Testing your database backups should be a part of any backup strategy. Properly testing your database backups helps you to make sure that your backups can be restored – ensuring that your backups can be restored should help you decide whether your backup strategy is good enough or not. Backups also can be restored manually or by using specific tools that help you accomplish the task – before deciding on how to restore your backups, weigh all possible options and choose wisely.