怎么用mysqlbinlog做基于時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù)

這篇文章將為大家詳細(xì)講解有關(guān)怎么用mysqlbinlog做基于時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù)，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)


mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)


mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)


mysql>
mysql>
mysql> flush logs;
Query OK, 0 rows affected (0.16 sec)


mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       167 |
| mysql-bin.000002 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)

創(chuàng)建一個(gè)新表chenfeng并插入三條記錄:
mysql> create table chenfeng(t1 int not null primary key,t2 varchar(50),t3 datetime);
Query OK, 0 rows affected (0.13 sec)


mysql> insert into chenfeng values (1,"beijing",now());
Query OK, 1 row affected (0.02 sec)


mysql> insert into chenfeng values (2,"shanghai",now());
Query OK, 1 row affected (0.02 sec)


mysql> insert into chenfeng values (3,"zhengzhou",now());
Query OK, 1 row affected (0.03 sec)


mysql> select * from chenfeng;
+----+-----------+---------------------+
| t1 | t2        | t3                  |
+----+-----------+---------------------+
|  1 | beijing   | 2017-01-25 15:33:54 |
|  2 | shanghai  | 2017-01-25 15:34:08 |
|  3 | zhengzhou | 2017-01-25 15:34:23 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)


現(xiàn)在我們執(zhí)行delete誤操作，刪除所有的數(shù)據(jù)：
mysql> delete from chenfeng;
Query OK, 3 rows affected (0.04 sec)


先查看binlog,生成002.sql:
mysqlbinlog mysql-bin.000002 > 002.sql

查看002.sql,并只摘取delete部分內(nèi)容:
BEGIN
/*!*/;
# at 1094
#170125 15:37:10 server id 1  end_log_pos 1188 CRC32 0x53d8348f         Query   thread_id=197   exec_time=0     error_code=0
SET TIMESTAMP=1485329830/*!*/;
delete from chenfeng
/*!*/;
# at 1188
#170125 15:37:10 server id 1  end_log_pos 1219 CRC32 0xfe067937         Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


可以看到在時(shí)間2017-01-25 15:37:10我們做了delete誤操作?，F(xiàn)在需要用mysqlbinlog恢復(fù)到這個(gè)時(shí)間點(diǎn)前的數(shù)據(jù):
# mysqlbinlog mysql-bin.000002 --stop-date='2017-01-25 15:37:10' > resume.sql

執(zhí)行resume.sql內(nèi)容后發(fā)現(xiàn)數(shù)據(jù)已恢復(fù):
mysql> select * from chenfeng;
+----+-----------+---------------------+
| t1 | t2        | t3                  |
+----+-----------+---------------------+
|  1 | beijing   | 2017-01-25 15:33:54 |
|  2 | shanghai  | 2017-01-25 15:34:08 |
|  3 | zhengzhou | 2017-01-25 15:34:23 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)

關(guān)于“怎么用mysqlbinlog做基于時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù)”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。