MySQL主从同步不一致解决办法
在确保主从数据一致性的前提下，可以在从库进行错误跳过。
像从库如果不提供什么服务的话可以在从库中开启 read_only 参数，禁止在从库进行写入操作，还有用户必须没有super 权限，设置read_only才会生效。
这是正常的状态
10.0.0.150 是主
10.0.0.151 是从
先模拟故障
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
# 这是本次的表结构mysql> show create table students;| students | CREATE TABLE `students` ( `sid` int(11) NOT NULL, `sname` varchar(20) DEFAULT NULL, `sex` int(11) DEFAULT NULL, PRIMARY KEY (`sid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8#主库执行，是个空表mysql> select * from students;Empty set (0.00 sec)#在从库 ，给students 加1条数据：mysql> insert into students values (1,'keme',0);mysql> show slave status\G;... Slave_IO_Running: Yes Slave_SQL_Running: Yes...看主从状态是正常的# 从库查看students 数据mysql> select * from students;+-----+-------+------+| sid | sname | sex |+-----+-------+------+| 1 | keme | 0 |+-----+-------+------+# 在主库查看students 表mysql> select * from students;Empty set (0.00 sec)# 插入相同主键的值mysql> insert into students values (1,'keme',1);# 查看students表mysql> select * from students;+-----+-------+------+| sid | sname | sex |+-----+-------+------+| 1 | keme | 1 |+-----+-------+------+# 查看从库状态mysql> show slave status\G;...Slave_IO_Running: YesSlave_SQL_Running: NoLast_SQL_Error: Could not execute Write_rows event on table beta.students; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000023, end_log_pos 11789...# 主从状态不一致了，造成的原因是主键冲突 |
解决办法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
# 停止从库mysql> stop slave;# 在从库删除主键冲突的那条语句， 把主库执行的那条语句在从库执行mysql> delete from students where sid=1;mysql> insert into students values (1,'keme',1);# 同步跳过临时错误mysql> set global sql_slave_skip_counter = 1;mysql> start slave;mysql> show slave status\G;...Slave_IO_Running: YesSlave_SQL_Running: Yes...# 主库再次插入数据，看看从库是不是能够同步mysql> insert into students values (2,'keme',1);# 从库查看mysql> select * from students;+-----+-------+------+| sid | sname | sex |+-----+-------+------+| 1 | keme | 1 || 2 | keme | 1 |+-----+-------+------+2 rows in set (0.00 sec)# OK,同步成功了， 一般主从错误也就解决了 |
首先主从问题不一致了，你的监控预警机制了，给你发短信或者钉钉，这时候你应该尽快去修复从库，比如就像上面跳过临时同步错误，暂时让其恢复正常同步。
现在修改我的主从模式为GTID，这是我的测试环境随便改，
enforce_gtid_consitency=on ：使用GTID模式复制时，需要开启此参数，用来保证GTID的一致性。
log-bin=on ：Msql 做主从必须开启binlog
log-slave-updates=1 ：觉得slave 从master 接收到的更新且执行完之后，执行的binlog是否记录到slave的binlog中，建议开启
binlog_format=row ：强烈建议binlog_format使用row格式 在mysql 5.7.6 版本以后默认就是row
skip-slave-start=1 ：当slave 数据库启动的时候，slave 不会自动开启复制
主库操作，在[mysqld] 加一下参数，我这个做过主从， 只加一部分参数
|
1
2
3
4
5
|
# my.cnf 中内容[mysqld]gtid-mode=onenforce-gtid-consistency=onlog-slave-updates=1 |
从库操作
|
1
2
3
4
5
6
|
# my.cnf 中内容[mysqld]gtid-mode=onenforce-gtid-consistency=onlog-slave-updates=1skip-slave-start=1 |
重启主从数据库
在从库 操作重新设置主从库的复制关系
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> CHANGE MASTER TOMASTER_HOST = '10.0.0.150',MASTER_PORT = 3306,MASTER_USER = 'repl',MASTER_PASSWORD = '123456',MASTER_AUTO_POSITION = 1;mysql> start slave;#查看主从状态mysql> show slave status\G;... Slave_IO_Running: Yes Slave_SQL_Running: Yes... |
如果是在GTID模式下出现复制报错， 则使用SQL_SLAVE_SKIP_COUNTER语句会报错
在GTID 模式的复制情况下，如果slave 发生错误，则可以通过跳过该事务的方式恢复主从复制。
现在人为制造slave错误
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
# 在从库的sutdents 表插入一条数据mysql> select * from students;+-----+-------+------+| sid | sname | sex |+-----+-------+------+| 1 | keme | 1 || 2 | keme | 1 |+-----+-------+------+mysql> insert into students values (3,'keme',0);# 在查看从库的数据mysql> select * from students;+-----+-------+------+| sid | sname | sex |+-----+-------+------+| 1 | keme | 1 || 2 | keme | 1 || 3 | keme | 0 |+-----+-------+------+# 主库也插入主键为3这条数据，引发主从同步错误mysql> insert into students values (3,'keme',1); |
主从报错了：
开始位置(Exec_Master_Log_Pos)是154 ，结束位置是(end_log_pos ) 395,可以去主库分析下binlog ，看一下发生冲突的事务是哪个。
可以看到接收并且执行了GTID事件 是
|
1
2
3
4
5
6
|
从库执行了这些5a13910d-1496-11e9-8375-000c29f859ce:1-3,f6c31435-38dd-11e9-ac93-000c299bcbee:1-53096收到却没执行的事务号：Retrieved_Gtid_Set: f6c31435-38dd-11e9-ac93-000c299bcbee:53097 |
|
1
2
3
4
5
6
|
mysql> select * from students where sid=3;+-----+-------+------+| sid | sname | sex |+-----+-------+------+| 3 | keme | 0 |+-----+-------+------+ |
基于GTID模式的复制，跳过一个事务，需要利用一个空事务来完成。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> set GTID_NEXT='f6c31435-38dd-11e9-ac93-000c299bcbee:53097';Query OK, 0 rows affected (0.00 sec)mysql> begin;commit;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> set GTID_NEXT='AUTOMATIC';Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec) |
查看slave 状态
哪主从库数据是否一致，就看3那条
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
# 主库数据mysql> select * from students where sid=3;+-----+-------+------+| sid | sname | sex |+-----+-------+------+| 3 | keme | 1 |+-----+-------+------+#从库数据mysql> select * from students where sid=3;+-----+-------+------+| sid | sname | sex |+-----+-------+------+| 3 | keme | 0 |+-----+-------+------+ |
1 手动修改或者插入
2 用pt用具 来修复或者检查不一致数据
由于我这是我的本地环境，我只手动修改数据,再看主从状态
|
1
2
3
|
mysql> update students set sex=1 where sid=3;Query OK, 1 row affected (0.03 sec)Rows matched: 1 Changed: 1 Warnings: 0 |
注：不止是要主从解决错误，还要主从数据的一致性
如果是生产的核心库主从不一致，一定要查明原因，不然老是 dba 或者运维 背锅
还有如果主从不一致性实在是太多太多不一致了，就重做数据库吧
如果检查的数据某几张表不一致的情况下，可以把这几张道出来，恢复到从库
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· C#/.NET/.NET Core技术前沿周刊 | 第 29 期（2025年3.1-3.9）
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
2021-01-12 Python小技巧（三）
2021-01-12 Python小技巧（二）
2021-01-12 Python小技巧（一）
2021-01-12 解决linux（centos7）重新安装mysql systemctl start mysqld.service时报错
2021-01-12 MySQL启动报错-The server quit without updating PID file[FAILED]mysql/mysql.pid)