MySql教程

MySQL-binlog解析工具

本文主要是介绍MySQL-binlog解析工具,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

mysqlbinlog

MySQL官方原生提供的解析(binlog)二进制日志的工具

用法

mysqlbinlog --no-defaults --help

Usage: mysqlbinlog [options] log-files
  -?, --help          Display this help and exit.
  --base64-output=name 
                      Determine when the output statements should be
                      base64-encoded BINLOG statements: 'never' disables it and
                      works only for binlogs without row-based events;
                      'decode-rows' decodes row events into commented
                      pseudo-SQL statements if the --verbose option is also
                      given; 'auto' prints base64 only when necessary (i.e.,
                      for row-based events and format description events).  If
                      no --base64-output[=name] option is given at all, the
                      default is 'auto'.
  --bind-address=name IP address to bind to.
  --character-sets-dir=name 
                      Directory for character set files.
  -d, --database=name List entries for just this database (local log only).
  --rewrite-db=name   Rewrite the row event to point so that it can be applied
                      to a new database
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       This is a non-debug version. Catch this and exit.
  --debug-info        This is a non-debug version. Catch this and exit.
  --default-auth=name Default authentication client-side plugin to use.
  -D, --disable-log-bin 
                      Disable binary log. This is useful, if you enabled
                      --to-last-log and are sending the output to the same
                      MySQL server. This way you could avoid an endless loop.
                      You would also like to use it when restoring after a
                      crash to avoid duplication of the statements you already
                      have. NOTE: you will need a SUPER privilege to use this
                      option.
  -F, --force-if-open Force if binlog was not closed properly.
                      (Defaults to on; use --skip-force-if-open to disable.)
  -f, --force-read    Force reading unknown binlog events.
  -H, --hexdump       Augment output with hexadecimal and ASCII event dump.
  -h, --host=name     Get the binlog from server.
  -i, --idempotent    Notify the server to use idempotent mode before applying
                      Row Events
  -l, --local-load=name 
                      Prepare local temporary files for LOAD DATA INFILE in the
                      specified directory.
  -o, --offset=#      Skip the first N entries.
  -p, --password[=name] 
                      Password to connect to remote server.
  --plugin-dir=name   Directory for client-side plugins.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -R, --read-from-remote-server 
                      Read binary logs from a MySQL server. This is an alias
                      for read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
  --read-from-remote-master=name 
                      Read binary logs from a MySQL server through the
                      COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by
                      setting the option to either BINLOG-DUMP-NON-GTIDS or
                      BINLOG-DUMP-GTIDS, respectively. If
                      --read-from-remote-master=BINLOG-DUMP-GTIDS is combined
                      with --exclude-gtids, transactions can be filtered out on
                      the master avoiding unnecessary network traffic.
  --raw               Requires -R. Output raw binlog data instead of SQL
                      statements, output is to log files.
  -r, --result-file=name 
                      Direct output to a given file. With --raw this is a
                      prefix for the file names.
  --secure-auth       Refuse client connecting to server if it uses old
                      (pre-4.1.1) protocol. Deprecated. Always TRUE
  --server-id=#       Extract only binlog entries created by the server having
                      the given id.
  --server-id-bits=#  Set number of significant bits in server-id
  --set-charset=name  Add 'SET NAMES character_set' to the output.
  -s, --short-form    Just show regular queries: no extra info and no row-based
                      events. This is for testing only, and should not be used
                      in production systems. If you want to suppress
                      base64-output, consider using --base64-output=never
                      instead.
  -S, --socket=name   The socket file to use for connection.
  --ssl-mode=name     SSL connection mode.
  --ssl               Deprecated. Use --ssl-mode instead.
                      (Defaults to on; use --skip-ssl to disable.)
  --ssl-verify-server-cert 
                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
  --ssl-ca=name       CA file in PEM format.
  --ssl-capath=name   CA directory.
  --ssl-cert=name     X509 cert in PEM format.
  --ssl-cipher=name   SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.
  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1,
                      TLSv1.2
  --server-public-key-path=name 
                      File path to the server public RSA key in PEM format.
  --get-server-public-key 
                      Get server public key
  --start-datetime=name 
                      Start reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  -j, --start-position=# 
                      Start reading the binlog at position N. Applies to the
                      first binlog passed on the command line.
  --stop-datetime=name 
                      Stop reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  --stop-never        Wait for more data from the server instead of stopping at
                      the end of the last log. Implicitly sets --to-last-log
                      but instead of stopping at the end of the last log it
                      continues to wait till the server disconnects.
  --stop-never-slave-server-id=# 
                      The slave server_id used for --read-from-remote-server
                      --stop-never. This option cannot be used together with
                      connection-server-id.
  --connection-server-id=# 
                      The slave server_id used for --read-from-remote-server.
                      This option cannot be used together with
                      stop-never-slave-server-id.
  --stop-position=#   Stop reading the binlog at position N. Applies to the
                      last binlog passed on the command line.
  -t, --to-last-log   Requires -R. Will not stop at the end of the requested
                      binlog but rather continue printing until the end of the
                      last binlog of the MySQL server. If you send the output
                      to the same MySQL server, that may lead to an endless
                      loop.
  -u, --user=name     Connect to the remote server as username.
  -v, --verbose       Reconstruct pseudo-SQL statements out of row events. -v
                      -v adds comments on column data types.
  -V, --version       Print version and exit.
  --open-files-limit=# 
                      Used to reserve file descriptors for use by this program.
  -c, --verify-binlog-checksum 
                      Verify checksum binlog events.
  --binlog-row-event-max-size=# 
                      The maximum size of a row-based binary log event in
                      bytes. Rows will be grouped into events smaller than this
                      size if possible. This value must be a multiple of 256.
  --skip-gtids        Do not preserve Global Transaction Identifiers; instead
                      make the server execute the transactions as if they were
                      new.
  --include-gtids=name 
                      Print events whose Global Transaction Identifiers were
                      provided.
  --exclude-gtids=name 
                      Print all events but those whose Global Transaction
                      Identifiers were provided.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)

  • --no-defaults: 默认charset问题

