MYSQL+Binglog分析利器binlog2sql使用详解

1、MYSQL Binglog分析利器:binlog2sql使用详解 大部分使用mysql的业务也都会开启binlog，用以记录数据库的更新操作。当然binlog的格式大家也都比较清楚，分别是statement、mixed、row模式。针对row格式的binlog，它以二进制形式记录相应的SQL，通过编辑器打开无法知道具体的SQL是什么。当然，我们也可以通过mysqlbinlog分析，但分析结果不利于我们做数据的回滚和redo。前段时间同事分享了一个小工具binlog2sql，觉得蛮有意思，凑着今天有时间装了学习下。一、binlog2sql简介binlog2sql是一开源工具，其可以从MySQL

2、binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要用途如下：（1）数据快速回滚(闪回)（2）主从切换后数据不一致的修复（3）从binlog生成标准SQL，带来的衍生功能二、binlog2sql安装1、binlog2sql下载2、binlog2sql依赖包安装python2.6+PyMySQL=0.7.8+wheel=0.24.0+mysql-replication=0.9+（1）PyMySQL-0.7.10安装/pypi/PyMySQL/rootnode1 binlogsql# ta

3、r -xzvf PyMySQL-0.7.10.tar.gz rootnode1 binlogsql# cd PyMySQL-0.7.10rootnode1 PyMySQL-0.7.10# python setup.py install（2）wheel-0.30.0a0安装/pypi/wheel/rootnode1 binlogsql# tar -xzvf wheel-0.30.0a0.tar.gz rootnode1 binlogsql# cd wheel-0.30.0a0rootnode1 wheel-0.30.0a0# pyt

4、hon setup.py install（3）python-mysql-replication安装rootnode1 binlogsql# unzip python-mysql-replication-master.zip rootnode1 binlogsql# cd python-mysql-replication-masterrootnode1 python-mysql-replication-master# python setup.py  install（4）可以通过pip安装相应的依赖包/pypi/piprootnod

5、e1 tools# tar -xzvf pip-9.0.1.tar.gz rootnode1 tools# cd pip-9.0.1rootnode1 pip-9.0.1# python setup.py installrootnode1 binlog2sql-master# pip install  -r requirements.txt 3、binlog2sql安装直接下载解压缩即可，运行相应的py脚本rootnode1 tools# unzip binlog2sql-master.zip rootnode1 tools# cd binlog2sql

6、-master设置别名，方便命令调用：alias binlog2sql='python /tools/binlogsql/binlog2sql-master/binlog2sql/binlog2sql.py'三、binlog2sql使用1、binlog2sql帮助手册rootnode1 binlog2sql# binlog2sql  -helpusage: binlog2sql.py -h HOST -u USER -p PASSWORD -P PORT         

7、0;           -start-file STARTFILE -start-position STARTPOS                     -stop-file ENDFILE -stop-position ENDPOS     

8、                -start-datetime STARTTIME -stop-datetime STOPTIME                     -stop-never -help -d DATABASES DATABASE

9、S .                     -t TABLES TABLES . -K -BParse MySQL binlog to SQL you wantoptional arguments:  -stop-never          Wait for more data from th

10、e server. default: stop                        replicate at the last binlog when you start binlog2sql  #持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置  -help    

11、;            help infomation  -K, -no-primary-key  Generate insert sql without primary key if exists #对INSERT语句去除主键。可选。  -B, -flashback       Flashback data to start_postition of start_file 

12、#生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句SLEEP SELECT(1)。可选。与stop-never或no-primary-key不能同时添加。connect setting:  -h HOST, -host HOST  Host the MySQL database server located  -u USER, -user USER  MySQL Username to log in as  -p PASSWORD, -password PASSWORD     &#

13、160;                  MySQL Password to use  -P PORT, -port PORT  MySQL port to userange filter:  -start-file STARTFILE             

14、60;          Start binlog file to be parsed  #起始解析文件。必须。  -start-position STARTPOS, -start-pos STARTPOS #start-file的起始解析位置。可选。默认为start-file的起始位置。               

15、         Start position of the -start-file   -stop-file ENDFILE, -end-file ENDFILE            #末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。       &

16、#160;                Stop binlog file to be parsed. default: '-start-file'  -stop-position ENDPOS, -end-pos ENDPOS    #stop-file的末尾解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。  

17、                      Stop position of -stop-file. default: latest position                    

