6 Star 29 Fork 4

Succy / bin2sql

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
克隆/下载
贡献代码
同步代码
取消
提示: 由于 Git 不支持空文件夾,创建文件夹后会生成空的 .keep 文件
Loading...
README
Apache-2.0

MySQL binlog转Sql工具

简介

工具起源

​ 一开始,我只想找一个工具去解析mysql的binlog,以便于不时之需恢复数据,首当其冲肯定是想到mysql官方都提供了哪些,因此最开始是研究mysqlbinlog这个工具的,但是后面发现,它只能指定到database级别,并不能到table级别,而且有时候我们不是很关心create/drop之类的sql,比较关心DML,甚至关心我能不能只过滤出来指定的表指定的类型的SQL语句。也正是由于这个原因,我在网上寻找了很多这方便的工具,总结一下网上的工具主要都是如下两方面的:

1、通过伪装成slave拉取binlog来进行处理。以binlog2sql为代表.

2、直接解析binlog文件,然后对数据做二次过滤的,以mysqlbinlog和本程序为代表。

​ 然而binlog2sql在我使用过程中,总得来说也还不错的,就是其运行有点慢,我就想能不能有一个工具可以直接解析binlog又能满足我的要求的?寻求了一圈之后我发现并没有很符合我的要求的,于是我打算自己写一个。一开始我并不打算使用Perl语言写的,因为我对这个语言不是很熟悉,我是想着用go或者python写的,但是在我用go和python写了一段时间之后,都发现在处理文本这块有些许不足,可能也是因为我的水平问题吧,因此我选择了Perl语言,简单看了下语法和网上的一些案例之后,就开始写了,Perl同时有着强大的正则表达式和能直接运行shell命令的强大特点让我在编写过程中节约了很多时间。

​ 这个小工具采用了mysqlbinlog作为主要产生数据的工具,通过Perl调用mysqlbinlog获取到可读的binlog数据,然后再进行流式处理,从而达到目的,在目前我的使用过程中发现,这样的配合执行速度还是不错的。由于我的Perl水平实在是有限,程序也难免会有错误,如果发现有错误或者有更好的写法,欢迎issue和pr或者直接邮件联系本人1459307744@qq.com

功能介绍

这是一款使用perl语言开发的mysql binlog转sql的工具,主要是弥补了mysqlbinlog这个自带的工具不能指定表名的不足,主要包含如下功能

  • 支持指定start-datetime和stop-datetime解析binlog。
  • 支持指定start-position和stop-position解析binlog。
  • 支持解析指定的databases、指定的tables。
  • 支持指定dml类型,仅支持INSERT、UPDATE、DELETE
  • 支持only-dml属性,如果指定该属性,则不会输出dml以外的sql。
  • 支持闪回(flashback)
  • 支持获取远端服务器的Binlog

设计思路

整个脚本设计思路非常简单,大致分为三步

1、通过操作指定的数据源,获取到information_schema存储的表字段信息。

2、通过mysqlbinlog工具,获取指定binlog文件。

3、解析binlog,提取出来sql,替换占位符为字段名,还原SQL,通过反转SQL,起到闪回的目的。

经过本人实践,依托Perl和mysqlbinlog,其性能还是不错的,整个过程都是使用流式处理的,理论上可以应对比较大的binlog文件,但是本人公司内毕竟需要恢复数据的场景并不是那么多(如果非常多误操作,怕是饭碗不保了),因此可能有些地方会略为片面,有需要的可以自行进行测试和修改或者提ISSUE。

注意事项

这里的内容非常关键,请务必注意

Q:如果我把表删了,还能恢复数据吗?

A:如果你把表删了,有备份在,并且表结构也没发生变化,可以恢复的。

Q:我就是把表删了,但是没有重建表结构,能把这个表之前发生的SQL操作恢复吗?

A:不可以。因为我们依赖information_schema获取表的字段名和顺序,根据这个替换binlog的占位符,如果把表删了,并且没有先恢复表结构,会忽略DML的解析的。