示例

# 普通权限的用户只读,不能写数据
set global read_only=1; 

# 查看操作记录信息
mysql> show binlog events in 'mysql-bin.000002';

# 恢复指定位置的操作
mysqlbinlog --start-position=120 --stop-position=520 --database=demo /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -u root -p  -v demo

# 基于时间点恢复
/usr/bin/mysqlbinlog --start-datetime="2021-06-27 20:57:55" --stop-datetime="2021-06-27 20:58:18" --database=demo /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p -v demo

MyFlash

美团点评的开源MySQL闪回工具。

安装

推荐下载源码之后,进行动态编译链接安装

动态编译链接

cc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback

静态编译链接

gcc -w -g `pkg-config --cflags  glib-2.0` source/binlogParseGlib.c   -o binary/flashback /usr/lib64/libglib-2.0.a -lrt
注意:
  • 确认目标设备上 glib库的版本和位置(ldd --version)

使用

语法

cd binary
./flashback --help

Usage:
  flashback [OPTION...]

Help Options:
  -?, --help                  Show help options

Application Options:
  --databaseNames             databaseName to apply. if multiple, seperate by comma(,)
  --tableNames                tableName to apply. if multiple, seperate by comma(,)
  --start-position            start position
  --stop-position             stop position
  --start-datetime            start time (format %Y-%m-%d %H:%M:%S)
  --stop-datetime             stop time (format %Y-%m-%d %H:%M:%S)
  --sqlTypes                  sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
  --maxSplitSize              max file size after split, the uint is M
  --binlogFileNames           binlog files to process. if multiple, seperate by comma(,)
  --outBinlogFileNameBase     output binlog file name base
  --logLevel                  log level, available option is debug,warning,error
  --include-gtids             gtids to process
  --exclude-gtids             gtids to skip

