MySQL 主从复制场景单表数据出错导致复制终止如何快速修复
场景描述:
如果从库上表 t 数据与主库不一致,导致复制错误,整个库的数据量很大,重做从库很慢,如何单独恢复这张表的数据?
通常认为是不能修复单表数据的,因为涉及到各表状态不一致的问题。
下面就列举备份单表恢复到从库会面临的问题以及解决办法
一、本次演示环境描述:
Dell物理服务器r620 两台
网络环境都是内网
master:192.168.1.220
slave:192.168.1.217
OS系统环境:centos7.8 X86_64位最小化安装,关闭iptables,关闭selinux
测试软件版本:mysql5.7.27二进制包
提前配置好基于Gtid的MySQL主从复制
创建模拟测试数据,模拟故障场景
修复MySQL主从复制
pt-table-checksum 校验修复后的MySQL主从复制数据是否一致
二、配置主从复制
MySQL的安装过程此处不再描述,自行百度
配置主从复制
给一个master机器配置一个新的slave的话,记得在mysqldump备份数据时加参数–set-gtid-purged=ON
知识补充:
1.常规备份是要加–set-gtid-purged=OFF解决备份时的警告
[root@localhost ~]# mysqldump -uroot -p’dXdjVF#(y3lt’ --set-gtid-purged=OFF --single-transaction -A -B |gzip > 2020-09-17.sql.gz
2.构建主从时做的备份,不需要加–set-gtid-purged=OFF 这个参数,而是要加–set-gtid-purged=ON
[root@localhost ~]# mysqldump -uroot -p’dXdjVF#(y3lt’ --set-gtid-purged=ON --single-transaction -A -B --master-data=2 |gzip > 2020-09-17.sql.gz
提示:
在构建主从复制是,千万不要OFF。在日常备份时,可以OFF。
–set-gtid-purged=AUTO,ON,OFF
1.–set-gtid-purged=OFF可以使用在日常备份参数中。
2.–set-gtid-purged=ON在构建主从复制环境时需要的参数配置
基于Gtid配置主从复制具体步骤如下:
master库:
** slave库操作:**
原因是slave机器配置my.cnf中配置了relay-log的存放路径,但是slave服务器实际不存在这个路径,导致的报错,把这目录新建出来,授权mysql权限,然后重新change master
主从复制配置完成。
三、准备测试数据并模拟故障
在master库上创建模拟演示表,已经定时器和存储过程,定时写入数据到测试表,方便下面主从复制故障恢复演示
创建测试表:
创建定时器,从当前时间1分钟后每一秒写入一条数据:
类似上面的方式再新建一个测试表txt,定时写入数据
slave库模拟故障:
然后在master库上再删除id=200的记录
master端操作: delete from txt where id=200;
此时slave库查看复制状态已经停止复制:
四、故障恢复
场景 1
如果复制报错后,没有使用跳过错误、复制过滤等方法修复主从复制。主库数据一直在更新,从库数据停滞在报错状态(假设GTID 为8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42083)。
修复步骤:
在主库上备份表test_event (假设备份快照 GTID 为 8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262);
恢复到从库;
启动复制。
这里的问题是复制起始位点是8a9fb9a3-f579-11ea-830d-90b11c12779c:42084,从库上表test_event 的数据状态是领先其他表的。
8a9fb9a3-f579-11ea-830d-90b11c12779c:42084-42262 这些事务中只要有修改表test_event数据的事务,就会导致复制报错 ,比如主键冲突、记录不存在(而8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42084这个之前复制报错的事务必定是修改表 t 的事务)
解决办法:启动复制时跳过8a9fb9a3-f579-11ea-830d-90b11c12779c:42084-42262 这些事务中修改表 t 的事务。
正确的修复步骤:
- 在主库上备份表test_event(备份快照 GTID 为 8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262),恢复到从库;
- 设置复制过滤,过滤表 t:
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (‘dbtest01.test_event’); - 启动复制,回放到8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262时停止复制(此时从库上所有表的数据都在同一状态,是一致的);
START SLAVE UNTIL SQL_AFTER_GTIDS = ‘8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262’; - 删除复制过滤,正常启动复制。
**注意事项:**这里要用 mysqldump --single-transaction --master-data=2,记录备份快照对应的 GTID
具体的详细步骤如下:
A.要在master库上dump出导致复制停止的表test_event:
B.获取出单独备份表的快照gtid值:
8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262
C.恢复此表到slave库上,由于GTID_EXECUTED不是空值,导致导入表test_event到slave库失败,具体报错如下:
slave库操作:
解决办法就是登陆slave库:
mysql> reset master;
这个操作可以将当前库的GTID_EXECUTED值置空
D.在线开启复制过滤:
** E.启动复制,回放到8a9fb9a3-f579-11ea-830d-90b11c12779c:42262时停止复制(此时从库上所有表的数据都在同一状态,是一致的)**
虽然此时SQL线程是no,但是复制不再报错:
F.在线关闭复制过滤:
G.开启slave复制SQL线程:
主从复制恢复:
**注意事项:**这里要用 mysqldump --single-transaction --master-data=2,记录备份快照对应的 GTID
五、校验主从数据一致性
采用校验工具pt-table-checksum来验证。具体如何安装使用参考下面的博文地址:
https://blog.51cto.com/wujianwei/2409523
发现主库的 test01.txt 这个表和slave中的test01.txt存在不一致。
原因是:刚才模拟演示,先在slave库上执行了删除动作
delete from txt where id=200;导致slave库表txt中比master库txt表少一条记录
**修复数据: **
再次校验,数据一致
六、附带共网友测试案例
附带案例二,基本和场景1是一样的。此处不再细说,留给感兴趣的网友们。
** 下面简单描述下场景和恢复方法:**
如果复制报错后,使用跳过错误、复制过滤等办法修复了主从复制。主、从库数据一直在更新。
错误的修复步骤:
在主库上备份表 t (假设备份快照 GTID为 aaaa:1-10000);
停止从库复制,GTID为 aaaa:1-20000;
恢复表 t 到从库;
启动复制。
原因分析:
这里的问题是复制起始位点是 aaaa:20001,aaaa:10000-20000 这些事务将不会在从库上回放,如果这里面有修改表 t 数据的事务,从库上将丢失这部分数据
解决办法:从备份开始到启动复制,锁定表 t,保证 aaaa:10000-20000 中没有修改表 t 的事务。
正确修复步骤:
对主库表 t 加读锁;
在主库上备份表 t;
停止从库复制,恢复表 t;
启动复制;
解锁表 t。
**建议的解决方法:**如果不想对表t进行加锁,可以直接把从库的复制暂停,然后采用场景一的方式进行恢复。这样就避免了锁表。