使用binlog2sql工具来恢复数据库
- 2020 年 4 月 24 日
- 筆記
- ----260 MySQL备份与恢复, --200 MySQL
（一）binlog2sql介绍
binlog2sql是国内MySQL大佬danfengcao开发，许多MySQL爱好者参与改进的一款MySQL binlog解析软件。根据不同选项，可以得到原始SQL、回滚SQL、去除主键的SQL等。
github地址为：//github.com/danfengcao/binlog2sql
该工具主要用于：
- 数据快速回滚（闪回）；
- 从binlog生成标准SQL；
适用MySQL版本：MySQL5.6 、MySQL5.7
（二）安装binlog2sql
# 安装git shell> yum install -y git # 安装pip工具 shell> yum install -y epel-release shell> yum 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在使用时需要连接到shuju库上，连接用户的权限为：
select,spuer/replication client,replication slave -- 建议授权： grant select,replication slave,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-file 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, birthday date not null ); -插入3条数据 insert into test01 values(1,'小明','1993-01-02'); insert into test01 values(2,'小华','1994-08-15'); insert into test01 values(3,'小丽','1995-07-12'); mysql> select * from test01; +----+--------+------------+ | id | name | birthday | +----+--------+------------+ | 1 | 小明 | 1993-01-02 | | 2 | 小华 | 1994-08-15 | | 3 | 小丽 | 1995-07-12 | +----+--------+------------+ 3 rows in set (0.00 sec)
STEP2：模拟误删除数据
mysql> delete from test01; Query OK, 3 rows affected (0.00 sec)
STEP3：确认最后的日志
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 1600 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
STEP4：解析出标准SQL，用于定位回滚的开始和结束位置
[root@masterdb binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p123456 -dlijiamandb -t test01 --sql-type DELETE --start-file='master-bin.000001' USE lijiamandb; create table test01 ( id int primary key, name varchar(30) not null, birthday date not null ); DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1993-01-02' AND `id`=1 AND `name`='小明' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26 DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1994-08-15' AND `id`=2 AND `name`='小华' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26 DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1995-07-12' AND `id`=3 AND `name`='小丽' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26
值得注意的是，虽然我指定了只解析DELETE语句，但还是把DDL给解析出来了。
STEP5：解析出回滚SQL
[root@masterdb binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p123456 --flashback -dlijiamandb -t test01 --sql-type DELETE --start-file='master-bin.000001' INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1995-07-12', 3, '小丽'); #start 1287 end 1569 time 2020-04-24 13:40:26 INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1994-08-15', 2, '小华'); #start 1287 end 1569 time 2020-04-24 13:40:26 INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26
STEP6：还原到数据库
[root@masterdb binlog2sql]# mysql -uroot -p123456 lijiamandb mysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1995-07-12', 3, '小丽'); #start 1287 end 1569 time 2020-04-24 13:40:26 69 time 2020-04-24 13:40:26 INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1994-08-15', 2, '小华'); #start 1287 end 1569 time 2020-04-24 13:40:26 Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26 Query OK, 1 row affected (0.00 sec) mysql> select * from test01; +----+--------+------------+ | id | name | birthday | +----+--------+------------+ | 1 | 小明 | 1993-01-02 | | 2 | 小华 | 1994-08-15 | | 3 | 小丽 | 1995-07-12 | +----+--------+------------+ 3 rows in set (0.00 sec)
（五）总结
使用binlog2sql最大的好处就是解析出来的SQL语句非常直观，并且在注释中还包含了时间，这对于我们去查找故障发生点非常实用。想一想之前用过的mysqlbinlog工具，解析出来的结果中含有大量无关的信息，为我们排查问题增加了难度，而binlog2sql解析出来的SQL非常干净，便于我们排查问题，恢复数据。