参数说明

  • databaseNames

    指定需要回滚的数据库名。多个数据库可以用“,”隔开。如果不指定该参数,相当于指定了所有数据库。

  • tableNames

    指定需要回滚的表名。多个表可以用“,”隔开。如果不指定该参数,相当于指定了所有表。

  • start-position

    指定回滚开始的位置。如不指定,从文件的开始处回滚。请指定正确的有效的位置,否则无法回滚

  • stop-position

    指定回滚结束的位置。如不指定,回滚到文件结尾。请指定正确的有效的位置,否则无法回滚

  • start-datetime

    指定回滚的开始时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定,则不限定时间

  • stop-datetime

    指定回滚的结束时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定,则不限定时间

  • sqlTypes

    指定需要回滚的sql类型。目前支持的过滤类型是INSERT, UPDATE ,DELETE。多个类型可以用“,”隔开。

  • maxSplitSize

    一旦指定该参数,对文件进行固定尺寸的分割(单位为M),过滤条件有效,但不进行回滚操作。该参数主要用来将大的binlog文件切割,防止单次应用的binlog尺寸过大,对线上造成压力

  • binlogFileNames

    指定需要回滚的binlog文件,目前只支持单个文件,后续会增加多个文件支持

  • outBinlogFileNameBase

    指定输出的binlog文件前缀,如不指定,则默认为binlog_output_base.flashback

  • logLevel

    仅供开发者使用,默认级别为error级别。在生产环境中不要修改这个级别,否则输出过多

  • include-gtids

    指定需要回滚的gtid,支持gtid的单个和范围两种形式。

  • exclude-gtids

    指定不需要回滚的gtid,用法同include-gtids

示例

回滚整个文件

# 闪回结果存放到binlog_output_base.flashback中
./flashback --binlogFileNames=haha.000041

# 应用闪回的日志
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p

回滚该文件中的所有insert语句

./flashback  --sqlTypes='INSERT' --binlogFileNames=haha.000041
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p

回滚大文件

#回滚
./flashback --binlogFileNames=haha.000042
#切割大文件
./flashback --maxSplitSize=1 --binlogFileNames=binlog_output_base.flashback
#应用
mysqlbinlog binlog_output_base.flashback.000001 | mysql -h<host> -u<user> -p
...
mysqlbinlog binlog_output_base.flashback.<N> | mysql -h<host> -u<user> -p

综合测试用例

