The method of restoring mistakenly deleted operational data by Mysql combined with backup and binlog

Editor to share with you Mysql combined with backup + binlog restore mistakenly deleted operation data method, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Combine backup and binlog to restore data.

Scene: it was done last night, and the user accidentally deleted a table this morning.

Solution: it is not recommended to restore directly in the original production environment, it is recommended to restore the full database + logs on another machine, and then import to the production environment.

1) create a table

Select now (); create table itpuxfg1 (`id` int (10) unsigned not null auto_increment, `name` varchar (16) not null, `sex` enum (`mage tinyint (3) unsigned not null,primary key (`id`)) engine=innodb default charset=utf8 Insert into itpux.itpuxfg1 (`name`, `sex`, `age`) values ('itpux1','w',21), (' itpux2','m',22), ('itpux3','w',23), (' itpux4','m',24), ('itpux5','w',25); commit;select * from itpux.itpuxfg1

2) make a logical backup

Mysqldump-uroot-p-F-R-- all-databases > alldb_bak.sql

3) simulate the business operation 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;

4) erroneous deletion at noon

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

5) restore on another machine (my case is local, production is not local)

Show master status; uses this file name to back up the required binlog forward.

-- 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 that there are two different versions of the log)

Demo:

Delete the itpux library from drop database itpux

Restore:

Create the database first:

Mysql > create database itpux default character set utf8;-if the condition is not clear, it is recommended to query show create database mysql in the original database

Mysql-uroot-p-o itpux

< alldb_bak.sql -- 恢复后，查不到今天上午更新的记录 mysql>

Show tables

-- before the table is deleted through the increment of the binlog log

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

-- restore this table to the original library

Back up this table from another library first

Mysqldump-uroot-p itpux itpuxfg1 > sa.sql

Then restore the production database directly (the tables in the original library have been deleted)

Mysql-uroot-p itpux

< sa.sql mysql>

Select * from itpuxfg1

1) restore to this table to the source database

Mysqldump-uroot-p itpux itpuxfg1 > sa.sql

The above is all the contents of the method of Mysql combined with backup + binlog to restore mistakenly deleted operation data, thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!