2022-10-27

MysQL系列干货

一款MySQL开源的Binlog解析工具-binlog2sql

一、简介

（一）简述

binlog2sql，可以说是DBA同胞们的福音，当然也是兼职DBA的开发者的福音。纯Python编写，核心代码不超200行。从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。DBA或开发人员，有时会误删或者误更新数据，binlog2sql可以实现快速回滚。

github官网：https://github.com/danfengcao/binlog2sql

（二）功能介绍

数据快速回滚(闪回)
主从切换后新master丢数据的修复
从binlog生成标准SQL，带来的衍生功能

二、环境准备

（一）客户端环境

这里环境是指使用binlog2sql工具的环境，我说一下我这边的环境

windows 10
python-3.11.0 下载（安装中会自动安装pip管理工具）
binlog2sql 官方下载（可能要用梯子）本博客备份下载
MySQL8.0.18 下载（我要回滚的binlog也是这个版本，应该保证大版本一直8.0.x）

（二）服务器环境

这里环境是指需要回滚的binlog的mysql服务器
测试回滚的mysql的版本是8.0.18

开启binlog

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row #必须是row
binlog_row_image = full #必须是full

# 或者我这样简单的
# 信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1
log-bin-trust-function-creators=1
#skip-log-bin

mysql用户需要设置的最小权限的集合，当然大了也没问题

select, super/replication client, replication slave

-- 建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 


-- 权限说明

-- select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
-- super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
-- replication slave：通过BINLOG_DUMP协议获取binlog内容的权限

三、安装部署

（一）安装python-3.11.0

出现上面的界面说明我们的python已经安装完成了！！！

（一）下载binlog2sql并解压

1666865393808

（二）修改配置

binlog2sql 默认的requirements中的依赖包版本比较低，不适合mysql8，需要用如下的几个包：
找到D:\Server\binlog2sql-master\requirements.txt，修改为：

PyMySQL==0.9.3
wheel==0.29.0
mysql-replication==0.21

此外，为了防止遇到乱码问题，还需要改下D:\Server\binlog2sql-master\binlog2sql\ binlog2sql_util.py 里面

def reversed_lines(fin):
    """Generate the lines of file in reverse order."""
    part = ''
    for block in reversed_blocks(fin):
        if PY3PLUS:
            block = block.decode("utf-8","ignore")  # 修改后的写法
            #block = block.decode("utf-8")   # 原先是这个写法
        for c in reversed(block):
            if c == '\n' and part:
                yield part[::-1]
                part = ''
            part += c
    if part:
        yield part[::-1]

（三）用pip命令安装所需要的环境

cd  D:\Server\binlog2sql-master
pip install -r requirements.txt

四、基本用法

解析出标准SQL

shell> cd D:\Server\binlog2sql-master\binlog2sql
python binlog2sql.py -h192.168.1.60 -P3306 -ub2sql -pb2sql -dtest_binlog2sql -tuser --start-file="binlog.000001" --start-datetime="2022-10-28 10:42:28" --stop-datetime="2022-10-28 10:50:00" > d:/raw.sql

输出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 570 end 736
UPDATE `test`.`test3` SET `addtime`='2016-12-10 12:00:00', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954
DELETE FROM `test`.`test3` WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147

解析出回滚SQL


shell> 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

输出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 981 end 1147
UPDATE `test`.`test3` SET `addtime`='2016-12-10 13:03:22', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954

选项

mysql连接配置

-h host; -P port; -u user; -p password

解析模式

–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。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析

五、应用案例

模拟某一时刻，我们对数据库做了删除操作：

（1）准备测试数据

--Create User
CREATE USER 'b2sql'@'%' IDENTIFIED BY 'b2sql';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'b2sql'@'%';

--Create Database
CREATE DATABASE test_binlog2sql;

--Create Table
USE test_binlog2sql;

CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT NULL,
    `addtime` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

--Insert Test Data
INSERT INTO user(name,addtime) values('小赵','2013-11-11 00:04:33');
INSERT INTO user(name,addtime) values('小钱','2014-11-11 00:04:48');
INSERT INTO user(name,addtime) values('小孙','2016-11-11 20:25:00');
INSERT INTO user(name,addtime) values('小李','2013-11-11 00:00:00');

--Select Data
SELECT * FROM user;

（2）删除数据

select * from user;
select now();
delete from user where addtime>'2014-01-01';
select * from user;

