My09_mysql specify point-in-time recovery

Scenario Description

*********************************************

The remote server is fully prepared and has been restored, using Binlog server Backup Binlog

After that, all tables in the library were deleted, and then a batch of tables were rebuilt, and a large amount of data was inserted, and the rebuilt table had a table with the same name as the original table (for example, Sbtest1)

Then ask to recover the data before deleting one of the table Sbtest1

Recovery ideas

************************************************

As the remote server is fully prepared and has been fully prepared for recovery, then you can follow the following ideas

1. A location before the transaction location of the delete operation is found from the remote server backup Binlog POS

2. Create a user on the remote server that has all permissions on table Sbtest1 only to recover sbtest1, skipping other transactions

3. Find a fully prepared starting point and also the starting point for recovery (after adding the Master-data parameter to the backup, the backup file is initially-change MASTER to master_log_file= ' mysql-bin.000012 ', master_log_pos= 190;)

Recovery readiness

**********************************************

Fully prepared starting point
--Change MASTER to master_log_file= ' mysql-bin.000016 ', master_log_pos=190;

Original Library Settings data
Update sbtest3 set pad= ' sweep this two microcode, free to receive small gifts ' where id=101;
Update sbtest2 set pad= ' sweep this two microcode, free to receive small gifts ' where id=101;
Update sbtest1 set pad= ' sweep this two microcode, free to receive small gifts ' where id=101;

Error operation, full table update
Update sbtest3 set pad= ' This activity has ended ' where id=101;
drop table sbtest3;

Confirm the specific location of the error operation

Mysqlbinlog--no-defaults--database=txdb-v-v--base64-output=decode-rows--skip-gtids--start-datetime= "2018-07-31 16:10:00 "--stop-datetime=" 2018-07-31 16:20:00 "mysql-bin.000016>/tmp/3.sql

Split-l 100000 11.sql S_

BEGIN/*!*/; # at319#180731  -: -: AboutServerID 3318End_log_pos416rows_query# update sbtest3 set pad='Sweep This two microcode, free to receive small gifts'whereID=101# at416#180731  -: -: AboutServerID 3318End_log_pos469Table_map: ' txdb '. ' Sbtest3 ' mapped to number the# at469#180731  -: -: AboutServerID 3318End_log_pos861Update_rows:tableID  theflags:stmt_end_f### UPDATE ' txdb '. ' Sbtest3 ' # # # where### @1=101 /*INT meta=0 nullable=0 is_null=0*/###   @2=150912 /*INT meta=0 nullable=0 is_null=0*/###   @3='99505216625-44652318903-87633088031-12891470052-20814553735-53032754476-32543784485-39935334177-82742270613-557675222 -' /*STRING (meta=61032) nullable=0 is_null=0*/###   @4='46607138393-89004745809-36733255854-15721737050-37472852755' /*STRING (meta=65204) nullable=0 is_null=0*/# # set### @1=101 /*INT meta=0 nullable=0 is_null=0*/###   @2=150912 /*INT meta=0 nullable=0 is_null=0*/###   @3='99505216625-44652318903-87633088031-12891470052-20814553735-53032754476-32543784485-39935334177-82742270613-557675222 -' /*STRING (meta=61032) nullable=0 is_null=0*/###   @4='Sweep This two microcode, free to receive small gifts' /*STRING (meta=65204) nullable=0 is_null=0*/# at861#180731  -: -: AboutServerID 3318End_log_pos888Xid =2071COMMIT/*!*/; # at888# at949#180731  -: the: -ServerID 3318End_log_pos1017Query thread_id= -Exec_time=0Error_code=0SET TIMESTAMP=1533024953/*!*/;

The above method in MySQL backup and recovery is universal, a fully prepared, Binlog, recovery starting point and endpoint. There are several ways to recover, as illustrated below.

The most common recovery

**************************************************************

Suitable for small data volume, that is, the full-time distance failure at the time of the production of Binlog, basically the data needed to recover in a Binlog file

Mysqlbinlog--no-defaults--database=txdb--skip-gtids--start-position=190--stop-position=888 mysql-bin.000016 > /tmp/rec.sql

Confirm the end of the/tmp/rec.sql file, if there are any deletions, make sure no more deletions are done.

mysql-uautomng-pautomng_123-p3319--database=txdb </tmp/rec.sql

Then confirm the database, the data has reached the expected point in time

Mysql> SelectPad fromSbtest3whereId=101;+-----------------------------------------+|Pad|+-----------------------------------------+|Sweep this two microcode, free to receive small gifts|+-----------------------------------------+1Rowinch Set(0.01Sec

Export the table from the recovery machine, and then import the library to the wrong operation

mysqldump-uautomng-pautomng_123-p3319--databases txdb--tables sbtest3 >/tmp/sbtest3.sql

Scp/tmp/sbtest3.sql mysql01:/tmp

A statement with the delete table is already on the default in/tmp/sbtest3.sql

DROP TABLE IF EXISTS ' sbtest3 ';

Mysql-uautomng-pautomng_123-s/data0/mysql/log/bak/mysql_bak.sock txdb</tmp/sbtest3.sql

Confirm that the database for misoperation is recovering

Mysql> SelectPad fromSbtest3whereId=101;+-----------------------------------------+|Pad|+-----------------------------------------+|Sweep this two microcode, free to receive small gifts|+-----------------------------------------+1Rowinch Set(0.00Sec

Review summary

This example version mysql5.7.22

Don't do it yourself again, never know how many pits there are! Other methods will be added later.

My09_mysql specify point-in-time recovery