文章	评论	标签
95	128	101

Mysql使用binlog来恢复误操作的数据

以下操作基于开启了binlog并且binlog格式为raw时才可以，否则不适用于本教程

本教程使用了一个github上的开源工具来辅助操作，因为mysql自带的binlog工具操作起来过于繁琐，新手不易理解

初步操作

检查binlog是否开启

show variables like '%log_bin%'

如果log_bin一项为ON则为已经开启

检查binlog是否为raw格式记录

show variables like '%binlog_row_image%'

如果binlog_row_image一项为FULL则为raw格式记录

至此，只要binlog没被删除，数据就可以正常恢复

查看binlog列表

show master logs;

数字编号最大的一般都是现在正在使用的binlog，如果数据刚刚删掉，那么记下现在正在使用的binlog的Log_name，比我这边这个是mysql-binlog.000063，如果你要恢复更早以前的数据，请确保你已经定位数据所在的binlog，这里不再说明

准备环境

请在Linux环境下操作！！！！！否则会出现一系列诡异的问题

安装python3环境

yum install python3

如果原来装过了就不用装了，安装完毕后执行以下语句安装依赖，否则后面装依赖的时候可能会报错：

pip3 install -U pip setuptools

安装binlog2sql

clone项目（当然也可以直接github上下载）

https://github.com/michael-liumh/binlog2sql

git clone https://github.com/michael-liumh/binlog2sql.git
cd binlog2sql

安装依赖

pip3 install -r requirements.txt

如果使用的是mysql8.0，必须保证PyMySQL的版本等于0.9.3，如果版本不对，重新安装执行以下指令：

pip3 uninstall PyMySQL
pip install PyMySQL==0.9.3

操作

此工具的使用说明可以查看README.md，具体的命令都在下面：

