Backups - one of the most important things to take care of while managing databases. It is said there are two types of people - those who backup their data and those who will backup their data. In this blog post, we will discuss good practices around backups and show you how you can build a reliable backup system using ClusterControl.
There are two main types of backup:
- Logical backup - backup of data is stored in a human-readable format like SQL
- Physical backup - backup contains binary data
Both complement each other - logical backup allows you to (more or less easily) retrieve up to a single row of data. Physical backups would require more time to accomplish that, but, on the other hand, they allow you to restore an entire host very quickly (something which may take hours or even days when using logical backup).
ClusterControl supports both types of backup: mysqldump for logical backup and xtrabackup for physical backup.
Obviously, you’d want to have a fixed schedule for your backups. How often you want the backup to execute? It depends on your application, importance of data, time needed to take the backup and so on. We’d recommend to take a backup at least daily. When possible, you’d want to take both mysqldump and xtrabackup backups on a daily basis. To cover even more bases, you may want to schedule several incremental xtrabackup runs per day.
In ClusterControl you can easily schedule these different types of backups. There are a couple of settings to decide on. You can store a backup on the controller or locally, on the database node where the backup is taken. You need to decide on the location in which the backup should be stored, and which databases you’d like to backup - all data set or separate schemas? Depending on which backup type you’ve chosen, there are separate settings to configure. For Xtrabackup and Galera Cluster, you may choose if a node should be desynced or not. Are you going to use backup locks or maybe ‘FLUSH TABLES WITH READ LOCK’ should be used instead? Should the backup be compressed or not?
Which options to use will depend on your particular setup and hardware. For Galera Cluster, to avoid impact on the rest of the cluster, we’d suggest at least to think about desyncing the node for the duration of the backup. Please keep in mind this may also remove your backup node from rotation, this is especially true if you use HAProxy or MaxScale proxies.
Another popular way of minimizing the impact of a backup on a Galera Cluster or a replication master is to deploy a replication slave and then use it as a source of backups - this way Galera Cluster will not be affected at any point.
You can deploy such a slave in just a few clicks using ClusterControl.
Checking backup status
Taking a backup is not enough - you have to check if it actually completed correctly. ClusterControl can help with this. You can go to the Backup -> Reports tab and check the status of your backups. Have they completed successfully? What’s their size - is it as expected? This is a good way to do a quick sanity check - if your dataset is around 1GB of size, there’s no way a full backup can be as small as 100KB - something must have gone wrong at some point. To dig even deeper, you can take a look at the backup log and look for any errors.
Storing backups within the cluster (either directly on a database node or on the ClusterControl host) comes in handy when you want to quickly restore your data: all backup files are in place and can be decompressed and restored promptly. When it comes to disaster recovery, this may not be the best option. Different issues may happen - servers may crash, network may not work reliably, even whole data centers may not be accessible due to some kind of outage. It may happen whether you work with a small service provider with a single data center, or a global vendor like Amazon or Rackspace. It is therefore not safe to keep all your eggs in a single basket - you should make sure you have a copy of your backup stored in some external location. ClusterControl supports Amazon S3 and Glacier services for that .
For those who would like to implement their own DR policies, ClusterControl backups are stored in a nicely structured directory. So it is perfectly fine to build and deploy your own set of scripts and handle DR according to your exact requirements.
Finally, another great way of implementing a Disaster Recovery policy is to use an asynchronous replication slave - something we mentioned earlier in this blog post. You can deploy such asynchronous slave in a remote location, some other data center maybe, and then use it to do backups and store them locally on that slave. Of course, you’d want to take a local backup of your cluster to have it around locally if you’d need to recover the cluster. Moving data between datacenters may take a long time, so having a backup files available locally can save you some time. In case you lose the access to your main production cluster, you may still have an access to the slave. This setup is very flexible - first, you have a running MySQL host with your production data so it shouldn’t be too hard to deploy your full application in the DR site. You’ll also have backups of your production data which you could use to scale out your DR environment.
We hope this gives you enough information to build a safe and reliable backup system.