测试表结构
CREATE TABLE `testFlashback2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nameShort` varchar(20) DEFAULT NULL,
  `nameLong` varchar(260) DEFAULT NULL,
  `amount` decimal(19,9) DEFAULT NULL,
  `amountFloat` float DEFAULT NULL,
  `amountDouble` double DEFAULT NULL,
  `createDatetime6` datetime(6) DEFAULT NULL,
  `createDatetime` datetime DEFAULT NULL,
  `createTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `nameText` text,
  `nameBlob` blob,
  `nameMedium` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
插入&回滚
插入数据
flush logs
insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
flush logs;

回滚数据
./binary/flashback --binlogFileNames=/var/lib/mysql/haha.000048

# 在当前运行目录下产生binlog_output_base.flashback文件
mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql --socket=/var/lib/mysql/mysql.sock test
删除&回滚
删除数据
delete from testFlashback2;
insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
flush logs;
delete from testFlashback2;

回滚数据
./binary/flashback --binlogFileNames=/var/lib/mysql/haha.000050
# 在当前运行目录下产生binlog_output_base.flashback文件
mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql --socket=/var/lib/mysql/mysql.sock test
更新&回滚
更新数据
delete from testFlashback2;
insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.111,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
flush logs;
mysql> checksum table testFlashback2;
+---------------------+-----------+
| Table               | Checksum  |
+---------------------+-----------+
| test.testFlashback2 | 717087411 |
+---------------------+-----------+
update testFlashback2 set amount=10.222;
mysql> checksum table testFlashback2;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| test.testFlashback2 | 3797190846 |
+---------------------+------------+
回滚数据
/binary/flashback --binlogFileNames=/var/lib/mysql/haha.000052
mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql --socket=/var/lib/mysql/mysql.sock test

binlog2sql

binlog2sql是一个开源的Python开发的MySQL Binlog解析工具。根据选项不同,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

要求

MySQL server必须设置以下参数

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
  • 参数 binlog_row_image 必须为FULL,暂不支持MINIMAL

用户所需最小权限

select, super/replication client, replication slave

-- 建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO USER_NAME;
说明
  • select:需要读取server端information_schema.COLUMNS表,获取表结构的元信息,拼接成可视化的sql语句
  • super/replication client:两个权限都可以,需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
  • replication slave:通过BINLOG_DUMP协议获取binlog内容的权限

mysql server必须开启,不支持离线解析

通过 BINLOG_DUMP 协议来获取 binlog 内容,需要读取server端 information_schema.COLUMNS 表,来获取表结构的元信息,才能拼接成 SQL 语句。因此,必须开启mysql server。

安装配置

依赖

  • Python 2.7, 3.4+
  • MySQL 5.6, 5.7

安装

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

使用

用法

解析出标准SQL
python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'
解析回滚SQl
python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

选项说明

解析模式
  • --stop-never :持续解析binlog。可选。默认False,同步至执行命令时最新的binlog位置。

  • -K, --no-primary-key :对INSERT语句去除主键。可选。默认False

  • -B, --flashback :生成回滚SQL,可解析大文件,不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。

  • --back-interval :在-B模式下,每打印一千行回滚SQL,加一句SLEEP多少秒,如不想加SLEEP,请设为0。可选。默认1.0。

解析范围
  • --start-file: 起始解析文件,只需文件名,无需全路径 。必须。
  • --start-position/--start-pos: 起始解析位置。可选。默认为start-file的起始位置。
  • --stop-file/--end-file :终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never,此选项失效。
  • --stop-position/--end-pos :终止解析位置。可选。默认为stop-file的最末位置;若解析模式为stop-never,此选项失效。
  • --start-datetime :起始解析时间,格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
  • --stop-datetime :终止解析时间,格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
过滤对象
  • -d, --databases :只解析目标db的sql,多个库用空格隔开,如-d db1 db2。可选。默认为空。

  • -t, --tables :只解析目标table的sql,多张表用空格隔开,如-t tbl1 tbl2。可选。默认为空。

  • --only-dml :只解析dml,忽略ddl。可选。默认False。

  • --sql-type :只解析指定类型,支持INSERT, UPDATE, DELETE。多个类型用空格隔开,如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型,则三者都不解析。

示例

创建测试数据
--构造测试表
create table tbl
 (
    id        int          primary key,
    name      varchar(30)  not null,
    birthday  date         not null
 );
-- 插入3条数据
insert into tbl values(1,'小明','1993-01-02');
insert into tbl values(2,'小华','1994-08-15');
insert into tbl values(3,'小丽','1995-07-12');

-- 模拟误删除数据
delete from tbl;
恢复数据
查看binlog日志
show master status;
解析出标准SQL
 python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'
使用flashback模式生成回滚sql
python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-position=3346 --stop-position=3556 -B > rollback.sql | cat
应用回滚数据
 mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < rollback.sql

my2sql

go版MySQL binlog解析工具,通过解析MySQL binlog ,可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等,也可以生成DML统计信息。它基于my2fback、binlog_rollback工具二次开发而来。

限制

  • 使用回滚/闪回功能时,binlog格式必须为row,且binlog_row_image=full, DML统计以及大事务分析不受影响
  • 只能回滚DML, 不能回滚DDL
  • 支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-start-datetime与结束时间-stop-datetime也会使用此指定的时区, 但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp
  • 此工具是伪装成从库拉取binlog,需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限
  • MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password,用户密码认证必须是mysql_native_password才能解析

安装配置

编译

git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .

已编译二进制文件

https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql

使用

语法


参数说明
  • -U: 优先使用unique key作为where条件,默认false

  • -mode:

    • repl: 伪装成从库解析binlog文件, 默认repl
    • file: 离线解析binlog文件
  • -local-binlog-file: 当指定-mode=file 参数时,需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以连续解析多个binlog文件,只需要指定起始文件名,程序会自动持续解析下个文件

  • -add-extraInfo: 是否把database/table/datetime/binlogposition...信息以注释的方式加入生成的每条sql前,默认false

  • -big-trx-row-limit n: 找出满足n条sql的事务,默认500条

  • -databases 、 -tables: 库及表条件过滤, 以逗号分隔

  • -sql: 要解析的sql类型,可选参数insert、update、delete,默认全部解析

  • -doNotAddPrifixDb: 默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql,也可以生成不带库名的sql

  • -file-per-table: 为每个表生成一个sql文件

  • -full-columns: 生成的sql是否带全列信息,默认false

  • -ignorePrimaryKeyForInsert: 生成的insert语句是否去掉主键,默认false

  • -output-dir: 将生成的结果存放到制定目录

  • -output-toScreen: 将生成的结果打印到屏幕,默认写到文件

  • -threads: 线程数,默认8个

  • -work-type:

    • 2sql:生成原始sql
    • rollback:生成回滚sql
    • stats:只统计DML、事务信息
解析出标准SQL
根据时间点解析出标准SQL
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl -work-type 2sql  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode file -local-binlog-file ./mysql-bin.011259  -work-type 2sql  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir
根据pos点解析出标准SQL
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl  -work-type 2sql  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -mode file -local-binlog-file ./mysql-bin.011259  -work-type 2sql  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir
解析出回滚SQL
根据时间点解析出回滚SQL
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl -work-type rollback  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -mode file -local-binlog-file ./mysql-bin.011259 -work-type rollback  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir ./tmpdir
根据pos点解析出回滚SQL
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl -work-type rollback  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306   -mode file -local-binlog-file ./mysql-bin.011259  -work-type rollback  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -output-dir ./tmpdir
统计DML以及大事务
统计时间范围各个表的DML操作数量,统计一个事务大于500条、时间大于300秒的事务
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -mode repl -work-type stats  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00"  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode file -local-binlog-file ./mysql-bin.011259   -work-type stats  -start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00"  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir
统计一段pos点范围各个表的DML操作数量,统计一个事务大于500条、时间大于300秒的事务
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -mode repl -work-type stats  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir
#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode file -local-binlog-file ./mysql-bin.011259  -work-type stats  -start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  -big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir
从某一个pos点解析出标准SQL,并且持续打印到屏幕
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306 -mode repl  -work-type 2sql  -start-file mysql-bin.011259  -start-pos 4   -output-toScreen 

示例

误删整张表数据,需要紧急回滚
测试数据
CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  --`add_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加的时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into tb1 values(1, 'biu');
insert into tb1 values(2, 'biao');
commit;

