再来介绍另一个binlog文件解析的第三方工具my2sql

suoyue_zhan

已于 2023-10-06 09:55:44 修改

阅读量878

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 binlog dba

于 2023-10-05 11:58:57 首次发布

本文链接：https://blog.csdn.net/suoyue_py/article/details/133578509

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

看腻了文字就来听听视频演示吧：https://www.bilibili.com/video/BV1rp4y1w74B/

github项目：https://github.com/liuhr/my2sql
gitee链接：https://gitee.com/mirrors/my2sql

my2sql

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

用途

数据快速回滚(闪回)
主从切换后新master丢数据的修复
从binlog生成标准SQL，带来的衍生功能
生成DML统计信息，可以找到哪些表更新的比较频繁
IO高TPS高，查出哪些表在频繁更新
找出某个时间点数据库是否有大事务或者长事务
主从延迟，分析主库执行的SQL语句
除了支持常规数据类型，对大部分工具不支持的数据类型做了支持，比如json、blob、text、emoji等数据类型sql生成

github给出的产品性能对比

测试场景	my2sql	binlog2sql
1.1G binlog生成回滚SQL	1分40秒	65分钟
1.1G binlog生成原始SQL	1分30秒	50分钟
1.1G binlog生成表DML统计信息、以及事务统计信息	40秒	不支持

有闲得测试一下

使用限制

使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响
只能回滚DML，不能回滚DDL，即drop table是恢复不了的
支持指定-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才能解析

下载安装

方式一：下载二进制版本
有编译好的linux版本(CentOS release 7.x)点击下载Linux版

方式二：编译安装
需要go语言环境来做编译

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

直接下载源码包，找到里面的二进制版本直接使用

[root@dba ~]# unzip my2sql-master.zip
[root@dba my2sql-master]# cd my2sql-master
[root@dba my2sql-master]# cp releases/centOS_release_7.x/my2sql /usr/bin/
[root@dba my2sql-master]# my2sql --help
my2back V1.0
  # 优先使用unique key作为where条件，默认false
  -U    prefer to use unique key instead of primary key to build where condition for delete/update sql
  -add-extraInfo
        Works with -work-type=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
  # 找出满足n条sql的事务，默认500条
  -big-trx-row-limit int
        transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 1 to 10, default 30000 (default 30000)
  -databases string
        only parse these databases, comma seperated, default all.
  # 生成不带库名的sql
  -do-not-add-prifixDb
        Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1). 
  # 为每个表生成一个sql文件
  -file-per-table
        One file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
  # 生成的sql是否带全列信息，默认false
  -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
  -host string
        mysql host, default 127.0.0.1 . (default "127.0.0.1")
  -ignore-databases string
        ignore parse these databases, comma seperated, default null
  # 生成的insert语句是否去掉主键，默认false
  -ignore-primaryKey-forInsert
        for insert statement when -workType=2sql, ignore primary key
  -ignore-tables string
        ignore parse these tables, comma seperated, default null
  # 当指定-mode=file 参数时，需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以连续解析多个binlog文件，只需要指定起始文件名，程序会自动持续解析下个文件
  -local-binlog-file string
        local binlog files to process, It works with -mode=file 
  -long-trx-seconds int
        transaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 0 to 1, default 3600 (default 3600)
  # repl：默认，伪装成从库解析binlog；file：直接读取binlog文件解析
  -mode string	
        valid options are:  repl,file. repl: as a slave to get binlogs from master. file: get binlogs from local filesystem. default repl (default "repl")
  -mysql-type string
        valid options are:  mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")
  # 将生成的结果存放到制定目录
  -output-dir string
        result output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space
  # 将生成的结果打印到屏幕，默认写到文件
  -output-toScreen
        Just output to screen,do not write to file
  -password string
        mysql user password.
  -port uint
        mysql port, default 3306. (default 3306)
  -print-interval int
        works with -w='stats', print stats info each PrintInterval. Valid values range from 1 to 600, default 30 (default 30)
  -server-id uint
        this program replicates from mysql as slave to read binlogs. Must set this server id unique from other slaves, default 1113306 (default 1113306)
  # 要解析的sql类型，可选参数insert、update、delete，默认全部解析
  -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)
  -start-datetime string
        Start reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2020-01-01 01:00:00"
  -start-file string
        binlog file to start reading
  -start-pos uint
        start reading the binlog at position (default 4)
  -stop-datetime string
        Stop reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2020-12-30 01:00:00"
  -stop-file string
        binlog file to stop reading
  -stop-pos uint
        Stop reading the binlog at position (default 4)
  -tables string
        only parse these tables, comma seperated, DONOT prefix with schema, default all.
  # 线程数
  -threads uint
        Works with -workType=2sql|rollback. threads to run (default 2)
  -tl string
        time location to parse timestamp/datetime column in binlog, such as Asia/Shanghai. default Local (default "Local")
  -user string
        mysql user. 
  -v    print version
  # 2sql：生成原始sql，rollback：生成回滚sql，stats：只统计DML、事务信息
  -work-type string
        valid options are:  2sql,rollback,stats. 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: 2sql (default "2sql")

开干

# 参数要求，也是默认参数
binlog_format=ROW 
binlog_row_image=FULL

# 伪装成从库解析binlog
my2sql -host 127.0.0.1 -user root -password root -port 3306 -mode repl -work-type 2sql -start-file mysql-bin.000011 -output-toScreen
# 直接读取binlog文件解析
my2sql -host 127.0.0.1 -user root -password root -port 3306 -mode file -local-binlog-file /mysqldata/data/mysql-bin.000011 -work-type 2sql -start-file /mysqldata/data/mysql-bin.000011 -output-toScreen

# 重新生成一个binlog日志
mysql> flush logs;

# 误删表数据
delete from t_student;
# 查看当前biglog日志所在的点
show master logs;

# 立即定位结束时间
mysql> select now();

# 根据操作时间解析binlog（定位时间缩小范围，避免解析多余的内容），生成回滚日志
my2sql -host 127.0.0.1 -user root -password root -port 3306 -databases mdb -tables t_student -work-type rollback -start-file mysql-bin.000011 -sql delete -start-datetime "2023-02-23 02:36:17" --stop-datetime "2023-02-23 02:38:17" -output-dir /mysqldata/backup/

# 将生成的DML语句在库里source恢复
source /mysqldata/backup/rollback.11.sql

MySQL的binlog系列和奇技操作：

先来聊聊MySQL的binlog文件解析
 接着说说mysqlbinlog解析工具如何做数据恢复
 再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的闪回功能
 接着聊聊如何从binlog文件恢复误delete的数据，模拟Oracle的闪回功能
 借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能
 再来介绍另一个binlog文件解析的第三方工具my2sql
顺带来聊聊MySQL误删ibdata数据文件的恢复
 MySQL大表直接复制文件的copy方式