mysql 主从同步异常处理

说明

mysql 主从同步，由于主从数据不一致，可能会导致主从同步异常，现象如下：

 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


mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: backup_user
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000059
          Read_Master_Log_Pos: 48348684
               Relay_Log_File: relay-bin.000103
                Relay_Log_Pos: 45822597
        Relay_Master_Log_File: mysql-bin.000036
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
			........................
                   Last_Errno: 1813
                   Last_Error: Error 'Tablespace '`test`.`t_ts_extrnal`' exists.' on query. Default database: 'test'. Query: 'create table t_ts_extrnal (id int,name varchar(64))  DATA DIRECTORY = '/new_data/''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 45822384
              Relay_Log_Space: 1602815107
			.........................
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1813
               Last_SQL_Error: Error 'Tablespace '`test`.`t_ts_extrnal`' exists.' on query. Default database: 'test'. Query: 'create table t_ts_extrnal (id int,name varchar(64))  DATA DIRECTORY = '/new_data/''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 13001
                  Master_UUID: b4924d15-db10-11ec-8f08-9c5c8ebe680e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 220719 10:41:09
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: b4924d15-db10-11ec-8f08-9c5c8ebe680e:15795-286216
            Executed_Gtid_Set: b4924d15-db10-11ec-8f08-9c5c8ebe680e:1-257345
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql 从库同步过来的 sql 按 binlog 顺序执行，如果同步过程中，中间某个 sql 由于主从库数据不一致会导致导致无法执行，主从同步会直接停止，后续语句也无法执行，常规解决思路有如下几种：
- 重建主从或修正数据后重新开启主从
- 如果同步失败的 sql 处于可控范围内，可以忽略此语句并重新开启同步

修正主从数据

plan1：重建主从

详见 MySQL 不停机配置主从
- MySQL 5.7 不停机配置主从
- MySQL 8.0 不停机配置主从

plan2：使用 pt-table-checksum/pt-table-sync 修正数据

说明

需要先跳过异常，主从仍正常同步且时间间隔不大(具体操作可参照后面的忽略语句)
实际操作为通过 pt-table-checksum 校验主从数据库数据不一致的行
然后通过 pt-table-sync，同步数据不一致的表的数据

例

使用 pt-table-checksum 比对差异

1
2
3
4
5
6


$ pt-table-checksum h='127.0.0.1',u='xxx',p='123456',P=3306  --nocheck-binlog-format --ignore-databases=mysql --engines=innodb --replicate-check-only
Checking if all tables can be checksummed ...
Starting checksum ...
Differences on rocky02
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
sbtest.sbtest5 1 0 1

使用 pt-table-sync 在从库比对生成修正数据的 sql

1
2
3


$ pt-table-sync --print --sync-to-master h='127.0.0.1',u='xxx',p='123456',P=3306 --databases=sbtest > ./tmp_repair.sql
$ cat tmp_repair.sql
REPLACE INTO `sbtest`.`sbtest5`(`id`, `k`, `c`, `pad`) VALUES ('5679', '10056', '11432127566-15964990456-74940856735-37551709021-11224990069-69665383594-71138362827-04717782978-00926678792-68769309529', '80278789837-50045205329-44203794226-34083900368-75059954146') /*percona-toolkit src_db:sbtest src_tbl:sbtest5 src_dsn:P=3306,h=172.16.2.181,p=...,u=xxx dst_db:sbtest dst_tbl:sbtest5 dst_dsn:P=3306,h=127.0.0.1,p=...,u=xxx lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:41964 user:root host:rocky02*/;

在从库执行 sql 修正数据

忽略语句

跳过一个事务(非 GTID 模式)

1
2
3
4


slave stop;
-- 数字是几表示跳过后续的几个事务
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
slave start;

如果开启 GTID 模式，会有如下报错，此时需要使用 GTID 的模式跳过下一个事务

1
2


mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

GTID 模式下跳过事务

处理方式说明：

关闭主从
执行 BEGIN; COMMIT;，用一个空事务填充无法正常执行的 GTID
然后恢复 GTID AUTOMATIC 和主从同步，至此实现在 GTID 模式下跳过事务的目的

操作

从库停止 slave

1

mysql> stop slave;

从库查异常事务的 GTID
- Retrieved_Gtid_Set：代表已经接受到的GTID集合
- Executed_Gtid_Set：代码已经执行的GTID集合
- 由 Retrieved_Gtid_Set 和 Executed_Gtid_Set 可知 slave mysql 已接受到的 GTID 是 15795-295122，已经执行过的是 1-257345
- 下一个要执行的 sql 的 GTID 也是导致主从失败的 GTID 是 257346

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


