MySQL 8.0 高可用之如何解决从库数据被修改引起的主从同步错误

作者公众号，欢迎一起交流。

 在MySQL主从复制过程中，由于各种原因，不可避免在复制过程中出现错误，那么在复制过程中出现错误该如何解决呢？本篇将演示基于普通复制模式和基于GTID复制模式由于从库数据被修改导致的主从不一致时，如何在从库跳过相应的错误。

1 常见错误

1）ERROR：1062 从库插入数据，发生唯一性冲突

2）ERROR：1032 从库找不到要删除的数据

3）ERROR：1452 无法在外键的表插入或者更新参考主键没有的数据

2 普通复制模式从库出现错误

2.1 模拟错误

这里将在普通主从复制环境模拟【Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY】错误，即从库由于误操作导致主库将数据同步到从库时出错：

1）主库初始化数据，ID=1、2的记录同步到从库

mysql> create table test.test(id int auto_increment not null primary key,name varchar(20));

Query OK, 0 rows affected (0.16 sec)



mysql> insert into test.test values(1,'Alen'),(2,'Tom');

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0



mysql> select * from test.test;

+----+------+

| id | name |

+----+------+

| 1 | Alen |

| 2 | Tom |

+----+------+

2 rows in set (0.01 sec)

2）查看从库同步到的数据，主从一致，然后在从库插入一条数据ID=3的记录

mysql> select * from test.test;

+----+------+

| id | name |

+----+------+

| 1 | Alen |

| 2 | Tom |

+----+------+

2 rows in set (0.00 sec)



mysql> insert into test.test values(3,'Test');

Query OK, 1 row affected (0.00 sec)



mysql> select * from test.test;

+----+------+

| id | name |

+----+------+

| 1 | Alen |

| 2 | Tom |

| 3 | Test |

+----+------+

3 rows in set (0.00 sec)

3）在主库插入ID=3的记录

mysql> insert into test.test values(3,'Test');

Query OK, 1 row affected (0.55 sec)



mysql> select * from test.test;

+----+------+

| id | name |

+----+------+

| 1 | Alen |

| 2 | Tom |

| 3 | Test |

+----+------+

3 rows in set (0.00 sec)

4）查看从库复制状态，发现出错

mysql> show replica status\G

*************************** 1. row ***************************

Replica_IO_State: Waiting for source to send event

Source_Host: 192.168.56.201

Source_User: repl

Source_Port: 3306

Connect_Retry: 60

Source_Log_File: mysql-bin.000009

Read_Source_Log_Pos: 1405

Relay_Log_File: mysql-relay-bin.000013

Relay_Log_Pos: 1295

Relay_Source_Log_File: mysql-bin.000009

Replica_IO_Running: Yes

Replica_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1062

Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000009, end_log_pos 1374. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Skip_Counter: 0

Exec_Source_Log_Pos: 1120