Record the actual combat record of MySQL Slave library once

Status description:

Today, I logged into an MySQL database slave node host and found that a large number of mysql-relay-bin files were stored under/var/lib/mysql. The earliest file creation date was even 2018. I remember that these files will be deleted after the slave library synchronizes the log operation records of master (the default setting will not be deleted, I remember wrong), so I checked the status of 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 mysql-bin. 00007 format on the master node, including mysql-bin. 000075, so the slave library cannot find the file and cannot synchronize.

Solution:

1. Reassign the synchronization location on the slave library. (Not feasible)

slave stop;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000095',MASTER_LOG_POS=869242147; //mysql master Node mysql-bin.000095 Existing location of 
slave start;

show slave status on slave node still reports errors, and the specific error content has not been copied. I only remember that errno is 1236, Slave_IO_Running process does not run, and Slave_SQL_Running process runs, which roughly describes that there is a problem with a table in a certain library.

The error persists after multiple attempts to specify different sync locations (the location where the error was reported, where mysql-bin-000095 was just written on master).

In fact, table records are already problematic. Take the table mentioned in the description. The slave inventory has about 1200 records, while the master library has 1900 + records. Unless these data are supplemented manually, because the log recording operation data has been lost (deleted by me), the nearest log operation execution location cannot be found.

2. Redo the slave library.

Because the data difference is too big, and I think not only one table has the problem of different data, so clean it up and redo it from the library.
1) Compare the configuration information of master and slave node libraries to ensure 1. (I don't know why the dual main mode is set, in fact, I only have one instance running on master node.)

2) Check the traffic situation on master and slave nodes (show processlist) to ensure that there is no traffic access on the slave library to be redone.

3) Stop the slave process on the master node. (After this stops, I haven't opened it. I don't know if there is any problem, wait and see.)

4) Record the logging 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 safe, back up the slave node library:

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

6) Start redo: Copy the master library backup file to the slave node and import the backup file

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

7) On the slave node, reassign where to read the master log:

slave stop;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000095',MASTER_LOG_POS=871760173; //POS For the record just now master Node logging location 
slave start;

8) show slave status on slave node; At this time, Slave_IO_Running, Slave_SQL_Running are all running, slave status is refreshed, and the values of Read_Master_Log_Pos also start to increase, and synchronization starts again.

Summary:

When cleaning up files, pay attention to where mysql-bin files are read and written in master and slave nodes! Before deleting, 1 must confirm that the log position has been read in master and slave, and do not delete it indiscriminately, otherwise the slave library cannot be synchronized. Even if the master log reading position is forcibly specified on the slave node or the error is skipped, the possibility of data loss on the slave library cannot be ruled out.