Q:如果表结构发生了变化,能恢复吗?

A:不能,都改表了,你恢复以前的数据有什么意义?就算强行解析出来,可能都错位了或者这个SQL就是有问题的了

安装和配置

github: https://github.com/Succy/bin2sql

gitee: https://gitee.com/succy/bin2sql

免安装版本

直接到release下载可执行文件,到Linux系统,添加可执行权限就可以运行了

源码安装

首先说明一下,我都是在Linux下测试的,并且也是针对Linux环境开发的,因为Perl语言在很多Linux发行版都内置,我所使用的是CentOS7,内置的Perl是5.16.3,如果想要在Windows下使用,请安装Perl语言环境,并且修改内部的mysqlbinlog为mysqlbinlog.exe(前提是mysqlbinlog.exe在环境变量内)。

特别注意:MySQL Server一定要开启binlog,而且要开启binlog_format=row,因为只有row模式下,才会记录更新前后的数据,如果您用的是mixed模式,请到mixed分支,mixed模式下,不支持闪回

我测试的Mysql数据库版本是5.7.25 理论上>=5.6的版本都支持。不过我没有做过测试

下面是安装步骤:

1、安装DBI/DBD

由于row模式下,记录了更新前后的数据,并且没有记录表的字段名,都是使用@1这样的占位符来代替字段名,所以,要还原原本的字段名必须通过mysql的information_schema这个库来获取对应表的字段。因此在脚本内,使用了Perl的DBI来操作数据库。

在CentOS7下面,关于安装DBI/DBD::mysql的方法有很多,有直接用yum安装的,也有下载源码安装的,不过有些我也没试过,下面是我本人安装的步骤。

1、通过yum安装cpan(cpan是Perl的一个包管理工具,类似nodejs的npm,python的pip)
yum -y install cpan
2、通过cpan安装DBI,如果是第一次使用cpan,会有一些配置的选项需要询问配置,此时,全部默认即可
cpan install DBI
3、通过cpan安装DBD:mysql
cpan install DBD::mysql

2、克隆项目并授权执行

1、克隆本项目到服务器本地
2、进入项目根目录并且授予可执行权限
cd bin2sql && chmod +x bin2sql
3、运行bin2sql

直接运行bin2sql会出现如下的帮助文档

MySQL Binlog to SQL
Options:
    -h, --host=name             Get the binlog from server, default localhost.
    -u, --user=name             Connect to the remote server as username, default root.
    -P, --port=#                Port number to use for connection or 3306 for default to.
    -p, --password[=name]       Password to connect to remote server.
    -t, --tables=name           Export tables in table names, delimiter by comma.
    -d, --database=name         List entries for just this database (local log only).
    -B, --flashback             Is print flashback SQL, only DML could be flashback.
    --start-datetime=name       Start reading the binlog at first event having a datetime
                                equal or posterior to the argument; the argument must be
                                a date and time in the local time zone, in any format
                                accepted by the MySQL server for DATETIME and TIMESTAMP
                                types, for example: 2004-12-25 11:25:56 (you should
                                probably use quotes for your shell to set it properly).

    --start-position=#          Start reading the binlog at position N. Applies to the
                                first binlog passed on the command line.

    --stop-datetime=name        Stop reading the binlog at first event having a datetime
                                equal or posterior to the argument; the argument must be
                                a date and time in the local time zone, in any format
                                accepted by the MySQL server for DATETIME and TIMESTAMP
                                types, for example: 2004-12-25 11:25:56 (you should
                                probably use quotes for your shell to set it properly).

    --stop-position=#           Stop reading the binlog at position N. Applies to the
                                last binlog passed on the command line.

    --only-dml                  Only print dml sql, optional, default disabled.
    --sql-type                  Sql type you want to process, support INSERT, UPDATE, DELETE.
    -f, --binlog=name           Read from binlog file.
    --help                      Print help message.

