Troubleshooting MySQL replication relay log read failure

First, the problem description

In MySQL master-slave replication mode, slave reported the wrong "relay log read failure", causing the master-slave synchronization to stop.

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:

master_host:10.0.0.93

Master_user:slaveuser

master_port:3306

Connect_retry:60

master_log_file:dd-bin.002542

read_master_log_pos:752973519

relay_log_file:dd-relay.002949

relay_log_pos:950583160

relay_master_log_file:dd-bin.002540

Slave_io_running:yes

Slave_sql_running:no

replicate_do_db:

Replicate_ignore_db:mysql,test,information_schema

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'll be a able to know their names by issuing ' SHOW s Lave STATUS ' on this slave.

skip_counter:0

exec_master_log_pos:950583017

relay_log_space:2900478067

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'll be a able to know their names by issuing ' SHOW s Lave STATUS ' on this slave.

Replicate_ignore_server_ids:

master_server_id:93

1 row in Set (0.00 sec)

=============================================

Second, reason analysis

The error message is from the library "unable to read entries in relay log", possibly due to the Binglog error of the Master library, or the slave library's relay log error. or for network problems and bug causes.

Generally due to network failure or slave library pressure is too large, resulting in relay-log format error. Find the currently synchronized point in time, reset master-slave synchronization, will generate a new trunk log, return to normal.

Third, problem-handling

From the output of "show slave Status\g", find the following information:

Relay_master_log_file: dd-bin.002540//slave Library Read Master's Binlog

Exec_master_log_pos: 950583017//position location points that have been performed on the slave

Stop slave to slave the Binlog file that has been read, and the position that has been executed as the starting point to reset the synchronization. A new relay log is generated and the problem is resolved.

(You do not need to specify Host,user,password, etc., default to use the currently set)

Mysql>slave stop; Mysql>change Master to master_log_file= ' dd-bin.002540 ', master_log_pos=950583017; Mysql>slave start;

Iv. Verification Results

Check again, the error has been resolved, slave started chasing master's log

Mysql>show slave Status\g

exec_master_log_pos: The position that have been performed on 225927489//slave have changed

Seconds_behind_master: 58527 //slave The time behind the main library, in seconds

After a few seconds, check again. Closer to master Sync

Mysql>show slave Status\g

exec_master_log_pos:307469867

seconds_behind_master:29570

Five, verify the last synchronization log position from the Relay-log. (as validation, not required)

can also be from relay_log_file:dd-relay.002949 records to find the position that are currently synchronized.

Use Mysqlbinlog to view the last record dd-relay.002949 the trunk log

#mysqlbinlog dd-relay.002949 >/tmp/relay_log.sql #tail/tmp/relay_log.sql # at 950582947 #140914 3:32:30 server ID end_log_pos 950582990 querythread_id=1256813exec_time=0error_code=0 SET timestamp=1410636750/*!*/; INSERT into Blog_month_post_count (ID, ' count ') of values (34509691, 0) on duplicate key update ' count ' =values (' count ') /*!*/; # at 950583133 #140914 3:32:30 server ID end_log_pos 950583017 Xid = 14033635514 commit/*!*/; # at 950583160 #140914 3:32:30 server ID end_log_pos 950583092 querythread_id=1256815exec_time=0error_code=0 SET timestamp=1410636750/*!*/; BEGIN /*!*/; DELIMITER; # End of log file ROLLBACK/* Added by Mysqlbinlog */; /*!50003 SET [email protected]_completion_type*/;

as you can see, the last successful execution of position in the trunk log is 950583017 , with exec_master_log_pos:950583017 records are consistent.

Troubleshooting MySQL replication relay log read failure