快照和复制技术的结合可以保证我们得到一个实时的在线MySQL备份解决方案，当主库发生误操作时，只需要恢复备库上的快照，然后再根据binlog执行point-in-time的恢复即可。
下面假定一个场景：
主从架构，没有延迟，某DBA误操作：drop database
接下来我们按照以上场景进行备份恢复模拟测试
⑴ 主库准备测试数据
mysql> create database cnfol; Query OK, 1 row affected (0.00 sec) mysql> create table cnfol.t (id int primary key); Query OK, 0 rows affected (0.02 sec) mysql> insert into cnfol.t select 1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into cnfol.t select 2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0到备库确认：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cnfol | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> select * from cnfol.t; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)⑵ 加个全局读锁
在备库：
mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)⑶ 为备库所在分区创建快照
[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% Move Log Copy% Convert backup_mysql vg swi-a- 1.00G mysql 0.00 mysql vg owi-ao 2.00G⑷ 获取二进制日志坐标
在备库：
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 727 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)⑸ 解锁
在备库：
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)⑹ 挂载快照
[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⑺ 主库某无经验DBA误操作
mysql> drop database cnfol; Query OK, 1 row affected (0.05 sec)记录下此时时间：2013-10-14 10:17:10
备库确认是否存在库cnfol:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.01 sec)⑻ 备份快照
[root@localhost backup]# pwd /mnt/backup [root@localhost backup]# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *这里做备份的原因有2点
其一，昂贵的IO，因为磁头要在快照区和系统区来回跑
其二，快照区空间不足，因为是COW原理
⑼ 删除快照
[root@localhost ~]# umount /mnt/backup [root@localhost ~]# lvremove --force /dev/vg/backup_mysql Logical volume "backup_mysql" successfully removed⑽ 格式化备库所在分区
[mysql@localhost ~]$ mysqladmin -uroot -pOracle shutdown 131014 10:32:40 mysqld_safe mysqld from pid 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% Move Log 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⑾ 解压缩快照到备库所在分区
# tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm/ [root@localhost lvm]# pwd /mnt/lvm [root@localhost lvm]# ls lost+found mysql⑿ 启动MySQL
⒀ 利用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⒁ 确认数据
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cnfol | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> select * from cnfol.t; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx