Jump to Navigation

MySQL: Backups with file system snapshots

This is a guide about creating MySQL backups by using file system snapshots.

Using snapshot backup has the following characteristics:

  • Online backup;
  • Full backup;
  • All databases and tables are always backed up and restored;
  • For both MyISAM and InnoDB tables;
  • Must be run locally
  • A short read lock during the snapshot

Preparation

If you want to use file system snapshots for your backup, you have to make sure that all relevant files are on one file system1 and that there are no files on that file system that should not be overwritten on a restore.

If the server you backup is a slave to some other server, you have to include the replication logs in your snapshot.

The binlogs should be available too after a restore; at least the latest binlog file at the time of the snapshot is needed. Often, though, the binlogs will be handled and backed up separately and you may not want to lose them on a restore, in order to be able to do a roll forward. In that case, locate them on some other file system.

So a typical situation is:

  • Make /var/lib/mysql a separate file system;
  • Put the binlogs somewhere outside this file system (using log-bin in the my.sql);
  • Put the log files (slow query log, error log, general log) outside this file system (for example, into /var/log);
  • Put everything else on /var/lib/mysql.

See also the list of MySQL files.

Preparation for using LVM

Create a new LVM logical volume in a volume group which is not completely in use. The remaining space will be used to create the snapshots. It may be a good idea to create a completely new volume group for this, if you are using new physical volumes (disks).

Create a file system on this (ext4 or some other file system of your choice).

Stop MySQL. Move /var/lib/mysql to a temporary location. Recreate /var/lib/mysql and mount the newly created file system here. Copy the contents of the old /var/lib/mysql. Put /var/lib/mysql into /etc/fstab. Start MySQL again.

Creating a backup

Start a MySQL session and issue the following commands:

--- In MySQL
FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;
-- Keep this session open

While keeping the MySQL session alive (as soon as it is closed the locks will go away) create your file system snapshot.

Once the snapshot is complete, end the MySQL session or release the locks explicitly:

 UNLOCK TABLES;

Creating a snapshot using LVM

Creating an LVM snapshot is easy, once all data is on one logical volume. The only thing to consider is how big the snapshot should be. Initially, the snapshot will take no space at all, as each file system block is identical with the underlying source. Any changes made to the snapshot will of course be recorded in the snapshot and take up space; but the same will happen to all changes made in the underlying source. So the snapshot will grow as the source MySQL file system changes.

The big question thus is: how long will the snapshot be kept and how much will it and the underlying file system change in that time?

If you want to be sure that the snapshot will always have enough space, make it just as big as the underlying source.

Once the snapshot is out of space, it will no longer be usable. It is possible, though, to extend the reserved space by using lvextend before that happens.

To create the snapshot:

# On the command line
# UNDERLYING is the underlying file system
UNDERLYING=/dev/lvm_main/var.lib.mysql
# NEWNAME is the name of the snapshot
NEWNAME=mysql-snapshot
# SIZE is the size of the snapshot
SIZE=10G
lvcreate --snapshot --name $NEWNAME -L $SIZE $UNDERLYING

This snapshot can now be mounted as a normal file system. It can be changed without affecting the underlying source file system; just as the underlying file system can be changed without affecting the snapshot.

It is usually a good idea to mount the snapshot and copy the files offline.

Remove the snapshot as usual:

# NEWNAME is the name of the snapshot
NEWNAME=/dev/lvm_main/mysql-snapshot
lvremove $NEWNAME

Restoring a backup

Restoring is not complicated:

  • Stop MySQL;
  • Copy all files back to their original locations;
  • If not all binlogs were included in the backup, edit the binlog index (often mysqld-bin.index) and remove those binlogs that are not available;
  • If this is a clone of a still existing machine, remove auto.cnf;
  • Start MySQL.

The copying back of files can usually be done a lot faster if the snapshot is still around.

Restore a snapshot using LVM

If you have a snapshot around and want to copy the contents to the underlying source file system, you can do this as follows:

# On the command line
# ORIGINAL is the path to the underlying source file system device
ORIGINAL=/dev/lvm_main/var.lib.mysql
# SNAPSHOT is the path to the snapshot file system device
SNAPSHOT=/dev/lvm_main/mysql-snapshot
umount $SNAPSHOT
umount $ORIGINAL
lvm --merge $SNAPSHOT
mount $ORIGINAL

This will remove the snapshot.

  • 1. or on two or more, provided you make snapshots at the same moment
Tags:


Technical_article | by Dr. Radut