使用 mysqlbinlog 做数据恢复时 如何根据时间戳还原信息
|
这篇文章将为大家详细讲解有关怎么用mysqlbinlog做基于时间点的数据恢复，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 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) 创建一个新表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) 现在我们执行delete误操作，删除所有的数据： mysql> delete from chenfeng; Query OK, 3 rows affected (0.04 sec) 先查看binlog,生成002.sql: mysqlbinlog mysql-bin.000002 > 002.sql 查看002.sql,并只摘取delete部分内容: 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*/; 可以看到在时间2017-01-25 15:37:10我们做了delete误操作。现在需要用mysqlbinlog恢复到这个时间点前的数据: # mysqlbinlog mysql-bin.000002 --stop-date='2017-01-25 15:37:10' > resume.sql 执行resume.sql内容后发现数据已恢复: 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) 关于“怎么用mysqlbinlog做基于时间点的数据恢复”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
