使用mysqlbinlog提取二进制日志

MySQL binlog日志记录了MySQL数据库从启用日志以来所有对当前数据库的变更。binlog日志属于二进制文件，我们可以从binlog提取出来生成可阅读的SQL语句来重建当前数据库以及根据需要实现时点恢复或不完全恢复。本文主要描述了如果提取binlog日志，并给出相关示例。

有关binlog的介绍与描述请参考：MySQL 二进制日志(Binary Log)

 

1、提取mysqlbinlog的几种方式

 

2、演示show binlog events方式

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

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                              | +-----------------+-----+------------+-----------+-------------+----------------------------------------------------+

3、演示mysqlbinlog方式提取binlog

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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日志并输出到压缩文件 # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz   d、提取指定position位置的binlog日志导入数据库 # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p   e、提取指定开始时间的binlog并输出到日志文件 # mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql   f、提取指定位置的多个binlog日志文件 # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more   g、提取指定数据库binlog并转换字符集到UTF8 # mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql   h、远程提取日志，指定结束时间 # mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more   i、远程提取使用row格式的binlog日志并输出到本地文件 # mysqlbinlog -urobin -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql

4、获取mysqlbinlog的帮助信息(仅列出常用选项)

-d, --database=name

-f, --force-read

-h, --host=name

-l, --local-load=name

-p, --password[=name]

-P, --port=#

--protocol=name

-R, --read-from-remote-server|--read-from-remote-master=name

-r, --result-file=name

-s, --short-form

-S, --socket=name

--start-datetime=name

--stop-datetime=name

-j, --start-position=#

--server-id=#

--set-charset=name

-D, --disable-log-bin

-u, --user=name

-v, --verbose

-V, --version

 

原文地址：http://www.2cto.com/database/201412/361968.html