18、    of '-stop-file'  -start-datetime STARTTIME                        Start reading the binlog at first event having a      

19、60;                 datetime equal or posterior to the argument; the                        argument must

20、 be a date and time in the local time                        zone, in any format accepted by the MySQL server for            

21、            DATETIME and TIMESTAMP types, for example: 2004-12-25                        11:25:56 (you should probably use quotes

22、for your                        shell to set it properly). #从哪个时间点的binlog开始解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。  -stop-datetime STOPTIME   

23、                     Stop reading the binlog at first event having a                      

24、  datetime equal or posterior to the argument; the                        argument must be a date and time in the local time         &#

25、160;              zone, in any format accepted by the MySQL server for                        DATETIME and TIMESTAMP typ

26、es, for example: 2004-12-25                        11:25:56 (you should probably use quotes for your              &

27、#160;         shell to set it properly).  #到哪个时间点的binlog停止解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。schema filter:  -d DATABASES DATABASES ., -databases DATABASES DATABASES .         &#

28、160;              dbs you want to process  #只输出目标db的sql。可选。默认为空。  -t TABLES TABLES ., -tables TABLES TABLES .                  

29、60;     tables you want to process  #只输出目标tables的sql。可选。默认为空。2、binlog2sql要求mysql配置参数需包括以下内容mysqldlog-bin-index = /home/mysql/bin-indexmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = fulllog-bin = /home/mysql/mysql-bin3、binlog2sql解析binlog案例（1）解析某个binlog全部内容点击(此

30、处)折叠或打开· #构造binlog内容· (root:localhost:Wed Mar 15 11:45:32 2017)(none)>flush logs;· Query OK, 0 rows affected (0.00 sec)·· (root:localhost:Wed Mar 15 11:45:36 2017)(none)>show master status G· * 1. row *·    &#

31、160;         File: mysql-bin.000028·          Position: 120·      Binlog_Do_DB: ·  Binlog_Ignore_DB: · Executed_Gtid_Set: · 1 row 

32、in set (0.00 sec)· (root:localhost:Wed Mar 15 11:45:40 2017)(none)>use dbtest;· Database changed· (root:localhost:Wed Mar 15 11:46:53 2017)dbtest>show tables;· +-+· | Tables_in_dbtest |· +-+· | T |· | T1 

33、|· | t |· | t1 |· | t2 |· +-+· 5 rows in set (0.00 sec)·· (root:localhost:Wed Mar 15 11:46:54 2017)dbtest>drop table t1;· Query OK, 0 rows affected (0.00 sec)·· (root:localhost:Wed M

34、ar 15 11:46:57 2017)dbtest>drop table T;· Query OK, 0 rows affected (0.01 sec)·· (root:localhost:Wed Mar 15 11:47:01 2017)dbtest>drop table T1;· Query OK, 0 rows affected (0.01 sec)·· (root:localhost:Wed Mar 15 11:47:02 2017

35、)dbtest>show tables;· +-+· | Tables_in_dbtest |· +-+· | t |· | t2 |· +-+· 2 rows in set (0.00 sec)·· (root:localhost:Wed Mar 15 11:47:07 2017)dbtest>drop table t2;· Query OK, 0 ro

36、ws affected (0.00 sec)·· (root:localhost:Wed Mar 15 11:47:12 2017)dbtest>create table t1 select * from t;· Query OK, 0 rows affected (0.02 sec)· Records: 0 Duplicates: 0 Warnings: 0·· (root:localhost:Wed Mar 15 11:47:20 20

37、17)dbtest>select * from t;· Empty set (0.00 sec)·· (root:localhost:Wed Mar 15 11:47:23 2017)dbtest>desc t;· +-+-+-+-+-+-+· | Field | Type | Null | Key | Default | Extra |· +-+-+-+

38、-+-+-+· | id | int(11) | YES | | NULL | |· +-+-+-+-+-+-+· 1 row in set (0.00 sec)·· (root:localhost:Wed Mar 15 11:47:27 2017)dbtest>insert into t values(1),(2),(3);· Query OK, 3 rows

39、 affected (0.01 sec)· Records: 3 Duplicates: 0 Warnings: 0·· (root:localhost:Wed Mar 15 11:47:38 2017)dbtest>insert into t1 select * from t;· Query OK, 3 rows affected (0.00 sec)· Records: 3 Duplicates: 0 Warnings: 0··

