MySQL多源复制,Slave_IO_Running异常?竟然是半同步惹的祸!

原创于 2025-10-14 12:04:47 发布 · 307 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #adb #android

国产数据库专栏收录该内容

9 篇文章

订阅专栏

环境说明:

MySQL通过级联复制的方式进行迁移升级，源库MySQL 5.7.x双主，目标库MySQL 8.0.x双主。

问题现象:

在MySQL 8.0配置多源级联复制时，Slave_IO_Running 无法启动，一直是NO状态。
复制报错:

mysql> show slave status\G;
Last_IO_Errno: 13122
Last_IO_Error: Relay log write failure: could not queue event from source

后台 error.log

2025-09-XXT17:48:46.438554+08:00 40 [Warning] [MY-011184] [Repl] Plugin semisync reported: 'Source server does not support semi-sync, fallback to asynchronous replication'
2025-09-XXT17:48:46.586643+08:00 34 [ERROR] [MY-013115] [Repl] Replica I/O for channel '': Replication event checksum verification failed while reading from network. Error_code: MY-013115
2025-09-XXT17:48:46.586699+08:00 34 [ERROR] [MY-013122] [Repl] Replica I/O for channel '': Relay log write failure: could not queue event from source, Error_code: MY-013122

可以看到:源库不支持半同步，回退到异步复制

Source server does not support semi-sync, fallback to asynchronous replication

问题原因:

要使用半同步复制，必须满足以下要求：
不得配置多个复制通道。半同步复制仅与默认复制通道兼容。

https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-installation.html
To use semisynchronous replication, the following requirements must be satisfied:
There must not be multiple replication channels configured. Semisynchronous replication is only compatible with the default replication channel.

解决方案:

临时卸载半同步插件

问题重现:

配置级联复制

MySQL 8.0.X其中一个节点配置级联复制

change master to
master_host='192.*.*.101',
master_port=3306,
master_user='xxtmp',
master_password='******',
master_log_file='mysql-bin.000XXX',
master_log_pos=XXX
FOR CHANNEL "cjctmp";

问题重现

启动，检查主从状态

start slave;
start slave for channel "cjctmp";
show slave status\G;

出现报错:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.*.*.102
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.0000xx
          Read_Master_Log_Pos: xxx131xxx
               Relay_Log_File: mysql-relay-bin.0000xxx
        Relay_Master_Log_File: mysql-bin.0000xx
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
......
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13122
                Last_IO_Error: Relay log write failure: could not queue event from source
               Last_SQL_Errno: 0
               Last_SQL_Error: 
......
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.*.*.101
                  Master_User: xxtmp
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000XXX
          Read_Master_Log_Pos: ***
               Relay_Log_File: mysql-relay-bin-cjctmp.000003
                Relay_Log_Pos: ***
        Relay_Master_Log_File: mysql-bin.000XXX
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: cjctmp
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
2 rows in set, 1 warning (0.00 sec)

ERROR: 
No query specified

复制报错:

Last_IO_Errno: 13122
Last_IO_Error: Relay log write failure: could not queue event from source

后台 error.log

2025-09-XXT17:48:46.438554+08:00 40 [Warning] [MY-011184] [Repl] Plugin semisync reported: 'Source server does not support semi-sync, fallback to asynchronous replication'
2025-09-XXT17:48:46.586643+08:00 34 [ERROR] [MY-013115] [Repl] Replica I/O for channel '': Replication event checksum verification failed while reading from network. Error_code: MY-013115
2025-09-XXT17:48:46.586699+08:00 34 [ERROR] [MY-013122] [Repl] Replica I/O for channel '': Relay log write failure: could not queue event from source, Error_code: MY-013122

解决方案:

卸载半同步

mysql> stop slave;
mysql> uninstall plugin rpl_semi_sync_source;
mysql> uninstall plugin rpl_semi_sync_source;

同步恢复正常

mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

迁移完成操作:

删除新增的临时级联复制:

stop replica;
reset slave all for channel "cjctmp";
show replica status\G;
start replica;

添加半同步

INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';

节点1：

SET GLOBAL rpl_semi_sync_replica_enabled = 1;

节点2：

SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 3000;

节点1：从库重启replica

STOP replica;
START replica;

配置另一边
节点2：

SET GLOBAL rpl_semi_sync_replica_enabled = 1;

节点1：

SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 3000;

节点2：主库重启replica

STOP replica;
START replica;

检查参数

show variables like '%semi%';
show status like '%Rpl_semi_sync%';

参考:

https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-installation.html

欢迎关注我的公众号《IT小Chen》