MySQL Backup Strategy

The importance of backups
MySQL backup tools
Backup is not only running the backup command
Backup storage
Backup time and frequency
Testing backups routinely
Automating the process

The importance of backups

Backups are an important part of the database administration process management. Developing a robust and sophisticated backup strategy can help keep your database safe from a myriad of potential problems.

The list of reasons that might cause problems is overwhelming, and as a rule, we cannot foreknow the reason why our database got broken. However, as a result of any breakdown, the database can find itself in two possible states:

  1. The database is corrupted and won’t start. Typically, this is a consequence of hardware issues, as a result of which the database files were partially or completely damaged. This is not only possible when a meteorite hits your server, it usually happens due to errors in data storage devices. 
  2. The database is starting up, but invalid data was written into it. This can be either due to a human factor (you accidentally deleted a table) or as a result of an error in the software that works with the database (for example, a programmer wrote a code that, instead of deleting data from a particular day, deletes data for the entire year).

The first case can be quickly resolved if you have a hot standby server. However, both cases can be solved only if you have a backup with correct data for every day (backup-point-in-time).

So even if your budget allows you to set up master-slave replication and keep a standby server running at all times, you still need to back up your databases regularly.

MySQL backup tools

A backup is a file or a group of files that contain information that allows you to restore data in a database.

There are three different ways to back up your data in MySQL. Each has its own pros and cons.

mysqldump is a utility that is part of MySQL Server. It generates sql script that can recreate a database. The main advantage of this backup method is that the backup is created in an open editable format.

The main drawback of the method is the speed when working with large databases. Creating a backup and restoring a database from a backup takes a while. It should also be noted that in order to implement incremental backups, you will need to additionally backup bin log files.

Xtrabackup is an open-source utility developed by percona. Xtrabackup makes a backup by copying the database files. The distinctive feature of Xtrabackup is that you do not need to stop MySQL instance (hot backup), you only need to stop it to perform the restore operation.

This backup is much faster than mysqldump. Xtrabackup allows you to create incremental and differential backups as well as particular backups. However, unlike mysqldump, the backup file cannot be opened and edited in a text editor.

MySQL Enterprise Backup is a hot backup utility developed by Oracle. It is largely identical to Xtrabackup. Detailed feature comparison of the two is available here.

Backup is not only running the backup command

The command that creates the backup is just a step in a number of actions required to secure your data. To be on the safe side, after creating a backup, you need to perform a few more steps.

Backup compression

Normally a backup compresses very well. It is always advisable to perform compression, as this saves space in the storage, and also reduces the load on the network when transferring a backup file. You can compress a backup either by streaming or after backup is created.

Backup encryption

Most likely, the data in your database is protected at least by a password. However, if someone takes possession of the backup file, then they will have access to all the data. Therefore, the backup file must be encrypted before being sent to the storage. The easiest way to do this is to set a password during file compression.

Transferring the file to storage

Keeping backups on the same server where the MySQL instance is located is nuts because, in case of a disk breakdown, you will lose both the MySQL server and the backups.

At the very least, the files should be transferred to a separate device. However, in the era of cloud storage, it makes sense to delegate file storage to a large corporation, AWS S3, Azure Storage, or Google Cloud Storage.

Deleting outdated backups

Wherever you store your compressed backups, they must be deleted, otherwise, over time, they may exhaust the available free space, and this, in turn, may make it impossible to save new backups to the storage.

Failure notifications

It is unlikely that you will perform these steps manually over and over. In one way or another, you will have to automate the process, containing all the steps described above. But at each of these steps, errors may occur, in this case, a notification must be sent to the DBA’s email so that they analyze the issue and fix it.

Backup time and frequency

In a nutshell – the more often, the better. However, the backup process consumes server resources and backups consume storage space, so a compromise must be found. Incremental backups can allow you to back up more often, and they take up less space. However, even with incremental backups, you need to make full backups regularly.

When deciding the backup frequency and timing, one has to account for various factors: the maximum tolerable downtime hours for data recovery, the intensity of the server load, the time and how often the software that works with the database is updated, working hours of employees that can break something in the database.

Most companies run backups every night.

Testing backups routinely

Perhaps the most important thing that people tend to forget is to regularly check the recovery script. The automated backup process with failure notifications is cool. However, the main task of the backup is not to put the backup file in the cloud storage, but to have a copy of the database that can be restored. Try to restore your database to a different server from time to time, and make sure that the data is restored exactly the way it should be restored.

Automating the process

You can create a backup and send it to the storage manually. However, you should not rely on doing it manually day in day out. You can forget or get sick, and your time costs. The process of creating a backup can and should be automated.

There are two notable ways to approach this.

Creating a number of scripts to automate the process

Thanks to very powerful shell scripts, process automation in Linux is not the most difficult task. By creating a bash script, you can have in place the solution that suits you. An example of such a script can be found here.

You might face difficulties if you need to send a backup to cloud storage that has no client for Linux.

Another displeasing matter is when you have multiple servers, you have to spend time supporting the scripts you have written, run regular tests, and make sure that backups are created and restored consistently.

Using third party utilities

And although you can automate literally everything with a bash script, in practice, it will take time to implement a working, transparent, and properly tested solution. Therefore, it makes sense to consider third-party utilities that implement the entire process.

For example, SqlBak service can create a backup, compress, encrypt and transfer it to cloud storage automatically, as well as test the recovery process by performing a recovery on another server, and email the result and the execution log to you.

Leave a Reply

Your email address will not be published. Required fields are marked *