MySQL主从同步不一致解决办法

一般主从同步错误首先要考虑是不是在从库中误操作导致的。结果发现，有人在从库中进行了一条针对有主键表的 sql 语句的插入，导致主库再插入相同 sql 的时候，主从状态出现异常。发生主键冲突的报错。

解决方法：

在确保主从数据一致性的前提下，可以在从库进行错误跳过。

像从库如果不提供什么服务的话可以在从库中开启 read_only 参数，禁止在从库进行写入操作，还有用户必须没有super 权限，设置read_only才会生效。

1 一般主从复制错误的解决办法

这是正常的状态

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: Yes
Slave_SQL_Running: No
Last_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: Yes
Slave_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,同步成功了， 一般主从错误也就解决了

　　

那这时候有问题，主从问题不一致了， 主上面插入了很多数据， 这时候该怎么解决了。

首先主从问题不一致了，你的监控预警机制了，给你发短信或者钉钉，这时候你应该尽快去修复从库，比如就像上面跳过临时同步错误，暂时让其恢复正常同步。

其次 后期就是用pt工具：比如用pt-table-checksum 找出主从表数据不一致的， pt-table-sync进行修复从库

 

2 GTID 主从复制错误解决办法

现在修改我的主从模式为GTID，这是我的测试环境随便改，

生产环境不能这样瞎改

搭建GTID主从时，需要注意的 mysql 参数：

server_id：设置 mysql 实例的 server_id,每个实例的server_id必须不一样

gtid_mode=on：MYSQL 实例开启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=on
enforce-gtid-consistency=on
log-slave-updates=1

　　

从库操作

1
2
3
4
5
6
# my.cnf 中内容
[mysqld]
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=1
skip-slave-start=1

重启主从数据库

 

　

在从库 操作重新设置主从库的复制关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> CHANGE MASTER TO
MASTER_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);

　　

 

 主从报错了：

 

 从图中可以看出，出错事务的binlog文件为mysql-bin.000026

开始位置(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

　　

可以看出发现冲突的事务号是：f6c31435-38dd-11e9-ac93-000c299bcbee:53097，这时候就要确定哪一个事务发生了冲突，还可以直接从show slave status\G;结果中通过比对的方式找到冲突位置。

严谨起见，通过对binlog 内容分析得知冲突事务是插入了一条数据，主键为3。在从库中查看这条记录是否真的存在

1
2
3
4
5
6
mysql> select * from students where sid=3;
+-----+-------+------+
| sid | sname | sex  |
+-----+-------+------+
|   3 | keme  |    0 |
+-----+-------+------+

　　

发现slave 中存在这条记录了，这时，可以通过跳过该事务的方式来放弃该事务在slave上的执行，使slave 能够正常运行。

基于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 或者运维 背锅

还有如果主从不一致性实在是太多太多不一致了，就重做数据库吧

如果检查的数据某几张表不一致的情况下，可以把这几张道出来，恢复到从库

posted on 2025-01-22 08:52  数据库那些事儿  阅读(1074)  评论(0)    收藏  举报