Database Point in Time Snapshots

(a.k.a Database Roll Back, Versioning)

Overview

Linux servers are frequently used to host MySQL database services. While Oracle and PostgreSQL are also commonplace and will work with ClusterMaker, we'll focus here on MySQL because it is more widely used in the well known LAMP stack. In this scenario, a single server or a high availability pair of servers is used to run the MySQL daemon.

While there are many situations that could cause the database to become corrupted, we are mostly concerned with two common failures. First, the data files may be accidentally deleted or emptied. Second, the primary server in a high availability pair could fail and cause the secondary server to go live with a database that was in an inconsistent state at the time of the failure. This happens because MySQL keeps some data in memory as well as on disk, and systems that only replicate what is flushed to disk do not have a "consistent" picture of all the data.

This problem is inherent to many types of failover software, including DRBD and many storage area network technologies which can quickly serve the same database files to another server running MySQL. If the new server goes live with the inconsistent database files, there is no guarantee that the data accurately represents any specific point in time. Usually, the new server will only lose whatever data has not been flushed to disk, but sometimes the integrity of the entire database can be affected.

What is needed in this case is the ability to "roll back" your database to a previous point in time that is guaranteed consistent. When you take a snapshot with our tools, the following process ensures that a consistent snapshot is taken:

Flushing the database contents to disk
Locking it for a few milliseconds
Taking a snapshot
Releasing the lock
Backing up the snapshot while the live database is back in service

Getting Started

Three executables provide the snapshot and restore capability. They are:

/cluster/bin/setup_dbsnapshots

/cluster/bin/db_snapshot

/cluster/bin/db_restore

Before using database snapshots, three installation steps need to be completed:

1. Install a database and set a root password. To install a typical MySQL server instance, just run:

yum install mysql-server

mysqladmin password somepassword

Substitute a database root password for "somepassword" and then start mysql:

service mysqld start

2. Run the setup_dbsnapshots executable. This will check for the proper filesystem setup that is need for snapshotting and will ask for a MySQL username and password that can be used to flush and manipulate the database. It will test the username and password, and store them in the configuration file if they are correct. For other database types, you can choose to skip the MySQL authentication questions.

3. Open the database snapshot configuration file in a text editor. The path is /cluster/db_snapshots.conf and it has the following contents:

mysqluser="root"        ## mysql username w/ access to all databases
mysqlpassword="somepassword"    ## mysql user password
vgname=VolGroup00       ## volume group the database files are located on
lvname=LogVol00         ## logical volume the database files are located on
backupdir=/var/lib/mysql_recovery   ## where the database snapshots are stored
relpath=var/lib/mysql   ## path to database files, relative to root
lvsize=768M             ## temp volume, snapshot working directory. If too small, snapshot fails!
total_backup_size_limit=1.5  ## a multiple of db size. If 1 GB db, 1.5 GB of backups = default limit
mylvmbackup=/usr/local/bin/mylvmbackup
config=/etc/mylvmbackup.conf
db_stop="/etc/init.d/mysqld stop"    # commands to start and stop your database services
db_start="/etc/init.d/mysqld start"

You must edit this file if the default settings are not correct for your installation. For instance, if you want the snapshots stored stored on a non-default path, you will need to change the setting for "backupdir=". If your MySQL database is not located at /var/lib/mysql, you would need to change the relative path setting "relpath=". Note that if you change either of these, you should also update the /cluster/database_locations file with the correct path to any database directories.

Other settings that might need adjustment are "vgname=" and "lvname=". By default, these are the volume group and logical volume names where the MySQL database files will be located in a standard RHEL / Centos environment. Wherever they are, this is the LVM2 volume that needs to be snapshotted and so it must be listed correctly here. If necessary, use the tools 'vgdisplay' and 'lvdisplay' and 'mount' to discover what volume groups and physical volumes exist and where they are mounted. Also check /etc/fstab, which usually contains the the names of any volume groups and physical volumes that are mounted at boot time.

One last setting to consider is the backup size limit. This is expressed as a multiple of the size of the actual database, as follows: If the database is 10GB and the size limit is "1.5", then only 15GB of backups (which is really a 10GB database replica plus 5GB of diffs) will be stored. The minimum allowed setting is "1.25". If a snapshot is attempted and the backup directory is over the size limit, old snapshots will automatically be removed until the directory is within the limit. Snapshots are removed in order of age, oldest first. However, there are two internal constraints that allow the backup size limit to be exceeded. First, any snapshot less than 24 hours old will not be automatically removed, and second, a minimum of two snapshots must be kept. So even if the size limit is exceeded, snapshots will run without removing old snapshots if there are either less than two or if the snapshots to be removed are less than 24 hours old.

Taking Snapshots

To take a snapshot, just run the db_snapshot executable. The first time you run this, it has to build a replica of your database structure and will take much longer than on subsequent runs. It will create the backup files by default in /var/lib/mysql_recovery but the directory can be elsewhere if needed.

The first run will also take up a lot of space because this initial replica is the same size as your original database directory. Subsequent updates store all changes as compressed diffs, and in general a large number of additional database restore points can be stored without using up much additional storage. This depends on how quickly the data in your database turns over.

Typical output for db_snapshot looks like this:

root@sqlmaster bin # ./db_snapshot
current database size in MB = 20
current backup dir size in MB = 20

Checking to make sure database snapshots do not exceed 1.5 times the
size of the database (limit set in /cluster/db_snapshots.conf configuration file)
and removing old snapshots as needed to create space.

Calling mylvmbackup to freeze mysql and snapshot it...

