使用binlog2sql工具来恢复数据库

（一）binlog2sql介绍

binlog2sql是国内MysqL大佬danfengcao开发，许多MysqL爱好者参与改进的一款MysqL binlog解析软件。根据不同选项，可以得到原始sql、回滚sql、去除主键的sql等。

github地址为：https://github.com/danfengcao/binlog2sql

该工具主要用于：

数据快速回滚（闪回）；
从binlog生成标准sql；

适用MysqL版本：MysqL5.6 、MysqL5.7

（二）安装binlog2sql

# 安装git
shell> yum install -y git

# 安装pip工具
shell> install -y epel-release
shell> install -y python-pip


# 安装binlog2sql
shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt

（三）使用binlog2sql

要使用binlog2sql，MysqL服务器需要设置以下参数：

[MysqLd] server_id = 1 log_bin = /var/log/MysqL/MysqL-bin.log max_binlog_size = 100M binlog_format = row binlog_row_image = full

binlog2sql在使用时需要连接到数据库上，连接用户的权限为：

select,spuer/replication client,replication slave -- 建议授权： grant replication slave,1)">replication client on *.* to user;

binlog2sql的语法为：

[root@masterdb binlog2sql]# pwd
/root/binlog2sql/binlog2sql
[root@masterdb binlog2sql]# python binlog2sql.py --help
usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]]
                     [-P PORT] [--start-file START_FILE]
                     [--start-position START_POS] [--stop- END_FILE]
                     [--stop-position END_POS] [--start-datetime START_TIME]
                     [--stop-datetime STOP_TIME] [--stop-never] [--help]
                     [-d [DATABASES [DATABASES ...]]]
                     [-t [TABLES [TABLES ...]]] [--only-dml]
                     [--sql-type [sql_TYPE [sql_TYPE ...]]] [-K] [-B]
                     [--back-interval BACK_INTERVAL]

语法解析：

MysqL 链接配置参数

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

解析模式参数：

--stop-never : 持续解析binlog，同步至执行命令时最新的binlog位置，默认false。
-K,--no-primary-key ：对insert语句去除主键，默认false。
-B,--flashback : 生成回滚sql，可解析大文件，不受内存限制，默认false。与stop-never或no-primary-key不能同时添加。
--back-interval :在-B模式下，每打印1000条回滚sql，SLEEP多少秒，默认为1。

范围控制参数：

--start-file ：起始解析文件，只需文件名，无需全路径。必需参数。
--stop-file / --end-file : 终止解析文件，默认为start-file同一个文件，若解析模式为stop-never，则该参数失效。
--start-position / --start-pos : 起始解析位置，默认start-file文件起始位置。
--stop-position / --stop-pos : 终止解析位置，默认stop-file终点位置。
--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 : 只解析目标表的sql，多张表用空格隔开，如：-t table1 table2。
--only-dml ：只解析DML，忽略DDL。
--sql-type : 只解析指定类型，支持INSERT,UPDATE,DELETE。多个类型用空格隔开，如--sql-type INSERT UPDATE，默认全解析。

（四）binlog2sql测试

测试目的：本次实验模拟误删除数据，通过使用binlog2sql将数据找回来。

STEP1：构造测试数据

构造测试表 create table test01 ( id int primary key,name varchar(30) not null ); -插入3条数据 insert into test01 values(1,'小明',1)">1993-01-02'); 2,1)">小华1994-08-153,1)">小丽1995-07-12); MysqL> select from test01; +--+--------+------------+ | id | name | birthday | | 1 | 小明 | 1993-0102 2 | 小华 19940815 3 | 小丽 19950712 --+--------+------------+ 3 rows in set (0.00 sec)

STEP2：模拟误删除数据

MysqLdelete  test01;
Query OK,3 rows affected (
STEP3：确认最后的日志

> show master status;
-----------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| master-bin.000001 |     1600 |              |                  |                   1 row STEP4：解析出标准sql，用于定位回滚的开始和结束位置

[root@masterdb binlog2sql]# python binlog2sql.py -h127.0.0.-P3306 -uroot -p123456 -dlijiamandb -t test01 sql-type DELETE --start-file='master-bin.000001'
 USE lijiamandb;
 
 );
 DELETE FROM `lijiamandb`.`test01` WHERE `birthday`=' AND `id`=AND `name`' LIMIT 1; #start 1287 end 1569 time 20200424 13:40:26
 26
值得注意的是，虽然我指定了只解析DELETE语句，但还是把DDL给解析出来了。

STEP5：解析出回滚sql

-p123456 flashback  -dlijiamandb -t test01 --sql-type DELETE --start-file='master-bin.000001' 
INSERT INTO `lijiamandb`.`test01`(`birthday`,`id`,`name`) VALUES ('); #start 26
26

STEP6：还原到数据库

]# MysqL p123456 lijiamandb MysqL26 69 time 40:26Query OK,1); font-weight: bold">1 row affected (0.00 sec) MysqL26 Query OK,1); font-weight: bold">0.01 test01; 0.00 sec)

（五）总结

使用binlog2sql最大的好处就是解析出来的sql语句非常直观，并且在注释中还包含了时间，这对于我们去查找故障发生点非常实用。想一想之前用过的MysqLbinlog工具，解析出来的结果中含有大量无关的信息，为我们排查问题增加了难度，而binlog2sql解析出来的sql非常干净，便于我们排查问题，恢复数据。

使用binlog2sql工具来恢复数据库

猜你在找的MySQL相关文章