If your MySQL data lives on an LVM volume, using an LVM snapshot is a great low-impact method of getting a point-in-time backup of your entire database. The only real gotcha is that snapshot must be made while the data files are in a "safe" state (meaning that all of the data has been written to file, and not only present in memory). Here are the basic steps needed to get a backups
- Connect to MySQL and flush your tables to disk and lock them so nothing new can be written to memory with
FLUSH TABLES WITH READ LOCK
Do not do this with mysqladmin, and be sure to leave your database session open, otherwise the read lock will be removed as soon the the client disconnects.
- In another terminal session, create the LVM snapshot of the filesystem where your MySQL data resides. For this example, we will assume that /var/lib/mysql is its own volume at /dev/vg0/mysql. Make sure to make this snapshot big enough so that there is enough room to accommodate new data coming in to the database as we perform the backup
lvcreate -L8G -s -n mysql-backup /dev/vg0/mysql
- Back at your open MySQL connection, release the table lock so that normal database operation can resume
- Mount the LVM snapshot somewhere convenient
mkdir -p /mnt/mysql-backup mount -o nouuid -t xfs /dev/vg0/mysql-backup /mnt/mysql-backup
If you look at the contents of /mnt/mysql-backup now, you should see a replica of /var/lib/mysql exactly as it appeared when the snapshot was created.
- Copy the entire directory to a location of your choice, using the method of your choice. For example
tar -C /mnt -czf ~/mysql-backup.tar.gz mysql-backup
- Once the backup is done, unmount and remove the snapshot
umount /mnt/mysql-backup lvremove -f /dev/vg0/mysql-backup
As mentioned above, the database connection must remain open while the snapshot is created in order for the lock to be maintained. The only way to script this is to make use of a language that supports database connections. Here is a perl example.
#!/usr/bin/perl use DBI; # Connect to the local database $dbh = DBI->connect('DBI:mysql:host=localhost;database=mysql', 'root', 'password') || die; # Flush and lock tables to prepare for LVM snapshot $dbh->do('FLUSH TABLES WITH READ LOCK;'); # Create LVM snapshot volume system('lvcreate -L8G -s -n mysqlbackup /dev/vg0/mysql'); # Release table lock $dbh->do('UNLOCK TABLES;'); # Disconnect from database $dbh->disconnect();