Part1:写在最前
1594这个错误看起来挺严重的，会提示你binlog文件或者Relay log损坏了,例如binary log is corrupted、relay log is corrupted之类的看起来很吓人是吧，多数是由于掉电引发的，这也说明了机房配备UPS的重要性。本文来自真实生产案例，感谢网友加内特提供，本人加以故障重现校验。一起来看下如何解决吧。
Part2:完整报错信息
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
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: 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.
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: 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.
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: 161205 21:57:01
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row
in
set
(0.00 sec)
|
解决办法
Part1:停止从库
|
1
2
3
4
5
6
|
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_HOST=
'192.168.1.250'
,MASTER_USER=
'mysync'
,MASTER_PASSWORD=
'MANAGER'
,MASTER_PORT=3306,MASTER_LOG_FILE=
'mysql-bin.000006'
,MASTER_LOG_POS=1675875;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
|
上述的POS号就是Exec_Master_Log_Pos: 1675875
Part2:起库校验
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
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: Reading event from the relay log
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
1 row
in
set
(0.00 sec)
|
可以看到从库已经开始在追主库了。
Part3:checksum校验
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
[root@HE1 ~]
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 9
Server 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
its
affiliates. Other names may be trademarks of their respective
owners.
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>
[root@HE3 ~]
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 12
Server 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
its
affiliates. Other names may be trademarks of their respective
owners.
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通常由于掉电引起，虽然报错内容看起来挺吓人的，但只要手稳心不慌，可以很快解决。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1879970
，如需转载请自行联系原作者