3、作者建议

如果数据库服务器有多台的话,建议在一台空的服务器运行本脚本,通过设定-h参数远程抓去binlog解析即可,不要随意放在数据库服务器上面运行,其原因有2

  • 不需要到处安装DBI和DBD这些Perl的数据库依赖
  • mysqlbinlog和bin2sql两个进程同时运行,对CPU有一定的消耗,可能会影响到数据库服务器的IO吞吐

用法和示例

1、用法选项

mysql相关连接配置

不管是获取schema信息还是mysqlbinlog获取binlog信息,都是使用同一套mysql的连接参数

-h host; -P port; -u user; -p password
这几个参数都是给mysqlbinlog使用的,这里强调一个参数,就是-u这个参数,必须拥有 REPLICATION SLAVE 权限。
建议授权如下
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO User

对象过滤

-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。可选。默认为增删改都解析。

范围解析

这里的参数都是配合mysqlbinlog使用的

-f,--binlog 要解析的Binlog文件名,无需全路径 。必须。

--start-position 起始解析位置。可选。

--stop-position 终止解析位置。可选。

--start-datetime 起始解析时间,格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

--stop-datetime 终止解析时间,格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

闪回

-B, --flashback 生成回滚SQL,可选,默认为false,只有DML才支持闪回,DDL不支持闪回。

2、示例

创建示例表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `username` varchar(255) DEFAULT NULL COMMENT '用户名',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `phone_no` varchar(255) DEFAULT NULL COMMENT '电话号码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查看表原有数据

mysql> select * from user;
+----+-----------+-----------------+---------------------+--------------+
| id | username  | address         | create_time         | phone_no     |
+----+-----------+-----------------+---------------------+--------------+
|  1 | Succy     | 广西南宁市      | 2021-05-03 21:05:14 | 1300000001   |
|  2 | 王小花    | 山西太原市      | 2021-05-03 21:05:46 | 15099999999  |
|  3 | 江小白    | 重庆市          | 2021-05-03 21:06:21 | 19788888888  |
|  4 | 郭靖      | 湖北襄阳        | 2021-05-03 21:06:43 | 188888898999 |
|  5 | 杨过      | 江苏苏州        | 2021-05-03 21:07:10 | 16666666878  |
|  6 | 陆无双    | 山东济南市      | 2021-05-03 21:07:35 | 155236995454 |
+----+-----------+-----------------+---------------------+--------------+
6 rows in set (0.00 sec)

随机对数据进行修改,添加,删除,然后查询其binlog记录的sql。

解析出user表所有操作的sql

shell> ./bin2sql -d demo -f mysql-bin.000002 -h 127.0.0.1 -t user

