MySQL实时在线备份恢复方案:Replication+LVM Snapsho

快照和复制技术的结合可以保证我们得到一个实时的在线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)  

原文出处:jh-floor -> http://www.jh-floor.com/shujuku/Mariadb/10070.html

相关日志

最近评论

Archives