MySQL备份和同步时使用LVM

If someone asks me about MySQL Backup advice my first question would 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 would work as well.

如果有人问我关于MySQL备份的建议，我会先问操作系统是否安装了LVM或者其他相似功能的软件。Solaris下的Veritas文件系统也可以做到同样的功能。大多数SAN系统也可以。

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 ?

为什么基于MySQL快照的备份很好？

There are number of reasons:

原因如下几点：

Almost Hot backup In 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.

几乎是热备 在大多数情况下，可以在应用程序仍在运行的时候执行备份。无需关机，只需设置为只读或者类似只读的限制。

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

支持所有基于本地磁盘的存储引擎 它支持MyISAM, Innodb, BDB，还支持 Solid, PrimeXT 和 Falcon。

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

快速备份 只需拷贝二进制格式的文件，在速度方面无以匹敌。

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

低开销 只是文件拷贝，因此对服务器的开销很细微。

Easy to Integrate Do 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.

容易保持完整性 想要压缩备份文件吗？把它们备份到磁带上，FTP或者网络备份软件 -- 十分简单，因为只需要拷贝文件即可。

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

快速恢复 恢复的时间和标准的MySQL崩溃恢复或数据拷贝回去那么快，甚至可能更快，将来会更快。

Free No extra commercial tools as Innodb Hot Backup are required to perform backup.

免费 无需额外的商业软件，只需Innodb热备工具来执行备份。

Are there any downsides ?

有什么缺点吗？

Need to have snapshot campatibility - this is obvious one.

需要兼容快照 -- 这是明显的。

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

需要超级用户(root) 在某些组织，DBA和系统管理员来自不同部门不同的人，因此权限各不一样。

Hard to predict downtime I 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 LOCK may take quite a while to complete on systems with long queries.

停工时间无法预计 我提到的这个方法通常指热备，但是谁也无法预料到底是不是热备 -- FLUSH TABLES WITH READ LOCK 可能会需要执行很长时间才能完成。

Problems with data on multiple volumes If you have logs on separate devices or just your database spanning across multiple volumes you will be in trouble as you will not get consistent snapshot across all the database. Some systems may be able to do atomic snapshot of many volumes.

多卷上的数据问题 如果你把日志放在独立的设备上或者你的数据库分布在多个卷上，这就比较麻烦了，因为无法得到全部数据库的一致性快照。不过有些系统可能能自动做到多卷快照。

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-mirroring. 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.

现在讲讲LVM和通常情况下的快照。确实，它们的实现的方式不同，但是目的都是让卷和存储卷创建的数据保持一致。LVM是边写边读。设备中专门分配了一个专用的区域用于保存老版本的内存页变化情况。你可以把它想象成为一个简单的版本管理形式，就像Innodb。另一方面，快照实现的方式就像三重镜像。如果你有RAID1卷，不过数据拷贝的分数是3而不是2。因此可以把一个设备从镜像中去掉，把它作为数据快照，并且还能保持数据安全。

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.

快照有2种方式 -- 一种是只读另一种是可读写。如果你只须拷贝数据，那么只读快照看起来不错，不过可读写快照则有好几个优点。首先是无需额外处理日至文件系统 -- 你可以快照上简单地实现日志恢复。只读快照则必须保证文件系统在开始快照之前就得和设备同步，因此需要日志重现。

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 corrupted is very nasty problem you want to avoid.

可读写快照的另一个好处是可以启动MySQL服务器，执行恢复、检察数据表或者其他任何要保证备份一致性所需的操作。必须避免备份已经损坏的数据库。

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

现在让我们来看看Linux上用LVM2备份MySQL数据库(或者它的slave)所需执行的操作：

1) Connect to MySQL and run FLUSH 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) 连接到MySQL上，运行 FLUSH TABLES WITH READ LOCK
注意 -- 如果你当前正在执行一个较长时间的查询，那么这个命令可能需要较长时间才能完成。在这里 FLUSH TABLES WITH READ LOCK 需要捕获所有的语句都完成了，甚至是 SELECT。因此如果有较长时间的查询时要小心。如果你只使用到Innodb表，就无需同步二进制日志可直接略过这个步骤。

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

2) 保持连接，运行 lvcreate -L16G -s -n dbbackup /dev/Main/Data -- 它会创建本地卷 Main/Data 的快照，命名为 dbbackup。备份过程中务必指定足够大的撤销空间用于保存发生变化的东西 -- 我指定了16GB。如果撤销空间不够大，快照就会无效并且备份就终止了。

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 running modprobe dm-snapshot

在这个步骤中，你有时候会碰到错误，最近我经常碰到的一个是：snapshot: Required device-mapper target(s) not detected in your kernel -- 意思是内核默认没有加载快照模块，需要你自己加载，运行命令 modprobe dm-snapshot 即可。

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

3) 现在已经创建完本地逻辑卷，可以释放表锁了，不过在这之前，需要记录一下二进制日志的位置，运行 SHOW MASTER STATUS 可以看到 -- 这个位置在MySQL slave上创建快照时需要用到。。

4) Snapshot created, now you want to let MySQL Server to continue, which is done by running UNLOCK TABLES or simply closing connection.

4) 快照创建完毕，运行 UNLOCK TABLES 释放锁或者关闭连接，让MySQL服务器继续运行。

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

5) 挂载备份文件系统：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) 拷贝备份数据。通常备份时可以略过慢查询日志和错误日志。也可以略过大部分的二进制日志 -- 然而如果有些slave远远落后于master的话，就必须保留所需的二进制日志了，或者你可以假设这种情况下在slave上从备份上恢复数据也可以忽略掉二进制日志。

7) Unmount filesystem umount /mnt/backup

7) 卸载文件系统：umount /mnt/backup

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

8) 删除快照：lvremove -f /dev/Main/dbbackup

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

如果你想创建基于slave的快照，就需要多做2个步骤。

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

9) 提取/拷贝数据库到slave的数据库目录下。

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

10) 启动MySQL服务器，等待执行恢复。

11) Use CHANGE MASTER TO to point slave to saved binary log position:

11) 用 CHANGE MASTER TO 告诉slave要保存的二进制日志位置:

PLAIN TEXT
SQL:

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

12) Run SLAVE START to restart replication.

12) 运行 SLAVE START 重启复制。

With slightly modified process you can clone slaves from the slaves without stopping them - you just need to use SHOW SLAVE STATUS instead 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 could have accomulated - especially if you use slave_skip_errors or sql_slave_skip_counter. Cloning master you're starting from consistent copy.

使用稍微修改过的进程，就能无需停止slave的情况下克隆它 -- 运行 SHOW SLAVE STATUS 而不是 SHOW MASTER STATUS，找出合适的二进制日志位置。不过要小心 -- 克隆slave的时候也会把它积累的不一致的数据也克隆了 -- 尤其是使用 slave_skip_errors 或 sql_slave_skip_counter时。克隆master就可以拷贝保持一致的数据。

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

如果你对上面的过程有兴趣，可以试试 Lenz Grimmer 的 mylvmbackup。