Last_Errno: 1594 the master's binary log is corrupted Last_SQL_Errno: 1062

本文介绍了MySQL主从复制过程中遇到的不同步异常情况及其解决步骤。包括定位问题原因、修复binlog损坏、处理主键冲突等操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

mysql主从复制不同步异常

查看从库同步状态,发现Slave_SQL_Running: No状态异常,主备库已经不再同步,初步怀疑是断电倒是binlog的问题
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000030
          Read_Master_Log_Pos: 54007656
               Relay_Log_File: slave01-relay-bin.000068
                Relay_Log_Pos: 63524817
        Relay_Master_Log_File: log-bin.000029
       ..........................................
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry.
          The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), 
          the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log),
          a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log 
          or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
          

 查看下slave的error log 发现binlog最后截止在 'log-bin.000029' position 63524608

2017-10-13T05:44:56.382911Z 1 [Note] Slave I/O thread killed while reading event for channel ''
2017-10-13T05:44:56.382941Z 1 [Note] Slave I/O thread exiting for channel '', read up to log 'log-bin.000030', position 54007656
2017-10-13T05:44:58.675958Z 7 [Note] Slave I/O thread: Start semi-sync replication to master 'repl@192.168.1.3:3306' in log 'log-bin.000030' at position 54007656
2017-10-13T05:44:58.675993Z 7 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-10-13T05:44:58.677146Z 7 [Note] Slave I/O thread for channel '': connected to master 'repl@192.168.1.3:3306',replication started in log 'log-bin.000030' at position 54007656
2017-10-13T05:44:58.687664Z 8 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-10-13T05:44:58.688769Z 8 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'log-bin.000029' at position 63524608, relay log './slave01-relay-bin.000068' position: 63524817
2017-10-13T05:44:58.688858Z 8 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 65, event_type: 34
2017-10-13T05:44:58.688865Z 8 [ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O error
2017-10-13T05:44:58.688877Z 8 [ERROR] Slave SQL for channel '': Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), 
the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know
 their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
2017-10-13T05:44:58.688881Z 8 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". 
We stopped at log 'log-bin.000029' position 63524608

mysql>show binlog events in 'log-bin.000029' from 63524608 limit 3; 查看位于 pos 为 63524608 的一行记录下 endpos 63524608

于是采用在slave上重新定位master的binlog的方法以重新同步主备库 

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

mysql> change master to master_host='192.168.1.3',master_user='repl',master_password='ppay', master_log_file='log-bin.000029',master_log_pos=63524608;

Query OK, 0 rows affected, 2 warnings (0.16 sec)

开启slave 
mysql> start slave ;
Query OK, 0 rows affected (0.02 sec)

查看同步状态

mysql> show  slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000030
          Read_Master_Log_Pos: 54416555
               Relay_Log_File: slave01-relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
            ...................................
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table log.logsver; Duplicate entry '874655' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log log-bin.000029, end_log_pos 63525006
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 63524608
              Relay_Log_Space: 54417775
            ....................................
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table log.logsver; Duplicate entry '874655' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log log-bin.000029, end_log_pos 63525006
  Replicate_Ignore_Server_Ids: 
            ....................................
ERROR: 
No query specified


重新启动slave之后,又出现了新的错误,常见的error 1062,主键重复

主键重复

在slave已经有874655该条记录,又在master上插入了同一条记录,所以报错。
Last_SQL_Error: Could not execute Write_rows event on table log.logsver; 
Duplicate entry '874655' for key 'PRIMARY', 
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
the event's master log log-bin.000029, end_log_pos 63525006

解决方法:

在slave上先看下log.logsver表结构,在主备库上查询重复的记录状态

mysql> desc log.logsver;

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(100)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | NO   |     | NULL    |                |
| level    | int(2)       | NO   |     | NULL    |                |
| msg      | varchar(255) | NO   |     | NULL    |                |
| intime   | datetime     | NO   |     | NULL    |                |
| codename | varchar(25)  | YES  |     | NULL    |                |
| codefunc | varchar(25)  | YES  |     | NULL    |                |
| codeline | varchar(10)  | YES  |     | NULL    |                |
| process  | varchar(10)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
停止slave
mysql>stop slave;

