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

标签：+-----+-------+------+ 解决办法 slave students MySQL 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 或者运维 背锅

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

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

标签：+-----+-------+------+,解决办法,slave,students,MySQL,mysql,从库,主从
From： https://www.cnblogs.com/gdjgs/p/18666599