41.MySQL 闪回技术mysqlbinlog_flashback / binlog2sql 基于 python3

戒掉贪嗔痴(薛双奇)

已于 2023-02-20 16:06:04 修改

阅读量161

点赞数

分类专栏： MySQL数据库运维文章标签： mysql 数据库

于 2023-02-20 15:53:38 首次发布

本文链接：https://blog.csdn.net/weixin_43346403/article/details/129125908

版权

MySQL数据库运维专栏收录该内容

125 篇文章 7 订阅

订阅专栏

1.准备安装包

[root@mysql2 ~]# ll
total 23968
-rw-r--r--. 1 root root 328614 Feb 21 15:11 mysqlbinlog_flashback_for_py3.tar.gz --这个我改造过,适合Python3;
-rw-r--r--. 1 root root 75293 Feb 21 15:11 PyMySQL-0.9.3.tar.gz
-rw-r--r--. 1 root root 24033206 Feb 21 15:11 Python-3.7.14.tgz
-rw-r--r--. 1 root root 96750 Feb 21 15:11 python-mysql-replication-main.zip

2.安装python3.7
--依赖包安装。
yum -y install zlib-devel python-devel gcc libffi-devel tk-devel libpcap-devel bzip2-devel db4-devel \
xz-devel openssl-devel ncurses-devel patch readline-devel gdbm-devel sqlite-devel

tar xvf Python-3.7.14.tgz
cd Python-3.7.14
./configure --prefix=/usr/local/python3 --with-ssl
make && make install
ln -s /usr/local/python3/bin/python3 /usr/bin/python3
ln -s /usr/local/python3/bin/pip3 /usr/bin/pip3

3.安装 python-mysql-replication-main.zip
unzip python-mysql-replication-main.zip
cd python-mysql-replication-main
python3 setup.py install

4.安装 PyMySQL-0.9.3.tar.gz
tar xvf PyMySQL-0.9.3.tar.gz
cd PyMySQL-0.9.3
python3 setup.py install

5.安装 mysqlbinlog_flashback_for_py3.tar.gz
tar xvf mysqlbinlog_flashback_for_py3.tar.gz

--闪回技术需要表又主键。
use test;
create table test6(id int,name varchar(10));
alter table test6 add primary key(16);
insert into test6 values(1,'xsq'),(1,'xsq'),(1,'xsq');

mysql> show binary logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| mysql-binlog.000065 | 1613 |
| mysql-binlog.000066 | 2762 |
+---------------------+-----------+

6.使用闪回技术。
mkdir log
python3 /root/mysqlbinlog_flashback-master/mysqlbinlog_back.py --host="192.168.1.51" --port=3306 --username="root" \
--password="rootroot" --schema=test --tables="test6" -S "mysql-binlog.000065"

[root@mysql2 ~]# cd log
[root@mysql2 log]# ll
total 12
-rw-r--r--. 1 root root 244 Feb 21 15:28 flashback_test_20230221_152837.sql
-rw-r--r--. 1 root root 182 Feb 21 15:28 save_data_create_table_test_20230221_152837.sql
-rw-r--r--. 1 root root 502 Feb 21 15:28 save_data_dml_test_20230221_152837.sql

--查看DML 的闪回语句。
more flashback_test_20230221_152837.sql
#end_log_pos 2215 2023-02-21T13:31:20 1676957480 mysql-binlog.000066;
delete from `test6` where `id`=1;
#end_log_pos 2731 2023-02-21T14:50:08 1676962208 mysql-binlog.000066;
delete from `test6` where `id`=2;
delete from `test6` where `id`=3;

