【MySQL】複製1594錯誤(從庫relaylog損壞)
現象:
mysql> show slave status\G;
*************************** 1. row ***************************
-------------------------------------------------
Master_Log_File: mysql-bin.000191
Read_Master_Log_Pos: 334164088
Relay_Log_File: mysql-srelay.000533
Relay_Log_Pos: 902042386
Relay_Master_Log_File: mysql-bin.000190
Slave_IO_Running: Yes
Slave_SQL_Running: No
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.
error_log如下:
160519 18:42:21 [ERROR] Error reading relay log event: Could not open log file
160519 18:42:21 [ERROR] Slave SQL: 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 abl
e to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
160519 18:42:21 [ERROR] Slave (additional info): File '/data/mysql3307/logs/mysql-srelay.030613' not found (Errcode: 2) Error_code: 29
160519 18:42:21 [Warning] Slave: File '/data/mysql3307/logs/mysql-srelay.030613' not found (Errcode: 2) Error_code: 29
160519 18:42:21 [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.009095' position 1069989357
解析:
從日誌我們看出可能是master或者slave的binlog損壞或者網路問題
但是網路測過是正常的
然後stop slave,發現主庫上的binlog繼續增加,且沒產生error_log
由此懷疑從庫中繼binlog損害
解決:
既然找出了原因,解決就相對容易了
1.從庫執行reset slave,清除掉binlog日誌
2.從出錯前的位置開始啟動複製
本例的複製位置為:
'mysql-bin.009095' position 1069989357
mysql> show slave status\G;
*************************** 1. row ***************************
-------------------------------------------------
Master_Log_File: mysql-bin.000191
Read_Master_Log_Pos: 334164088
Relay_Log_File: mysql-srelay.000533
Relay_Log_Pos: 902042386
Relay_Master_Log_File: mysql-bin.000190
Slave_IO_Running: Yes
Slave_SQL_Running: No
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.
error_log如下:
160519 18:42:21 [ERROR] Error reading relay log event: Could not open log file
160519 18:42:21 [ERROR] Slave SQL: 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 abl
e to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
160519 18:42:21 [ERROR] Slave (additional info): File '/data/mysql3307/logs/mysql-srelay.030613' not found (Errcode: 2) Error_code: 29
160519 18:42:21 [Warning] Slave: File '/data/mysql3307/logs/mysql-srelay.030613' not found (Errcode: 2) Error_code: 29
160519 18:42:21 [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.009095' position 1069989357
解析:
從日誌我們看出可能是master或者slave的binlog損壞或者網路問題
但是網路測過是正常的
然後stop slave,發現主庫上的binlog繼續增加,且沒產生error_log
由此懷疑從庫中繼binlog損害
解決:
既然找出了原因,解決就相對容易了
1.從庫執行reset slave,清除掉binlog日誌
2.從出錯前的位置開始啟動複製
本例的複製位置為:
'mysql-bin.009095' position 1069989357
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30221425/viewspace-2107638/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【MySQL】解決mysql的 1594 錯誤MySql
- MySQL 主從複製錯誤1837MySql
- MySQL主從複製錯誤——列型別轉換錯誤MySql型別
- mysql replication複製錯誤(zt)MySql
- DG rman duplicate 複製庫錯誤
- 解決mysql使用GTID主從複製錯誤問題MySql
- 【Mysql】mysql5.7無損複製MySql
- SqlServer 主從複製錯誤分析--20598SQLServer
- MySQL GTID複製錯誤修復演示MySql
- mysql複製--主從複製配置MySql
- MySQL 主從複製,常見的binlog錯誤及解決方法MySql
- mysql master-slave複製錯誤[解決事例]MySqlAST
- 主庫reset master清理binlog日誌到主從複製錯誤AST
- 物理DG從庫損壞後的重建
- MySQL主從複製_複製過濾MySql
- MySQL主從複製Last_SQL_Errno錯誤程式碼彙總說明MySqlAST
- MySQL 主從複製MySql
- 【MySql】主從複製MySql
- MySQL主從複製MySql
- MySQL-主從複製之搭建從資料庫MySql資料庫
- Mysql(Mariadb)資料庫主從複製MySql資料庫
- mysql線上建立半同步複製的從庫MySql
- mysql資料庫實現主從複製MySql資料庫
- MySQL入門--MySQL複製技術之主從複製MySql
- MySQL入門--MySQL複製技術之主從從級聯複製MySql
- mysql多源複製跳過錯誤處理方法MySql
- mysql資料庫的主從複製和主主複製實踐MySql資料庫
- MySQL主從複製之GTID複製MySql
- MySQL主從複製之半同步複製MySql
- MySQL主從複製之非同步複製MySql非同步
- MySQL主從複製與主主複製MySql
- 複製錯誤案例分享(一)
- 複製錯誤案例分享(二)
- MySQL的主從複製與MySQL的主主複製MySql
- MySQL主從複製、半同步複製和主主複製MySql
- MySQL複製--最快的從庫搭建方法(tar包)MySql
- MySQL主從複製原理MySql
- MySQL的主從複製MySql