mysql主从数据恢复 mysql主从修复_mysql 主从 矫正数据

背 景 概 述

MySQL主从复制技术应用非常广泛，M-S复制架构、keepalived＋M-M复制架构、MHA等高可用架构都基于MySQL主从复制技术。主从复制是基于binlog的逻辑复制，实际应中,可能会因为各种原因出现主从数据不一致的情况,因此我们需要定期或不定期地开展主从复制数据一致性的校验和修复工作;

如果发生了mysql主从数据不一致的情况,我们应该怎样修复呢?本次分享一种不影响主库业务的修复思路。

环 境 准 备

Master节点

创建测试表t1和t2CREATE 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节点检查复制状态

mysql主从数据恢复 mysql主从修复_mysql主从数据恢复_02

此时slave复制已发生异常,无法应用主库操作

mysql主从数据恢复 mysql主从修复_sql_03

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信息

mysql主从数据恢复 mysql主从修复_mysql主从数据恢复_04

导入数据

mysql主从数据恢复 mysql主从修复_主从复制_05

导入数据时发生报错,无法更新gtid信息,该错误不需理会,千万不能为了解决该错误执行resetmaster;

再次检查gtid信息,未发生变化

mysql主从数据恢复 mysql主从修复_sql_06

设置复制过滤并应用日志

mysql主从数据恢复 mysql主从修复_mysql主从数据恢复_07

mysql主从数据恢复 mysql主从修复_mysql主从数据恢复_08

此处设置的SQL_AFTER_GTIDS为备份文件中gtid,复制应用日志到该gtid后停止应用日志,此时异常的表数据已修复,清除复制过滤规则后启动sql应用即可CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ();

start slave sql_thread;

数 据 验 证

master节点

mysql主从数据恢复 mysql主从修复_主从复制_09

slave节点

mysql主从数据恢复 mysql主从修复_SQL_10

演示中数据较少使用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  }

mysql主从数据恢复 mysql主从修复_SQL_11

END