An example of MySQL replication error reporting

Posted by BMorganVA on Sun, 05 Dec 2021 02:55:57 +0100

A master-slave copy error reporting problem encountered before, although the solution process is relatively fast, I feel it still has some reference significance. When encountering similar error code s, you can refer to:

The error message is as follows:

             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

From the perspective of error type, it should be that the corresponding relay log event cannot be found from the library. There are many such problems. Four prompts are given in the error message:

1. There is a problem with the binlog of the master

2. There is a problem with the slice's binlog

3. Network problems

4. It may be a bug in the MySQL source code in the master or slave (confess ~ ~ ~)

This reminds me that this machine just underwent disk expansion yesterday. Because it is a slave library, it was shut down and expanded directly, and then the MySQL instance was restarted. It may be shut down, expanded and restarted, which has a certain impact on the instance... (as for what impact? I think the most likely one is the change of configuration file parameters)

What shall I do? Use the network management thinking to solve the following problems first:

"Network management, there is something wrong with this machine. Let's have a look",

"Restart and try",

"Restart, no",

"Change the machine"

Try restarting replication first, as follows:

stop slave;start slave;show slave status\G

Still not, but the error message has changed:

             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: 1593
                   Last_Error: Error initializing relay log position: Could not find target log file mentioned 
                   in relay log info in the index file './relay-bin.index' during relay log initialization
                 Skip_Counter: 0

This prompt means that the target relay log index file is not found. The index file is defined in the specified directory. The relay log file path of the master-slave database is checked (although there is no relay log on the master database). The relevant parameters are as follows:

Main library
mysql:(none) 09:42:05>>show variables like '%relay%';
+-----------------------+--------------------------------------------+
| Variable_name         | Value                                      |
+-----------------------+--------------------------------------------+
| max_relay_log_size    | 0                                          |
| relay_log             | /data/mysql_3306/log/slave-relay-bin       |
| relay_log_index       | /data/mysql_3306/log/slave-relay-bin.index |
| relay_log_info_file   | relay-log.info                             |
| relay_log_purge       | ON                                         |
| relay_log_recovery    | OFF                                        |
| relay_log_space_limit | 0                                          |
+-----------------------+--------------------------------------------+
7 rows in set (0.00 sec)

From library
mysql:(none) 09:46:32>>show variables like '%relay%';
+---------------------------+----------------+
| Variable_name             | Value          |
+---------------------------+----------------+
| max_relay_log_size        | 0              |
| relay_log                 |                |
| relay_log_basename        |                |
| relay_log_index           |                |
| relay_log_info_file       | relay-log.info |
| relay_log_info_repository | FILE           |
| relay_log_purge           | ON             |
| relay_log_recovery        | OFF            |
| relay_log_space_limit     | 0              |
+---------------------------+----------------+
9 rows in set (0.00 sec)

It can be seen that the relevant relay log path is not configured on the slave library, so the find linux command is used to find the location of the relay-bin.index file of the slave library, which is placed under the / data folder by default. It seems that the above error is related to the location of the relay log file.

For the above reasons, we need to use the IO thread to regenerate the corresponding relay log to apply it in the slave library, so we use the following method to repair it in the slave Library:

1,reset slave;

2. Record the corresponding offset master_log_file and read_master_log_pos

3. Reuse the change master statement and the offset recorded above for replication

4,start slave; Restart the replication relationship

Programmer Think

An example of MySQL replication error reporting

Hot Topics