-- 查看测试表校验值
checksum table tb1;

flush logs;
-- 查看当前binlog
show master status;

-- 删除表数据
delete from tb1;
commit;
生成标准SQL
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1 -port 3306 -databases devdb -tables tb1 -mode repl -work-type 2sql -start-file mysql-bin.000046 -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir /tmp

#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1 -port 3306 -databases devdb -tables tb1 -mode file -local-binlog-file ./mysql-bin.000046  -work-type 2sql  -start-file mysql-bin.000046  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir /tmp
解析出回滚SQL
#伪装成从库解析binlog
./my2sql  -user root -password xxxx -host 127.0.0.1 -port 3306 -databases devdb -tables tb1 -mode repl -work-type rollback  -start-file mysql-bin.000046 -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir /tmp

#直接读取binlog文件解析
./my2sql  -user root -password xxxx -host 127.0.0.1 -port 3306 -databases devdb -tables tb1 -mode file -local-binlog-file ./mysql-bin.000046 -work-type rollback -start-file mysql-bin.000046  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" -output-dir /tmp
  • 查看回滚SQL

    cat rollback.46.sql
    
应用回滚SQL恢复数据
mysql -uroot -proot -P3306 -h127.0.0.1 devdb < /tmp/rollback.46.sql

Maxwell

Maxwell 是一个读取 MySQL binlogs 并将修改行字段的更新写入 Kafka, Kinesis, RabbitMQ, Google Cloud Pub/Sub 或 Redis (Pub/Sub or LPUSH) 以作为 JSON 的应用程序。

my2fback

my2fback 实现了基于row格式binlog的回滚闪回功能,让误删除或者误更新数据,可以不停机不使用备份而快速回滚误操作。也可以解释binlog(支持非row格式binlog)生成易读的SQL。

