Use LVM during MySQL backup and Synchronization

Peter Zaitsev Source: www. mysqlperformanceblog. com20060821using-lvm-for-mysql-backup-and-replication-setup Translator: ye Jinrong (Email: IfsomeoneasksmeaboutMySQLBackupadvicemyfirstquestionwouldbeiftheyhaveL

Peter Zaitsev Source: http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup Translator: ye Jinrong (Email: If someone asks me about MySQL Backup advice my first question wocould be if they have L

Author: Peter Zaitsev
Source: http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup
Translator: ye Jinrong (Email:

If someone asks me about MySQL Backup advice my first question wocould be if they have LVM installed or have some systems with similar features set for other operation systems. veritas File System can do it for Solaris. most SAN systems wowould work as well.

If someone asks me for advice on MySQL backup, I will first ask if the operating system has installed LVM or other software with similar features. The Veritas File System under Solaris can also achieve the same function. Most SAN systems are also supported.

What is really needed is ability to create atomic snapshot of the volume, which can be later mounted same as original file system

Why snapshot based MySQL backups are great?

Why is MySQL snapshot-based backup good?

There are number of reasons:

The reasons are as follows:

Almost Hot backupIn most cases you can perform this type of backup while your application is running. No need to shut down server, make it read only or anything like it.

Almost hot standbyIn most cases, backup can be performed while the application is still running. No shutdown is required. You only need to set it to read-only or similar read-only restrictions.

Support for all local disk based storage enginesIt works with MyISAM and Innodb and BDB, It also shoshould work with Solid, PrimeXT and Falcon storage engines.

Supports all storage engines based on local disksIt supports MyISAM, Innodb, BDB, and Solid, PrimeXT, and Falcon.

Fast BackupYou simply do file copy in the binary form so it is hard to beat in speed.

Quick backupThe speed of copying binary files is unmatched.

Low OverheadIt is simply file copy so overhead to the server is minimal.

Low overheadIt is only a file copy, so the overhead of the server is very small.

Easy to IntegrateDo you want to compress backup? Backup it to tape, FTP or any network backup software-it is easy as you just need to copy files.

Easy to maintain integrityDo you want to compress the backup file? Back up them to tape, FTP or network backup software-very simple, because you only need to copy files.

Fast RecoveryRecovery time is as fast as putting data back and standard MySQL crash recovery, and it can be CED even further. More on this later.

Quick RecoveryRecovery Time and standard MySQL crash recovery or data copy back so fast, or even faster, will be faster in the future.

FreeNo extra inclucial tools as Innodb Hot Backup are required to perform backup.

FreeNo additional commercial software is required, and only Innodb Hot Backup tools are required for backup.

Are there any downsides?

Are there any disadvantages?

Need to have snapshot campatibility-This is obvious one.

Snapshot compatibility-- This is obvious.

May need root accessIn some organizations DBA and System Administrator are different people from different between mnents which might not like to trade access rights between each other.

Need Super User (root)In some organizations, the DBA and System Administrator come from different people in different departments, so their permissions are different.

Hard to predict downtimeI mentioned this solution is often hot backup, but bad thing it is hard to estimate when it is hot and when it is not-FLUSH TABLES WITH READ LOCKMay take quite a while to complete on systems with long queries.

Unavailable timeThe method I mentioned usually refers to hot backup, but no one can predict whether it is hot backup --FLUSH TABLES WITH READ LOCKThe execution may take a long time to complete.

Problems with data on multiple volumesIf you have logs on separate devices or just your database spanning internal SS multiple volumes you will be in trouble as you will not get consistent snapshot into SS all the database. some systems may be able to do atomic snapshot of specified volumes.

Data on multiple volumesIf you store logs on an independent device or your database is distributed on multiple volumes, this is troublesome because you cannot obtain consistent snapshots of all databases. However, some systems may automatically create multi-volume snapshots.

Lets speak a bit about how LVM and snapshotting in general works. really there are different implementations but the sake of them is to provide you with volume which consistently matches state of the volume at the time storage is created. in LVM it is implementeed as copy on write. special storage area allocated on device where old version of changed pages are stored. you can think about it as about simplified form of versioning like in Innodb if it is closer to you. in other cases snapshot may be implemented by tripple-processing ing. ie you have RAID1 volume but there are 3 copies of data rather than 2. so you can move one devices out of mirror and use it as snapshot while still having your data safe and secure.

Now let's talk about LVM and general snapshots. Indeed, they are implemented in different ways, but the goal is to keep the data created by the volume and the storage volume consistent. LVM reads and writes data simultaneously. A dedicated area is assigned to the device to save the Memory Page changes of the old version. You can think of it as a simple version management form, just like Innodb. On the other hand, snapshot implementation is like a triple image. If you have RAID1, but the data copy score is 3 instead of 2. Therefore, you can remove a device from the image as a data snapshot and maintain data security.

There are two types of snapshots-some of them are read-only while others can be read-write. read-only snapshots may sound good enough as you're only going to read data anyway, but in reality read-write snapshots have number of benefits. first no extra handling is needed for journaling file sytems-you can simply do journal recovery on snapshot. with read-only snapshot you need to make sure filesystem synchronizes device before snapshot is taken so no journal replay is needed.

Two snapshot methods are available: Read-only and read-write. If you only need to copy data, the read-only snapshot looks good, but the read/write snapshot has several advantages. First, you do not need to process logs to the file system. You can easily restore logs from snapshots. Read-only snapshots must ensure that the file system must be synchronized with the device before the snapshot starts. Therefore, the log must be reproduced.

The other benefit of read-write snapshot is you can actually start MySQL Server on it and perform recovery, check tables or do whatever else you might need to do to ensure your backup is consistent. backing up database which was already specified upted is very nasty problem you want to avoid.

Another benefit of reading and writing snapshots is that you can start the MySQL server and perform operations to restore, check data tables, or any other operation required to ensure backup consistency. You must avoid backing up corrupted databases.

Let's now see what exactly you need to do to perform backup of MySQL Database (or create slave) using LVM2 on Linux.

Now let's take a look at the operations required to back up the MySQL database (or its slave) with LVM2 in Linux:

1) Connect to MySQL and runFLUSH TABLES WITH READ LOCK
Note-this command may take a while to complete if you have long running queries. the catch here is flush tables with read lock actually waits for all statements to complete, even selects. so be careful if you have any long running queries. if you're using only Innodb tables and do not need to synchronize binary log position with backup you can skip this step.

1) connect to MySQL and runFLUSH TABLES WITH READ LOCK
Note: If you are currently executing a long query, this command may take a long time to complete. Here, flush tables with read lock needs to capture all the statements, even SELECT. Therefore, be careful when querying for a long time. If you only use the Innodb table, skip this step without synchronizing binary logs.

2) While holding connection open run:Lvcreate-L16G-s-n dbbackup/dev/Main/Data-This will create snapshot namedDbbackupFor Logical VolumeMain/Data. You shoshould specify enough of undo space to hold modifications during backup process-I 've specified 16 GB in this case. if your undo size is not large enough snapshot will get invalidated and backup will be aborted.

