摘要：
mysqlbinlogMySQL官方原生提供的解析(binlog)二进制日志的工具用法mysqlbinlog--no-defaults--helpUsage:mysqlbinlog[options]log-files-?,--helpDisplaythishelpandexit.--base64-output=nameDeterminewhentheoutputstatementsshouldbe
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情况， 找出长事务与大事务