MySQL combined backup +binlog to recover deleted operation data

Restore data with backup +binlog.

Scene: Last night was made fully prepared, this morning the user accidentally deleted a table by mistake.

Solution: It is not recommended to restore directly in the original production environment, it is recommended to restore the whole library + log on another machine and then import into the production environment.

1) Create a table

Select Now (); CREATE TABLE ITPUXFG1 (' id ' int () unsigned not null auto_increment, ' name ' varchar (+) NOT null, ' Sex ' enum (' m ', ' W ') ' N ' ull default ' m ', ' Age ' tinyint (3) unsigned not null,primary key (' id ')) engine=innodb default Charset=utf8;insert into ITPU X.ITPUXFG1 (' name ', ' sex ', ' age ') VALUES (' Itpux1 ', ' W ', +), (' itpux2 ', ' m ', '), (' Itpux3 ', ' W ', Max), (' itpux4 ', ' m ', 24), (' Itpux5 ', ' W ', +); Commit;select * from ITPUX.ITPUXFG1;

2) Make Backup logical backup

Mysqldump-uroot-p-f-r--all-databases > Alldb_bak.sql

3) Simulate business operations in the morning

Show master status; --154select now (); --2018-04-27 06:27:40update itpux.itpuxfg1 set name= ' itpux04 ' where Id=4;commit;select * from Itpux.itpuxfg1;select now ( );  --2018-04-27 06:28:03update itpux.itpuxfg1 set name= ' itpux05 ' where Id=5;commit;select * from Itpux.itpuxfg1;show Master Status --890

4) Accidental deletion at noon

Select Now (); --2018-04-27 06:29:00drop table Itpuxfg1;select * from itpux.itpuxfg1;show master status; --1078

5) Restore in another machine (my case is local, production is not local)

Show master status; Use this file name to back up the required Binlog

--Remember to copy binlog logs

Show Binlog events in ' itpuxdb-binlog.000003 ';

Mysqlbinlog itpuxdb-binlog.000003 |grep "DROP TABLE"

Mysqlbinlog itpuxdb-binlog.000003 |grep "Itpuxfg1" ( note the log two different versions please differentiate )

Demonstrate:

Remove the Itpux library from the drop database itpux;

Recovery:

Create the database First:

Mysql> CREATE DATABASE itpux default character set UTF8; -If the condition is not clear, it is recommended to query the original library show create database mysql;

Mysql-uroot-p-O Itpux < Alldb_bak.sql

--After recovery, I can't find the records updated this morning.

Mysql> Show tables;

--Before deletion by Binlog Log Incremental Recovery table

[Root@mysqldb binlog]# mysqlbinlog-vv--start-position=219--stop-position=913--database=itpux itpuxdb-binlog.000001 >sa.sql

--Restore this form to the original library

Back up this form from the other library first.

Mysqldump-uroot-p Itpux itpuxfg1 > Sa.sql

Then the production library, the direct recovery (the original library of the table has been deleted)

Mysql-uroot-p Itpux < Sa.sql

Mysql> select * from ITPUXFG1;

1) Revert to this table in the Source library

Mysqldump-uroot-p itpux itpuxfg1 >sa.sql