mysql的binlog并做简单恢复

在mysql中有个命令很方便查询二进制日志
  [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed.
The LIMIT clause has the same syntax as for the SELECT statement

比如

mysql> show binlog events in 'mysql-binlog1.000001' from  1702;
+----------------------+------+------------+-----------+-------------+----------------------------+
| Log_name             | Pos  | Event_type | Server_id | End_log_pos | Info                       |
+----------------------+------+------------+-----------+-------------+----------------------------+
| mysql-binlog1.000001 | 1702 | Xid        |        10 |        1733 | COMMIT /* xid=30 */        |
| mysql-binlog1.000001 | 1733 | Rotate     |        10 |        1784 | mysql-binlog1.000002;pos=4 |
+----------------------+------+------------+-----------+-------------+----------------------------+
2 rows in set (0.01 sec)

mysql> show binlog events in 'mysql-binlog1.000001' from  1702 limit 1;
+----------------------+------+------------+-----------+-------------+---------------------+
| Log_name             | Pos  | Event_type | Server_id | End_log_pos | Info                |
+----------------------+------+------------+-----------+-------------+---------------------+
| mysql-binlog1.000001 | 1702 | Xid        |        10 |        1733 | COMMIT /* xid=30 */ |
+----------------------+------+------------+-----------+-------------+---------------------+
1 row in set (0.00 sec)

mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql-binlog1.000001 |      1784 |
| mysql-binlog1.000002 |       154 |
+----------------------+-----------+

另外， SHOW MASTER LOGS is equivalent to SHOW BINARY LOGS

清空日志：reset master

切换日志-重新创建切换新文件：flush logs;

PURGE BINARY LOGS TO 'mysql-bin.000001';
PURGE BINARY LOGS BEFORE '2016-12-21 00:00:00';

比如：

mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql-binlog1.000003 |       154 |
+----------------------+-----------+
1 row in set (0.01 sec)

mysql> show binlog events;
+----------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+----------------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql-binlog1.000003 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.9-log, Binlog ver: 4 |
| mysql-binlog1.000003 | 123 | Previous_gtids |        10 |         154 |                                      |
+----------------------+-----+----------------+-----------+-------------+--------------------------------------+
2 rows in set (0.00 sec)

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

mysql> show binlog events;
+----------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+----------------------+-----+----------------+-----------+-------------+--------------------------------------+
| mysql-binlog1.000003 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.9-log, Binlog ver: 4 |
| mysql-binlog1.000003 | 123 | Previous_gtids |        10 |         154 |                                      |
| mysql-binlog1.000003 | 154 | Rotate         |        10 |         205 | mysql-binlog1.000004;pos=4           |
+----------------------+-----+----------------+-----------+-------------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql-binlog1.000003 |       205 |
| mysql-binlog1.000004 |       154 |
+----------------------+-----------+
2 rows in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

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

恢复实例：

1，直接恢复，这个需要查询你要恢复的数据时间区间或者pos

mysqlbinlog --start-position=650 --stop-position=788 -vv -d test1 mysql-binlog1.000002 | mysql -uroot -p123456

2，可以把要恢复的数据导入到一个sql

    mysqlbinlog --start-position=650 --stop-position=788 -vv -d test1 mysql-binlog1.000002〉 1.sql

     mysql -uroot -p123456 -Dtest1 <1.sql  -- test1 是数据库名