使用mysqlbinlog提取二进制日志_MySQL

mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.6.12-log |
+---------------+------------+

mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| APP01bin.000001 | 120 |
+-----------------+-----------+

a、只查看第一个binlog文件的内容(show binlog events) 
mysql> use replication;
Database changed
mysql> select * from tb;
+------+-------+
| id | val |
+------+-------+
| 1 | robin |
+------+-------+

mysql> insert into tb values(2,'jack');
Query OK, 1 row affected (0.02 sec)

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

mysql> insert into tb values(3,'fred');
Query OK, 1 row affected (0.00 sec)

mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| APP01bin.000001 | 409 |
| APP01bin.000002 | 363 |
+-----------------+-----------+

mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000001 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| APP01bin.000001 | 120 | Query | 11 | 213 | BEGIN |
| APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') |
| APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ |
| APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
-- 在上面的结果中第3行可以看到我们执行的SQL语句，第4行为自动提交
-- Author : Leshami
-- Blog : http://blog.csdn.net/leshami

b、查看指定binlog文件的内容(show binlog events in 'binname.xxxxx')
mysql> show binlog events in 'APP01bin.000002';
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000002 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| APP01bin.000002 | 120 | Query | 11 | 213 | BEGIN |
| APP01bin.000002 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(3,'fred') |
| APP01bin.000002 | 332 | Xid | 11 | 363 | COMMIT /* xid=394 */ |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+

c、查看当前正在写入的binlog文件(show master status\G) 
mysql> show master status\G
*************************** 1. row ***************************
 File: APP01bin.000002
 Position: 363
 Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

d、获取指定位置binlog的内容(show binlog events from) 
mysql> show binlog events from 213;
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') |
| APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ |
| APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+

a、提取指定的binlog日志
# mysqlbinlog /opt/data/APP01bin.000001
# mysqlbinlog /opt/data/APP01bin.000001|grep insert
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
insert into tb values(2,'jack')

b、提取指定position位置的binlog日志
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001

c、提取指定position位置的binlog日志并