限制

  • 使用回滚/闪回功能时,binlog格式必须为row,且binlog_row_image=full, 其它功能支持非row格式binlog
  • 只能回滚DML, 不能回滚DDL
  • 支持V4格式的binlog, V3格式的没测试过,测试与使用结果显示,mysql5.1,mysql5.5, mysql5.6与mysql5.7的binlog均支持
  • 支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-sdt与结束时间-edt也会使用此指定的时区,
    • 但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp
  • decimal字段使用float64来表示, 但不损失精度
  • 所有字符类型字段内容按golang的utf8(相当于mysql的utf8mb4)来表示

安装配置

使用GO>=1.11.x版本来编译

开启GO111MODULE参数

编译linux 平台
CGO_ENABLED=0 GOOS=linux GOARCH=amd64 go build -o releases/my2fback -ldflags "-s -w" main.go
编译windows 平台
CGO_ENABLED=0 GOOS=windows GOARCH=amd64 go build -o releases/my2fback -ldflags "-s -w" main.go

没有开启GO111MODULE参数

编译linux 平台
CGO_ENABLED=0 GOOS=linux GOARCH=amd64 go build -o releases/my2fback -ldflags "-s -w" main.go

编译windows 平台
CGO_ENABLED=0 GOOS=windows GOARCH=amd64 go build -o releases/my2fback -ldflags "-s -w" main.go

使用

语法

my2fback -h
my2fback V2.0 By WangJiemin.
	E_mail: 278667010@qq.com

*****************************************************************************************************
*	system_command: /usr/local/bin/my2fback																		*
*	system_goos: linux																			*
*	system_arch: amd64																			*
*	hostname: test_dbs2.yz.babytree-ops.org																			*
*	hostaddress: 10.10.1.221																			*
*	blog: https://jiemin.wang																					*
*		read binlog from master, work as a fake slave: ./my2fback -m repl opts...					*
*		read binlog from local filesystem: ./my2fback -m file opts... mysql-bin.000010				*
*****************************************************************************************************

  -C	works with -w='stats', keep analyzing transations to last binlog for -m=file, and keep analyzing for -m=repl
  -H string
	master host, DONOT need to specify when -w=stats. if mode is file, it can be slave or other mysql contains same schema and table structure, not only master. default 127.0.0.1 (default "127.0.0.1")
  -I	for insert statement when -wtype=2sql, ignore primary key
  -M string
	valid options are:  mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")
  -P uint
	master port, default 3306. DONOT need to specify when -w=stats (default 3306)
  -S string
	mysql socket file
  -U	prefer to use unique key instead of primary key to build where condition for delete/update sql
  -a	Works with -w=2sql|rollback. for update sql, include unchanged columns. for update and delete, use all columns to build where condition.
	default false, this is, use changed columns to build set part, use primary/unique key to build where condition
  -b int
	transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 10 to 30000, default 500 (default 500)
  -d	Works with -w=2sql|rollback. Prefix table name with database name in sql, ex: insert into db1.tb1 (x1, x1) values (y1, y1). Default true (default true)
  -dbs string
	only parse database which match any of these regular expressions. The regular expression should be in lower case because database name is translated into lower case and then matched against it.
		Multi regular expressions is seperated by comma, default parse all databases. Useless when -w=stats
  -dj string
	dump table structure to this file. default tabSchame.json (default "tabSchame.json")
  -e	Works with -w=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
  -ebin string
	binlog file to stop reading
  -edt string
	Stop reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2004-12-25 11:25:56"
  -epos uint
	Stop reading the binlog at position
  -f	Works with -w=2sql|rollback. one file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
  -i int
	works with -w='stats', print stats info each PrintInterval. Valid values range from 1 to 600, default 30 (default 30)
  -ies string
	for sql which is error to parsed and matched by this regular expression, just print error info, skip it and continue parsing, otherwise stop parsing and exit.
		The regular expression should be in lower case, because sql is translated into lower case and then matched against it. (default "^create definer.+trigger")
  -k	Works with -w=2sql|rollback. wrap result statements with 'begin...commit|rollback'
  -l int
	transaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 1 to 3600, default 300 (default 300)
  -m string
	valid options are:  repl,file. repl: as a slave to get binlogs from master. file: get binlogs from local filesystem. default file (default "file")
  -mid uint
	works with -m=repl, this program replicates from master as slave to read binlogs. Must set this server id unique from other slaves, default 1113306 (default 1113306)
  -o string
	result output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space
  -oj
	Only use table structure from -rj, do not get or merge table struct from mysql
  -ors
	for mysql>=5.6.2 and binlog_rows_query_log_events=on, if set, output original sql. default false
  -p string
	mysql user password. DONOT need to specify when -w=stats
  -r int
	Works with -w=2sql|rollback. rows for each insert sql. Valid values range from 1 to 500, default 30 (default 30)
  -rj string
	Works with -w=2sql|rollback, read table structure from this file and merge from mysql
  -sbin string
	binlog file to start reading
  -sdt string
	Start reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2004-12-25 11:25:56"
  -spos uint
	start reading the binlog at position
  -sql string
	valid options are:  insert,update,delete. only parse these types of sql, comma seperated, valid types are: insert, update, delete; default is all(insert,update,delete)
  -stsql
	when -w=2sql, also parse plain sql and write into result file even if binlog_format is not row. default false
  -t uint
	Works with -w=2sql|rollback. threads to run, default 4 (default 2)
  -tbs string
	only parse table which match any of these regular expressions.The regular expression should be in lower case because database name is translated into lower case and then matched against it.
		 Multi regular expressions is seperated by comma, default parse all tables. Useless when -w=stats
  -tl string
	time location to parse timestamp/datetime column in binlog, such as Asia/Shanghai. default Local (default "Local")
  -u string
	mysql user. DONOT need to specify when -w=stats
  -v	print version
  -w string
	valid options are:  tbldef,stats,2sql,rollback. tbldef: only get table definition structure; 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: stats (default "stats")

