当前位置:Gxlcms > 数据库问题 > binlog2sql闪回工具的使用

binlog2sql闪回工具的使用

时间:2021-07-01 10:21:17 帮助过:3人阅读

install openssl-devel bzip2-devel expat-devel gdbm-devel readline-devel sqlite-devel yum install python34 -y python3 --version wget --no-check-certificate https://bootstrap.pypa.io/get-pip.py python3 get-pip.py pip3 -V

切换升级本地python版本

python -V; python2.6 -V     
# 查看当前python版本 这两个应该都是原始的2.6.x
ls -hli /usr/bin/python*
cp -f /usr/bin/python2.6 /usr/bin/python2.6.bak
rm -f /usr/bin/python /usr/bin/python
cp /usr/bin/python3.4 /usr/bin/python
ls -hli /usr/bin/python*
python -V; python2.6 -V 
# 验证python版本是否OK(python->2.7, python2.6->python2.6.x)


yum -h # 验证yum已经不可用,命令会出错退出
head -n 2 /usr/bin/yum
sed -i s/python$/&2\.6/ /usr/bin/yum
head -n 2 /usr/bin/yum    
# 将/usr/bin/yum 中的python 替换为python2.6
yum -h # 验证yum可用,此时不会再出错退出

二、安装binlog2sql

# 参考资料:https://www.runoob.com/w3cnote/python-pip-install-usage.html
cd /opt/
yum -y install  python-pip
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt -i https://pypi.tuna.tsinghua.edu.cn/simple

查看帮助

[root@dba_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/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]

Parse MySQL binlog to SQL you want

optional arguments:
  --stop-never          Continuously parse binlog. default: stop at the latest
                        event when you start.
  --help                help information
  -K, --no-primary-key  Generate insert sql without primary key if exists
  -B, --flashback       Flashback data to start_position of start_file
  --back-interval BACK_INTERVAL
                        Sleep time between chunks of 1000 rollback sql. set it
                        to 0 if do not need sleep

connect setting:
  -h HOST, --host HOST  Host the MySQL database server located
  -u USER, --user USER  MySQL Username to log in as
  -p [PASSWORD [PASSWORD ...]], --password [PASSWORD [PASSWORD ...]]
                        MySQL Password to use
  -P PORT, --port PORT  MySQL port to use

interval filter:
  --start-file START_FILE
                        Start binlog file to be parsed
  --start-position START_POS, --start-pos START_POS
                        Start position of the --start-file
  --stop-file END_FILE, --end-file END_FILE
                        Stop binlog file to be parsed. default: --start-file
  --stop-position END_POS, --end-pos END_POS
                        Stop position. default: latest position of --stop-
                        file  --start-datetime START_TIME
                        Start time. format %Y-%m-%d %H:%M:%S
  --stop-datetime STOP_TIME
                        Stop Time. format %Y-%m-%d %H:%M:%S;

schema filter:
  -d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]]
                        dbs you want to process
  -t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]]
                        tables you want to process

type filter:
  --only-dml            only print dml, ignore ddl
  --sql-type [SQL_TYPE [SQL_TYPE ...]]
                        Sql type you want to process, support INSERT, UPDATE,
                        DELETE.
[root@dba_test_002 binlog2sql]# 

 三、使用该工具的前提

1. binlog_format为ROW,且binlog_row_image为full或noblog,默认为full。

2. 必须开启MySQL Server,理由有如下两点:

   1> 它是基于BINLOG_DUMP协议来获取binlog内容

   2> 需要读取server端information_schema.COLUMNS表,获取表结构的元信息,拼接成可视化的sql语句

该工具所需权限如下:

mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘flashbak_user‘@‘%‘ identified by ‘flashbak_user_pwd‘;

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

因为是伪装成slave来获取主的二进制事件,故无需对binlog有可读权限。

四、测试验证

4-1、创建测试表

# 创建测试表
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

insert into user(name) values(小赵);
insert into user(name) values(小王);
insert into user(name) values(小李);
insert into user(name) values(小钱);
insert into user(name) values(小孙);

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  6 | 小赵   |
|  7 | 小王   |
|  8 | 小李   |
|  9 | 小钱   |
| 10 | 小孙   |
+----+--------+
5 rows in set (0.00 sec)

4-2、update回滚实践

# update误操作闪回
mysql> update user set name=王源 where id=8;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#发现修改错了。
[mysql@dba_test_001 binlogdir]$ /data/mysql/percona_server/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv mysql-bin.000024 >./24.binlog
# 搜索到王源,找到position点:--startposition=2082 --stop-position=2268

# 先解析出原始语句,以便确认
# 主库IP: 10.10.50.60 用户名:flashbak_user 密码:flashbak_user_pwd
/usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py  -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ 

