在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
另外,
恢复实例:
[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 是数据库名