MySQL实时在线备份恢复方案

港控/mmm° 2021-07-26 17:53 422阅读 0赞

![1803089889.png][]

开源Linux

长按二维码加关注~

![undefined][]

![format_png][]

上一篇：[2020年MySQL数据库面试题总结][2020_MySQL]

快照和复制技术的结合可以保证我们得到一个实时的在线MySQL备份解决方案。

当主库发生误操作时，只需要恢复备库上的快照，然后再根据binlog执行point-in-time的恢复即可。

下面假定一个场景：  
主从架构，没有延迟，某DBA误操作：drop database  
接下来我们按照以上场景进行备份恢复模拟测试

**1.主库准备测试数据**

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

到备库确认：

**2.加个全局读锁**  
在备库

mysql> flush tables with read lock;
    Query OK, 0 rows affected (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%  Move Log Copy%  Convert
      backup_mysql vg   swi-a- 1.00G mysql    0.00                        
      mysql        vg   owi-ao 2.00G

**4.获取二进制日志坐标**

**解锁**  
在备库：

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

**6.挂载快照**

[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

**7.主库某无经验DBA误操作**

mysql> drop database cnfol;
    Query OK, 1 row affected (0.05 sec)

记录下此时时间：2013-10-14 10:17:10

备库确认是否存在库cnfol:

**8.备份快照**

[root@localhost backup]# pwd
    /mnt/backup
    [root@localhost backup]# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *

这里做备份的原因有2点  
\* 其一，昂贵的IO，因为磁头要在快照区和系统区来回跑  
\* 其二，快照区空间不足，因为是COW原理

**9.删除快照**

[root@localhost ~]# umount /mnt/backup
    [root@localhost ~]# lvremove --force /dev/vg/backup_mysql 
    Logical volume "backup_mysql" successfully removed

**10.格式化备库所在分区**

[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

**11.解压缩快照到备库所在分区**

# tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm/
    [root@localhost lvm]# pwd
    /mnt/lvm
    
    
    [root@localhost lvm]# ls
    lost+found  mysql

**12.启动MySQL**

**13.利用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

**14.确认数据**

原文链接：http://www.jh-floor.com/shujuku/Mariadb/10070.html  
作者：jh-floor

**- End -**

![format_png 1][]

[1803089889.png]: /images/20210726/c13e41e538904366bf59128b4e4de52b.png
[undefined]: /images/20210726/0822c4720dd646ae85d20888bb8f101b.png
[format_png]: /images/20210726/af3f00b582564f8f86119b9bd37532d6.png
[2020_MySQL]: http://mp.weixin.qq.com/s?__biz=MzI3NzQ4MTE4Mw%3D%3D&chksm=eb673242dc10bb544b87ca688c1b0fcfe73b853cb211b7bf1ea8aaffe6bad4c92114214f1c9d&idx=2&mid=2247492082&scene=21&sn=0d10bb71ff4b53cb516f774d0cf3f7b3#wechat_redirect
[format_png 1]: /images/20210726/13a70cd1badc406abe8892290f7afefe.png