聊聊 Mysql 的 Flashback

大家好，大表哥这次带来的是mysql的flashback.

相信用过ORACLE的小伙伴都知道， flashback 闪回这个功能很强大。 具体的可以分为 database 级别的闪回和表 级别的闪回。

database 级别的闪回依赖的是 oracle FRA 中保存的 flashback 的log 日志
table 级别的闪回依赖是 undo 表空间的中保存的数据。实际上是基于MVCC的 snapshot 的查询召回历史的数据。

Mysql 做为开源产品，原生是不支持闪回这个功能的。 但是开源社区是强大的，大众点评开源了一款基于mysql binlog为依赖的类似于flashback 的工具: binlog2sql. 这个在业界是十分知名的。

下面我们来看一下这个工具：
官网：https://github.com/danfengcao/binlog2sql （需要科学上网）

我们根据readme这个文件来下载和安装一下

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

复制

下载完成后：

INFRA [mysql@wqdcsrv3352 binlog2sql]# ls
binlog2sql-master.zip
INFRA [mysql@wqdcsrv3352 binlog2sql]# unzip binlog2sql-master.zip
Archive:  binlog2sql-master.zip
5a8e65c432e74950b48b7ead28f424ec931b755d
   creating: binlog2sql-master/
  inflating: binlog2sql-master/.gitignore  
  inflating: binlog2sql-master/LICENSE  
  inflating: binlog2sql-master/README.md  
   creating: binlog2sql-master/binlog2sql/
  inflating: binlog2sql-master/binlog2sql/__init__.py  
  inflating: binlog2sql-master/binlog2sql/binlog2sql.py  
  inflating: binlog2sql-master/binlog2sql/binlog2sql_util.py  
   creating: binlog2sql-master/example/
  inflating: binlog2sql-master/example/mysql-flashback-priciple-and-practice.md  
  inflating: binlog2sql-master/requirements.txt  
   creating: binlog2sql-master/tests/
  inflating: binlog2sql-master/tests/test_binlog2sql_util.py

复制

我们根据 requirements.txt 安装一下依赖的类库：

INFRA [mysql@wqdcsrv3352 binlog2sql-master]# pip install -r requirements.txt
Requirement already satisfied: PyMySQL==0.7.11 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 1))
Requirement already satisfied: wheel==0.29.0 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 2))
Requirement already satisfied: mysql-replication==0.13 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 3))
You are using pip version 9.0.1, however version 21.3.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.

复制

现在我们尝试一下; 把binlog文件中的语句 抽取出来：

我们先创建一张表：
操作的时间抽顺序是：

1）记录上时间是 2022-01-02 16:44:04
2)在testdb下创建表t2
3)向T2表插入一条记录 jason
4)再次向T2表中 插入记录 jason2

Current database: performance_schema


+---------------------+
| now()               |
+---------------------+
| 2022-01-02 16:44:04 |
+---------------------+
1 row in set (0.18 sec)

mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed

