Databases Reference
In-Depth Information
mysql vg owi-ao 225.00G
tmp vg -wi-ao 10.00G
var vg -wi-ao 10.00G
Notice that the snapshot's attributes are different from the original device's, and that
the display shows a little extra information: its origin and how much of the allocated
16 GB is currently being used for copy-on-write. It's a good idea to monitor this as you
make your backup, so you can see if the device is getting full and is about to fail. You
can monitor your device's status interactively, or with a monitoring system such as
Nagios:
#
watch 'lvs | grep backup'
As you saw from the output of
mount
earlier, the
mysql
volume contains a filesystem.
That means the snapshot volume does too, and you can mount and use it just like any
other filesystem:
#
mkdir /tmp/backup
#
mount /dev/mapper/vg-backup_mysql /tmp/backup
#
ls -l /tmp/backup/mysql
total 5336
-rw-r----- 1 mysql mysql 0 Nov 17 2006 columns_priv.MYD
-rw-r----- 1 mysql mysql 1024 Mar 24 2007 columns_priv.MYI
-rw-r----- 1 mysql mysql 8820 Mar 24 2007 columns_priv.frm
-rw-r----- 1 mysql mysql 10512 Jul 12 10:26 db.MYD
-rw-r----- 1 mysql mysql 4096 Jul 12 10:29 db.MYI
-rw-r----- 1 mysql mysql 9494 Mar 24 2007 db.frm
... omitted ...
This is just for practice, so we'll unmount and remove the snapshot now with the
lvremove
command:
#
umount /tmp/backup
#
rmdir /tmp/backup
#
lvremove --force /dev/vg/backup_mysql
Logical volume "backup_mysql" successfully removed
LVM snapshots for online backups
Now that you've seen how to create, mount, and remove snapshots, you can use them
to make backups. First, let's look at how to back up an InnoDB database without
stopping the MySQL server, but with a global read lock. Connect to the MySQL server
and flush the tables to disk with a global read lock, then get the binary log coordinates:
mysql>
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Record the output from
SHOW MASTER STATUS
, and make sure you keep the connection
to MySQL open so the lock doesn't get released. You can then take the LVM snapshot
and immediately release the read lock, either with
UNLOCK TABLES
or by closing the
connection. Finally, mount the snapshot and copy the files to the backup location.
The major problem with this approach is that it might take a while to get the read lock,
especially if there are long-running queries. All queries will be blocked while the
