Overview

The most straightforward method of performing a backup on a MySQL database is with the mysqldump utility. This is a great tool with many advanced features! It is not without its pitfalls, though.

The default behavior of mysqldump is to lock all of the tables in a database while it is performing the dump. This means that the data cannot be changed until the backup has finished, which is great for data integrity, but not so great for an active database that is frequently updated. Any updates need to sit and wait for the lock to be lifted, which is especially problematic for the Drupal sessions table, for example.

This default behavior can be modified using --skip-lock-tables, which is fine in some cases, but generally speaking it is best to have that point-in-time state of the database preserved in a backup. Also, if every table in every database being backed up is innoDB, then --single-transaction (combined with --quick for large tables) can provide this point-in-time, but even this does not protect against certain statements like ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, or TRUNCATE TABLE.

Enter: Logical Volume Manager (LVM)

The Logical Volume Manager (LVM) is a block device subsystem provided by Linux that sits between the filesystem (ext3/4, xfs, etc.) and the physical disks (/dev/sda, /dev/sdb, etc.). It acts as a translation layer that allows some very advanced operations, such as the snapshot feature covered here, and is completely transparent to the filesystem.

Configuring LVM is outside the scope of this article, but most mainstream Linux distributions provide it as an option during installation, and many even use it by default. One thing to note, though, is that it is important to reserve some of the physical drive space as unallocated. The unallocated space is what allows snapshots to be created. Generally speaking, a good practice is to allocate the amount of space currently required plus 10% for growth. Unlike traditional disk partitions, expanding an LVM volume is quick, easy, and does not require any downtime, so there is no danger to underestimating at this stage.

Now, for the bad news. Many cloud providers do not support LVM. But, for those servers that can use LVM, the snapshot feature can be used to get a point-in-time backup of MySQL with minimal table locking.

Discovery Phase

Before proceeding, some preliminary information is needed.

1. Where are the MySQL data files stored?
   ~# mysqladmin variables | grep datadir
| datadir                         | /var/lib/mysql/             |

   This shows that the data files are located at /var/lib/mysql.

2. Which logical volume hosts this location?
   ~# df /var/lib/mysql
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg0-var  209698268  45532856 164165412  22% /var

   This shows that the volume group is vg0, and the logical volume name is var. So, the full block device path that the LVM tools will understand is /dev/vg0/var. This can be confirmed with the following

   ~# lvscan
  ACTIVE            '/dev/vg0/var' [200.00 GB] inherit
3. How much unallocated space is available for the snapshot in the vg0 volume group?
   ~# vgdisplay vg0 | grep Free
  Free  PE / Size       9551 / 37.31 GB

   This shows that there are 37.31GB of free space on the vg0 volume group. This is important to note, because this free space is where changes to the live database are tracked while the snapshot is present.

Create an LVM Snapshot

1. Connect to MySQL, flush the tables to disk, and lock them. Do not do this with mysqladmin, and be sure to leave the database session open. As soon as a client (such as mysqladmin) disconnects, this lock is lifted. In order to guarantee data integrity, the database must remained locked until the LVM snapshot is created. The amount of time that this operation takes will vary based on how much data needs to be flushed to disk, but it is generally very quick.
   FLUSH TABLES WITH READ LOCK
2. In another terminal session, create the LVM snapshot. This snapshot needs to be large enough to accommodate the changes that will be made to the database while the snapshot is present. Because this snapshot will be short lived, the shortcut "100%FREE" can be used, which will use all 37.31GB of unallocated space in this case. This process is nearly instantaneous because LVM uses a copy-on-write (COW) snapshot method.
   lvcreate -l100%FREE -s -n mysql-backup /dev/vg0/var
3. Back at the original MySQL session, release the read lock so that normal database operation can resume.
   UNLOCK TABLES

At this point, there is a consistent, point-in-time snapshot of the MySQL file structure stored in the LVM snapshot. The database can now go on with its business, and the only locking required was to flush any data from memory to disk.

Snapshot Magic

Using lvscan again, the new snapshot can be seen.

The snapshot can now be mounted at an arbitrary location (If /dev/vg0/var is an XFS volume, add "-o nouuid -t xfs" to the mount command).

From here, any standard filesystem backup method can be used to store a copy of /var/lib/mysql as mounted under /mnt/snapshot. This method could range from rsync/ssh, to a simple tarball, to some enterprise backup solution.

Portability

In order to safely restore a filesystem level MySQL backup, it would need to be restored to a MySQL server with the same major and minor versions (5.0, vs 5.1, etc). While this is may not be a problem when using stock packages from a distribution, a traditional mysqldump backup is much more portable. However, as powerful as mysqldump is, it cannot create a dump file from a set of raw MySQL files, it must pull the data from an active MySQL server.

The solution is to start a second instance of MySQL using the data files from the snapshot mounted at /mnt/snapshot. There are three things to keep in mind for this second instance

1. The TCP port must be different than the primary MySQL instance, which can be found with
   ~# mysqladmin variables | grep port
| innodb_support_xa               | ON                          |
| large_files_support             | ON                          |
| port                            | 3306                        |
2. The MySQL socket must be different than the primary MySQL instance, which can be found with
   ~# mysqladmin variables | grep socket
| socket                          | /var/run/mysqld/mysqld.sock |
3. The --innodb-log-file-size must be identical to the primary MySQL instance, which can be found with
   ~# mysqladmin variables | grep innodb_log_file_size
| innodb_log_file_size            | 268435456

Taking these values into consideration, start the second MySQL instance

Now, a full mysqldump can be performed against this second MySQL instance, which can lock all it wants without affecting the primary instance. In order to ensure that mysqldump is using the second instance, specify the MySQL socket file.

Cleanup

That's it! There is now a portable, consistent mysqldump file at /path/to/mysql/backup.sql.gz, and the only locking required was to flush the data to disk. All that is left is to clean up.

1. Stop the second MySQL instance with mysqladmin
   mysqladmin -S /var/run/mysqld/mysqld-snapshot.sock shutdown
2. Unmount the snapshot volume
   umount /mnt/snapshot
3. Delete the LVM snapshot
   lvremove /dev/vg0/mysql-backup

More Information

There were some advanced topics touched on here, but were not covered in detail. More information about these topics can be found at the following locations.

• http://en.wikipedia.org/wiki/Logical_Volume_Manager_(Linux)
• http://tldp.org/HOWTO/LVM-HOWTO/
• http://en.wikipedia.org/wiki/Copy-on-write
• http://en.wikipedia.org/wiki/Device_mapper
• http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html