Two incomplete restoration methods for MySQL

Source: Internet
Author: User

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.