MySQL · 主从复制中断

发表于 | 更新于 | 分类于 | 阅读次数：

Slave_IO_Running: No

找不到初始日志

  • 报错信息

image-20190920095751451

1
2
3
4
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
  • 原因分析

该错误发生在从库 io 进程从主库拉取日志时，发现主库的 mysql_bin.index 文件中第一个文件不存在，出现此类报错可能有两个原因：

1）Slave 由于某种原因停了好长一段是时间，当你重启 Slave 复制的时候，在主库上找不到相应的 binlog 。

2）由于某些设置主库上的 binlog 被删除了，导致从库获取不到对应的 binlog。

1
2
3
4
5
6
7
8
9
10
11
12
[root@hop02 mzjh]# ll | grep mysql-bin
-rw-rw----. 1 mysql mysql  1073742013 Sep 12 14:55 mysql-bin.001948
-rw-rw----. 1 mysql mysql  1073742073 Sep 13 11:53 mysql-bin.001949
-rw-rw----. 1 mysql mysql  1073742120 Sep 14 08:39 mysql-bin.001950
-rw-rw----. 1 mysql mysql  1073859355 Sep 15 06:21 mysql-bin.001951
-rw-rw----. 1 mysql mysql  1074054892 Sep 16 06:18 mysql-bin.001952
-rw-rw----. 1 mysql mysql  1075243641 Sep 17 06:18 mysql-bin.001953
-rw-rw----. 1 mysql mysql  1074801420 Sep 18 06:16 mysql-bin.001954
-rw-rw----. 1 mysql mysql  1075257243 Sep 19 06:16 mysql-bin.001955
-rw-rw----. 1 mysql mysql   619405162 Sep 19 13:32 mysql-bin.001956
-rw-rw----. 1 mysql mysql  1072898197 Sep 20 09:44 mysql-bin.001957
-rw-r-----. 1 mysql mysql         190 Sep 19 13:32 mysql-bin.index
  • 解决方法

为了避免数据丢失，需要重新搭建 Slave

磁盘空间不足，日志被截断

  • 报错信息
1
2
3
4
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.006130' at 733347710, the last event read from './mysql-bin.006130' at 733347710, the last byte read from './mysql-bin.006130' at 733347840.'
  • 原因分析

该错误和主库的空间和 sync_binlog 配置有关，当主库 sync_binlog = N 不等于 1 且磁盘空间满时，MySQL 每写 N 次 binlog，系统才会同步到磁盘，但是由于存储日志的磁盘空间满而导致 MySQL 没有将日志完全写入磁盘，binlog event 被截断，从库读取该 binlog 时就会报错 binlog truncated in the middle of event。

  • 解决方法

在从库重新指向到主库下一个可用的 binlog 并且从初始化的位置开始：

1
2
3
stop slave;
change master to master_log_file = 'mysql-bin.006131', master_log_pos = 4;
start slave;

Slave_SQL_Running: No

1026：HA_ERR_RBR_LOGGING_FAILED

  • 报错信息
1
2
3
4
Slave_IO_Running: Yes
Slave_SQL_Running: No
Error_code: 1026
Last_Error: Could not execute Update_rows event on table cosmo-aps.bns_aps_versiontolineinfo; Error writing file '/data/mysql/tmp/MLpTCcJu' (Errcode: 28 - No space left on device), Error_code: 3; Error writing file 'mysql-bin' (errno: 28 - No space left on device), Error_code: 1026; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log mysql-bin.000528, end_log_pos 674303230

  • 原因分析

    • Master 和 Slave 数据不一致导致
    • Slave 服务器重启，事务回滚造成

  • 解决方法

    • 手动跳过
    1
    2
    3
    stop slave ;
    set global sql_slave_skip_counter=1;
    start slave ;

1205：HA_ERR_LOCK_WAIT_TIMEOUT

报错信息：

1
Last_Error: Could not execute Update_rows event on table haier.haier_weixin_user; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.000001, end_log_pos 629671840

1032：HA_ERR_END_OF_FILE

  • 报错信息
1
2
3
4
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table haier.haier_yonghuzhongxin_shebei; Can't find record in 'haier_yonghuzhongxin_shebei', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000030, end_log_pos 130643848
  • 原因分析
1
2
3
191208 23:06:33 [ERROR] Slave SQL: Could not execute Update_rows event on table haier.haier_yonghuzhongxin_shebei; Can't find record in 'haier_yonghuzhongxin_shebei', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000030, end_log_pos 130643848, Error_code: 1032
191208 23:06:33 [Warning] Slave: Can't find record in 'haier_yonghuzhongxin_shebei' Error_code: 1032
191208 23:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000030' position 130643274
  • 解决方法
1
2
3
stop slave;
set global sql_slave_skip_counter=1;
start slave;

1032：HA_ERR_KEY_NOT_FOUND

Last_SQL_Errno: 1032

Last_SQL_Error: Could not execute Delete_rows event on table hce.dim_r_cei_general; Can’t find record in ‘dim_r_cei_general’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000273, end_log_pos 139807561