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

Description:

Log in to a MySQL database slave node host today and find a large number of mysql-relay-bin files stored under/var/lib/mysql, the earliest file creation date is even 2018, I remember that after the slave library synchronizes the master log operation records, these files will be deleted (default settings will not be deleted, I remember correctly), 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: NULLMaster_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 a file named mysql-bin.00007 on the master node, including mysql-bin.000075, so the slave library couldn't find it and sync it.

Solution:

Re-specify 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 has an existing location for mysql-bin.000095 slave start;

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

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

In fact, table records are already problematic. For the table presented in the description, the slave library holds about 1200 records and the master library has 1900+ records. Unless this data is manually added, since the logs recording the operation data have been lost (deleted by me), it is impossible to find the most recent consistent log operation execution location.

Redo the slave library.

Because the data difference is too big, and I think not only a table has different data problems, so clean point, from the library redo.

1) Compare the configuration information of master and slave node libraries to ensure consistency. (I don't know why I set up dual master mode, actually I only have one instance running on master node ah?)

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

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

4) Record the log location of the database on the master node, and then backup 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, backup the slave node library:

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

6) Redo start: 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, re-specify the location to read the master log:

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

8) show slave status on slave node; Slave_IO_Running and Slave_SQL_Running are running, slave status is refreshed, Read_Master_Log_Pos value also starts to increase, and synchronization starts again.

Summary:

When cleaning up files, pay attention to the location where mysql-bin files are read and written in master and slave node logs! Before deleting, be sure to confirm that the log location has been read in master and slave. Don't delete randomly, otherwise the slave library cannot be synchronized. Even if you forcibly specify the master log reading location on the slave node or skip this error, it does not rule out the possibility of data loss on the slave library.

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.