MySQL 数据恢复系列一：数据闪回工具 binlog2sql

已于 2024-02-28 16:39:15 修改
阅读量546 收藏
点赞数
分类专栏： MySQL 文章标签： mysql python
于 2021-08-18 11:52:28 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Tony_stark_L/article/details/119776623
版权

由于个人能力有限，文中可能存在错误，并且很多细节没有深入分析，欢迎批评指正。

文章目录

1.解决哪些问题？

1）数据误删
2）binlog 日志解析

2.前置条件

1）log_bin 为 ON ， binlog_format 为 ROW ( Mixed 也可以)，且 binlog_row_image 为 full 或 noblog ，默认为 full ；

mysql> show variables where variable_name in ('log_bin','binlog_format','binlog_row_image');
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_bin          | ON    |
| binlog_format    | ROW   |
| binlog_row_image | FULL  |
+------------------+-------+

2）必须开启 MySQL Server ，离线模式无法解析；
3）用来闪回数据的 user 需要的最小权限集合。
select, super/replication client, replication slave

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

授权命令如下：

mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO [user]@'%' identified by '[password]';

3.软件部署

3.1.下载软件包

binlog2sql.tar.gz （请自行 git ）

3.2.安装

shell> tar -zxvf binlog2sql.tar.gz
shell> cd binlog2sql/binlog2sql_dependencies/
shell> tar -zxvf setuptools-0.6c11.tar.gz
shell> cd setuptools-0.6c11
shell> python setup.py install
shell> cd ..
shell> tar -zxvf pip-9.0.1.tar.gz
shell> cd pip-9.0.1
shell> python setup.py install
shell> cd ..
shell> pip install *.whl mysql-replication-0.9.tar.gz

4.使用帮助

1）mysql 连接配置

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

2）解析模式

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

3）解析范围控制

--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' 。可选，默认不过滤。

4）对象过滤

-d, --databases 				只解析目标 db 的 sql ，多个库用空格隔开，如 -d db1 db2 。可选，默认为空。
-t, --tables 					只解析目标 table 的 sql ，多张表用空格隔开，如 -t tbl1 tbl2 。可选，默认为空。
--only-dml 					只解析 dml，忽略ddl 。可选，默认 TRUE。
--sql-type 					只解析指定类型，支持 INSERT, UPDATE, DELETE 。多个类型用空格隔开，可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

5.命令示例

1）查看某个时间段内对某表的 sql 执行记录

shell> python binlog2sql.py -h[host] -P[port] -u[user] -p'[password]' --start-file='[binlog_file_name]' -d[table_schema] -t[table_name] --start-datetime='[%Y-%m-%d %H:%M:%S]' --stop-datetime='[%Y-%m-%d %H:%M:%S]'

2）结合上述命令确定事务及其提交 position ，并生产目标事务的回滚 sql

shell> python binlog2sql.py -h[host] -P[port] -u[user] -p'[password]' --start-file='[binlog_file_name]' -d[table_schema] -t[table_name] --start-position='[position]' --stop-position='[position]'  -B

6.案例测试

# 创建测试表 t1
mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`employee` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_employee` (`employee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
	
# 插入测试数据
mysql> insert into t1 (employee) values ('111'),('222'),('333');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 查看 t1 表当前数据
mysql> select * from t1;
+----+----------+
| id | employee |
+----+----------+
|  1 | 111      |
|  2 | 222      |
|  3 | 333      |
+----+----------+
3 rows in set (0.01 sec)

# 刷新 binlog 日志，生成新的日志
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)

# 删除 t1 表所有数据
mysql> delete from t1;
Query OK, 3 rows affected (0.00 sec)

# 查看 t1 表数据
mysql> select * from t1;
Empty set (0.00 sec)

# 查看 binlog 文件
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000013
         Position: 520
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 7c54e776-807d-11eb-b035-fa163ee04d48:1-1331,
7d5edd3b-807d-11eb-ae54-fa163ee4c1f8:1
1 row in set (0.00 sec)

