首页 > 网络 > 其他 >

解决MySQL主从1594错误

2016-12-10

1594这个错误看起来挺严重的,会提示你binlog文件或者Relay log损坏了,例如binarylogiscorrupted、relay log is corrupted之类的看起来很吓人是吧,多数是由于掉电引发的,这也说明了机房配备UPS的重要性。本文来自真实生产案例

简介

Part1:写在最前

1594这个错误看起来挺严重的,会提示你binlog文件或者Relay log损坏了,例如binarylogiscorrupted、relay log is corrupted之类的看起来很吓人是吧,多数是由于掉电引发的,这也说明了机房配备UPS的重要性。本文来自真实生产案例,感谢网友加内特提供,本人加以故障重现校验。一起来看下如何解决吧。

Part2:完整报错信息

mysql>showslavestatus\G

***************************1.row***************************

Slave_IO_State:Waitingformastertosendevent

Master_Host:192.168.1.250

Master_User:mysync

Master_Port:3306

Connect_Retry:60

Master_Log_File:mysql-bin.000006

Read_Master_Log_Pos:2091061

Relay_Log_File:mysql-relay-bin.000002

Relay_Log_Pos:1675027

Relay_Master_Log_File:mysql-bin.000006

Slave_IO_Running:Yes

Slave_SQL_Running:No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:1594

Last_Error:Relaylogreadfailure:Couldnotparserelaylogevententry.Thepossiblereasonsare:themaster'sbinarylogiscorrupted(youcancheckthisbyrunning'mysqlbinlog'onthebinarylog),theslave'srelaylogiscorrupted(youcancheckthisbyrunning'mysqlbinlog'ontherelaylog),anetworkproblem,orabuginthemaster'sorslave'sMySQLcode.Ifyouwanttocheckthemaster'sbinarylogorslave'srelaylog,youwillbeabletoknowtheirnamesbyissuing'SHOWSLAVESTATUS'onthisslave.

Skip_Counter:0

Exec_Master_Log_Pos:1675875

Relay_Log_Space:2093990

Until_Condition:None

Until_Log_File:

Until_Log_Pos:0

Master_SSL_Allowed:No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:NULL

Master_SSL_Verify_Server_Cert:No

Last_IO_Errno:0

Last_IO_Error:

Last_SQL_Errno:1594

Last_SQL_Error:Relaylogreadfailure:Couldnotparserelaylogevententry.Thepossiblereasonsare:themaster'sbinarylogiscorrupted(youcancheckthisbyrunning'mysqlbinlog'onthebinarylog),theslave'srelaylogiscorrupted(youcancheckthisbyrunning'mysqlbinlog'ontherelaylog),anetworkproblem,orabuginthemaster'sorslave'sMySQLcode.Ifyouwanttocheckthemaster'sbinarylogorslave'srelaylog,youwillbeabletoknowtheirnamesbyissuing'SHOWSLAVESTATUS'onthisslave.

Replicate_Ignore_Server_Ids:

Master_Server_Id:1250

Master_UUID:975d0e4f-bb5d-11e6-98a3-000c29c6361d

Master_Info_File:/data/mysql/master.info

SQL_Delay:0

SQL_Remaining_Delay:NULL

Slave_SQL_Running_State:

Master_Retry_Count:86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:16120521:57:01

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0

1rowinset(0.00sec)

解决办法

Part1:停止从库

mysql>stopslave;

QueryOK,0rowsaffected(0.00sec)

mysql>resetslaveall;

QueryOK,0rowsaffected(0.25sec)

mysql>CHANGEMASTERTOMASTER_HOST='192.168.1.250',MASTER_USER='mysync',MASTER_PASSWORD='MANAGER',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=1675875;

QueryOK,0rowsaffected,2warnings(0.01sec)

上述的POS号就是Exec_Master_Log_Pos: 1675875

Part2:起库校验

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql>showslavestatus\G

***************************1.row***************************

Slave_IO_State:Waitingformastertosendevent

Master_Host:192.168.1.250

Master_User:mysync

Master_Port:3306

Connect_Retry:60

Master_Log_File:mysql-bin.000006

Read_Master_Log_Pos:2091061

Relay_Log_File:mysql-relay-bin.000002

Relay_Log_Pos:354960

Relay_Master_Log_File:mysql-bin.000006

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0

Last_Error:

Skip_Counter:0

Exec_Master_Log_Pos:2030552

Relay_Log_Space:415642

Until_Condition:None

Until_Log_File:

Until_Log_Pos:0

Master_SSL_Allowed:No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:796

Master_SSL_Verify_Server_Cert:No

Last_IO_Errno:0

Last_IO_Error:

Last_SQL_Errno:0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1250

Master_UUID:975d0e4f-bb5d-11e6-98a3-000c29c6361d

Master_Info_File:/data/mysql/master.info

SQL_Delay:0

SQL_Remaining_Delay:NULL

Slave_SQL_Running_State:Readingeventfromtherelaylog

Master_Retry_Count:86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0

1rowinset(0.00sec)

可以看到从库已经开始在追主库了。

Part3:checksum校验

mysql>checksumtablehelei;

+-------------+------------+

|Table|Checksum|

+-------------+------------+

|helei.helei|2698376487|

+-------------+------------+

1rowinset(0.00sec)

mysql>checksumtablehelei;

+-------------+-----------+

|Table|Checksum|

+-------------+-----------+

|helei.helei|416306435|

+-------------+-----------+

1rowinset(0.00sec)

可以看到这里两表已经不一致了,虽然从库完成了同步。但需要重进对主从进行校验了。

主从校验的方法不是本文重点,需要的可移步

http://suifu.blog.51cto.com/9167728/1836551

[root@HE1~]#mysql-uroot-p

Enterpassword:

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis9

Serverversion:5.6.25-logMySQLCommunityServer(GPL)

Copyright(c)2000,2015,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheirrespective

owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>select@@hostname;

+------------+

|@@hostname|

+------------+

|HE1|

+------------+

1rowinset(0.00sec)

mysql>selectcount(*)fromhelei.helei;

+----------+

|count(*)|

+----------+

|4738|

+----------+

1rowinset(0.00sec)

mysql>

[root@HE3~]#mysql-uroot-p

Enterpassword:

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis12

Serverversion:5.6.25-logMySQLCommunityServer(GPL)

Copyright(c)2000,2015,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheirrespective

owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>select@@hostname;

+------------+

|@@hostname|

+------------+

|HE3|

+------------+

1rowinset(0.00sec)

mysql>selectcount(*)fromhelei.helei;

+----------+

|count(*)|

+----------+

|5000|

+----------+

1rowinset(0.00sec)

mysql>

这里能看到两表的行数也已经不一致了。

——总结——

MySQL1594通常由于掉电引起,虽然报错内容看起来挺吓人的,但只要手稳心不慌,可以很快解决。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。

相关文章
最新文章
热点推荐