A practical record of restoring a MySQL Slave library

Description of the situation:

Today, I logged into a MySQL database slave node host and found a large number of mysql-relay-bin files stored in /var/lib/mysql. The earliest file creation date was even in 2018. I remember that after the slave library synchronized the log operation records of the master, these files would be deleted (the default setting would not delete them, I remembered it wrong), so I checked the status of the slave library and found the following error:

mysql> show slave status\G;
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: *.*.*.*
         Master_User: dbsync
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File:mysql-bin.000095
     Read_Master_Log_Pos: 869242147
        Relay_Log_File:mysqld-relay-bin.000146
        Relay_Log_Pos: 871280529
    Relay_Master_Log_File: mysql-bin.000075
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
       Replicate_Do_DB: cdb,cdb_admin
     Replicate_Ignore_DB: mysql
      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: 871280384
       Relay_Log_Space: 19994786573
       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.
1 row in set (0.00 sec)

ERROR: 
No query specified

reason:

I deleted the file named in the format of mysql-bin.00007 on the master node, including mysql-bin.000075. As a result, the slave library could not find the file and could not be synchronized.

Solution:

1. Re-specify the synchronization position on the slave library. (Not feasible)

slave stop;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000095',MASTER_LOG_POS=869242147; //The existing position of mysql-bin.000095 on the mysql master node slave start;

When I run show slave status on the slave node, an error message is still reported. I did not copy down the specific error message, but I remember that errno is 1236, the Slave_IO_Running process is not running, and the Slave_SQL_Running process is running. The general description is that there is a problem with a table in a certain library.

After multiple attempts to specify different synchronization locations (the location where the error was reported, the location where mysql-bin-000095 on the master had just been written), the error still occurred.

In fact, there are already problems with the table records. Take the table mentioned in the description as an example. The slave database stores about 1,200 records, while the master database has more than 1,900 records. Unless I manually add this data, I won't be able to find the most recent consistent log operation execution location because the logs recording the operation data have been lost (deleted by me).

2. Redo the slave library.

Since the data difference is too big, and I think the problem is not limited to one table, so be clean and redo the slave database.
1) Compare the master and slave node library configuration information to ensure consistency. (I don't know why the dual-master mode is set, but I actually only have one instance running on the master node?)

2) Check the traffic status on the master and slave nodes (show processlist) to ensure that no business traffic is connected to the slave database to be redone.

3) Stop the slave process on the master node. (I haven't opened it since it stopped, so I don't know if there are any problems. I'll wait and see)

4) Record the log record location of the library on the master node, and then back up the database:

mysql> show master status;
+------------------+----------+-------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------------------+------------------+
| mysql-bin.000095 | 871760173 | cdb,cdb_admin | mysql |
+------------------+----------+-------------------------------+------------------+
1 row in set (0.01 sec)
 mysqldump -u root -p --databases cdb,cdb_admin > bak.master.sql

5) To be on the safe side, back up the slave node library:

mysqldump -u root -p --databases cdb,cdb_admin > bak.slave.sql

6) Restart: Copy the master database backup file to the slave node and import the backup file

mysql -u root -p < bak.master.sql

7) On the slave node, re-specify the location for reading the master log:

slave stop;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000095',MASTER_LOG_POS=871760173; //POS is the master node log record position just recorded slave start;

8) Show slave status on the slave node; at this time, Slave_IO_Running and Slave_SQL_Running are both running. Refresh the slave status, and the value of Read_Master_Log_Pos also starts to increase, and synchronization starts again.

Summarize:

When cleaning files, pay attention to the location of the mysql-bin file in the master and slave node log reading and writing! Before deleting, you must confirm that the log position has been read on both the master and slave nodes. Do not delete randomly, otherwise the slave database will be unable to synchronize. Even if you forcibly specify the master log reading position on the slave node or skip the error, the possibility of data loss on the slave database cannot be ruled out.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.