Shulou(Shulou.com)06/01 Report--

When slave goes down unexpectedly, it is possible to damage the relay log relay-log, and when synchronous replication is turned on again, an error occurs.

Solution: find the synchronized binlog log and POS point, and then synchronize again so that you can have a new relay log.

Take a look at an example below, which simulates the damage to the relay log. The information you can see is as follows:

Click (here) to collapse or open

Mysql > show slave status\ G

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 4

Current database: test

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.10.200

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 220

Relay_Log_File: mysql-relay-bin.000010

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000004

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: 1032

Last_Error: Could not execute Update_rows event on table test.test01; Can't find record in 'test01', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 310

Skip_Counter: 0

Exec_Master_Log_Pos: 120

Relay_Log_Space: 2251

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: 1032

Last_SQL_Error: Could not execute Update_rows event on table test.test01; Can't find record in 'test01', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 310

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 4adfcd1d-4059-11e7-9532-080027d597f9

Master_Info_File: mysql.slave_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: 170808 11:45:26

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0 where several important parameters are involved:

Slave_IO_Running: accept the information of master's binlog

Master_Log_file: reading binlog log name on master

Read_master_Log_Pos: reading the current binlog log POS point on master.

Slave_SQL_Running: perform write operation

Relay_master_Log_File: synchronizing the binlog log name on master.

Exec_master_Log_Pos: synchronizing the POS point of the current binlog log.

Based on the Relay_master_Log_File parameter value and the Exec_master_Log_Pos parameter value.

Relay_Log_File: mysql-relay-bin.000010

Relay_Log_Pos: 283

Next, start resetting master-slave replication:

Click (here) to collapse or open

Mysql > stop slave

Query OK, 0 rows affected (0.04 sec)

Mysql > change master to master_log_file='mysql-relay-bin.000010',master_log_pos=283

Query OK, 0 rows affected (0.06 sec)

Mysql > start slave

Query OK, 0 rows affected (0. 12 sec) can fix the relay log in this way, but after the MySQL5.5 version, you can add relay_log_recovery=1 to slave's my.cnf configuration file.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.