|
基于时间点的恢复,是可以后悔已经提交的数据。 使用条件必须先开启二进制日志,最好不要开启企业监视器,因为如果开启企业监视器,会不停的查询数据库,会造成恢复失败。
恢复的语法: (root@localhost) [test]> show binlog events in 'PC2014101864-bin.000002'\G *************************** 1. row *************************** Log_name: PC2014101864-bin.000002 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 120 Info: Server ver: 5.6.21-enterprise-commercial-advanced-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: PC2014101864-bin.000002 Pos: 120 Event_type: Query Server_id: 1 End_log_pos: 236 Info: use `test`; DROP TABLE `upl` /* generated by server */ *************************** 3. row *************************** Log_name: PC2014101864-bin.000002 Pos: 236 Event_type: Query Server_id: 1 End_log_pos: 352 Info: use `test`; create table upl(id int, user varchar(16)) *************************** 4. row *************************** Log_name: PC2014101864-bin.000002 Pos: 352 Event_type: Query Server_id: 1 End_log_pos: 468 Info: use `test`; DROP TABLE `upl` /* generated by server */ *************************** 5. row *************************** Log_name: PC2014101864-bin.000002 Pos: 468 Event_type: Query Server_id: 1 End_log_pos: 584 Info: use `test`; create table upl(id int, user varchar(16)) *************************** 6. row *************************** Log_name: PC2014101864-bin.000002 Pos: 584 Event_type: Query Server_id: 1 End_log_pos: 663 Info: BEGIN *************************** 7. row *************************** Log_name: PC2014101864-bin.000002 Pos: 663 Event_type: Query Server_id: 1 End_log_pos: 791 Info: use `test`; insert into upl values (1,'tom'),(2,'mary'),(3,'bean') *************************** 8. row *************************** Log_name: PC2014101864-bin.000002 Pos: 791 Event_type: Xid Server_id: 1 End_log_pos: 822 Info: COMMIT /* xid=124 */ 8 rows in set (0.00 sec)
(root@localhost) [test]>
查看当前那些点上,做了哪些操作。
然后找到相应的pos 进行恢复。 F:\mysql-advanced-5.6.21-winx64\data>mysqlbinlog --start-position=763 PC20141018 64-bin.000001 | mysql -u root -p
则会恢复跳过误操作。直接到最新的。
|
|