# 使用前文的命令示例，补全信息，查看对 t1 表的 sql 执行记录
[root@dbtest01 binlog2sql]# python binlog2sql.py -h127.1 -P3306 -uroot -p'******' --start-file='mysql-bin.000013' -ddbtest01 -tt1 --start-datetime='2021-08-18 14:00:00' --stop-datetime='2021-08-18 15:00:00'
DELETE FROM `dbtest01`.`t1` WHERE `employee`='111' AND `id`=1 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26
DELETE FROM `dbtest01`.`t1` WHERE `employee`='222' AND `id`=2 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26
DELETE FROM `dbtest01`.`t1` WHERE `employee`='333' AND `id`=3 LIMIT 1; #start 299 end 489 time 2021-08-18 14:21:26

# 根据执行记录中的 position 生成回滚 sql
[root@dbtest01 binlog2sql]# python binlog2sql.py -h127.1 -P3306 -uroot -p'******' --start-file='mysql-bin.000013' -ddbtest01 -tt1 --start-position='299' --stop-position='489'  -B
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('333', 3); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('222', 2); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('111', 1); #start 299 end 489 time 2021-08-18 14:21:26

# 根据误删时间范围生成回滚 sql
[root@dbtest01 binlog2sql]# python binlog2sql.py -h127.1 -P3306 -uroot -p'******' --start-file='mysql-bin.000013' -ddbtest01 -tt1 --start-datetime='2021-08-18 14:00:00' --stop-datetime='2021-08-18 15:00:00'  -B
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('333', 3); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('222', 2); #start 299 end 489 time 2021-08-18 14:21:26
INSERT INTO `dbtest01`.`t1`(`employee`, `id`) VALUES ('111', 1); #start 299 end 489 time 2021-08-18 14:21:26

# 将上述回滚 sql 导出文件 rollback.sql，导入数据库
mysql>source /backup/rollback.sql;

# 查看 t1 表数据
mysql> select * from t1;
+----+----------+
| id | employee |
+----+----------+
|  1 | 111      |
|  2 | 222      |
|  3 | 333      |
+----+----------+
3 rows in set (0.01 sec)

7.其他

该工具本身的核心代码比较少，主要是在 pymysqlreplication 的基础上进行了二次开发。pymysqlreplication 实现了 MySQL 复制协议，可捕捉不同类型的 EVENT 事件。

具体可参考：https://github.com/noplay/python-mysql-replication

注意事项：
1）binlog2sql 强烈依赖于 MySQL 复制协议，如果复制协议发生改变，则该工具将不可用。虽然，复制协议发生改变的可能性很小（一般都会保持向前兼容），但相对而言，自带的 mysqlbinlog 肯定更懂 binlog ，基于 mysqlbinlog 解析后的结果进行处理，可完全屏蔽复制协议等底层细节。
2）用 python 来解析文本格式的 binlog ，本身也不是件难事。比如， update 语句在 binlog 中的对应的文本，在得到表结构的情况下，基本上可离线解析。

8.参考资料

MySQL下实现闪回的设计思路 (MySQL Flashback Feature)

