Mysql combines backup + binlog to restore accidentally deleted operation data

Recover data by combining the backup binlog.

Scenario: After making all preparations last night, 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 entire database log on another machine and then import it into the production environment.

1) Create table

select now(); 
create table itpuxfg1 (
`id` int(10) unsigned not null auto_increment,
`name` varchar(16) not null,
`sex` enum('m','w') not null default 'm',
`age` 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 backup logical backup

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

3) Simulate business operations in the morning

show master status; -- 154
select now(); -- 2018-04-27 06:27:40
update itpux.itpuxfg1 set name='itpux04' where id=4;
commit;
select * from itpux.itpuxfg1;
select now(); -- 2018-04-27 06:28:03
update 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:00
drop table itpuxfg1;
select * from itpux.itpuxfg1;
show master status; -- 1078

5) Restore on another machine (in my case, it is local, production should not be local)

show master status;pass This file name is the binlog required for forward backup

--remember to copy the binlog log

show binlog events in 'itpuxdb-binlog.000003';

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

mysqlbinlog itpuxdb-binlog.000003 |grep "itpuxfg1" (Please distinguish between two different versions of the log)

Demo:

Delete the itpux library drop database itpux;

Restore:

Create the database first:

mysql> create database itpux default character set utf8; -- If the conditions are not clear, it is recommended to query the original database show create database mysql;

mysql -uroot -p - o itpux

-- After recovery, the records updated this morning cannot be found

mysql> show tables;

##-- Incremental recovery of table through binlog log before deletion

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

-- Restore this table to the original database

Back up this table from another database first

mysqldump -uroot - p itpux itpuxfg1 > sa.sql

Restore the production library directly (the tables in the original library have been deleted)

mysql -uroot -p itpux mysql> select * from itpuxfg1;

1) Restore this table to the source library

mysqldump -uroot -p itpux itpuxfg1 >sa.sql

Related recommendations:

How to restore mysql deleted database without backup

MySQL replication snapshot recovery accidental deletion operation experimental test

The above is the detailed content of Mysql combines backup + binlog to restore accidentally deleted operation data. For more information, please follow other related articles on the PHP Chinese website!