[root@dba_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py  -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ 
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO flashbak_user@% IDENTIFIED BY PASSWORD *0589CD47DC016BC67FC7720BADEC3C1368C15F60;
USE bsbtest;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (6, 小赵); #start 822 end 995 time 2019-12-23 10:38:40
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (7, 小王); #start 1074 end 1247 time 2019-12-23 10:38:40
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (8, 小李); #start 1326 end 1499 time 2019-12-23 10:38:40
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (9, 小钱); #start 1578 end 1751 time 2019-12-23 10:38:40
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (10, 小孙); #start 1830 end 2003 time 2019-12-23 10:38:41
UPDATE `sbtest`.`user` SET `id`=8, `name`=王源 WHERE `id`=8 AND `name`=小李 LIMIT 1; #start 2082 end 2268 time 2019-12-23 10:41:20
[root@dba_test_002 binlog2sql]# 

# 进一步筛选
/usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py  -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=UPDATE --start-position=2082 --stop-position=2268
[root@dba_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py  -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=UPDATE --start-position=2082 --stop-position=2268
UPDATE `sbtest`.`user` SET `id`=8, `name`=王源 WHERE `id`=8 AND `name`=小李 LIMIT 1; #start 2082 end 2268 time 2019-12-23 10:41:20
[root@dba_test_002 binlog2sql]# 

# 生成回滚语句
[root@dba_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -B -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=UPDATE --start-position=2082 --stop-position=2268  
UPDATE `sbtest`.`user` SET `id`=8, `name`=小李 WHERE `id`=8 AND `name`=王源 LIMIT 1; #start 2082 end 2268 time 2019-12-23 10:41:20
[root@dba_test_002 binlog2sql]# 

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  6 | 小赵   |
|  7 | 小王   |
|  8 | 王源   |
|  9 | 小钱   |
| 10 | 小孙   |
+----+--------+
5 rows in set (0.00 sec)

mysql> UPDATE `sbtest`.`user` SET `id`=8, `name`=小李 WHERE `id`=8 AND `name`=王源 LIMIT 1; #start 2082 end 2268 time 2019-12-23 10:41:20
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  6 | 小赵   |
|  7 | 小王   |
|  8 | 小李   |
|  9 | 小钱   |
| 10 | 小孙   |
+----+--------+
5 rows in set (0.00 sec)

mysql> 

4-3、delete操作回滚实践

# delete语句回滚
mysql> delete from user where id in (6,8,9,10);
Query OK, 4 rows affected (0.01 sec)

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  7 | 小王   |
+----+--------+
1 row in set (0.00 sec)

[root@dba_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=‘DELETE‘
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO flashbak_user@% IDENTIFIED BY PASSWORD *0589CD47DC016BC67FC7720BADEC3C1368C15F60;
USE bsbtest;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
DELETE FROM `sbtest`.`user` WHERE `name`=小赵 AND `id`=6 LIMIT 1; #start 2612 end 2821 time 2019-12-23 11:03:34
DELETE FROM `sbtest`.`user` WHERE `name`=小李 AND `id`=8 LIMIT 1; #start 2612 end 2821 time 2019-12-23 11:03:34
DELETE FROM `sbtest`.`user` WHERE `name`=小钱 AND `id`=9 LIMIT 1; #start 2612 end 2821 time 2019-12-23 11:03:34
DELETE FROM `sbtest`.`user` WHERE `name`=小孙 AND `id`=10 LIMIT 1; #start 2612 end 2821 time 2019-12-23 11:03:34

# 生成delete的回滚语句
[root@dba_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -B -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=‘DELETE‘ --start-position=2612 --stop-position=2821 
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (10, 小孙); #start 2612 end 2821 time 2019-12-23 11:03:34
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (9, 小钱); #start 2612 end 2821 time 2019-12-23 11:03:34
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (8, 小李); #start 2612 end 2821 time 2019-12-23 11:03:34
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (6, 小赵); #start 2612 end 2821 time 2019-12-23 11:03:34
[root@dba_test_002 binlog2sql]# 

# 应用回滚
mysql>  INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (10, 小孙); #start 2612 end 2821 time 2019-12-23 11:03:34
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (9, 小钱); #start 2612 end 2821 time 2019-12-23 11:03:34
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (8, 小李); #start 2612 end 2821 time 2019-12-23 11:03:34
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (6, 小赵); #start 2612 end 2821 time 2019-12-23 11:03:34
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  6 | 小赵   |
|  7 | 小王   |
|  8 | 小李   |
|  9 | 小钱   |
| 10 | 小孙   |
+----+--------+
5 rows in set (0.00 sec)

mysql> 

4-4、insert操作回滚实践

# insert语句回滚
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (1, 小赵x);
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (2, 小赵x);
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (3, 小赵x);
INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (5, 小赵x);

[root@dba_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=‘INSERT‘
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO flashbak_user@% IDENTIFIED BY PASSWORD *0589CD47DC016BC67FC7720BADEC3C1368C15F60;
USE bsbtest;
CREATE TABLE `user` (
  `id` int(11) NOT 

                  

	 	
                    
                    
                    
                    
                    
                

人气教程排行