MySQL主从复制技术应用非常广泛，M-S复制架构、keepalived＋M-M复制架构、MHA等高可用架构都基于MySQL主从复制技术。主从复制是基于binlog的逻辑复制，实际应中,可能会因为各种原因出现主从数据不一致的情况,因此我们需要定期或不定期地开展主从复制数据一致性的校验和修复工作;
如果发生了mysql主从数据不一致的情况,我们应该怎样修复呢?本次分享一种不影响主库业务的修复思路。
Master节点
创建测试表t1和t2
CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `phone` int(11) NOT NULL, `create_time` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=innodb DEFAULT CHARSET=utf8mb4; insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("李四","0123456789",now()); insert into t1(username,phone,create_time) values("李四","0123456789",now()); insert into t1(username,phone,create_time) values("李四","0123456789",now()); CREATE TABLE `t2` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `phone` int(11) NOT NULL, `create_time` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=innodb DEFAULT CHARSET=utf8mb4; insert into t2(username,phone,create_time) values("张三","0123456789",now()); insert into t2(username,phone,create_time) values("张三","0123456789",now()); insert into t2(username,phone,create_time) values("张三","0123456789",now()); insert into t2(username,phone,create_time) values("李四","0123456789",now()); insert into t2(username,phone,create_time) values("李四","0123456789",now()); insert into t2(username,phone,create_time) values("李四","0123456789",now()); |
Slave节点
检查复制是否正常,确保复制正常
Salve节点执行以下操作
set sql_log_bin=0; delete from t1 where id=3; |
Master节点执行
delete from t1 where id=3; |
Slave节点检查复制状态
此时slave复制已发生异常,无法应用主库操作
Master持续操作,模拟业务
insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("李四","0123456789",now()); insert into t2(username,phone,create_time) values("李四","0123456789",now()); insert into t2(username,phone,create_time) values("李四","0123456789",now()); |
Master节点导出slave异常的数据表
mysqldump -uxxx-pxxxx -P3306 -S 3306.sock --single-transaction --master-data=2 test t1 > t1.sql |
查看gtid信息并记录
grep -A10 'GLOBAL.GTID_PURGED' t1.sql SET @@GLOBAL.GTID_PURGED='fdc161ff-5aea-11eb-ab62-000c29e9d0c1:1-81'; |
Master持续模拟业务
insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("张三","0123456789",now()); insert into t1(username,phone,create_time) values("李四","0123456789",now()); insert into t2(username,phone,create_time) values("李四","0123456789",now()); insert into t2(username,phone,create_time) values("李四","0123456789",now()); |
Slave节点导入数据
检查gtid信息
导入数据
导入数据时发生报错,无法更新gtid信息,该错误不需理会,千万不能为了解决该错误执行resetmaster;
再次检查gtid信息,未发生变化
设置复制过滤并应用日志
此处设置的SQL_AFTER_GTIDS为备份文件中gtid,复制应用日志到该gtid后停止应用日志,此时异常的表数据已修复,清除复制过滤规则后启动sql应用即可
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (); start slave sql_thread; |
master节点
slave节点
演示中数据较少使用count(*)和select* 即可进行比较,生产中数据量大,环境复杂建议使用pt-table-checksum进行一致性校验.
演示环境为启用GTID的主从复制,同样适用于非GTID复制环境,根据情况选用合适的startslave选项,参考官方文档STARTSLAVE Statement部分:
START SLAVE [thread_types] [until_option] [connection_options] [channel_option] thread_types: [thread_type [, thread_type] ... ] thread_type: IO_THREAD | SQL_THREAD until_option: UNTIL { {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set | MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos | RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos | SQL_AFTER_MTS_GAPS } |
