溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下如何解決磁盤寫滿導致MySQL復制失敗的問題，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
今天在線上發現一個問題，由于監控沒有覆蓋到，某臺機器的磁盤被寫滿了，導致線上MySQL主從復制出現問題。問題如下：
localhost.(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.xx.xx.xx Master_User: replica Master_Port: 5511 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-bin.001605 Relay_Log_Pos: 9489761 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Last_Errno: 13121 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, 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 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 log，發現error log中的內容如下：
2021-03-31T11:34:39.367173+08:00 11 [Warning] [MY-010897] [Repl] 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. 2021-03-31T11:34:39.368161+08:00 12 [ERROR] [MY-010596] [Repl] Error reading relay log event 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 '': 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, 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 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: 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". We stopped at log 'mysql-bin.000446' position 9489626
從描述中可以看到，error log是比較智能的，發現了磁盤問題，并提示我們需要"consider out of disk space"
登錄服務器，很快就發現是MySQL所在的服務器磁盤使用率達到100%了，問題原因跟error log中的內容一致。
現在就解決這個問題。基本的思路就是清理磁盤文件，然后重新搭建復制關系，這個過程似乎比較簡單，但是實際操作中，在搭建復制關系的時候出現了下面的報錯：
### 基于gtid的復制，想重新搭建復制關系 localhost.(none)>reset slave; ERROR 1371 (HY000): Failed purging old relay logs: Failed during log reset localhost.(none)>reset slave all; ERROR 1371 (HY000): Failed purging old relay logs: Failed during log reset
第一步：因為復制是基于gtid進行的，所以直接記錄show slave status的狀態后，就可以重新reset slave，并利用change master語句來重建復制關系了。
但是卻出現上面的報錯，從報錯信息看是mysql無法完成purge relay log的操作，這看起來不科學。好吧，既然你自己不能完成purge relay logs的操作，那就讓我來幫你吧。
第二步：手工rm -f 刪除所有的relay log，發現報錯變成了：
localhost.(none)>reset slave all; ERROR 1374 (HY000): I/O error reading log index file
嗯，好吧，問題沒有得到解決。
然后思考了下，既然不能通過手工reset slave 來清理relay log，直接stop
slave 然后change master行不行呢？
第三步：直接stop slave，然后change master，不執行reset slave all的語句，結果如下：
localhost.(none)>change master to master_host='10.13.224.31', -> master_user='replica', -> master_password='eHnNCaQE3ND', -> master_port=5510, -> master_auto_position=1; ERROR 1371 (HY000): Failed purging old relay logs: Failed during log reset
得，問題依舊。
第四步：反正復制已經報錯斷開了，執行個start slave看看，結果戲劇性的一幕出現了：
localhost.(none)>start slave; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 262 Current database: *** NONE *** Query OK, 0 rows affected (0.01 sec) localhost.(none)> [root@ ~]#
執行start slave之后，實例直接掛了。
到這里，復制徹底斷開了，從庫實例已經掛了。
第五步：看看實例還能不能重啟，嘗試重啟實例，發現實例還能起來。實例重新起來后，查看復制關系，結果如下：
localhost.(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log Master_Host: 10.xx.xx.xx Master_User: replica Master_Port: 5511 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-bin.001605 Relay_Log_Pos: 9489761 Relay_Master_Log_File: Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 13121 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, 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 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. Skip_Counter: 0
復制關系依舊報錯。
第六步：重新reset slave all看看，結果成功了。
localhost.(none)>stop slave; Query OK, 0 rows affected (0.00 sec) localhost.(none)>reset slave all; Query OK, 0 rows affected (0.03 sec)
第七步：重新搭建復制關系并啟動復制
localhost.(none)>change master to master_host='10.xx.xx.xx', -> master_user='replica', -> master_password='xxxxx', -> master_port=5511, -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) localhost.(none)>start slave; Query OK, 0 rows affected (0.00 sec) localhost.(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.xx.xx.xx Master_User: replica Master_Port: 5511 Connect_Retry: 60 ... Slave_IO_Running: Yes Slave_SQL_Running: Yes
發現實例的復制關系可以建立起來了。
當磁盤寫滿的情況發生之后，mysql服務無法向元信息表中寫數據，relay log也可能已經不完整了，如果直接清理了服務器上的磁盤數據，再去重新change master修改主從復制關系，可能會出現報錯，不能直接修復，因為這不是一個正常的主從復制關系斷裂場景。
所以，正確的做法應該是：
1、清理服務器的磁盤
2、重啟復制關系斷開的那個從庫
3、重新reset slave all、change master來搭建主從復制關系即可
以上是“如何解決磁盤寫滿導致MySQL復制失敗的問題”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
