Backup MySQL for Fun and Profit!

Are you regularly backing up your MySQL database? Good! Have you read my previous blog on this topic, 3, 2, 1… MySQL Backup is Fun!? Excellent! So we’ve established how important backups are, RTO, RPO, and how you can be a hero by having backups that align with business objectives. We just need to pick a good backup tool(s) to take backups of your MySQL database.

mysqldump

A tried-and-true classic, mysqldump for years was THE method for backing up MySQL and is already installed in your environment. mysqldump simply queries the database and dumps all of the data into a portable format consisting of SQL queries that can later be used to load an empty database. It also can dump all of the table definitions, triggers, and stored procedures as well. Dumping is usually quick and also usually results in a compact file, since only the data and structure are saved. The actual rebuild of the indices happens upon load. This is an effective tool for small to medium sized databases, however when the database gets quite large, the dump and especially the load can be quite time consuming. A few other items to keep in mind:

• Using mysqldump can lock the database. Thus it’s generally not used on an active primary database.
• Use the --single-transaction parameter to get a consistent backup of the database (will add more locks, however).
• Perform the dump on a cluster replica, if possible, to avoid locking the primary database.
• It’s a good idea to also use --master-data=2 which will put the binary log position of the last transaction of the backup into the header of the dump file. This information can be used after restore to bring the database current.

There are also free tools that expand on the classic mysqldump, like mysqldumper and mysqlpump. These tools increase throughput by using parallelism to dump and to load data.

XtraBackup

Xtrabackup from Percona came to the rescue by allowing hot backups of MySQL. With XtraBackup, you can backup your primary database without downtime and without pausing your database. It has additional features too, like incremental backups and streaming backups to another server. After taking a backup with XtraBackup, the result is a copy of your database that can be quickly restored or copied, which is much faster than rebuilding with mysqldump. This is ideal for production databases where uptime is critical, and it also handles large databases quite well. Here a few things to keep in mind when using XtraBackup:

• Backing up a database could put additional load on your host.
• To minimize the performance impact to the database, backup to a different filesystem from your database. If possible use a filesystem that’s on a different storage controller or stream the backup to another host.
• Note the binary log info of the last transaction of the backup is located in xtrabackup_binlog_info in the database backup directory.
• Beginning with MySQL version 8, your version of XtraBackup MUST match the version of mysql. If you upgrade MySQL, you must upgrade XtraBackup at the same time. Sometimes XtraBackup will lag behind MySQL, so you should check to make sure the version of XtraBackup you need has been released before upgrading MySQL. You can use --no-server-version-check to force the backup, but it’s not guaranteed to work.
• When databases approach 1TB, it takes a long time to create a backup, and it may be too long for a timely backup.

Snapshots!

Using snapshots is one of my favorite methods for backing up MySQL! A snapshot is simply a point in time view of a filesystem. We can leverage the filesystem layer, hardware, or cloud tools to take a snapshot of the database filesystem and create our backup.

Here’s how a snapshot works:

• First, we must quiesce the database and tell MySQL to flush all dirty pages to disk. A simple way to do this is to issue FLUSH TABLES WITH READ LOCK; in mysql (https://dev.mysql.com/doc/refman/8.0/en/flush.html#flush-tables-with-read-lock)
  This will quiesce the database (note that queries to the database may hang after issuing this command.

• Take a snapshot of the volume:
  • Using LVM: lvcreate -s.
  • Using VM hypervisor.
  • Using cloud tools.
• Unlock the tables, for instance: UNLOCK TABLES.
• The above steps should only take a few seconds. After the previous step, the database is back online.
• Mount your snapshot somewhere convenient.
• Copy your database files from the snapshot volume to your (real) backup volume.
• Verify the backup, then destroy the snapshot.

Although while the snapshot is active there is additional load on the volume, the impact is usually MUCH less than directly querying or copying the database in the previous methods. It easily allows you to copy the database using basic tools like rsync or hardware level tools to clone the snapshot into a physical volume. You can even mount the copy later to the MySQL host instead of doing a restore, drastically cutting down your RTO! It’s also great for large databases, where other methods would simply take too long to perform the backup (and restore!).

Back That DB Up!

Databases evolve, grow, and business needs change. That’s why it is important to constantly reevaluate your backup strategy, because what worked last year may not be appropriate this year in terms of RPO/RTO, retention, or costs. Got a slick strategy for backing up MySQL? I’d love to hear it!