樱木...
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysqlbinlog工具_mysqlbinlog-日志管理工具
weixin_39886238的博客
12-05 2702
MySQL服务器生成的二进制日志保存在binlog文件中。要以文本格式显示binlog内容可以使用mysql自带的mysqlbinlog日志管理工具解析。二进制日志格式(binlog_format)在MySQL 5.7.7和更高版本中，默认为row，在row格式下我们要查找历史的SQL记录，需要开启binlog_rows_query_log_events来记录执行过的SQL。 mysqlbinlo...
MySQL通过binlog恢复数据
DmxExcalibur的博客
10-24 454
1.背景 MySQL一旦误删数据库之后恢复数据很麻烦，这里记录一下艰辛的恢复过程。 2.方法 2.1首先在MySQL中查看是否打开bin目录 mysql> show variables like 'log_%'; 这里可以看到log_bin是ON的状态，恢复有望。 2.2显示当前主分支的状态 mysql> show master status; 可以看到binl...
binlog2sql-master.zip
09-09
binlog2sql-master
MySQL二进制日志(binlog)解析工具
06-30 3060
binlog2parser.py是一款MySQL二进制日志(binlog)解析工具,支持mysql5.6/5.7。- binlog2parser.json文件配置日志文件对应的数据库连接信息，用于解析列名。- 支持日期、POS点、库、表过滤,SQL类型进行过滤。- 通过binlog文件生成DML语句。- 通过binlog文件生成回滚语句。
MySQL 使用 MyFlash 快速恢复误删除、误修改数据
最新发布
qq_44624290的博客
06-18 667
指定回滚开始的位置。如不指定，从文件的开始处回滚。请指定正确的有效的位置，否则无法回滚。：指定回滚结束的位置。请指定正确的有效的位置，否则无法回滚。的优势在于它提供了更多的过滤选项，使得回滚操作变得更加容易。操作，用来恢复之前误删除的数据，下面可以直接使用。如果不指定该参数，相当于指定了所有数据库。如果不指定该参数，相当于指定了所有表。：指定需要回滚的数据库名。的单个和范围两种形式，如有多种形式，用。的单个和范围两种形式，如有多种形式，用。：指定回滚的开始时间。：指定回滚的结束时间。
安装与配置binlog2sql以恢复mysql数据
雅冰石的专栏
03-14 977
利用binlog2sql恢复mysql数据
mysqlbinlog 获取云数据库binlog日志记录
weixin_42763067的博客
06-13 609
通过获取到的名称，执行命令下载binlog文件。获取当前数据库的binlog日志名字。将下载好的包上传到服务器。
mysql没开启binlog恢复数据_MySQL：binlog恢复数据
weixin_39676348的博客
01-21 1339
如果删库了，先别急着跑路。binlog是MySQL的二进制日志，会记录我们的操作。删除的数据可以通过binlog恢复，不过要先开启binlog功能，编辑my.cnf文件完成下面配置后重启。log-bin=/usr/local/mysql/data/binlog/mysql-bin# 自定义,保证唯一性server-id=111# binlog格式，有3种statement,row,mixed，使用...
mysql闪回工具binlog2sql安装配置教程详解
09-08
【MySQL闪回工具binlog2sql详解】 MySQL的binlog2sql是一款强大的开源工具，它由Python编写，专门用于解析MySQL的二进制日志（binlog），将binlog转换为可读的原始SQL语句，甚至能生成回滚SQL，帮助DBA和运维人员在...
MySQL 闪回工具binlog2sql
07-08
MySQL 闪回工具binlog2sql是美团公司开发的一款用于MySQL数据恢复的实用程序，它主要依赖于MySQL的二进制日志（binlog）功能。在数据库出现错误或者需要回溯到某个特定时间点的状态时，binlog2sql能够帮助用户快速、...
mysql数据库恢复 binlog2sql数据库恢复
08-25
本篇文章将深入探讨如何利用binlog2sql工具进行MySQL数据库的恢复。 首先，我们来理解一下什么是MySQL的二进制日志。MySQL的binlog是一种记录所有更改数据库的非临时性操作的日志，包括INSERT、UPDATE和DELETE等DML...
利用binlog还原sql或者恢复数据python工具
04-27
利用binlog还原sql或者恢复数据python工具binlog2sql
mysqlbinlog.exe文件,win 7mysql 5.6下可用
05-13
phpStudy的mysql没有这个 mysqlbinlog文件,不能查看二进制文件,下载这个附件后解压后的EXE文件放到phpStudy\MySQL\bin下即可使用
Mysql的Binlog数据恢复:不小心删除数据库详解
12-16
本篇文章将详细解析如何利用MySQL的Binlog进行数据恢复。 首先，启用Binlog功能是恢复的前提。在MySQL中，Binlog默认可能是关闭的，需要手动开启。这通常涉及到修改MySQL的配置文件，如在Linux系统中的`/etc/my.cnf...
mysqlbinlog工具_带你解析MySQL binlog
weixin_39849070的博客
11-24 607
前言：我们都知道，binlog可以说是MySQL中比较重要的日志了，在日常学习及运维过程中，也经常会遇到。不清楚你对binlog了解多少呢？本篇文章将从binlog作用、binlog相关参数、解析binlog内容三个方面带你了解binlog。1.binlog简介binlog即binary log，二进制日志文件。它记录了数据库所有执行的DDL和DML语句(除了数据查询语句select、show等)...
MySQL--binlog2sql 安装及使用
2301_76957510的博客
03-20 313
INSERT INTO `test`.`tt8`(`id`, `id_tye`, `zdr_id`, `zdr_name`, `zt_name`) VALUES (30, 'OUT', '辽宁KK服务有限公司', 'SYSADMIN', 'sysadmin');| 30 | OUT?| SYSADMIN?| 31 | OUT?| SYSADMIN?| 28 | OUT?| 29 | OUT?1 | "IN"?| KK科技有限公司"?2 | "IN"?| KK科技有限公司"?
MySQL Binlog 解析工具 Maxwell 详解
weixin_45526912的博客
12-28 1367
Maxwell是一个能实时读取MySQL二进制日志binlog，并生成 JSON 格式的消息，作为生产者发送给 Kafka，Kinesis、RabbitMQ、Redis、Google Cloud Pub/Sub、文件或其它平台的应用程序。它的常见应用场景有ETL、维护缓存、收集表级别的dml指标、增量到搜索引擎、数据分区迁移、切库binlog回滚方案等。maxwell相对于canal的优势是使用简单，它直接将数据变更输出为json字符串，不需要再编写客户端。注意：后台运行参数 --daemon。
mysql binlog2sql_mysql闪回工具binlog2sql 安装使用
weixin_33549415的博客
01-27 373
软件包下载地址：链接：https://pan.baidu.com/s/15dDeGufVWOgVrfATGWBzaA 密码：gnz8DBA或开发人员，有时会误删或者误更新数据，如果是线上环境会影响较大，这就需要能快速回滚;而MySQL闪回(flashback)利用binlog能直接进行回滚，并且能快速恢复数据(MySQL闪回现在只支持DML语句进行闪回)。利用binlog闪回需要的条件：Mysql...
mysql binlog 数据恢复工具
08-07
MySQL的binlog是一种二进制日志文件，用于记录数据库的修改操作。当数据库出现故障时，可以通过binlog来进行数据的恢复。 MySQL提供了多种工具来进行binlog数据恢复。其中最常用的工具是mysqlbinlog。mysqlbinlog是一个命令行工具，用于解析binlog文件，并将其中的SQL语句打印出来。通过mysqlbinlog，可以将binlog中记录的SQL语句重新执行，从而实现数据的恢复。 使用mysqlbinlog进行数据恢复的步骤如下： 1. 找到事故发生时的最新的binlog文件和位置。 2. 使用mysqlbinlog命令解析binlog文件： ```shell mysqlbinlog binlog.000001 --start-position=12345 ``` 这将会将binlog文件中从指定位置开始的所有SQL语句输出到终端。 3. 检查输出的SQL语句，确认其正确性，并逐条重新执行，以恢复数据。 除了mysqlbinlog工具外，还有其他一些第三方的工具可以进行binlog数据恢复。例如，Binlog Explorer、Binlog Viewer等工具提供了更加友好的图形界面，方便操作和查看binlog文件。 需要注意的是，在使用binlog进行数据恢复时，要确保事故发生后的binlog文件没有被删除或覆盖，否则无法完全恢复数据。另外，对于大规模的数据库，binlog数据恢复可能比较耗时和复杂，需要谨慎操作。 总而言之，mysqlbinlog是一种常用的binlog数据恢复工具，通过解析binlog文件中的SQL语句，可以重新执行这些SQL语句以恢复数据。此外，还有其他一些第三方工具可供选择。在进行binlog数据恢复时，需要注意保留好binlog文件，并进行逐条确认和执行。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值