磁盘写满导致MySQL复制失败的解决方案

作者：发布时间:2021-04-05 17:40:00点击:2991

今天在线上发现一个问题，由于监控没有覆盖到，某台机器的磁盘被写满了，导致线上MySQL主从复制出现问题。问题如下：

12345678910111213141516171819202122localhost.(none)>show slave status\G*************************** 1. row ***************************Slave_IO_State:Master_Host: 10.xx.xx.xxMaster_User: replicaMaster_Port: 5511Connect_Retry: 60Master_Log_File:Read_Master_Log_Pos: 4Relay_Log_File: relay-bin.001605Relay_Log_Pos: 9489761Relay_Master_Log_File:Slave_IO_Running: NoSlave_SQL_Running: NoLast_Errno: 13121Last_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 byrunning 'mysqlbinlog' on the relay log), a network problem, the server was unable to fetch akeyring key required to open an encrypted relay log file, or a bug in the master's orslave'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.于是查看errorlog，发现errorlog中的内容如下：

1234567891011121314151617181920

2021-03-31T11:34:39.367173+08:00 11 [Warning] [MY-010897] [Repl] Storing MySQL user name orpassword information in the master info repository is not secure and is therefore notrecommended. Please consider using the USER and PASSWORD connection options for START SLAVE;see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2021-03-31T11:34:39.368161+08:00 12 [ERROR] [MY-010596] [Repl] Error reading relay logevent for channel '': binlog truncated in the middle of event; consider out of disk space

2021-03-31T11:34:39.368191+08:00 12 [ERROR] [MY-013121] [Repl] Slave SQL for channel '': Relaylog read failure: Could not parse relay log event entry. The possible reasons are: the master'sbinary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), theslave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the master's or slave's MySQL code. If you want to check themaster'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: MY-013121

2021-03-31T11:34:39.368205+08:00 12 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". Westopped at log 'mysql-bin.000446' position 9489626

天津BGP当磁盘写满的情况发生之后，mysql服务无法向元信息表中写数据，relaylog也可能已经不完整了，如果直接清理了服务器上的磁盘数据，再去重新changemaster修改主从复制关系，可能会出现报错，不能直接修复，因为这不是一个正常的主从复制关系断裂场景。

所以，正确的做法应该是：

1、清理服务器的磁盘

2、重启复制关系断开的那个从库

3、重新resetslaveall、changemaster来搭建主从复制关系即可

上一篇：交换机口子区别

上一篇：超大规模数据中心的时代已经到来