A fast method for restoring fully prepared +binlog at MySQL time

A fast method for restoring fully prepared +binlog at MySQL time

For MySQL version: 4.1~5.7

All database machines are corrupted, only full standby and Binlog are restored to a certain point in time

Method 1: Fully standby +binlog server
Prepare two machines: Binlog server machine, Recovery machine

1. Construct Binlog Server
Copy all the Binlog files to a machine called Master Binlog server
This machine installs the MySQL empty instance, then sets up the Server-id, AUTO.CNF (SERVER-UUID)
Master Binlog Server:
Server-id from Binlog file
Mysql-bin.xxxx
Mysql-bin.index based on how many Binlog files are currently constructed Mysql-bin.index
Server-uuid from Binlog file

2.
The recovery machine needs to recover the full standby.
Mysqldump--single-stranaction--master-data=2

3.
Change the recovered data to master Binlog server based on the Binlog point

4.
Start slave UNTIL master_log_file= ' binlog.000002 ', master_log_pos=829090187

MySQL initiates asynchronous replication of the slave-side until syntax
START SLAVE UNTIL master_log_file= ' binlog.000002 ', master_log_pos=829090187

START SLAVE [Thread_type [, Thread_type] ...]
START SLAVE [Sql_thread] UNTIL
Master_log_file = ' Log_name ', Master_log_pos = Log_pos
START SLAVE [Sql_thread] UNTIL
Relay_log_file = ' Log_name ', Relay_log_pos = Log_pos

Until syntax can not be used on Io_thread

Method 2: Redo the Relay-log
1. Change the Binlog CP on the main library to the target library, to Relay-log's name
2. Use the change master to statement to let the restored library know that self is a library

Official documents
The next example shows an operation that's less frequently employed.
It is used when the slave have relay log files that you want it to
Execute again for some reason. To does this, the master need not being
Reachable. You need the SQL thread with the change MASTER and start
(START SLAVE Sql_thread):
Change MASTER to
Relay_log_file= ' slave-relay-bin.006 ',
relay_log_pos=4025;

1. Full recovery, get the binlog point position to change
file:mysql-bin.006
pos:4025

2. Change the Binlog CP on the main library to the target library, to the Relay-log name
ls mysql-bin.0*|awk-f. ' {print ' CP ' $ Relay-bin '. $ |sh} '

3. Use the change master to statement to let the restored library know that self is a library
Change master to master_host= ' xxx '; XXX Any IP line, even if there is no IP line, this sentence is mainly to let MySQL know that he is a library from
is automatically generated after execution
Relay-log.info
Relay-bin.index

4. If the second step is wrong, delete relay-log.info and Relay-bin.index once, and then perform multiple change master to

5. Construction Relay-bin.index
ls Relay-bin.0*|awk ' {print './' $} ' >relay-bin.index

6. Scrolling Relay-log
Flush logs;

7. Change MASTER to
Relay_log_file= ' relay-bin.006 ',
relay_log_pos=4025;

8. Start slave sql_thread;

9. Show slave status;

A fast method for restoring fully prepared +binlog at MySQL time