Quiesce Your Database for Backup

Introduction

Traditional database backup is a mundane but necessary process for every organization. If you’re still developing a backup strategy, then I would suggest reading this blog: 3, 2, 1… MySQL Backup is Fun!

Traditional backup methods like mysqldump and xtrabackup are quite effective, but as the database grows, these tools require more time and resources to complete. This is in addition to the high load that is put on the host during backup. In some cases, the backup could take hours, and in some cases the backup might be stale by the time the backup completes.

Let’s Quiesce

“Quiesce” means to make quiet. The simplest way to quiesce MySQL is to shut it down. At this point, you can be sure there are no new writes to the database and the database is in a consistent state. Then you can take a backup using your favorite backup method. Ideally, you would select a replica to perform the backup on to minimize the impact to the production environment. However, there still could be impact to production even when shutting down a replica: the replica is not available for reads, the buffer cache is cleared, and it will start to lag in replication. If the backup takes a long time, then this could cause issues in production. Obviously, doing this on a primary will cause production downtime since the database is shut down.

There is another way to quiesce a database: In MySQL this is quite simple: FLUSH TABLES WITH READ LOCK; This command closes all tables and obtains a global lock on the database. The result is the database being quiesced with all new writes blocked. This is a powerful feature in MySQL that’s described here: MySQL 5.7 Reference Manual :: 13.7.6.3 FLUSH Statement.

At this point you might be thinking, “Why do I care about quiescing the database when a backup tool (xtrabackup) already exists that doesn't require me to quiesce it?” The answer is quiescing a database allows us to use backup methods that are FAST. Let’s look at two examples, snapshots and rsync:

Snapshots

This method was mentioned in this blog: Backup MySQL for Fun and Profit!

The procedure looks like this:

• FLUSH TABLES WITH READ LOCK;
• Perform snapshot. This could be a Unix LVM snapshot, or file system snapshot using tools from your cloud or infrastructure provider
• UNLOCK TABLES;
• Backup is done, database is back online
• Copy snapshot to durable storage

This backup portion of this procedure takes just a few seconds with minimal impact to the database. When doing this on a replica, the master is unaffected and thus there is no impact to the application. If for some reason this is done on the primary, there will be a pause of writes, but just for a few seconds. Note that if doing this on the primary, you should run SHOW MASTER STATUS; just before taking the snapshot and record the binary log position, and store that with the database.

rsync

Using rsync is a great way to backup and reprovision databases. This procedure builds on the process as outlined here: How to Use Rsync to Provision a Tungsten Cluster Node.

The procedure is similar to snapshots:

• FLUSH TABLES WITH READ LOCK;
• Perform rsync
• UNLOCK TABLES;
• Backup is done, database is back online

Using rsync will most likely result in the database being offline for a longer period of time than with snapshots, but rsync is readily available and, when using a replica as a source, we can avoid application downtime. There are tricks to make rsync perform superfast, and I will show you those tricks in the next blog about using advanced rsync to backup and provision your database.

Summary

FLUSH TABLES WITH READ LOCK; is a powerful building block for successful consistent backups and reprovisioning of databases. It allows us to use OS level tools to perform backups quickly, especially on large databases where traditional tools might be too slow and resource intensive.