--保存数据创建的表。
[root@mysql2 log]# cat save_data_create_table_test_20230221_152837.sql
CREATE TABLE `_test6_keep_data_` (op varchar(64),op_datetime datetime,bfr_id int(11),
bfr_name varchar(10),aft_id int(11),aft_name varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--闪回的正向语句。
[root@mysql2 log]# cat save_data_dml_test_20230221_152837.sql
#end_log_pos 2215 2023-02-21T13:31:20 1676957480 mysql-binlog.000066;
insert into `_test6_keep_data_`(`op`,`op_datetime`,`aft_id`,`aft_name`) values('insert','2023-02-21 13:31:20',1,'xsq');
#end_log_pos 2731 2023-02-21T14:50:08 1676962208 mysql-binlog.000066;
insert into `_test6_keep_data_`(`op`,`op_datetime`,`aft_id`,`aft_name`) values('insert','2023-02-21 14:50:08',2,'xsq');
insert into `_test6_keep_data_`(`op`,`op_datetime`,`aft_id`,`aft_name`) values('insert','2023-02-21 14:50:08',3,'xsq');

7.使用另外一个工具 binlog2sql 闪回

[root@mysql1 binlog2sql]# python3 /root/binlog2sql/binlog2sql/binlog2sql.py --flashback --host 192.168.1.51 --port 3306 \
--user root --password rootroot -d test -t test6 --start-file='mysql-binlog.000066'

DELETE FROM `test`.`test6` WHERE `id`=3 AND `name`='xsq' LIMIT 1; #start 2488 end 2731 time 2023-02-21 14:50:08
DELETE FROM `test`.`test6` WHERE `id`=2 AND `name`='xsq' LIMIT 1; #start 2488 end 2731 time 2023-02-21 14:50:08
DELETE FROM `test`.`test6` WHERE `id`=1 AND `name`='xsq' LIMIT 1; #start 1991 end 2215 time 2023-02-21 13:31:20

生成立闪回语句。

8.mysqlbinlog_back 报错处理。

[root@mysql1 PyMySQL-0.9.3]# python3 /root/mysqlbinlog_flashback-master/mysqlbinlog_back.py --host="192.168.1.51" --port=3306 --username="root" --password="rootroot" --schema=test --tables="test6" -S "mysql-bin.000009"
Traceback (most recent call last):
File "/root/mysqlbinlog_flashback-master/mysqlbinlog_back.py", line 12, in <module>
from flashback import Parameter,deal_all_event,generate_create_table,convert_datetime_to_timestamp
File "/root/mysqlbinlog_flashback-master/flashback.py", line 24, in <module>
from joint_sql import joint_update_sql,joint_insert_sql,joint_delete_sql,joint_keep_data_sql,join_create_table
File "/root/mysqlbinlog_flashback-master/joint_sql.py", line 202, in <module>
_escape_table = [unichr(x) for x in range(128)]
File "/root/mysqlbinlog_flashback-master/joint_sql.py", line 202, in <listcomp>
_escape_table = [unichr(x) for x in range(128)]
NameError: name 'unichr' is not defined

vi /root/mysqlbinlog_flashback-master/joint_sql.py
#_escape_table = [unichr(x) for x in range(128)]
_escape_table = [chr(x) for x in range(128)]

vi /root/mysqlbinlog_flashback-master/joint_sql.py
File "/root/mysqlbinlog_flashback-master/joint_sql.py", line 106, in generate_dict_pk
if isinstance(pk,unicode):
NameError: name 'unicode' is not defined

所有：unicode 替换为 str;
--python2使用unicode
--python3使用str

vi /root/mysqlbinlog_flashback-master/flashback.py

#if not stat["flash_sql"].has_key(schema):
if schema not in stat["flash_sql"]:
stat["flash_sql"][schema]={}
#if not stat["flash_sql"][schema].has_key(table):
if table not in stat["flash_sql"][schema]:
出现has_key 的地方都需要进行改造。

在py2中，判断key是否属于dict的写法可以是：

d={'name':'abc','location':'BeiJing'}

if d.has_key('location'):

print(d['location'])

在py3中，判断key是否属于字典的写法可以是：

d={'name':'abc','location':'BeiJing'}

if 'location' in d:

print(d['location'])