40、; (root:localhost:Wed Mar 15 11:47:45 2017)dbtest>update t1 set id='11' where id<10;· Query OK, 3 rows affected (0.00 sec)· Rows matched: 3 Changed: 3 Warnings: 0·· (root:localhost:Wed Mar 15 11:48:12 2017)dbtest>show maste

41、r status G· * 1. row *·              File: mysql-bin.000028·          Position: 38968·      Binlog_Do_DB: ·  B

42、inlog_Ignore_DB: · Executed_Gtid_Set: · 1 row in set (0.00 sec)正常语句是：python binlog2sql.py  -h0 -P3306 -udb_user -p'abc.123' -ddbtest  -start-file='mysql-bin.000028' 由于上文做了binlog2sql别名，这里可以通过别名直接调用命令：rootnode1 binlog2sql# 

43、 binlog2sql -h0 -P3306 -udb_user -p'abc.123' -ddbtest  -start-file='mysql-bin.000028' USE dbtest;DROP TABLE t1 /* generated by server */;USE dbtest;DROP TABLE T /* generated by server */;USE dbtest;DROP TABLE T1 /* generated by server */;USE dbtest;DROP TABLE t2 /*

44、generated by server */;USE dbtest;CREATE TABLE t1 (  id int(11) DEFAULT NULL);INSERT INTO dbtest.t(id) VALUES (1); #start 35104 end 35274 time 2017-03-15 11:47:38INSERT INTO dbtest.t(id) VALUES (2); #start 35104 end 35274 time 2017-03-15 11:47:38INSERT INTO dbtest.t(id) VALUES (3); #start 35104

45、 end 35274 time 2017-03-15 11:47:38INSERT INTO dbtest.t1(id) VALUES (1); #start 36062 end 36233 time 2017-03-15 11:47:45INSERT INTO dbtest.t1(id) VALUES (2); #start 36062 end 36233 time 2017-03-15 11:47:45INSERT INTO dbtest.t1(id) VALUES (3); #start 36062 end 36233 time 2017-03-15 11:47:45UPDATE dbt

46、est.t1 SET id=11 WHERE id=1 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12UPDATE dbtest.t1 SET id=11 WHERE id=2 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12UPDATE dbtest.t1 SET id=11 WHERE id=3 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12（2）解析某几个binlog相应的表t,t1操作记录点击

47、(此处)折叠或打开· (root:localhost:Wed Mar 15 15:01:01 2017)dbtest>flush logs;· Query OK, 0 rows affected (0.00 sec)·· (root:localhost:Wed Mar 15 15:01:09 2017)dbtest>show master status G· * 1. row *·       

48、       File: mysql-bin.000030·          Position: 120·      Binlog_Do_DB: ·  Binlog_Ignore_DB: · Executed_Gtid_Set: · 1 row in set 

49、;(0.00 sec)· (root:localhost:Wed Mar 15 15:01:16 2017)dbtest>delete from t1;· Query OK, 3 rows affected (0.00 sec)·· (root:localhost:Wed Mar 15 15:01:51 2017)dbtest>create table t2(id int);· Query OK, 0 rows affected (0.00 s

50、ec)·· (root:localhost:Wed Mar 15 15:02:03 2017)dbtest>insert into t2 select * from t;· Query OK, 3 rows affected (0.01 sec)· Records: 3 Duplicates: 0 Warnings: 0#特别注意的是，如果值解析特定表的sql，DDL全部解析，DML过滤相应的表SQLrootnode1 mysql# binlog2sql  -

51、h0  -P3306 -udb_user -p'abc.123' -ddbtest -t t t1 -start-file='mysql-bin.000028' -stop-file='mysql-bin.000030' USE dbtest;DROP TABLE t1 /* generated by server */;USE dbtest;DROP TABLE T /* generated by server */;USE dbtest;DROP TABLE T1 /* generated by serve

52、r */;USE dbtest;DROP TABLE t2 /* generated by server */;USE dbtest;CREATE TABLE t1 (  id int(11) DEFAULT NULL);INSERT INTO dbtest.t1(id) VALUES (1); #start 36062 end 36233 time 2017-03-15 11:47:45INSERT INTO dbtest.t1(id) VALUES (2); #start 36062 end 36233 time 2017-03-15 11:47:45INSERT INTO db

