binlog2sql的安装及使用

binlog2sql的安装及使用
 
binlog2sql的用途：
 
1) 数据快速回滚(闪回)
2) 主从切换后数据不一致的修复
3) 从binlog生成标准SQL，带来的衍生功能
 
yum -y install epel-release 
yum -y install git  python-pip
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
 
MySQL的配置要开启以下选项：
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
 
要授权一个用户有以下权限：
SELECT, REPLICATION SLAVE, REPLICATION CLIENT
 
权限说明：
 
select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
 
binlog2sql的使用参数说明：
 
mysql连接配置
-h host; -P port; -u user; -p password
解析模式
--stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。
-B, --flashback 生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句SLEEP SELECT(1)。可选。与stop-never或no-primary-key不能同时添加。
解析范围控制
--start-file 起始解析文件。必须。
--start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime 从哪个时间点的binlog开始解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
--stop-datetime 到哪个时间点的binlog停止解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
对象过滤
-d, --databases 只输出目标db的sql。可选。默认为空。
-t, --tables 只输出目标tables的sql。可选。默认为空。
 
练习：
root@localhost:mysql3306.sock [hch]>select * from hch;
+----+-------+
| id | cname |
+----+-------+
|  1 | hch   |
|  2 | tom   |
|  3 | jerry |
+----+-------+
3 rows in set (0.00 sec)
 
# 删除表里的数据
root@localhost:mysql3306.sock [hch]>delete from hch;
Query OK, 3 rows affected (0.12 sec)
 
root@localhost:mysql3306.sock [hch]>select * from hch;
Empty set (0.00 sec)
 
# 查看binlog位置
root@localhost:mysql3306.sock [hch]>show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000010 |      713 |              |                  | 7c91060d-1927-11e8-a036-000c2930f871:1-139 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
 
下面我们使用binlog2sql进行格式为ROW的binlog生成hch库hch表的标准SQL
 
[root@hch binlog2sql]# python binlog2sql.py -h localhost -u root -p mysql123 -d hch -t hch --start-file mysql-bin.000009 > 1.sql
[root@hch binlog2sql]# cat 1.sql
USE hch;
create table hch(id int not null auto_increment, cname varchar(32), primary key(id));
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('hch', 1); #start 480 end 643 time 2018-03-04 17:59:25
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('tom', 2); #start 739 end 902 time 2018-03-04 17:59:29
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('jerry', 3); #start 998 end 1163 time 2018-03-04 17:59:34
DELETE FROM `hch`.`hch` WHERE `cname`='hch' AND `id`=1 LIMIT 1; #start 1259 end 1442 time 2018-03-04 18:02:57
DELETE FROM `hch`.`hch` WHERE `cname`='tom' AND `id`=2 LIMIT 1; #start 1259 end 1442 time 2018-03-04 18:02:57
DELETE FROM `hch`.`hch` WHERE `cname`='jerry' AND `id`=3 LIMIT 1; #start 1259 end 1442 time 2018-03-04 18:02:57
 
我们可以看到，刚刚执行过的sql都生成出来了。
我们现在对hch这个库的所有操作生成反向SQL，这个时候需要在上面语句的基础上带一个-B参数，就是flashback闪回的意思：
 
[root@hch binlog2sql]# python binlog2sql.py -h localhost -u root -p mysql123 -d hch -t hch --start-file mysql-bin.000009 -B
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('jerry', 3); #start 1259 end 1442 time 2018-03-04 18:02:57
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('tom', 2); #start 1259 end 1442 time 2018-03-04 18:02:57
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('hch', 1); #start 1259 end 1442 time 2018-03-04 18:02:57
DELETE FROM `hch`.`hch` WHERE `cname`='jerry' AND `id`=3 LIMIT 1; #start 998 end 1163 time 2018-03-04 17:59:34
DELETE FROM `hch`.`hch` WHERE `cname`='tom' AND `id`=2 LIMIT 1; #start 739 end 902 time 2018-03-04 17:59:29
DELETE FROM `hch`.`hch` WHERE `cname`='hch' AND `id`=1 LIMIT 1; #start 480 end 643 time 2018-03-04 17:59:25
 
可以看到生成了跟上面标准SQL相反的SQL了，通过这些反向SQL可以进行误操的数据恢复。
比如我们想恢复delete命令之前的数据。
 
[root@hch binlog2sql]# python binlog2sql.py -h localhost -u root -p mysql123 -d hch -t hch --start-file mysql-bin.000009 --start-pos=1259 --stop-pos=1442 -B > roll_1.sql 
[root@hch binlog2sql]# cat roll_1.sql
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('jerry', 3); #start 1259 end 1442 time 2018-03-04 18:02:57
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('tom', 2); #start 1259 end 1442 time 2018-03-04 18:02:57
INSERT INTO `hch`.`hch`(`cname`, `id`) VALUES ('hch', 1); #start 1259 end 1442 time 2018-03-04 18:02:57
[root@hch binlog2sql]# mysql -uroot -pmysql123 <roll_1.sql
 
查看数据已经恢复
 
root@localhost:mysql3306.sock [hch]>select * from hch;
+----+-------+
| id | cname |
+----+-------+
|  1 | hch   |
|  2 | tom   |
|  3 | jerry |
+----+-------+
3 rows in set (0.00 sec)