使用说明来源原作者:my2sql的GitHub地址:https://github.com/liuhr/my2sql
go版MySQL binlog解析工具,通过解析MySQL binlog ,可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等,也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等,本工具基于my2fback、binlog_rollback工具二次开发而来。
binlog2sql当前是业界使用最广泛的MySQL回滚工具,下面对my2sql和binlog2sql做个性能对比。
my2sql | binlog2sql | |
---|---|---|
1.1G binlog生成回滚SQL | 1分40秒 | 65分钟 |
1.1G binlog生成原始SQL | 1分30秒 | 50分钟 |
1.1G binlog生成表DML统计信息、以及事务统计信息 | 40秒 | 不支持 |
-U
优先使用unique key作为where条件,默认false
-mode
repl: 伪装成从库解析binlog文件,file: 离线解析binlog文件, 默认repl
-local-binlog-file
当指定-mode=file 参数时,需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以连续解析多个binlog文件,只需要指定起始文件名,程序会自动持续解析下个文件
-add-extraInfo
是否把database/table/datetime/binlogposition...信息以注释的方式加入生成的每条sql前,默认false
# datetime=2020-07-16_10:44:09 database=orchestrator table=cluster_domain_name binlog=mysql-bin.011519 startpos=15552 stoppos=15773
UPDATE `orchestrator`.`cluster_domain_name` SET `last_registered`='2020-07-16 10:44:09' WHERE `cluster_name`='192.168.1.1:3306'
-big-trx-row-limit n
transaction with affected rows greater or equal to this value is considerated as big transaction
找出满足n条sql的事务,默认500条
-databases 、 -tables
库及表条件过滤, 以逗号分隔
-sql
要解析的sql类型,可选参数insert、update、delete,默认全部解析
-doNotAddPrifixDb
Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1)
默认生成insert into db1.tb1 (x1, x1) values (y1, y1)类sql,也可以生成不带库名的sql
-file-per-table
为每个表生成一个sql文件
-full-columns
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
生成的sql是否带全列信息,默认false
-ignorePrimaryKeyForInsert
生成的insert语句是否去掉主键,默认false
-output-dir
将生成的结果存放到制定目录
-output-toScreen
将生成的结果打印到屏幕,默认写到文件
-threads
线程数,默认8个
-work-type
2sql:生成原始sql,rollback:生成回滚sql,stats:只统计DML、事务信息
#伪装成从库解析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
#伪装成从库解析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
#伪装成从库解析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
#伪装成从库解析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
#伪装成从库解析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
#伪装成从库解析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
#伪装成从库解析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
编译好的my2sql
使用验证:
环境mysql8.0.18
创建库表:
create database db_backup;
CREATE TABLE `db_archive_log` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '表id',
`server_source` varchar(640) DEFAULT NULL COMMENT '源服务器',
`db_source` varchar(64) DEFAULT NULL COMMENT '源数据库schema',
`table_source` varchar(128) DEFAULT NULL COMMENT '源数据库表',
`server_dest` varchar(640) DEFAULT NULL COMMENT '目标服务器',
`db_dest` varchar(64) DEFAULT NULL COMMENT '目标数据库schema',
`table_dest` varchar(128) DEFAULT NULL COMMENT '目标数据库表',
`archive_qty` int(10) DEFAULT NULL COMMENT '归档数量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据库归档执行日志表';
插入数据:
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130298', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_36', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_36', '642');
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130299', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_37', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_37', '5831');
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130300', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_38', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_38', '1964');
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130301', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_39', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_39', '1146');
INSERT INTO `db_backup`.`db_archive_log` (`id`, `server_source`, `db_source`, `table_source`, `server_dest`, `db_dest`, `table_dest`, `archive_qty`) VALUES ('130302', '172.16.37.219', 'guiyu_oss', 't_oss_plan_call_phone_4', '172.16.169.40', 'guiyu_oss', 't_oss_plan_call_phone_4', '3371');
mysql> select * from db_archive_log;
+--------+---------------+-----------+--------------------------+---------------+-----------+--------------------------+-------------+
| id | server_source | db_source | table_source | server_dest | db_dest | table_dest | archive_qty |
+--------+---------------+-----------+--------------------------+---------------+-----------+--------------------------+-------------+
| 130298 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_36 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_36 | 642 |
| 130299 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_37 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_37 | 5831 |
| 130300 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_38 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_38 | 1964 |
| 130301 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_39 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_39 | 1146 |
| 130302 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_4 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_4 | 3371 |
+--------+---------------+-----------+--------------------------+---------------+-----------+--------------------------+-------------+
update数据:
mysql> update db_archive_log set db_source='guiyu_ossxxx' where id in (130298,130301);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql>
mysql> select * from db_archive_log;
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
| id | server_source | db_source | table_source | server_dest | db_dest | table_dest | archive_qty |
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
| 130298 | 172.16.37.219 | guiyu_ossxxx | t_oss_plan_call_phone_36 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_36 | 642 |
| 130299 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_37 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_37 | 5831 |
| 130300 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_38 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_38 | 1964 |
| 130301 | 172.16.37.219 | guiyu_ossxxx | t_oss_plan_call_phone_39 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_39 | 1146 |
| 130302 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_4 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_4 | 3371 |
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
5 rows in set (0.00 sec)
删除数据:
mysql> delete from db_archive_log where id=130302;
Query OK, 1 row affected (0.00 sec)
mysql> select * from db_archive_log;
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
| id | server_source | db_source | table_source | server_dest | db_dest | table_dest | archive_qty |
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
| 130298 | 172.16.37.219 | guiyu_ossxxx | t_oss_plan_call_phone_36 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_36 | 642 |
| 130299 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_37 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_37 | 5831 |
| 130300 | 172.16.37.219 | guiyu_oss | t_oss_plan_call_phone_38 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_38 | 1964 |
| 130301 | 172.16.37.219 | guiyu_ossxxx | t_oss_plan_call_phone_39 | 172.16.169.40 | guiyu_oss | t_oss_plan_call_phone_39 | 1146 |
+--------+---------------+--------------+--------------------------+---------------+-----------+--------------------------+-------------+
4 rows in set (0.00 sec)
解析binlog里的原始sql语句:
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type 2sql -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/tmpsql
[2021/05/20 14:09:32] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/05/20 14:09:32] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/05/20 14:09:32] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3308 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/05/20 14:09:32] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000002, 4)
[2021/05/20 14:09:32] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/05/20 14:09:32] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:09:32] [info] repl.go:16 start to get binlog from mysql
[2021/05/20 14:09:32] [info] binlogsyncer.go:777 rotate to (mysql-bin.000002, 4)
[2021/05/20 14:09:37] [info] repl.go:84 deadline exceeded.
[2021/05/20 14:09:37] [info] repl.go:18 finish getting binlog from mysql
[2021/05/20 14:09:37] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/05/20 14:09:37] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/05/20 14:09:37] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/05/20 14:09:37] [info] events.go:274 finish writing redo/forward sql into file
[2021/05/20 14:09:37] [info] events.go:277 exit thread to write redo/rollback sql into file
[root@localhost tmpsql]# ll
total 12
-rw-r--r--. 1 root root 107 May 20 14:09 biglong_trx.txt
-rw-r--r--. 1 root root 576 May 20 14:09 binlog_status.txt
-rw-r--r--. 1 root root 1599 May 20 14:09 forward.2.sql
[root@localhost tmpsql]# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@localhost tmpsql]#
[root@localhost tmpsql]# cat binlog_status.txt -- binlog 信息
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000002 2021-05-20_14:01:38 2021-05-20_14:01:39 29863 33040 5 0 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:04:04 2021-05-20_14:04:04 33341 33914 0 2 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:05:01 2021-05-20_14:05:01 34170 34401 0 0 1 db_backup db_archive_log
[root@localhost tmpsql]# cat forward.2.sql --解析的原始sql语句
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130298,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_36','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_36',642);
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130299,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_37','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_37',5831);
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130300,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_38','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_38',1964);
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130301,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_39','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_39',1146);
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130302,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_4','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_4',3371);
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_ossxxx' WHERE `id`=130298;
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_ossxxx' WHERE `id`=130301;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130302;
[root@localhost tmpsql]#
解析binlog生产回滚语句:
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 -mode repl \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type rollback -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/rollbacktmp
[2021/05/20 14:14:53] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/05/20 14:14:53] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/05/20 14:14:53] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3308 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/05/20 14:14:53] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/05/20 14:14:53] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000002, 4)
[2021/05/20 14:14:53] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:14:53] [info] repl.go:16 start to get binlog from mysql
[2021/05/20 14:14:53] [info] binlogsyncer.go:777 rotate to (mysql-bin.000002, 4)
[2021/05/20 14:14:58] [info] repl.go:84 deadline exceeded.
[2021/05/20 14:14:58] [info] repl.go:18 finish getting binlog from mysql
[2021/05/20 14:14:58] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/05/20 14:14:58] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/05/20 14:14:58] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/05/20 14:14:58] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/05/20 14:14:58] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/05/20 14:14:58] [info] rollback_process.go:41 start to revert tmp file /home/rollbacktmp/.rollback.2.sql into /home/rollbacktmp/rollback.2.sql
[2021/05/20 14:14:58] [info] rollback_process.go:156 finish reverting tmp file /home/rollbacktmp/.rollback.2.sql into /home/rollbacktmp/rollback.2.sql
[2021/05/20 14:14:58] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/05/20 14:14:58] [info] events.go:272 finish reverting content order of tmp files
[2021/05/20 14:14:58] [info] events.go:277 exit thread to write redo/rollback sql into file
[root@localhost home]#
[root@localhost home]#
[root@localhost home]# cd rollbacktmp/
[root@localhost rollbacktmp]# ll
total 12
-rw-r--r--. 1 root root 107 May 20 14:14 biglong_trx.txt
-rw-r--r--. 1 root root 576 May 20 14:14 binlog_status.txt
-rw-r--r--. 1 root root 738 May 20 14:14 rollback.2.sql
[root@localhost rollbacktmp]# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@localhost rollbacktmp]# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000002 2021-05-20_14:01:38 2021-05-20_14:01:39 29863 33040 5 0 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:04:04 2021-05-20_14:04:04 33341 33914 0 2 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:05:01 2021-05-20_14:05:01 34170 34401 0 0 1 db_backup db_archive_log
[root@localhost rollbacktmp]# cat rollback.2.sql
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130302,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_4','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_4',3371);
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_oss' WHERE `id`=130301;
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_oss' WHERE `id`=130298;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130302;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130301;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130300;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130299;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130298;
[root@localhost rollbacktmp]#
统计DML以及事务信息:
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 -mode repl \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type stats -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/tmpstats
[2021/05/20 14:19:37] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3308 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/05/20 14:19:37] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000002, 4)
[2021/05/20 14:19:37] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:19:37] [info] repl.go:16 start to get binlog from mysql
[2021/05/20 14:19:37] [info] binlogsyncer.go:777 rotate to (mysql-bin.000002, 4)
[2021/05/20 14:19:42] [info] repl.go:84 deadline exceeded.
[2021/05/20 14:19:42] [info] repl.go:18 finish getting binlog from mysql
[2021/05/20 14:19:42] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[root@localhost home]#
[root@localhost home]# cd tmpstats/
[root@localhost tmpstats]# ll
total 8
-rw-r--r--. 1 root root 107 May 20 14:19 biglong_trx.txt
-rw-r--r--. 1 root root 576 May 20 14:19 binlog_status.txt
[root@localhost tmpstats]# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@localhost tmpstats]# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000002 2021-05-20_14:01:38 2021-05-20_14:01:39 29863 33040 5 0 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:04:04 2021-05-20_14:04:04 33341 33914 0 2 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:05:01 2021-05-20_14:05:01 34170 34401 0 0 1 db_backup db_archive_log
[root@localhost tmpstats]#
上面的原始sql和回滚sql中已经包括了dml事务的统计信息
注意当表被删除了之后是无法被解析的(如果表被删除,可以建立一个空表然后再解析binlog生成语句)
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 -mode repl \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type rollback -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/rollbacktmp
[2021/05/20 14:22:53] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/05/20 14:22:53] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3308 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/05/20 14:22:53] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/05/20 14:22:53] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000002, 4)
[2021/05/20 14:22:53] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:22:53] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/05/20 14:22:53] [info] repl.go:16 start to get binlog from mysql
[2021/05/20 14:22:53] [info] binlogsyncer.go:777 rotate to (mysql-bin.000002, 4)
[2021/05/20 14:22:53] [error] mysqlFuncs.go:256 Error 1146: Table 'db_backup.db_archive_log' doesn't exist fail to query mysql: SHOW COLUMNS FROM `db_backup`.`db_archive_log`
[2021/05/20 14:22:53] [error] mysqlFuncs.go:134 Error 1146: Table 'db_backup.db_archive_log' doesn't exist fail to query mysql: SHOW INDEX FROM `db_backup`.`db_archive_log`
[2021/05/20 14:22:53] [fatal] repl.go:138 no table struct found for db_backup.db_archive_log, it maybe dropped, skip it. RowsEvent position:(mysql-bin.000002, 30096)
[root@localhost home]#
离线直接读取binlog文件解析:(-mode file -local-binlog-file)
离线库上必须要创建需要恢复或者解析的库和表,不然解析会报错找不到表的错误
[root@localhost fileroolback]# cd ..
[root@localhost home]# ./my2sql -user root -password Guijidba@2021 -port 3308 -mode file -local-binlog-file ./mysql-bin.000002 \
> -host 127.0.0.1 -databases db_backup -tables db_archive_log \
> -work-type rollback -start-file mysql-bin.000002 \
> -start-datetime "2021-05-20 13:35:00" --stop-datetime "2021-05-20 14:35:00" \
> -output-dir /home/fileroolback
[2021/05/20 14:31:34] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/05/20 14:31:34] [info] file.go:32 start to parse binlog from local files
[2021/05/20 14:31:34] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/05/20 14:31:34] [info] file.go:35 start to parse mysql-bin.000002 4
[2021/05/20 14:31:34] [info] file.go:44 start to parse mysql-bin.000002 4
[2021/05/20 14:31:34] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/05/20 14:31:34] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/05/20 14:31:34] [info] file.go:60 mysql-bin.000003 not exists nor a file
[2021/05/20 14:31:34] [info] file.go:71 finish parsing binlog from local files
[2021/05/20 14:31:34] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/05/20 14:31:34] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/05/20 14:31:34] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/05/20 14:31:34] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/05/20 14:31:34] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/05/20 14:31:34] [info] rollback_process.go:41 start to revert tmp file /home/fileroolback/.rollback.2.sql into /home/fileroolback/rollback.2.sql
[2021/05/20 14:31:34] [info] rollback_process.go:156 finish reverting tmp file /home/fileroolback/.rollback.2.sql into /home/fileroolback/rollback.2.sql
[2021/05/20 14:31:34] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/05/20 14:31:34] [info] events.go:272 finish reverting content order of tmp files
[2021/05/20 14:31:34] [info] events.go:277 exit thread to write redo/rollback sql into file
[root@localhost home]#
[root@localhost home]# cd fileroolback/
[root@localhost fileroolback]# ls
biglong_trx.txt binlog_status.txt rollback.2.sql
[root@localhost fileroolback]# cat biglong_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@localhost fileroolback]# cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000002 2021-05-20_14:01:38 2021-05-20_14:01:39 29863 33040 5 0 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:04:04 2021-05-20_14:04:04 33341 33914 0 2 0 db_backup db_archive_log
mysql-bin.000002 2021-05-20_14:05:01 2021-05-20_14:05:01 34170 34401 0 0 1 db_backup db_archive_log
[root@localhost fileroolback]#
[root@localhost fileroolback]# cat rollback.2.sql
INSERT INTO `db_backup`.`db_archive_log` (`id`,`server_source`,`db_source`,`table_source`,`server_dest`,`db_dest`,`table_dest`,`archive_qty`) VALUES (130302,'172.16.37.219','guiyu_oss','t_oss_plan_call_phone_4','172.16.169.40','guiyu_oss','t_oss_plan_call_phone_4',3371);
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_oss' WHERE `id`=130301;
UPDATE `db_backup`.`db_archive_log` SET `db_source`='guiyu_oss' WHERE `id`=130298;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130302;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130301;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130300;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130299;
DELETE FROM `db_backup`.`db_archive_log` WHERE `id`=130298;
[root@localhost fileroolback]#
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。