53、test.t1(id) VALUES (3); #start 36062 end 36233 time 2017-03-15 11:47:45UPDATE dbtest.t1 SET id=11 WHERE id=1 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12UPDATE dbtest.t1 SET id=11 WHERE id=2 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12UPDATE dbtest.t1 SET id=11 WHERE id=3 LIM

54、IT 1; #start 36625 end 36812 time 2017-03-15 11:48:12DELETE FROM dbtest.t1 WHERE id=11 LIMIT 1; #start 10116 end 10287 time 2017-03-15 15:01:51DELETE FROM dbtest.t1 WHERE id=11 LIMIT 1; #start 10116 end 10287 time 2017-03-15 15:01:51DELETE FROM dbtest.t1 WHERE id=11 LIMIT 1; #start 10116 end 10287 t

55、ime 2017-03-15 15:01:51USE dbtest;create table t2(id int);（3）数据库回滚#通过-B或者-flashback参数解析回滚的SQL，但从解析内容看，也是只解析DMLrootnode1 mysql# binlog2sql -flashback   -h0  -P3306 -udb_user -p'abc.123' -ddbtest -tt -tt1  -start-file='mysql-bin.000028' -stop-file='mysql

56、-bin.000030'            INSERT INTO dbtest.t1(id) VALUES (11); #start 10116 end 10287 time 2017-03-15 15:01:51INSERT INTO dbtest.t1(id) VALUES (11); #start 10116 end 10287 time 2017-03-15 15:01:51INSERT INTO dbtest.t1(id) VALUES (11); #

57、start 10116 end 10287 time 2017-03-15 15:01:51UPDATE dbtest.t1 SET id=3 WHERE id=11 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12UPDATE dbtest.t1 SET id=2 WHERE id=11 LIMIT 1; #start 36625 end 36812 time 2017-03-15 11:48:12UPDATE dbtest.t1 SET id=1 WHERE id=11 LIMIT 1; #start 36625 end 36

58、812 time 2017-03-15 11:48:12DELETE FROM dbtest.t1 WHERE id=3 LIMIT 1; #start 36062 end 36233 time 2017-03-15 11:47:45DELETE FROM dbtest.t1 WHERE id=2 LIMIT 1; #start 36062 end 36233 time 2017-03-15 11:47:45DELETE FROM dbtest.t1 WHERE id=1 LIMIT 1; #start 36062 end 36233 time 2017-03-15 11:47:45四、知识扩

59、展1、binlog种类以及其优缺点参考：（1）Statement：每一条会修改数据的sql都会记录在binlog中。    优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)&#

60、160;   缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).使用以下函数的语句也无法被复制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE() (除非启动时启用

61、了 -sysdate-is-now 选项)，同时在INSERT .SELECT 会产生比 RBR 更多的行级锁（2）Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。    优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题    缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改

62、来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。（3）Mixed: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的My

63、SQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。2、binlog相关配置参数log_bin：设置此参数表示启用binlog功能，并指定路径名称log_bin_index：设置此参数是指定二进制索引文件的路径与名称binlog_do_db：此参数表示只记录指定数据库的二进制日志binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志max_binlog_cache_size：此参数表示binlog使用的

64、内存最大的尺寸binlog_cache_size：此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。binlog_cache_use：使用二进制日志缓存的事务数量binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量max_binlog_size：Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能

65、做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束sync_binlog：这个参数直接影响mysql的性能和完整性。sync_binlog=0表示当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。Mysql中默认的设置是sync_binlog=0，即不作任何

66、强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失binlog_format：binlog日志格式，statement/row/mixedbinlog_row_image:其是在5.6之后有的参数，FULL记录每一行的变更，minimal只记录影响后的行，前提是row模式。3、binlog删除方法reset master;   #删除master的binlogreset slave;    #删除slave的中继日志,reset slave allpurge maste

67、r logs before '2016-03-15 10:00:00'  #删除指定日期以前的日志索引中binlog日志文件purge master logs to 'binlog.000001'  #删除指定日志文件的日志索引中binlog日志文件4、mysqlbinlog解析row模式的binlog日志mysqlbinlog -base64-output=decode-rows -v -start-datetime='2016-07-13 10:00:00' -stop-datetime='2016-07-13 1

68、0:40:00'  mysql-bin.000021 5、主从binlog和relaylog文件内容主库：    File: binlog.007247                   #主库当前写入的binlog文件    Position: 132941350                #主库当前写入的binlog文件position从库：