快照和复制技术的结合可以保证我们得到一个实时的在线MySQL备份解决方案 当主库发生误操作时，只需要恢复备库上的快照，然后再根据binlog执行point-in-time的恢复即可 。下面假定一个场景：主从架构，没有延迟，某DBA误操作：drop database 接下来我们按照以上场景进行备份恢复模拟测试。
1.主库准备测试数据
mysql>createdatabasecnfol;
Query OK, 1 row affected (0.00 sec)
mysql> createtablecnfol.t (idintprimarykey);
Query OK, 0 rowsaffected (0.02 sec)
mysql> insertintocnfol.tselect1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insertintocnfol.tselect2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
到备库确认：
mysql> show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| cnfol |
| mysql |
| test |
+--------------------+
4 rowsinset(0.00 sec)
mysql> select*fromcnfol.t;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rowsinset(0.00 sec)
2.加个全局读锁 在备库
mysql> flush tableswithreadlock;
Query OK, 0 rowsaffected (0.00 sec)
3.为备库所在分区创建快照
[root@localhost ~]# lvcreate--size 1G --snapshot --name backup_mysql /dev/vg/mysql
Logical volume "backup_mysql"created
[root@localhost ~]# lvs
LV VG Attr LSize Origin snap% MoveLog Copy%Convert
backup_mysql vg swi-a- 1.00G mysql 0.00
mysql vg owi-ao 2.00G
4.获取二进制日志坐标
在备库：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 727 | | |
+------------------+----------+--------------+------------------+
1 row inset(0.00 sec)
解锁 在备库：
mysql> unlock tables;
Query OK, 0 rowsaffected (0.00 sec)
5.挂载快照
[root@localhost ~]# mount /dev/vg/backup_mysql /mnt/backup
[root@localhost ~]# cd /mnt/backup/mysql/data/cnfol/ && ls -alh
总计 32K
drwx------ 2 mysql dba 4.0K 10-14 09:57 .
drwx------ 5 mysql dba 4.0K 10-14 09:57 ..
-rw-rw---- 1 mysql dba 61 10-14 09:57 db.opt
-rw-rw---- 1 mysql dba 8.4K 10-14 09:57 t.frm
-rw-rw---- 1 mysql dba 14 10-14 09:57 t.MYD
-rw-rw---- 1 mysql dba 2.0K 10-14 10:06 t.MYI
6.主库某无经验DBA误操作
mysql>dropdatabasecnfol;
Query OK, 1 row affected (0.05 sec)
记录下此时时间：2013-10-14 10:17:10
备库确认是否存在库cnfol:
mysql> show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rowsinset(0.01 sec)
7.备份快照
[root@localhost backup]# pwd
/mnt/backup
[root@localhost backup]# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *
这里做备份的原因有2点 * 其一，昂贵的IO，因为磁头要在快照区和系统区来回跑 * 其二，快照区空间不足，因为是COW原理
8.删除快照
[root@localhost ~]# umount /mnt/backup
[root@localhost ~]# lvremove --force /dev/vg/backup_mysql
Logical volume "backup_mysql"successfully removed
9.格式化备库所在分区
[mysql@localhost ~]$ mysqladmin -uroot -poracle shutdown
131014 10:32:40 mysqld_safe mysqld frompid file /mnt/lvm/mysql/data/localhost.localdomain.pid ended
[1]+ Done mysqld_safe
[root@localhost ~]# umount /mnt/lvm
[root@localhost ~]# mkfs -t ext3 /dev/vg/mysql
[root@localhost ~]# mount /dev/vg/mysql /mnt/lvm
[root@localhost ~]# lvs
LV VG Attr LSize Origin snap% MoveLog Copy%Convert
mysql vg -wi-ao 2.00G
[root@localhost ~]# vgs
VG #PV #LV #SN Attr VSize VFree
vg 4 1 0 wz--n- 3.81G 1.81G
10.解压缩快照到备库所在分区
# tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm/
[root@localhost lvm]# pwd
/mnt/lvm
[root@localhost lvm]# ls
lost+found mysql
11.启动MySQL
12.利用binlog执行point-in-time恢复
[mysql@localhost ~]$ mysqlbinlog--stop-datetime="2013-10-14 10:17:10" /mnt/lvm/mysql/data/mysql-bin.000003 | mysql -uroot -poracle
13.确认数据
mysql> show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| cnfol |
| mysql |
| test |
+--------------------+
4 rowsinset(0.00 sec)
mysql> select*fromcnfol.t;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rowsinset(0.00 sec)
【编辑推荐】
【责任编辑：庞桂玉 TEL：(010)68476606】
点赞 0