在2022-10-28 10:42:28左右，删除了id为2和3的小钱和小孙两行数据。

（3）回滚操作

1. 查看当前binlog

show master logs;

2. 通过大概时间范围解析出误操作执行的SQL

最新的binlog是binlog.000001。筛选出要回滚的SQL，由于误操作人只知道大致的误操作时间，我们首先根据时间做一次过滤。只需要解析test_binlog2sql库user表。（注：如果有多个sql误操作，则生成的binlog可能分布在多个文件，需解析多个文件）

cd D:\Server\binlog2sql-master\binlog2sql
python binlog2sql.py -h192.168.1.60 -P3306 -ub2sql -pb2sql -dtest_binlog2sql -tuser --start-file="binlog.000001" --start-datetime="2022-10-28 10:42:28" --stop-datetime="2022-10-28 10:50:00" > d:/raw.sql

在D盘就生成一个raw.sql，我们打开看一下，这就是我们执行 delete from user where addtime>‘2014-01-01’;解析成的sql，删除了小钱和小孙的记录

DELETE FROM `test_binlog2sql`.`user` WHERE `id`=2 AND `name`='小钱' AND `addtime`='2014-11-11 00:04:48' LIMIT 1; #start 663611 end 663914 time 2022-10-28 10:42:49
DELETE FROM `test_binlog2sql`.`user` WHERE `id`=3 AND `name`='小孙' AND `addtime`='2016-11-11 20:25:00' LIMIT 1; #start 663611 end 663914 time 2022-10-28 10:42:49

这时，已将删除的语句解析出来了。同时，我们也得到了binlog的位置信息：#start 663611 end 663914

3. 根据上一步得到的位置信息，使用-B参数生成回滚SQL

通过binlog位置信息，我们确定了误操作SQL来自同一个事务，准确位置在663611-663914之间（binlog2sql对于同一个事务会输出同样的start position）。再根据位置过滤，使用-B选项生成回滚SQL，检查回滚SQL是否正确。（注：真实场景下，生成的回滚SQL经常会需要进一步筛选。结合grep、编辑器等）

cd D:\Server\binlog2sql-master\binlog2sql
python binlog2sql.py -h192.168.1.60 -P3306 -ub2sql -pb2sql -dtest_binlog2sql -tuser --start-file="binlog.000001" --start-position=663611 --stop-position=663914 -B > d:/rollback.sql

打开D:/rollback.sql文件，内容如下

INSERT INTO `test_binlog2sql`.`user`(`id`, `name`, `addtime`) VALUES (3, '小孙', '2016-11-11 20:25:00'); #start 663611 end 663914 time 2022-10-28 10:42:49
INSERT INTO `test_binlog2sql`.`user`(`id`, `name`, `addtime`) VALUES (2, '小钱', '2014-11-11 00:04:48'); #start 663611 end 663914 time 2022-10-28 10:42:49

这时，我们已经得到了回滚语句。

4. 确认回滚SQL没有问题，执行回滚操作

检查一下回滚sql文件，这就是我们删掉的那两条记录。没有问题，执行回滚语句。登录MySQL，确认回滚成功

select * from user;

至此，被误删的数据被回滚回来了。

六、问题

（一）编码问题

提示：python提示UnicodeEncodeError: ‘gbk‘ codec can‘t encode character
方案：解决这个问题最好方式是在cmd输入set PYTHONUTF8=1

（二）引号问题：记得把命令语句里面的单引号改成双引号

提示如下信息，都是因为单引号的问题，把单引号全部改成双引号就没问题：

D:\Server\binlog2sql-master\binlog2sql>python binlog2sql.py -h192.168.1.60 -P3306 -ub2sql -p'b2sql' -dtest_binlog2sql -tuser --start-file='binlog.000001' --start-datetime='2022-10-28 10:42:28' --stop-datetime='2022-10-28 10:50:00' > d:/raw.sql
usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD ...]] [-P PORT]
                     [--start-file START_FILE] [--start-position START_POS]
                     [--stop-file END_FILE] [--stop-position END_POS]
                     [--start-datetime START_TIME] [--stop-datetime STOP_TIME]
                     [--stop-never] [--help] [-d [DATABASES ...]]
                     [-t [TABLES ...]] [--only-dml]
                     [--sql-type [SQL_TYPE ...]] [-K] [-B]
                     [--back-interval BACK_INTERVAL]
