MySQL implements point-in-time recovery

Previous description: I designated the database at 1: 00 a. M. every day. On this day, someone accidentally deleted a table in a database and needed to recover. How to do it?

Reference: http://blog.csdn.net/zhaoyangjian724/article/details/48715321

1 confirm whether log_bin is open

Mysql > show global variables like 'log_bin'; +-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec)

If my.cnf is not enabled in the configuration file, add

[mysqld]

Log-bin=mysql-bin

2 create a new table upl in the test library

Use test;create table upl; inserts data insert into upl values (1), (2), (3); View data mysql > select * from upl;+-+-+ | id | user | +-+ | 1 | tom | 2 | mary | | 3 | bean

3 backup database test at a point in time

Mysqldump-uroot-p-- databases test > / data/test.sql

4 time point b log in to the database again and create the table test.t2

Mysql > create table T2 (an int); Query OK, 0 rows affected (0.00 sec) mysql > insert into T2 values (10); Query OK, 1 row affected (0.02 sec) mysql > commit; Query OK, 0 rows affected (0.00 sec) mysql > select * from T2; +-+ | a | +-+ | 10 | +-+ 1 row in set (0.00 sec)

5 delete upl,t2 at point in time c

Drop table upl

Drop table t2

6 requires recovery to point in time b (upl and T2 exist at the same time)

First restore the full library mysql-uroot-p

< /data/test.sql 登录mysql发现upl表已经存在了 挖掘log-bin日志 mysqlbinlog --start-datetime='2016-10-21 14:46:10' --stop-datetime='2016-10-21 14:50:35' mysql-bin.000001 >

Recovery.sql

Note: these two time points can only take the approximate time, can not determine the exact time, you can write the above database backup time, the end time to write the time to delete a table (about) the mysql-bin.000001 log file to choose the latest one (the closest to the deletion time)

And then pour the recovery.sql into the database.

Mysql-uroot-p < recovery.sql

Log in to the mysql database again and find that T2 already exists, and the recovery is complete!