[摘要]+------------------+-----+-------------+-----------+-------------+---------------------------------...
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
13 rows in set (0.00 sec)
或者：
mysql>
show binlog events in 'mysql-bin.000003'\G;
.........
.........
*************************** 12. row ***************************
Log_name: mysql-bin.000003
Pos:
875
Event_type: Query
Server_id: 1
End_log_pos:
954
Info:
drop database ops
*************************** 13. row ***************************
Log_name: mysql-bin.000003
Pos: 954
Event_type: Rotate
Server_id: 1
End_log_pos: 997
Info: mysql-bin.000004;pos=4
13 rows in set (0.00 sec)
通过分析，造成数据库破坏的pos点区间是介于 875--954 之间（这是按照日志区间的pos节点算的），只要恢复到875前就可。
7）
先把凌晨4点全备份的数据恢复：
[root@vm-002 ~]# cd /opt/backup/
[root@vm-002 backup]# lsmysql-bin.000003 ops_2016-09-25.sql.gz
[root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz
[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql Enter password:
--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------
--------------/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */--------------
..........................
--------------/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */--------------
这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了。
mysql> show databases; #发现ops库已经恢复回来了
mysql> use ops;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
关键词：mysql之binlog日志以及运用binlog日志恢复数据的示例代码分享