Two incomplete restoration methods for MySQL
MySQL 5.6.14
The backup at AM in the production environment cannot be completely recovered until noon.
(The content of xtrabackup_binlog_pos_innodb is mysql-bin.006946 3784607)
Method 1: mysqlbinlog
1. Find the binlog to be restored
Enter the binlog directory and run
Ll | awk '{print $9}'>/tmp/binlog. index
Modify the/tmp/binlog. index file to delete the file name that does not need to be restored.
2. parse binlog
Time mysqlbinlog $ (cat/tmp/binlog. index) -- start-position = 3784607 -- stop-datetime = '2017-07-13 12:00:00 '>/tmp/binlog
Real 2m32. 399 s
User 1m24. 514 s
Sys 1m5. 803 s
3. Set MySQL Server Parameters
Max_allowed_packet = 100 M
Innodb_flush_log_at_trx_commit = 0
4. Execute
Time mysql-uroot-p-S mysql. sock -- force </tmp/binlog
Real 50m24. 341 s
User5m1. 426 s
Sys 3m32. 348 s
It takes 50 minutes to execute a 3.4G file.
Method 2: Copy, run with SQL thread
1. Find the binlog to be restored
Enter the binlog directory and run
Ll | awk '{print $9}'>/tmp/binlog. index
Modify the/tmp/binlog. index file to delete the file name that does not need to be restored.
2. Copy the required binlog to a new directory.
Mkdir/tmp/testlog
Cp $ (cat/tmp/binlog. index)/tmp/testlog/
3. Modify server Parameters
Max_allowed_packet = 100 M
Innodb_flush_log_at_trx_commit = 0
Server_id = 111
Relay_log =/tmp/testlog/mysql-bin
Relay_log_index =/tmp/testlog/mysql-bin.index
Skip_slave_start = 1
Where
The server_id must be changed to a different value; otherwise, the binlog is directly discarded.
Skip_slave_start prevents automatic replication
4. Start the instance and generate copied files
Change master
Master_host = 'localhost ',
Master_port = 3306,
Master_user = 'repl ',
Master_password = 'repl ',
Master_log_file = 'dummy. binlog ',
Master_log_pos = 777;
Master is a casual value, mainly to generate master.info, relay-log.info and Other Related Files
5. Close the instance and modify the file
Modify relay-log.info
Modify/tmp/testlog/mysql-bin.index
Awk '{print "/tmp/testlog/" $ 1'}/tmp/binlog. index>/tmp/testlog/mysql-bin.index
6. Start the instance and start replication.
Start slave SQL _thread UNTIL RELAY_LOG_FILE = 'mysql-bin.006982 ', RELAY_LOG_POS = 37300415;
It takes 43 minutes to recover.
Mysqlbinlog is simpler, but once an error occurs, it is easy to give up.
Replication method. The preliminary configuration is complicated, but errors occur, making it easier to correct.
In terms of efficiency, the difference is not big
If a large number of binlogs need to be restored, we recommend that you use the replication method.
If only a small number of binlogs need to be restored, mysqlbinlog is more concise.
This article permanently updates the link address: