什么大师特 发表于 2024-9-11 18:06:20

解决MySQL主从复制错误1595:Relay log write failure…

先交代下背景:我这边正在研究基于 Docker 的 MariaDB Galera Cluster 的自动化集群部署方案【以下简称 MGC,后续有时间可以考虑分享下这个方案】,已经完成所有调试,想在生产环境部署一个 MGC 集群作为从先灰度测试。
生产环境主 DB 版本为 MySQL5.5,新的 MGC 采用 Mariadb 最新 10.3.12 stable 版本,做好 MGC 集群,并导入一份从主 DBdump 出来的完整 SQL 之后,change master 开始创建主从,结果如下报错:
MariaDB [(none)]> show slave status\G                                                                                                                                                      *************************** 1. row ***************************Slave_IO_State: Master_Host: x.x.x.xMaster_User: rplMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.001411Read_Master_Log_Pos: 360808406Relay_Log_File: mysql-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.001411Slave_IO_Running: NoSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 360808406Relay_Log_Space: 256Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 1595Last_IO_Error: Relay log write failure: could not queue event from masterLast_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 15410Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservativeSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itSlave_DDL_Groups: 0Slave_Non_Transactional_Groups: 0Slave_Transactional_Groups: 01 row in set (0.00 sec)
查了下资料,都是是磁盘满导致的,但是我这边磁盘空间、权限都没问题,于是继续检查了下 MGC 节点日志如下:
2019-01-28 10:47:26 11 Slave SQL thread initialized, starting replication in log 'mysql-bin.001411' at position 512281317, relay log './mysql-relay-bin.000003' position: 1514733772019-01-28 10:47:26 10 Slave I/O thread: connected to master 'rpl@x.x.x.x:3306',replication started in log 'mysql-bin.001413' at position 4126249452019-01-28 10:47:26 10 Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Internal MariaDB error code: 11932019-01-28 10:47:26 10 Slave I/O: Replication event checksum verification failed while reading from network, Internal MariaDB error code: 17432019-01-28 10:47:26 10 Slave I/O: Relay log write failure: could not queue event from master, Internal MariaDB error code: 15952019-01-28 10:47:26 10 Slave I/O thread exiting, read up to log 'mysql-bin.001413', position 412624945
里面有一个关键信息:binlog_checksum failed,看来报错和这个有关系了。查了下资料,应该是 Mariadb 默认打开了 slave_sql_verify_checksum(MySQL 版本默认关闭),而主从版本又不一致,导致 checksum 失败。
解决办法也很简单,在 MGC 各节点的配置中加上:slave_sql_verify_checksum=0 ,关闭这个特性即可,具体有没有负面影响暂未深入研究。
页: [1]
查看完整版本: 解决MySQL主从复制错误1595:Relay log write failure…