常用参数
-m string
valid options are:  repl,file. repl: as a slave to get binlogs from master. file: get binlogs from local filesystem. default file (default "file")
		relp: 模仿 SLAVE 的IO_THREAD连接到MASTER获取BINLOG EVENT
		file: 解析本地的BINLOG(default: file)
-w string
valid options are:  tbldef,stats,2sql,rollback. tbldef: only get table definition structure; 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: stats (default "stats")
		2sql: 解析成SQL语句
		rollback: 解析为回滚语句
-M string
valid options are:  mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")
		选择是MySQL还是Mariadb, 不选择默认为MySQL
-e  Works with -w=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
 在sql之前的行上打印database/table/datetime/binlogposition...info,默认为false
-f  Works with -w=2sql|rollback. one file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
 如果为true,则为一个表的一个文件,否则为所有表的一个文件。默认为false。注意,一个binlog总是一个文件
-r int
Works with -w=2sql|rollback. rows for each insert sql. Valid values range from 1 to 500, default 30 (default 30)
		INSERT SQL 语句每一行包含的values的行数
-t uint
Works with -w=2sql|rollback. threads to run, default 4 (default 2)
		开启几个thread进行来执行解析2sql|rollback
-o string
result output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space
		输入的目录
-k  Works with -w=2sql|rollback. wrap result statements with 'begin...commit|rollback'
 使用-w = 2sql | rollback。使用'begin ... commit | rollback'包装结果语句
-l int
transaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 1 to 3600, default 300 (default 300)
 
-b int
transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 10 to 30000, default 500 (default 500)
		
-dWorks with -w=2sql|rollback. Prefix table name with database name in sql, ex: insert into db1.tb1 (x1, x1) values (y1, y1). Default true (default true)
 使用-w=2sql|rollback。在sql中具有数据库名称的前缀表名

示例

file本地方式解析binlog
./my2fback -m file -w 2sql -M mysql -t 6 -H ***.***.***.*** -u test -p test -dbs babytree -tbs userbaby -e -f -d -r 20 -k -b 100 -l 10  -o /data/bak/20190626/tosql /data/bak/20190626/mysql-bin.002938

binlog_rollback

回滚/闪回, 前滚, DML分析报告, DDL信息

binlog_inspector

回滚/闪回,前滚, 分析各表DML情况, 找出长事务与大事务

这篇关于MySQL-binlog解析工具的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!