2) Keep the connection and runLvcreate-L16G-s-n dbbackup/dev/Main/Data-- It will create a local volumeMain/DataIs namedDbbackup. During the backup process, you must specify a large enough undo space to save the changed things-I specified 16 GB. If the Undo space is not large enough, the snapshot will be invalid and the backup will be terminated.

Sometimes you might run into the errors on this step, The most common one I 've resently seen is:Snapshot: Required device-mapper target (s) not detected in your kernel-This means snapshot module is not loaded in your kernel by default and you need to load it, which is done by runningModprobe dm-snapshot

In this step, you may encounter errors sometimes. Recently, I often encounter the following errors:Snapshot: Required device-mapper target (s) not detected in your kernel-- It means that the kernel does not load the snapshot module by default. You need to load it yourself and run the commandModprobe dm-snapshotYou can.

3) Now you have created logical volume and can unlock the tables, but before that you shoshould probably record binary log position which is done by runningSHOW MASTER STATUS-This is binary log position you'll need to point your MySQL Slaves created from this snapshot.

3) now the local logical volume has been created and the table lock can be released. However, before that, you need to record the location of the binary log and runSHOW MASTER STATUSWe can see that this location is required when creating a snapshot on the MySQL slave ..

4) Snapshot created, now you want to let MySQL Server to continue, which is done by runningUNLOCK TABLESOr simply closing connection.

4) after the snapshot is created, runUNLOCK TABLESRelease the lock or close the connection so that the MySQL server can continue to run.

5) Mount backup Filesystem:Mount/dev/Main/dbbackup/mnt/backup

5) mount the backup file system:Mount/dev/Main/dbbackup/mnt/backup

6) Copy data to backup. normally you can skip slow query logs and error log while taking backup. you also can skip most of binary logs-however if some of your slaves are far behind you might want to keep some of last binary logs just in case, or you can assume in case of recovery from the backup you will need to restore slaves as well and skip binary logs in your backup process.

6) copy the backup data. Generally, you can query logs and error logs slowly during Backup. You can also skip most of the binary logs. However, if some slave lags far behind the master, you must keep the required binary logs, alternatively, you can assume that in this case, you can recover data from the backup on the slave or ignore the binary log.

7) Unmount filesystemUmount/mnt/backup

7) uninstall the file system:Umount/mnt/backup

8) Remove snapshot:Lvremove-f/dev/Main/dbbackup

8) delete a snapshot:Lvremove-f/dev/Main/dbbackup

If you want to create slave based on such snapshot you need to perform couple of more simple steps

If you want to create a slave-based snapshot, You need to perform two more steps.

9) Extract/Copy database to the slave database directory.

9) extract/copy the database to the database directory of slave.

10) Start MySQL Server. Wait for it to perform recovery.

10) Start the MySQL server and wait for restoration.

11) UseCHANGE MASTERTo point slave to saved binary log position:

11) UseCHANGE MASTERTell slave the location of the binary log to be saved:

PLAIN TEXT
SQL:

CHANGEmasterTOmaster_host="master", master_user="user", master_password="password", master_log_file="host-bin.000335", master_log_pos=401934686;

12) RunSLAVE STARTTo restart replication.

12) RunSLAVE STARTRestart replication.

With slightly modified process you can clone slaves from the slaves without stopping them-you just need to useSHOW SLAVE STATUSInstead of show master status to find out appropriate binary log position. Be careful however-cloning slave from the slave also clones inconsistences in data which slave cocould have accomulated-especially if you useSlave_skip_errorsOrSQL _slave_skip_counter. Cloning master you're starting from consistent copy.

With a slightly modified process, you can clone it without stopping the slave-runSHOW SLAVE STATUSInsteadSHOW MASTER STATUSTo locate the appropriate binary log location. But be careful-When cloning slave, the inconsistent data accumulated by slave will also be cloned-especially when usingSlave_skip_errorsOrSQL _slave_skip_counter. Clone the master to copy data that remains consistent.

If you're interested in ready script you can try mylvmbackup by Lenz Grimmer

If you are interested in the above process, try Lenz Grimmer's mylvmbackup.