删除重复的主键
mysql> delete from logsver where id=874655;
Query OK, 1 row affected (0.07 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

重新开启salve
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000030
          Read_Master_Log_Pos: 54997622
               Relay_Log_File: slave01-relay-bin.000003
                Relay_Log_Pos: 43225
        Relay_Master_Log_File: log-bin.000030
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.................................
1 row in set (0.00 sec)


在master上和slave上分别确认,

mysql> select count(*) from  logsver where id=874655;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

mysql> select count(*) from  logsver where id=874655;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

至此,主备库重新同步正常

<think>我们正在排查MySQL主从复制中Slave_IO_Running状态为Connecting的问题。这个状态表示从库的I/O线程正在尝试连接主库但尚未成功。以下是完整的排查步骤: ### 1. 检查主从基础配置 - **主库IP/域名**:确保从库配置的主库地址正确(`MASTER_HOST`) - **端口**:主库防火墙是否开放复制端口(默认3306) - **复制账号**:检查复制账号权限及密码 - **主库`server_id`**:必须唯一 **验证命令**: ```sql SHOW SLAVE STATUS\G ``` 关注字段: ``` Master_Host: 192.168.1.100 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 ``` ### 2. 网络连通性测试 在从库执行: ```bash # 测试TCP连接 telnet <主库IP> 3306 # 或使用nc nc -zv <主库IP> 3306 ``` > 若连接失败,检查: > - 主库防火墙:`sudo ufw status`(Ubuntu) > - 云服务器安全组规则 > - 网络路由(尤其跨机房/VPC场景) ### 3. 复制账号权限验证 登录主库检查复制账号权限: ```sql SHOW GRANTS FOR 'repl_user'@'从库IP'; ``` 必需权限: ```sql GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'从库IP'; ``` ### 4. 主库状态检查 登录主库查看: ```sql SHOW MASTER STATUS; ``` 确保从库连接的`MASTER_LOG_FILE`和`MASTER_LOG_POS`在有效范围内(新搭建复制时需特别注意) ### 5. 错误日志分析 - **从库错误日志**: ```bash tail -f /var/log/mysql/error.log ``` 常见错误: ``` [ERROR] Slave I/O: error connecting to master 'repl_user@主库IP:3306' - retry-time: 60 retries: 1, Error_code: 2003 ``` ### 6. 主库连接限制检查 - 主库`max_connections`是否已满 - 主库`max_user_connections`是否限制复制账号 ### 7. SSL/TLS配置问题 若主从启用SSL: ```sql SHOW SLAVE STATUS\G ``` 检查: ``` Master_SSL_Allowed: Yes Master_SSL_CA_File: /path/to/ca.pem ``` > 证书路径需在从库存在且可读 ### 8. 跳过DNS解析 在从库`my.cnf`添加: ```ini [mysqld] skip-name-resolve ``` 重启MySQL服务后重试 ### 9. 临时停止IO线程 若之前有未完成的复制操作: ```sql STOP SLAVE IO_THREAD; -- 引用[1]中提到需先停止IO线程 START SLAVE; ``` ### 故障排查流程图 ```mermaid graph TD A[Slave_IO_Running=Connecting] --> B{网络连通性} B -->|失败| C[检查防火墙/路由] B -->|成功| D{复制账号权限} D -->|不足| E[主库授权REPLICATION SLAVE] D -->|正常| F{主库SHOW MASTER STATUS} F -->|无输出| G[主库未启用二进制日志] F -->|正常| H{错误日志分析} H -->|SSL错误| I[检查证书路径] H -->|连接数超限| J[调整max_connections] H -->|其他错误| K[根据错误码处理] ``` ### 关键命令汇总 | 步骤 | 命令 | |------|------| | 检查复制状态 | `SHOW SLAVE STATUS\G` | | 测试主库端口 | `nc -zv 主库IP 3306` | | 主库授权复制账号 | `GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'从库IP' IDENTIFIED BY '密码'` | | 重置复制线程 | `STOP SLAVE; START SLAVE;` | > **注意**:修改配置后需重启MySQL服务:`sudo systemctl restart mysql` §§相关问题§§ 1. 如何修复MySQL主从复制错误1236? 2. 主从复制延迟持续增长该如何处理? 3. MySQL多源复制如何配置? 4. 如何监控MySQL复制状态? 5. 如何配置安全的复制账号?</think>### MySQL Slave_IO_Running 状态为 "Connecting" 的排查指南 当 MySQL 主从复制中 Slave_IO_Running 状态显示为 "Connecting" 时,表示从库的 I/O 线程正在尝试连接主库但尚未成功。以下是系统化的排查步骤: --- #### 1. **检查基础配置** ```sql SHOW SLAVE STATUS\G ``` 关注以下字段: - `Master_Host`:主库 IP 是否正确 - `Master_User`:复制账号是否存在 - `Master_Port`:端口是否匹配主库 - `Master_SSL_Allowed`:SSL 配置是否一致 > **关键点**:确保从库配置的 IP、端口、账号与主库一致[^2]。 --- #### 2. **网络连通性测试** 在从库执行: ```bash # 测试主库端口连通性 telnet <Master_IP> 3306 # 或使用网络工具 nc -zv <Master_IP> 3306 ``` - 若连接失败,检查: - 防火墙规则(主/从双方) - 云服务器安全组 - 路由表/NAT 配置 --- #### 3. **验证复制账号权限** 登录主库检查账号权限: ```sql SHOW GRANTS FOR 'repl_user'@'从库IP'; ``` 必需权限: ```sql GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'从库IP'; ``` --- #### 4. **检查主库状态** 在主库执行: ```sql SHOW MASTER STATUS; ``` 确保从库连接的 `MASTER_LOG_FILE` 和 `MASTER_LOG_POS` 在有效范围内。 --- #### 5. **分析错误日志** 查看从库错误日志: ```bash tail -f /var/log/mysql/error.log ``` 常见错误示例: ``` [ERROR] Slave I/O: error connecting to master 'repl_user@主库IP:3306' - retry-time: 60 retries: 1, Error_code: 2003 ``` - **Error_code 2003**:网络连接问题 - **Error_code 1045**:账号认证失败 - **Error_code 2061**:SSL 配置错误 --- #### 6. **特殊场景处理** - **SSL 连接问题**: 若主库启用 SSL,需确保从库有正确的证书: ```sql CHANGE MASTER TO MASTER_SSL=1, MASTER_SSL_CA='/path/to/ca.pem', MASTER_SSL_CERT='/path/to/client-cert.pem', MASTER_SSL_KEY='/path/to/client-key.pem'; ``` - **多源复制冲突**: 检查 `REQUIRE_TABLE_PRIMARY_KEY_CHECK` 设置是否一致[^2] - **IO 线程阻塞**: 按引用[1]建议停止 IO 线程: ```sql STOP SLAVE IO_THREAD; -- 先停止线程 START SLAVE; -- 重新启动 ``` --- #### 7. **防火墙与 SELinux** ```bash # 检查防火墙 sudo ufw status # 临时开放端口 sudo ufw allow 3306 # 检查 SELinux sudo sestatus sudo setsebool -P mysqld_connect_any 1 ``` --- ### 故障排查流程图 ```mermaid graph TD A[Slave_IO_Running=Connecting] --> B{网络连通性} B -->|失败| C[检查防火墙/路由] B -->|成功| D{复制账号权限} D -->|不足| E[主库授权REPLICATION SLAVE] D -->|正常| F{主库SHOW MASTER STATUS} F -->|无输出| G[主库未启用二进制日志] F -->|正常| H{错误日志分析} H -->|SSL错误| I[配置证书] H -->|认证失败| J[重置复制密码] H -->|其他错误| K[根据错误码处理] ``` --- ### 修复后验证 ```sql STOP SLAVE; START SLAVE; SHOW SLAVE STATUS\G ``` 期望输出: ``` Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_IO_Error: ``` > **注意**:修改配置后需重启 MySQL 服务:`sudo systemctl restart mysql`
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值