Use MySQL's own replication to restore Binlog

If the binary logs that need to be restored are more complex, it is highly recommended that you use MySQL's own replication to recover Binlog instead of using Mysqlbinlog.

Directory [Hide]

• 1. How to Operate
  • 1.1 Execute the Binlog as relay log
  • 1.2 Pull Binlog from a specially built Binlog server
• 2. Other matters needing attention

In the MySQL manual, it is always recommended to use the Mysqlbinlog tool for data recovery at a specified point in time, which is, in fact, a often "frustrating" approach. A better approach is to use the SQL thread in the MySQL internal replication thread to do the recovery.

The idea came from Lazydba, a search in Google, a xaprb Baron Schwartz also mentioned earlier the use of similar methods to restore Binlog, in that discussion, you can also see Jeremy Cole also mentions that using the method recommended in the MySQL manual is difficult, and Mysqlbinlog is logically a mistake-because MySQL has to implement a set of identical logic in two different places, and it will inevitably make mistakes. Using Mysqlbinlog to recover, you may need the following "frustrating" questions:

(*) max_allowed_packet problem (*) Annoying blob/binary/text field problem (*) escape problem with special characters (*) There is no "breakpoint recovery": After an error occurs, there is not enough error, and it is difficult to resume from the failed place

1. How to Operate

This article does not intend to write a step by step document, only to introduce the main ideas and rough steps.

1.1 Execute the Binlog as relay log

Advantages: simple implementation; disadvantage: You need to close the database once (do not be sure not to close the database row);

Idea: Copy the Binlog directly to the relay log directory, and modify the Slave-info related files, so that MySQL will binlog as relay log to execute

Simple Operation steps:

* Close the current instance * Copy the Binlog to the corresponding relay log directory (DataDir or the directory specified by the Relay-log parameter) * Opens the Relay-log-info-file file specified by the Relay-log.info parameter ( The default is the Relay-log.info file in the DataDir directory, and the first two lines of the file are modified. The meanings of these two lines are: the currently executed relay log file, the location of the current execution to the relay log file (position) * Open Relay-log-index file (by the parameter--relay-log-index, the default is the data directory Host_ Name-relay-bin.index) will need to recover the Binlog file full path list that exists in the file * Start the database and start slave io_thread

1.2 Pull Binlog from a specially built Binlog server

This method eliminates the need to start the database, but needs to restart a completely new instance and copy the Binlog to that instance, which is called the Binlog server. Then copy the instance that needs to be restored to this Binlog server. What needs to be done here is to copy the log to the Binlog server directory and modify the corresponding Master-info file so that the repository can dump to these binlog files.

2. Other matters needing attention

* Skip-slave-start is recommended in the configuration file to avoid slave threads starting to execute themselves when not needed.

* When start slave, you can control where slave is executed by start slave until.

* Slave execution of the real site, the Relay-log.info or change master to specify

Good Luck.

http://www.orczhou.com/index.php/2013/11/use-mysql-replication-to-recove-binlog/

Use MySQL's own replication to restore Binlog