mysql> create table t2 (id int not null primary key, name varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values (1,'jason');

Query OK, 1 row affected (0.02 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values (2,'jason2');
Query OK, 1 row affected (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

复制

那么现在我们想捕获到 binlog日志中的 从时间点 2022-01-02 16:44:04 开始的SQL 语句：

INFRA [root@wqdcsrv3352 binlog2sql]# python ./binlog2sql.py -h 127.0.0.1 -P3210 -ujason -padmin123 -dtestdb --start-file='mysql1231_binlog.000005' --start-datetime='2022-01-02 16:44:04'
USE testdb;
create table t2 (id int not null primary key, name varchar(10));
INSERT INTO `testdb`.`t2`(`id`, `name`) VALUES (1, 'jason'); #start 1762 end 1984 time 2022-01-02 16:45:27
INSERT INTO `testdb`.`t2`(`id`, `name`) VALUES (2, 'jason2'); #start 2093 end 2317 time 2022-01-02 17:06:34

复制

目前是可以得到相应的SQL语句的：指定参数 --flashback 即可
1）建表的DDL语句
2）还有2条插入的语句

我们还可以测试生成一下flashback的语句：

INFRA [root@wqdcsrv3352 binlog2sql]# python ./binlog2sql.py -h 127.0.0.1 -P3210 -ujason -padmin123 -dtestdb --start-file='mysql1231_binlog.000005' --start-datetime='2022-01-02 16:44:04' --flashback
DELETE FROM `testdb`.`t2` WHERE `id`=2 AND `name`='jason2' LIMIT 1; #start 2093 end 2317 time 2022-01-02 17:06:34
DELETE FROM `testdb`.`t2` WHERE `id`=1 AND `name`='jason' LIMIT 1; #start 1762 end 1984 time 2022-01-02 16:45:27

复制

此时，工具已经为我们生成了2条 delete语句，并且是之前的倒序输出。

接下来我们更近一步， 对于熟悉python 编程的小伙伴来说， 下面我们可以看看 binlog2sql 的源代码是如何实现binlog的读取的：

我们用pycharm 把项目源代码打开：

我们来观察一下项目的目录：

目录来时是十分清晰的：
–binlog2sql 包是程序的只要目录：只要的文件是binlog2sql这个文件
–example 包是介绍 readme类似的
–tests 包是 mock 测试的程序

从项目的依赖的类库是比较少的：requirements.txt

PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13

复制

这里需要重点介绍一下这个mysql-replication 类库， 这个类库是核心解析mysql binlog的功能的类库。

这个类库是做mysql binlog的复制同步用的， 功能是解析mysql的binlog 之后，同步给下游数据库的mysql,甚至是异构的NOSQL数据库像是Mongo, ES, 消息队列kafka 什么的

我们可以参考一下 mysql-replication 类库的官网，里面有详细的介绍： https://python-mysql-replication.readthedocs.io/en/latest/

官方介绍这个类库的场景如下：

MySQL to NoSQL database replication – mysql 到 nosql 的数据库之间的复制
MySQL to search engine replication – mysql ETL到查询引擎
Invalidate cache when something change in database – ？？？ 这个暂时不太懂
Audit – 审计功能
Real time analytics --实时分析的功能

关于解析binlog的类库的一些限制:

1)binlog_row_image=full 日志的image必须是full
2)一些类型不支持：GEOMETRY和BOOLEAN，BOOL

核心的类： BinLogStreamReader 来读取Event： 其中Row events 只要包括： WriteRowsEvent，UpdateRowsEvent，DeleteRowsEvent， 也就是我们常用的insert,update,delete 语句。

下面我们跑一个测试程序来实时读取mysql binlog的event 来体验一下：

我们需要创建一下账户和授权：

mysql> create user binlog_reader@'%' identified with mysql_native_password by "admin123_abdD";
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO binlog_reader@'%';
Query OK, 0 rows affected (0.00 sec)

复制

我们来运行一下这段测试的代码：

from pymysqlreplication import BinLogStreamReader

MYSQL_SETTINGS = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "passwd": ""
}


def main():
    # server_id is your slave identifier, it should be unique.
    # set blocking to True if you want to block and wait for the next event at
    # the end of the stream
    stream = BinLogStreamReader(connection_settings=MYSQL_SETTINGS,
                                server_id=3,
                                blocking=True)

    for binlogevent in stream:
        binlogevent.dump()

    stream.close()


if __name__ == "__main__":
    main()

复制

从结果中我们可以看到我们之前创建账户和授权的语句都可以打印到控制台上：

我们在测试一下table DDL和DML的语句： mysql 终端中输入建表语句：

mysql> create table tab1 (id int not null, name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tab1 values (1,'jason');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


复制

观测python 程序的console输出：

=== QueryEvent ===
Date: 2022-04-12T14:15:00
Log position: 189051
Event size: 110
Read bytes: 110
Schema: b'test_db'
Execution time: 0
Query: create table tab1 (id int not null, name varchar(20))
=== WriteRowsEvent ===
Date: 2022-04-12T14:15:48
Log position: 189329
Event size: 23
Read bytes: 12
Table: test_db.tab1
Affected columns: 2
Changed rows: 1
Values:
--
* id : 1
* name : jason

复制

由此可见创建表的DDL属于Query Event, DML的insert 属于WriteRowsEvent， 以此类推 update和delete 属于UpdateRowsEvent，DeleteRowsEvent。

这3个DML Event 是mysql 的flashback的主要监听的event类型。

我们搞明白了mysql-replication 这个类库之后， 再次回到大众点评的 binlog2sql 这个flashback的工具：

核心类 binlog2sql的核心方法process_binlog 调用如下：

感兴趣的小伙伴，可以自行动手下载源代码调试一下，并结合自己工作中实际的需求进行自定义开发。

大表哥认为自己动手来实践才算是最大的乐趣！ 动起手来吧！