mysql的数据回滚binlog2sql

2201_76119904

已于 2023-09-14 14:32:43 修改

阅读量502

点赞数

文章标签： mysql adb 数据库 linux 运维服务器

于 2023-09-14 14:26:00 首次发布

本文链接：https://blog.csdn.net/2201_76119904/article/details/132873959

版权

1.什么是binlog2sql？

它是一个用于解析二进制日志的开源工具。
它具有从二进制日志中提取原始 SQL 语句的功能。
它具有从二进制日志生成回滚 SQL 以进行时间点恢复的功能。

2.安装binlog2sql

cd 
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

3.更改mysql配置文件

[root@test4 binlog2sql]# cat /etc/my.cnf

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin
server-id=3
#gtid_mode=ON
#enforce_gtid_consistency=1
binlog_format = row
binlog_row_image = full
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


增加
log-bin
server-id=3
binlog_format = row
binlog_row_image = full

原因

MySQL 服务器必须处于活动状态，原因有两个：

Binlog2SQL基于BINLOG_DUMP协议来获取二进制日志内容。
binlog2sql 是读取INFORMATION_SCHEMA所必需的。从服务器获取表的 COLUMNS 表，以获取该表的元数据。（具有大量表的服务器可能会产生很大的开销来查询INFORMATION_SCHEMA。列表。)

4.创建表插入数据

mysql> create table xp
    -> (id int,name varchar(50),sex enum('n','f') default 'n');
Query OK, 0 rows affected (0.02 sec)

mysql> desc xp;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int           | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('n','f') | YES  |     | n       |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into xp values(1,'dog','n'),(2,'cat','f'),(3,'fox','n');
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from xp;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | dog  | n    |
|    2 | cat  | f    |
|    3 | fox  | n    |
+------+------+------+
3 rows in set (0.00 sec)

5.进入binlog2sql目录

cd /root/binlog2sql/binlog2sql

6.运行脚本获取数据库里的内容

[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAdmin@123 -P3306 -d xp -t xp --start-file=test4-bin.000005  --stop-file=test4-bin.000006
Traceback (most recent call last):
  File "./binlog2sql.py", line 149, in <module>
    back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
  File "./binlog2sql.py", line 46, in __init__
    self.connection = pymysql.connect(**self.conn_setting)
  File "/usr/lib/python2.7/site-packages/pymysql/__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 706, in __init__
    self.connect()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 931, in connect
    self._get_server_information()
  File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1269, in _get_server_information
    self.server_charset = charset_by_id(lang).name
  File "/usr/lib/python2.7/site-packages/pymysql/charset.py", line 38, in by_id
    return self._by_id[id]
KeyError: 255

如果出现以上报错，更新PyMySQL 即可解决，mysql8的版本问题，安装对应的binlog2sql工具版本

pip uninstall PyMySQL
pip install PyMySQL==0.9.3

7.再次运行

[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAdmin@123 -P3306 -d xp -t xp --start-file=test4-bin.000006  
USE xp;
create table xp
(id int,name varchar(50),sex enum('n','f') default 'n');
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 1, 'dog'); #start 428 end 702 time 2023-09-14 11:39:23
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('f', 2, 'cat'); #start 428 end 702 time 2023-09-14 11:39:23
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 3, 'fox'); #start 428 end 702 time 2023-09-14 11:39:23


可以把他保存到指定了文件中
[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAb@123456 -P3306 -d xp -t xp --start-file=test4-bin.000006 > /root/xp1.md

8.参数解释：

-d 指定库名
-t 指定表
--start-file   起始解析文件，只需文件名，无需全路径

这个路径在mysql里可以查看
mysql> show master status \G
*************************** 1. row ***************************
             File: test4-bin.000006
         Position: 733
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 0cd27570-4564-11ee-86d0-2a26553b6ba7:1-60
1 row in set (0.00 sec)

9.binlog2sql 如何支持 PITR？

Binlog2sql 工具有选项“–flashback”，这将有助于生成 ROLLBACK 语句。
我们可以从 DELETE 和 UPDATE 语句中恢复数据。
它将不支持 DDL（ DROP， TRUNCATE ），因为实际的行事件在二进制日志中不可用。

10.测试

1.我们删除一条记录
mysql> select * from xp;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | dog  | n    |
|    2 | cat  | f    |
|    3 | fox  | n    |
+------+------+------+
3 rows in set (0.00 sec)

mysql> delete from xp where id=3;
Query OK, 1 row affected (0.00 sec)

11.我们再用命令查看一下

[root@test4 binlog2sql]# ./binlog2sql.py -uroot -pAdmin@123 -P3306 -d xp -t xp --start-file=test4-bin.000006
USE xp;
create table xp
(id int,name varchar(50),sex enum('n','f') default 'n');
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 1, 'dog'); #start 428 end 702 time 2023-09-14 11:39:23
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('f', 2, 'cat'); #start 428 end 702 time 2023-09-14 11:39:23
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 3, 'fox'); #start 428 end 702 time 2023-09-14 11:39:23
DELETE FROM `xp`.`xp` WHERE `sex`='n' AND `id`=3 AND `name`='fox' LIMIT 1; #start 733 end 987 time 2023-09-14 13:36:05

此时里面就有明确的delete语句

12.上面的 DELETE 是执行删除记录的确切语句。现在，我将使用选项“–flashback”生成ROLLBACK语句

[root@test4 binlog2sql]# ./binlog2sql.py  -uroot -pAdmin@123 -P3306 -d xp -t xp --start-file=test4-bin.000006  --flashback
INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 3, 'fox'); #start 733 end 987 time 2023-09-14 13:36:05
DELETE FROM `xp`.`xp` WHERE `sex`='n' AND `id`=3 AND `name`='fox' LIMIT 1; #start 428 end 702 time 2023-09-14 11:39:23
DELETE FROM `xp`.`xp` WHERE `sex`='f' AND `id`=2 AND `name`='cat' LIMIT 1; #start 428 end 702 time 2023-09-14 11:39:23
DELETE FROM `xp`.`xp` WHERE `sex`='n' AND `id`=1 AND `name`='dog' LIMIT 1; #start 428 end 702 time 2023-09-14 11:39:23


此时刚刚删除的delete语句就变成insert语句了，只需要复制到数据库里粘贴就好
mysql> INSERT INTO `xp`.`xp`(`sex`, `id`, `name`) VALUES ('n', 3, 'fox');
Query OK, 1 row affected (0.00 sec)

mysql> select * from xp.xp;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | dog  | n    |
|    2 | cat  | f    |
|    3 | fox  | n    |
+------+------+------+
3 rows in set (0.00 sec)