网站建设制度制定情况,建站快车凡科,最值得购买 wordpress,wordpress 插件下载3分钟解决MySQL主从1594错误简介Part1:写在最前1594这个错误看起来挺严重的#xff0c;会提示你binlog文件或者Relay log损坏了,例如binary log is corrupted、relay log is corrupted之类的看起来很吓人是吧#xff0c;多数是由于掉电引发的#xff0c;这也说明了机房配备U…3分钟解决MySQL主从1594错误简介Part1:写在最前1594这个错误看起来挺严重的会提示你binlog文件或者Relay log损坏了,例如binary log is corrupted、relay log is corrupted之类的看起来很吓人是吧多数是由于掉电引发的这也说明了机房配备UPS的重要性。本文来自真实生产案例感谢网友加内特提供本人加以故障重现校验。一起来看下如何解决吧。Part2:完整报错信息mysql show slave statusG*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.250Master_User: mysyncMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000006Read_Master_Log_Pos: 2091061Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos: 1675027Relay_Master_Log_File: mysql-bin.000006Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1594Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the masters binary log is corrupted (you can check this by running mysqlbinlog on the binary log), the slaves relay log is corrupted (you can check this by running mysqlbinlog on the relay log), a network problem, or a bug in the masters or slaves MySQL code. If you want to check the masters binary log or slaves relay log, you will be able to know their names by issuing SHOW SLAVE STATUS on this slave.Skip_Counter: 0Exec_Master_Log_Pos: 1675875Relay_Log_Space: 2093990Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1594Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the masters binary log is corrupted (you can check this by running mysqlbinlog on the binary log), the slaves relay log is corrupted (you can check this by running mysqlbinlog on the relay log), a network problem, or a bug in the masters or slaves MySQL code. If you want to check the masters binary log or slaves relay log, you will be able to know their names by issuing SHOW SLAVE STATUS on this slave.Replicate_Ignore_Server_Ids:Master_Server_Id: 1250Master_UUID: 975d0e4f-bb5d-11e6-98a3-000c29c6361dMaster_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State:Master_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp: 161205 21:57:01Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 01 row in set (0.00 sec)解决办法Part1:停止从库mysql stop slave;Query OK, 0 rows affected (0.00 sec)mysql reset slave all;Query OK, 0 rows affected (0.25 sec)mysql CHANGE MASTER TO MASTER_HOST192.168.1.250,MASTER_USERmysync,MASTER_PASSWORDMANAGER,MASTER_PORT3306,MASTER_LOG_FILEmysql-bin.000006,MASTER_LOG_POS1675875;Query OK, 0 rows affected, 2 warnings (0.01 sec)上述的POS号就是Exec_Master_Log_Pos: 1675875Part2:起库校验mysql start slave;Query OK, 0 rows affected (0.01 sec)mysql show slave statusG*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.250Master_User: mysyncMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000006Read_Master_Log_Pos: 2091061Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos: 354960Relay_Master_Log_File: mysql-bin.000006Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 2030552Relay_Log_Space: 415642Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 796Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1250Master_UUID: 975d0e4f-bb5d-11e6-98a3-000c29c6361dMaster_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Reading event from the relay logMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 01 row in set (0.00 sec)可以看到从库已经开始在追主库了。Part3:checksum校验mysql checksum table helei;-------------------------| Table | Checksum |-------------------------| helei.helei | 2698376487 |-------------------------1 row in set (0.00 sec)mysql checksum table helei;------------------------| Table | Checksum |------------------------| helei.helei | 416306435 |------------------------1 row in set (0.00 sec)可以看到这里两表已经不一致了虽然从库完成了同步。但需要重进对主从进行校验了。主从校验的方法不是本文重点需要的可移步http://suifu.blog.51cto.com/9167728/1836551[rootHE1 ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 9Server version: 5.6.25-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or h for help. Type c to clear the current input statement.mysql select hostname;------------| hostname |------------| HE1 |------------1 row in set (0.00 sec)mysql select count(*) from helei.helei;----------| count(*) |----------| 4738 |----------1 row in set (0.00 sec)mysql[rootHE3 ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 12Server version: 5.6.25-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or h for help. Type c to clear the current input statement.mysql select hostname;------------| hostname |------------| HE3 |------------1 row in set (0.00 sec)mysql select count(*) from helei.helei;----------| count(*) |----------| 5000 |----------1 row in set (0.00 sec)mysql这里能看到两表的行数也已经不一致了。——总结——MySQL1594通常由于掉电引起虽然报错内容看起来挺吓人的但只要手稳心不慌可以很快解决。由于笔者的水平有限编写时间也很仓促文中难免会出现一些错误或者不准确的地方不妥之处恳请读者批评指正。
