Using mysqldump Backup to recover error solution from library (Error 1872)

Version: MySQL5.7.22

First, error phenomenon

DBA: (None) > Start slave;

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Check error.log at this time:

2018-08-07t09:28:12.605775z 0 [error] failed to open the relay log   './localhost-relay-bin.000001 '   (relay_log_pos 4). 2018-08-07t09:28:12.605792z 0 [ Error] could not find target log file mentioned in relay log  info in the index file  '/application/mysql/relay-log/mysql-relay-bin.index '  during relay log initialization.2018-08-07T09:28:12.606062Z 0 [ERROR]  Slave: failed to initialize the master info structure for channel   '; its record may still be present in  ' mysql.slave_master_info '  table, consider deleting it.2018-08-07t09:28:12.606298z 0 [error] failed  to create or recover replication info repositories.2018-08-07t09 : 28:12.606316z 0 [note]  some of the channels are not created/initialized properly. check  For additional messages above. you will not be able to start  replication on those channels until the issue is resolved  And the server restarted.

How to solve it? First of all, to understand the basics of MySQL Relay log: From the error point of view, meaning is to start slave, the use of repository information initialized Relay log structure failed. Why did it fail? The localhost-relay-bin.000001 file was originally not found in the Mysql-relay-bin.index file. Here, the answer is very clear, because I use a cold backup file recovery instance, in the MySQL library in the Slave_relay_log_info table still retains the previous relay_log information, so the start slave error.

Second,MySQL Relay Log Introduction

Under the MySQL replication structure, the slave server generates three log files to hold the binary log events of the main library and the location and state where the relay log has been executed.

1. Relay log file: A binary log event that is read from the main library by the IO thread thread, which is executed by the SQL thread thread on the slave for data replication.

2, Master info log: This file holds the status of slave connection master and configuration information, such as user name, password, location of log execution, etc. Prior to version 5.6, the Master.info file was used, starting with 5.6, by configuring--master-info-repository=table in MY.CNF. This information will be written to the Mysql.slave_master_info table instead of the original Master.info file.

3. Relay Log Info log: This file holds the execution location of relay log on slave. Prior to version 5.6, the Relay-log.info file was used, starting with 5.6, by configuring--relay-log-info-repository=table in My.cnf, using Mysql.slave_relay_log_ The info table replaces the original file. The location information in the table is read every time the start slave is executed on the slave.

The new version uses the table instead of the original file, mainly for Crash-safe replication, which greatly improves the reliability from the library. In order to ensure reliability from the library in unexpected cases, the Mysql.slave_master_info and Mysql.slave_relay_log_info tables must be transactional tables, starting with 5.6.6, these tables use the InnoDB storage engine by default. The MyISAM engine is used by default in 5.6.5 and previous versions and can be converted using the following statement:

ALTER TABLE Mysql.slave_master_info Engine=innodb; ALTER TABLE Mysql.slave_relay_log_info Engine=innodb;

"Note" Do not attempt to manually update, insert, delete the contents of the above two tables, in order to avoid unexpected problems.

Third, problem solving

Through the above error and relay log introduction, it is easy to know that because the Mysql.slave_relay_log_info table retains the previous replication information, so that the new from the library can not be found when the corresponding file, then we clean up the records in the table is not enough. Again, do not manually delete the table data, MySQL has provided us with tools: RESET Slave:

Reset slave did those things:

1, delete slave_master_info, slave_relay_log_info Two tables of data, 2, delete all relay log files, and re-create a new relay log file, 3, will not change gtid_executed or gtid_ The value of the purged

The following resolves the issue:

1,DBA: (none) > Reset slave; Query OK, 0 rows Affected (0.00 sec)

2,DBA: (none) > Change Master to ...

3,DBA: (None) > Start slave; Query OK, 0 rows Affected (0.00 sec)

The problem has been solved here.

"Experience": after recovering an instance with a cold backup, reset slave clears the previous old information before starting slave.

Using mysqldump Backup to recover error solution from library (Error 1872)