Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of Slave library recovery in MySQL, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

Status description:

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

Solution:

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

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

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

This error still exists after several attempts to specify a different synchronization location (the location where the error was reported, the location that mysql-bin-000095 just wrote on the master).

In fact, there is already a problem with table records. Take the table mentioned in the description, for example, slave inventory holds about 1200 records, while the master library has 1900 + records. Unless the data is made up manually, because the log that records the operation data has been lost (deleted by me), the nearest consistent log operation execution location cannot be found.

2. Redo the slave library.

Because the data is so different, and I think there is not only one table with different data, so be clean and redo it from the database.

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, but in fact I only have one instance running on the master node. )

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

3) stop the slave process on the master node. (after this stopped, I haven't driven it. I don't know if there's any problem, waiting to be seen.)

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.01sec) 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) 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 where to read the master log:

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

8) the show slave status; on the slave node is running at this time, and the refresh slave status,Read_Master_Log_Pos value begins to increase, and synchronization starts again.

Thank you for reading this article carefully. I hope the article "sample Analysis of Slave Library recovery in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.