MySQL enables point-in-time recovery

Pre-Description: I specify the database 1 o'clock in the morning every day to do full preparation, this day someone accidentally, deleted a database inside a table, need to restore, how to get?

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

1 Confirm 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 not open in the config file my.cnf, add

[Mysqld]

Log-bin=mysql-bin

2 Create a new table inside the test library UPL

Use Test;create table UPL; Inserts data insert into UPL values (1, ' Tom '), (2, ' Mary '), (3, ' bean '); View data mysql> select * FROM upl;+--- ---+------+| ID |    User |+------+------+| 1 |    Tom | | 2 |    Mary | | 3 | Bean

3 Back up the database in point-in-time test

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

4 point in time B log in to the database again to create the table Test.t2

Mysql> CREATE TABLE t2 (a 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 require recovery to point B (UPL and T2 exist simultaneously)

Restore the full library Mysql-uroot-p </data/test.sql first

Login MySQL found the UPL table already exists

Mining log-bin log 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 points can only take approximate time, not to determine the exact time, you can write the above database backup time, the end time to write delete a table time (presumably) this mysql-bin.000001 log File Select the latest one (closest to delete time)

And then pour this recovery.sql into the database.

Mysql-uroot-p < Recovery.sql

Log in to the MySQL database again, found that T2 has already existed, to complete the recovery!

This article is from the "Drifting Away" blog, make sure to keep this source http://825536458.blog.51cto.com/4417836/1864945

MySQL enables point-in-time recovery