# MySQL备份和同步时使用LVM

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

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 ?

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.

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.

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.

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

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.

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.

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.

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.

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.

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

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

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

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

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


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.

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

## 评论

### 1) Connect to MySQL and run

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

• 本文已收录于以下专栏：

## MySQL备份和还原（四）--使用LVM快照备份

LVM：几乎热备   快照：snapshot  LVM机制还提供了对LV做快照的功能，以此来获得文件系统的状态一致性备份。LVM采用写时复制技术（Copy-On-Write，COW），不用停止服...

## Mysql学习第二课 tar、lvm、mysqldump全备份

20160830mysqlday2 # 复习 1.什么是mysql？ 1） 2） 3） 4） 5） 6） 7） 2.存储引擎的概念 3.知名的两大存储引擎MYISAM和INNODB的区别 事务 锁机制...

## LVM快照备份恢复mysql

LVM快照备份恢复mysql 使用lvm备份要满足 1.所有的innodb文件（数据文件和日志文件）必须在单个逻辑卷（lvm不能为多个lv同一时间做一致性快照） 2.卷组中必须有足够的空间来...

## MySQL实时在线备份恢复方案：Replication+LVM Snapshot【上篇】

LVM Snapshot和MySQL Replication结合实时在线备份方案步骤简介

## MySQL数据库的主从同步备份在windows下实现

• 2013年10月09日 14:01
• 33KB
• 下载

举报原因： 您举报文章：MySQL备份和同步时使用LVM 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)