binlog2sql.py: error: unrecognized arguments: 10:42:28' 10:50:00'

D:\Server\binlog2sql-master\binlog2sql>python binlog2sql.py -h192.168.1.60 -P3306 -ub2sql -p'b2sql' -dtest_binlog2sql -tuser --start-file='binlog.000001' --start-datetime="2022-10-28 10:42:28" --stop-datetime="2022-10-28 10:50:00" > d:/raw.sql
Traceback (most recent call last):
  File "D:\Server\binlog2sql-master\binlog2sql\binlog2sql.py", line 145, in <module>
    binlog2sql = Binlog2sql(connection_settings=conn_setting, start_file=args.start_file, start_pos=args.start_pos,
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Server\binlog2sql-master\binlog2sql\binlog2sql.py", line 46, in __init__
    self.connection = pymysql.connect(**self.conn_setting)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py", line 325, in __init__
    self.connect()
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py", line 599, in connect
    self._request_authentication()
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py", line 861, in _request_authentication
    auth_packet = self._read_packet()
                  ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\connections.py", line 684, in _read_packet
    packet.check_error()
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python311\Lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'b2sql'@'192.168.1.60' (using password: YES)")

D:\Server\binlog2sql-master\binlog2sql>python binlog2sql.py -h192.168.1.60 -P3306 -ub2sql -pb2sql -dtest_binlog2sql -tuser --start-file='binlog.000001' --start-datetime="2022-10-28 10:42:28" --stop-datetime="2022-10-28 10:50:00" > d:/raw.sql
Traceback (most recent call last):
  File "D:\Server\binlog2sql-master\binlog2sql\binlog2sql.py", line 145, in <module>
    binlog2sql = Binlog2sql(connection_settings=conn_setting, start_file=args.start_file, start_pos=args.start_pos,
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Server\binlog2sql-master\binlog2sql\binlog2sql.py", line 53, in __init__
    raise ValueError('parameter error: start_file %s not in mysql server' % self.start_file)
ValueError: parameter error: start_file 'binlog.000001' not in mysql server

# mysql 数据恢复 binlog 误删恢复 binlog工具

一款MySQL开源的Binlog解析工具-binlog2sql

一、简介

（一）简述

（二）功能介绍

二、环境准备

（一）客户端环境

（二）服务器环境

三、安装部署

（一）安装python-3.11.0

（一）下载binlog2sql并解压

（二）修改配置

（三）用pip命令安装所需要的环境

四、基本用法

解析出标准SQL

解析出回滚SQL

选项

mysql连接配置

解析模式

解析范围控制

对象过滤

五、应用案例

模拟某一时刻，我们对数据库做了删除操作：

（1）准备测试数据

（2）删除数据

（3）回滚操作

1. 查看当前binlog

2. 通过大概时间范围解析出误操作执行的SQL

3. 根据上一步得到的位置信息，使用-B参数生成回滚SQL

4. 确认回滚SQL没有问题，执行回滚操作

六、问题

（一）编码问题

（二）引号问题：记得把命令语句里面的单引号改成双引号

评论

分类

最新文章

标签

最新文章

标签

Your browser is out-of-date!

一款MySQL开源的Binlog解析工具-binlog2sql

一、简介

（一）简述

（二） 功能介绍

二、环境准备

（一）客户端环境

（二）服务器环境

三、安装部署

（一） 安装python-3.11.0

（一） 下载binlog2sql并解压

（二） 修改配置

（三） 用pip命令安装所需要的环境

四、基本用法

解析出标准SQL

解析出回滚SQL

选项

mysql连接配置

解析模式

解析范围控制

对象过滤

五、应用案例

模拟某一时刻，我们对数据库做了删除操作：

（1）准备测试数据

（2）删除数据

（3）回滚操作

1. 查看当前binlog

2. 通过大概时间范围解析出误操作执行的SQL

3. 根据上一步得到的位置信息，使用-B参数生成回滚SQL

4. 确认回滚SQL没有问题，执行回滚操作

六、问题

（一）编码问题

（二）引号问题：记得把命令语句里面的单引号改成双引号

评论

分类

最新文章

标签

最新文章

标签

Your browser is out-of-date!

（二）功能介绍

（一）安装python-3.11.0

（一）下载binlog2sql并解压

（二）修改配置

（三）用pip命令安装所需要的环境