#210503 21:03:54 end_log_pos: 7852
CREATE TABLE `demo`.`user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `username` varchar(255) NULL COMMENT '用户名',
  `address` varchar(255) NULL COMMENT '地址',
  `create_time` datetime(0) NULL COMMENT '创建时间',
  `phone_no` varchar(255) NULL COMMENT '电话号码',
  PRIMARY KEY (`id`)
);
#210503 21:05:24 end_log_pos: 8128
INSERT INTO `demo`.`user` VALUES(1, 'Succy', '广西南宁市', '2021-05-03 21:05:14', '1300000001');
#210503 21:05:53 end_log_pos: 8440
INSERT INTO `demo`.`user` VALUES(2, '王小花', '山西太原市', '2021-05-03 21:05:46', '15099999999');
#210503 21:06:27 end_log_pos: 8746
INSERT INTO `demo`.`user` VALUES(3, '江小白', '重庆市', '2021-05-03 21:06:21', '19788888888');
#210503 21:06:50 end_log_pos: 9053
INSERT INTO `demo`.`user` VALUES(4, '郭靖', '湖北襄阳', '2021-05-03 21:06:43', '188888898999');
#210503 21:07:14 end_log_pos: 9359
INSERT INTO `demo`.`user` VALUES(5, '杨过', '江苏苏州', '2021-05-03 21:07:10', '16666666878');
#210503 21:07:44 end_log_pos: 9672
INSERT INTO `demo`.`user` VALUES(6, '陆无双', '山东济南市', '2021-05-03 21:07:35', '155236995454');
#210503 21:10:45 end_log_pos: 10030
UPDATE `demo`.`user` SET `id`=5, `username`='杨过过', `address`='江苏南京市', `create_time`='2021-05-03 21:07:10', `phone_no`='16666666878' WHERE `id`=5 AND `username`='杨过' AND `address`='江苏苏州' AND `create_time`='2021-05-03 21:07:10' AND `phone_no`='16666666878';
#210503 21:11:07 end_log_pos: 10340
INSERT INTO `demo`.`user` VALUES(7, '公孙绿萼', '绝情谷', '2021-05-03 21:11:01', '188777738934');
#210503 21:11:30 end_log_pos: 10645
INSERT INTO `demo`.`user` VALUES(8, '程英', '桃花岛', '2021-05-03 21:11:24', '1778346836483');
#210503 21:11:33 end_log_pos: 10951
DELETE FROM `demo`.`user` WHERE `id`=3 AND `username`='江小白' AND `address`='重庆市' AND `create_time`='2021-05-03 21:06:21' AND `phone_no`='19788888888';
#210503 21:11:51 end_log_pos: 11315
UPDATE `demo`.`user` SET `id`=2, `username`='王小花', `address`='广东深圳市', `create_time`='2021-05-03 21:05:46', `phone_no`='15099999999' WHERE `id`=2 AND `username`='王小花' AND `address`='山西太原市' AND `create_time`='2021-05-03 21:05:46' AND `phone_no`='15099999999';

后面发现有些数据删错了,更新的也错了,想要把所有删除的和更新的数据恢复

闪回所有误操作的数据

shell> ./bin2sql -d demo -f mysql-bin.000002 -h 127.0.0.1 -t user -B --sql-type DELETE,UPDATE
#210503 21:10:45 end_log_pos: 10030
UPDATE `demo`.`user` SET `id`=5, `username`='杨过', `address`='江苏苏州', `create_time`='2021-05-03 21:07:10', `phone_no`='16666666878' WHERE `id`=5 AND `username`='杨过过' AND `address`='江苏南京市' AND `create_time`='2021-05-03 21:07:10' AND `phone_no`='16666666878';
#210503 21:11:33 end_log_pos: 10951
INSERT INTO `demo`.`user` VALUES(3, '江小白', '重庆市', '2021-05-03 21:06:21', '19788888888');
#210503 21:11:51 end_log_pos: 11315
UPDATE `demo`.`user` SET `id`=2, `username`='王小花', `address`='山西太原市', `create_time`='2021-05-03 21:05:46', `phone_no`='15099999999' WHERE `id`=2 AND `username`='王小花' AND `address`='广东深圳市' AND `create_time`='2021-05-03 21:05:46' AND `phone_no`='15099999999';

更多玩法等着你去发掘

鸣谢

binlog2sql 这个项目给我提供了我python版本的借鉴,虽然后面python版本的流产了。

MySQL_Binlog_Table_Filter 本工具借鉴了这个项目,由于我是Perl新手,这个项目给我提供了不少Perl相关写法借鉴。

Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright [yyyy] [name of copyright owner] Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

简介

mysql binlog文件解析成sql的小工具,支持ROW模式下SQL解析,DML闪回(生成回滚语句) 展开 收起
Perl
Apache-2.0
取消

发行版 (2)

全部

贡献者

全部

近期动态

加载更多
不能加载更多了
Perl
1
https://gitee.com/succy/bin2sql.git
git@gitee.com:succy/bin2sql.git
succy
bin2sql
bin2sql
master

搜索帮助

14c37bed 8189591 565d56ea 8189591