利用LVM快照备份mysql数据库

利用lvm的快照功能做mysql数据库备份，这种方法最大的好处是完全不影响mysql的运行，这是一种几乎热备的备份方式。一、步骤1、首先对数据库施加读锁2、记录二进

利用lvm的快照功能做mysql数据库备份，这种方法最大的好处是完全不影响mysql的运行，这是一种几乎热备的备份方式。

一、步骤
1、首先对数据库施加读锁
2、记录二进制日志文件的文件名和事件位置
3、创建快照卷
4、解锁数据库
5、挂载快照卷，复制数据文件
6、删除快照卷

二、准备工作

首先，要对LVM的快照有所了解（这篇博客写了LVM的快照），并将数据文件放在LVM上。

其次，在数据库中新建一张测试用的表

mysql> select * from data.info; +-----+-------+------+ | sid | name | age | +-----+-------+------+ | 1 | tom | 25 | | 2 | jerry | 26 | | 3 | jim | 40 | | 4 | ccr | 34 | | 5 | dss | 21 | | 6 | safe | 44 | +-----+-------+------+ 6 rows in set (0.00 sec)

三、开始备份

1. 首先对数据库施加读锁

mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

2. 记录二进制日志文件的文件名和事件位置

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 964 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

3. 创建快照卷

[root@mysql ~]# cat /etc/my.cnf [client] user=root password=mysql [mysqld] datadir=/mysql/data socket=/tmp/mysql.sock user=mysql [root@mysql ~]# df -Th /dev/mapper/myvg-mydata ext4 689M 128M 527M 20% /mysql #可以看到mysql的数据放在myvg下的mydata这个逻辑卷上[root@mysql ~]# lvcreate -n snap-data -L 100M -p r -s /dev/myvg/mydata Logical volume "snap-data" created #快照完成

4. 解锁数据库

mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

5. 挂载快照卷，复制数据文件

[root@mysql mnt]# mount -o ro /dev/myvg/snap-data /mnt/ [root@mysql mnt]# cd /mnt/ [root@mysql mnt]# ls data lost+found [root@mysql mnt]# tar -jcvf /root/mysql-backup-`date +%F`.tar.bz2 data/ [root@mysql mnt]# cd ~ [root@mysql ~]# ls anaconda-ks.cfg install.log install.log.syslog mysql-backup-2014-02-22.tar.bz2

6. 删除快照卷

[root@mysql ~]# umount /mnt/ [root@mysql ~]# lvremove /dev/myvg/snap-data Do you really want to remove active logical volume snap-data? [y/n]: y Logical volume "snap-data" successfully removed

这样就备份完成了。

三、恢复测试

1. 在新建的info表中再插入2行数据

mysql> insert into info(name,age) values('seven',30),('eight',49); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0

2. 备份上次lvm快照开始的position号以后的二进制文件

[root@mysql ~]# mysqlbinlog --start-position=964 /mysql/data/mysql-bin.000002 > /root/bak.sql

mysqlbinlog的用法：

  A.指定恢复时间     --start-date和--stop-date选项，格式“2014-2-22 16:33:04”

  B.指定恢复位置     --start-position和--stop-position选项

3. 删除所有文件

[root@mysql ~]# service mysqld stop [root@mysql ~]# rm /mysql/* -fr

4. 开始恢复（LVM完全备份的恢复）

[root@mysql ~]# tar -jxvf mysql-backup-2014-02-22.tar.bz2 -C /mysql/ [root@mysql ~]# service mysqld start Starting MySQL [ OK ] [root@mysql ~]# mysql mysql> select * from data.info; +-----+-------+------+ | sid | name | age | +-----+-------+------+ | 1 | tom | 25 | | 2 | jerry | 26 | | 3 | jim | 40 | | 4 | ccr | 34 | | 5 | dss | 21 | | 6 | safe | 44 | +-----+-------+------+ 6 rows in set (0.00 sec)

进行LVM快照后，我们新插入的数据没有恢复过来

5. 利用二进制文件基于时间点恢复（增量备份的恢复）

mysql> source /root/bak.sql #这是第2步从二进制文件中导出的脚本 mysql> select * from data.info; +-----+-------+------+ | sid | name | age | +-----+-------+------+ | 1 | tom | 25 | | 2 | jerry | 26 | | 3 | jim | 40 | | 4 | ccr | 34 | | 5 | dss | 21 | | 6 | safe | 44 | | 7 | seven | 30 | | 8 | eight | 49 | +-----+-------+------+ 8 rows in set (0.00 sec) #新插入的2行数据也恢复了

这样，完全备份+增量备份的备份和恢复就完成了。

附：

逻辑备份工具mysqldump介绍

-u                        #指定用户名
-p                        #指定用户密码
-h                        #指定主机地址
-A|--all-databases        #备份所有数据库
--databases               #备份指定数据库
--single-transcation      #基于此项可以实现对InnoDB表做热备份
--flush-logs              #备份之前刷新日志
--lock-all-tables         #执行备份时为所有表请求加锁
-E|--events               #备份事件调度器代码
-R|--routines             #备份存储过程和存储函数
--triggers                #备份触发器
--master-data=2           #该选项将会记录binlog的日志位置与文件名并追加到文件中，，如果为1将会输出CHANGE

#导出全库 mysqldump -u root -p --all-databases > dump.sql #导出单个库，或者单个库中的某个表 mysqldump --u root -p data > dump.sql mysqldump --u root -p data info > dump.sql #导出多个数据库 mysqldump --u root -p --databases data mysql > dump.sql

本文出自 “My favorite technology” 博客，请务必保留此出处