20091218 12:28:34 Info: Connecting to database...
20091218 12:28:34 Info: Flushing tables with read lock...
20091218 12:28:34 Info: Taking position record into /tmp/mylvmbackup-backup-20091218_122834_mysql-iXpRiS.pos...
20091218 12:28:34 Info: Running: lvcreate -s --size=512M --name=LogVol00_snapshot /dev/VolGroup01/LogVol00
  Logical volume "LogVol00_snapshot" created
20091218 12:28:35 Info: DONE: taking LVM snapshot
20091218 12:28:35 Info: Unlocking tables...
20091218 12:28:35 Info: Disconnecting from database...
20091218 12:28:35 Info: Mounting snapshot...
20091218 12:28:35 Info: Running: mount -o rw /dev/VolGroup01/LogVol00_snapshot /var/tmp/mylvmbackup/mnt/backup
20091218 12:28:36 Info: DONE: mount snapshot
20091218 12:28:36 Info: Copying /tmp/mylvmbackup-backup-20091218_122834_mysql-iXpRiS.pos to /var/tmp/mylvmbackup/mnt/backup-pos/backup-20091218_122834_mysql.pos...
20091218 12:28:36 Info: Copying /etc/my.cnf to /var/tmp/mylvmbackup/mnt/backup-pos/backup-20091218_122834_mysql_my.cnf...
20091218 12:28:36 Info: Archiving with rdiff-backup to /var/lib/mysql_recovery
20091218 12:28:36 Info: Running: rdiff-backup -b --force --print-statistics /var/tmp/mylvmbackup/mnt/backup/var/lib/mysql /var/lib/mysql_recovery/
--------------[ Session statistics ]--------------
StartTime 1261157317.00 (Fri Dec 18 12:28:37 2009)
EndTime 1261157317.45 (Fri Dec 18 12:28:37 2009)
ElapsedTime 0.45 (0.45 seconds)
SourceFiles 58
SourceFileSize 21673160 (20.7 MB)
MirrorFiles 58
MirrorFileSize 21673160 (20.7 MB)
NewFiles 0
NewFileSize 0 (0 bytes)
DeletedFiles 0
DeletedFileSize 0 (0 bytes)
ChangedFiles 0
ChangedSourceSize 0 (0 bytes)
ChangedMirrorSize 0 (0 bytes)
IncrementFiles 0
IncrementFileSize 0 (0 bytes)
TotalDestinationSizeChange 0 (0 bytes)
Errors 0
--------------------------------------------------
20091218 12:28:37 Info: DONE: create rdiff-backup archive
20091218 12:28:37 Info: Cleaning up...
20091218 12:28:37 Info: Running: umount /var/tmp/mylvmbackup/mnt/backup
20091218 12:28:37 Info: DONE: Unmounting /var/tmp/mylvmbackup/mnt/backup
20091218 12:28:37 Info: LVM Usage stats:
20091218 12:28:37 Info:   LV                VG         Attr   LSize   Origin   Snap%  Move Log Copy%  Convert
20091218 12:28:37 Info:   LogVol00_snapshot VolGroup01 swi-a- 512.00M LogVol00   0.57
20091218 12:28:37 Info: Running: lvremove -f /dev/VolGroup01/LogVol00_snapshot
  Logical volume "LogVol00_snapshot" successfully removed
20091218 12:28:37 Info: DONE: Removing snapshot

Done


2 restore points currently available (run /cluster/bin/db_restore to use them)

Usually, a db_snapshot run takes less than a minute on a database with relatively few changes since the last snapshot. This holds true for database sizes from a few MB to 10 GB. The number of changes and the speed of the CPU's are bigger factors than database size.

Exclusions

There are no exclusions in a database snapshot. Everything contained in the database directory will be included in the snapshot and is recoverable, even if it is not strictly a part of the database.

Automatically Creating Restore Points

Database restore points can be created automatically by scheduling snapshots in a cron job. For example, one could implement this by running:

crontab -e

and adding a line like this:

1 2 * * * /cluser/bin/db_snapshot

In this example, cron would then run db_snapshot on the first minute of the second hour of every day (2:01 AM). After a month, this system administrator would have 30 database restore points to "roll back" to if needed. The restore points contain all files that have since been modified, including deleted files.

Rolling back to a Restore Point

To use a restore point, there is a separate tool: /cluster/bin/db_restore. Running this tool will provide you with a list of available restore points, and ask you which, if any, you would like to revert to. Pressing Control-C will exit without reverting to any restore point. After choosing a restore point, you must type 'yes' to confirm that you want to restore your database to an earlier time:

root@sqlmaster bin # ./db_restore

Backups currently available:

Restore point 1: 12-11-2009 : at 09 hours, 19 min, and 18 seconds. ( 7 days, 4 hrs, 23 minutes ago)
Restore point 2: 12-18-2009 : at 12 hours, 28 min, and 37 seconds. ( 0 days, 1 hrs, 14 minutes ago)

The current server time is Fri Dec 18 13:42:48 EST 2009

 Enter the number of the restore point to recover to (or press CTRL-C to exit): 2

 ARE YOU SURE? THIS WILL REVERT THE DATABASE TO THIS POINT IN TIME!

 Changes since then will be lost. It is recommended that you make a
 copy of the database directory in case you change your mind later.

To continue and revert the database to a prior point in time, type 'yes' : yes

Confirmed. Restore will proceed:
Stopping services:
Shutting down System Monitoring daemon (mon):              [  OK  ]
Stopping MySQL:                                            [  OK  ]
Restoring...
Restore complete.
Starting services:
Starting MySQL:                                            [  OK  ]
Starting System Monitoring daemon (mon):                   [  OK  ]

The time required for a database restore to complete is entirely dependent on the size of the database and the extent of the changes that are being rolled back. A small number of changes will usually finish very quickly. This is an excellent way to recover from unwanted database changes.

rapidscaleclusters.com