MySQL Seventh article ~ Let's talk about how data recovery

Summary: Data recovery is a very important task that the DBA does not do much, but I have to understand

Background: Data mis-operation can be broadly divided into two categories

1 DML operations (such as non-conditional update deletions)

2 DDL operations (such as increasing the reduction field or even truncate even the drop table)

A method for:

1 for Operation:

1 set the time-delay library, the delay setting to see the line requirements, data change frequency, recommended not more than 5 hours.

Delay Library: 1 In fact, regardless of MySQL or mongo,oracle, there is a way to set the delay library for the recovery of Misoperation. This operation is extremely fast, eliminating the problem of recovering data for Binlog parsing

2 Setup method: 1 Stop slave

2 change MASTER to Master_delay = N (number of seconds);

3 Start slave

4 show slave status Sql_delay is the value you set

3 Recovery method: 1 Determine the postion of the misoperation, postpone the delay library, apply the data

2 pay attention to the read position until you stop the replication process until you position the operation

3 Export and import of tables

4 Research and development to determine the validity of data

4 Cons: 1 depends on the speed at which the machine's physical machine application logs are applied (slow recovery when the machine performance is poor), it is recommended to set the delay library to machine performance is very good, reduce recovery time

2 Using Binlog2sql parsing

In fact, many open-source software has long been implemented to parse Binlog extract the specified table statements of the tool, I strongly recommend this

1 How to Install

1 yum-y install git python-pip mysqldb

2 git clone https://github.com/danfengcao/binlog2sql.git && CD Binlog2sql

3 pip Install-r requirements.txt

2 Create the appropriate users and permissions:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT on * * to three kinds of permissions

3 Recovery methods

0 Preparatory work:

1 better prepare a machine with good performance for data recovery

2 This machine has the above three users for all the main libraries on the line

3 when misoperation occurs, the main library manually flush the logs and copy the Misoperation Binlog to this machine (it is not recommended to do it directly on the main library)

1 parsing Binlog

Python binlog2sql.py-h127.0.0.1-p3306-utest_all-p123456-d library-t table--start-file= binlog file--stop-file

          2 Generating a ROLLBACK statement

Python binlog2sql.py--flashback-h127.0.0.1-p3306-utest_all-p123456-d database-T table--start-file= binlog file--stop-file

Note: 1 start-file and Stop-file do not add a path here, otherwise you will be prompted for file is not in MySQL server because it is obtained through the show command

3 SQL ROLLBACK statement will be generated on the application library

4 Validation of research and development validation data

4 Advantages and disadvantages

Advantage: 1 can interpret the code for error shooting and interpretation, or even rewrite (after all, is the py, good understanding)

2 easy to use, common commands can be familiar quickly

3 This tool has been used by many industry DBAs and I am also using

Insufficient: 1 binlog2sql.py compared mysqlbinlog analysis efficiency is not many, so the analysis speed is not very fast

2 parameter binlog_row_image must be full, temporarily not supported minimal

3 This is not a disadvantage for this type of recovery tool and is not supported for DDL operations (DDL is not the data that is affected in binlog). In fact, some of the DDL operations through the tool does not have the means to recover (such as the drop table, if you want to recover the table to find all the Binlog scan recovery, obviously it is not possible, there are many online binlog will be regularly cleaned)

3 General Recovery methods:

Core idea: Yesterday Backup + Today Binlog (before the wrong operation)

Method: 1 Xtarbackup Backup to restore a recovery instance (the recovery method is no longer elaborated) and view the filename+position of the backup point at that time

J 2 Determine 2 position 1: Positon 2 positon required to recover the instance before the error

2 after the operation of the flush logs, will (backup required + misoperation) both Binlog to copy to the recovery machine,

3 Mysqlbinlog --start-position=368315/usr/local/mysql/data/binlog.123456 | mysql-u root-p password

Apply the Binlog of the recovery instance before applying the Binlog before the error operation

4 viewing export and import of tables

5 Research and development confirm data is normal

Cons: 1 time-consuming backup file recovery The time it takes to restore an instance to Binlog, think about it if a super big backup, the online switch a lot of times binlog, this recovery speed I'm afraid no one can accept it

41 Powerful audit and ROLLBACK statements tools: Inception

About this tool I do not elaborate here, and so later, I will be a series of articles to introduce, here first simply say its three core functions

1 powerful audit mechanism, rich rule combination

2 A corresponding ROLLBACK statement will be generated for each SQL execution (easy for recovery)

3 third-party tools can be called Pt-osc

Two summary: The core problem

Recover data 1 Fast 2 to be accurate, estimate your recovery time, and then to develop an accurate answer, this is a professional DBA to have, how to estimate your recovery time, if a friend has this idea, you can leave a message under the blog or add my QQ 390148350, can be elaborated, I'll make a supplement.

\

 

MySQL Seventh article ~ Let's talk about how data recovery