mysql> show slave status \G
*************************** 1. row ***************************
			    ...............
             ----- 关闭主从
             Slave_IO_Running: No
            Slave_SQL_Running: No
          ...............
           Retrieved_Gtid_Set: b4924d15-db10-11ec-8f08-9c5c8ebe680e:15795-295122
            Executed_Gtid_Set: b4924d15-db10-11ec-8f08-9c5c8ebe680e:1-257345
          ...............

设置 GTID 位置并使用空事务填充此 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="b4924d15-db10-11ec-8f08-9c5c8ebe680e:257346";
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.01 sec)

mysql> SET GTID_NEXT="AUTOMATIC";
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

查看当前主从状
- Slave_IO_Running Slave_SQL_Running 为 yes
- Seconds_Behind_Master 在正常减少
- Executed_Gtid_Set 在正常增长

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 35126
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
           Retrieved_Gtid_Set: b4924d15-db10-11ec-8f08-9c5c8ebe680e:15795-296145
            Executed_Gtid_Set: b4924d15-db10-11ec-8f08-9c5c8ebe680e:1-259654
                Auto_Position: 1

pt-slave-restart 自动跳过

说明

监视某些特定的复制错误，然后忽略，再次启动SLAVE进程（Watch and restart MySQL replication after errors）
- https://www.percona.com/doc/percona-toolkit/LATEST/pt-slave-restart.html
PS：
- 当采用多线程复制（slave_parallel_workers > 0）时，pt-slave-restart不能跳过事务。pt-slave-restart不能确定GTID事件是哪个特定slave线程执行失败的事务。

参数

–always :永不停止slave线程，手工停止也不行
–ask-pass :替换-p命令，防止密码输入被身后的开发窥屏
–error-numbers ：指定跳过哪些错误，可用,进行分隔
–error-text ：根据错误信息进行匹配跳过
–log ：输出到文件
–recurse ：在主端执行，监控从端
–runtime ：工具执行多长时间后退出：默认秒，m=minute,h=hours,d=days
–slave-user/–slave-password ：从库的账号密码，从主端运行时使用
–skip-count ：一次跳过错误的个数，胆大的可以设置大些，不指定默认1个
–master-uuid :级联复制的时候，指定跳过上级或者上上级事务的错误
–until-master :到达指定的master_log_pos,file位置后停止，格式：”file:pos“
–until-relay :和上面一样，但是根据relay_log的位置来停止

example

前台启动，忽略 1051 错误

1
2
3


$ pt-slave-restart -uroot -pXXX  --socket=/home/mysql/mysql_3308/mysql_data/data_3308/mysql.sock --error-numbers=1051
# A software update is available:
2022-07-20T18:22:47 S=/home/mysql/mysql_3308/mysql_data/data_3308/mysql.sock,p=...,u=root relay-bin.000178        6948 1051

数据库主从配置忽略某种类型的错误(不推荐)

说明

涉及参数 slave_skip_errors/replica_skip_errors(>=8.0.26)
需要重启mysql实例，且不推荐配置此参数
可以通过配置 mysql 配置文件指定主从复制过程中忽略某种类型的异常
- 当前的异常类型可以通过 show slave status \G 的 Last_SQL_Errno 查看
- 异常类型：https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_open_as_readonly
- 常见异常类型:
  - 1007：数据库已存在，创建数据库失败
  - 1008：数据库不存在，删除数据库失败
  - 1050：数据表已存在，创建数据表失败
  - 1051：数据表不存在，删除数据表失败
  - 1054：字段不存在，或程序文件跟数据库有冲突
  - 1060：字段重复，导致无法插入
  - 1061：重复键名
  - 1068：定义了多个主键
  - 1094：位置线程ID
  - 1146：数据表缺失，请恢复数据库
  - 1053：复制过程中主服务器宕机
  - 1062：主键冲突 Duplicate entry ‘%s’ for key %d
通过在配置文件中配置 slave_skip_errors/replica_skip_errors(>=8.0.26) 实现
- 有效值：
  - OFF(default)：关闭
  - [list of error codes]：如 1007,1008
  - all：所有
  - ddl_exist_errors：相当于1007,1008,1050,1051,1054,1060,1061,1068,1094,1146

配置

关闭 mysql 实例
更改 my.conf 增加:

1
2


[mysqld]
slave_skip_errors=1813

启动 mysql 实例
查看主从状态

1

show slave status;

mysql 主从同步异常处理

文章目录

说明

修正主从数据

plan1：重建主从

plan2：使用 pt-table-checksum/pt-table-sync 修正数据

说明

例

忽略语句

跳过一个事务(非 GTID 模式)

GTID 模式下跳过事务

处理方式说明：

操作

pt-slave-restart 自动跳过

说明

参数

example

数据库主从配置忽略某种类型的错误(不推荐)

说明

配置

参考

GTID 模式跳过语句

slave_skip_errors 忽略主从

pt-slave-restart