参数	说明
--help	输出帮助信息
-h, --host	连接 MySQL 服务器的地址
-P, --port	连接 MySQL 服务器的端口
-u, --user	连接 MySQL 服务器的用户
-p, --password	连接 MySQL 服务器的密码（不指定这个参数也可以，默认会要求通过终端交互的形式输入密码，空密码直接回车即可）
-d, --databases	选择输出指定库的 SQL
-t, --tables	选择输出指定表的 SQL
-id, --ignore-databases	排除输出指定库的 SQL
-it, --ignore-tables	排除数据指定表的 SQL
-ic, --ignore-columns	过滤掉 SQL 中的指定的列
--ignore-virtual-columns	过滤掉 SQL 中的虚拟列
--start-position, --start-pos	指定 binlog 的起始位点
--stop-position, --stop-pos	指定 binlog 的结束位点
--start-datetime	解析 binlog 中指定开始时间后的 SQL
--stop-datetime	解析 binlog 中指定结束时间前的 SQL
--include-gtids	只输出指定 gtid 的 SQL
--exclude-gtids	不输出被排除 gtid 的 SQL
--only-dml	只输出 DML 类型的 SQL（排除DDL）
--sql-type	只输出指定类型的 DML（但不排除DDL，要排除DDL的话需要加上 --only-dml 参数）
--stop-never	持续不间断解析从运行脚本这个时间点开始，往后新增的 binlog 内容
-K, --no-primary-key	排除 SQL 中的主键字段
-KK, --only-primary-key	UPDATE 与 DELETE 类型的 SQL 的条件只保留主键字段
-B, --flashback	生成回滚 SQL 而不生成 binlog 记录中的 SQL
--replace	将 INSERT 类型的 SQL 中的 INSERT INTO 关键字改为 REPLACE INTO
--insert-ignore	将 INSERT 类型的 SQL 中的 INSERT INTO 关键字改为 INSERT IGNORE INTO
--result-file	将生成的结果保存到指定的文件中（请直接指定文件名，不要添加目录，添加了也会自动去除，请用 --result-dir 指定目录）
--record-file	当使用 --stop-never 参数时，自动记录解析过的 binlog 文件（仅对解析 binlog 文件生效）
--result-dir	指定生成结果文件的报存目录
--table-per-file	当使用 --stop-never 参数解析本地 binlog 时，输出的结果将按《库名.表名.日期.sql》的格式保存到对应的文件中
--date-prefix	当使用 --table-per-file 参数解析本地 binlog 时，输出的结果将按《日期.库名.表名.sql》的格式保存到对应的文件中
-ma, --minutes-ago	当解析本地 binlog 时，只解析最后修改时间在 n 分钟前的文件（可用这个参数排除还没记录完的 binlog，不想排除的话，直接参数值为 0 即可）
--need-comment	选择输出的 SQL 是否需要保留注释，注释内容包括这条 SQL 在 binlog 中的起始位点、结束位点、gtid值，值为 1 表示保留（默认），0 表示不保留
--rename-db	选择将输出的 SQL 的库名进行重命名，格式：“旧库名新库名” 或者 “新库名”，只提供新库名的话，会将未提供旧库名的其它所有库名全部重命名成指定库名，因此，无特殊需求的情况下，请不要只提供新库名
--rename-tb	选择将输出的 SQL 的表名进行重命名，格式：“旧表名新表名” 或者 “新表名”，只提供新表名的话，会将未提供旧表名的其它所有表名全部重命名成指定表名，因此，无特殊需求的情况下，请不要只提供新表名
--remove-not-update-col	排除 UPDATE 语句中未被更新的字段（默认输出完整的更新前后的值）
--keep, --keep-not-update-col	当使用--remove-not-update-col参数来排除 UPDATE 语句中未被更新的字段时，会保留一些没更新的，但你想保留的字段，多个字段用空格分隔。示例：--remove-not-update-col --keep id col1 col2
--update-to-replace	将 UPDATE 语句转化成 REPLACE INTO 语句
-f, --file-path	解析指定的本地 binlog 文件
-fd, --file-dir	解析指定目录下的所有本地 binlog 文件（可用下面的参数过滤）
-fr, --file-regex	使用正则表达式指定选择的目录下的 binlog 文件
--start-file	通过字符串比较的方式，指定选择的目录下的起始的 binlog 文件
--stop-file	通过字符串比较的方式，指定选择的目录下的结束的 binlog 文件
--check	检查指定目录下被过滤的 binlog 文件是否符合预期
--supervisor	用 supervisor 管理后台解析进程
--where	根据指定条件过滤出需要的 SQL，支持同时传入多个条件，但不能将多个条件用一个括号包起来，多个条件直接传入多个参数即可。正确示例：--where 'c1=v1' 'c2=v2'；错误示例：--where 'c1=v1 and c2=v2'；单个条件支持使用 or，如：--where 'deleted_at = 0 or deleted_at is null'
--sync	开启同步开关
-sh, --sync-host	指定要同步的目标实例地址
-sP, --sync-port	指定要同步的目标实例端口
-su, --sync-user	指定连接到同步目标实例的用户
-sp, --sync-password	指定连接到同步目标实例的密码
-sd, --sync-database	指定连接到同步目标实例的库名
-sC, --sync-charset	指定连接到同步目标实例的字符集

筛选数据

如果只需要回滚一张表里面指定时间的操作，只需要像下面这样

python3 binlog2sql.py -h127.0.0.1 -P3306 -u数据库用户名 -p数据库密码 -d数据库名 -t表名 --start-file="mysql-binlog.000063（开始查找的binlog）" --start-datetime="2023-02-21 20:25:00（时间范围开始）" --stop-datetime="2023-02-21 23:30:00（时间范围结束）"

此命令可以直接输出该段时间内的所有操作完整数据sql，打印的sql后面会有注释写着position，如果需要精确到行的话，得拿这个范围来当条件，继续筛选出数据：

python3 binlog2sql.py -h127.0.0.1 -P3306 -u数据库用户名 -p数据库密码 -d数据库名 -t表名 --start-file="mysql-binlog.000063（开始查找的binlog）"  --start-position=123 --stop-position=1234

生成回滚SQL

python3 binlog2sql.py -h127.0.0.1 -P3306 -u数据库用户名 -p数据库密码 -d数据库名 -t表名 --start-file="mysql-binlog.000063（开始查找的binlog）"  --start-position=123 --stop-position=1234 -B > rollback.sql

后面加上-B选项，然后输出文件即可

如果没有报错并且这部分数据在选择的范围之后没有发生变化的话，将这份sql导入到表中就可以了