binlog2sql工具应用：MySQL日志提取原始SQL和时间点恢复

原创小小亮 2020-07-10

3616

本文将介绍曹丹凤设计的工具binlog2sql。该工具将帮助解码MySQL二进制日志并提取原始SQL，这也有助于使用“闪回”功能生成ROLLBACK语句以进行时间点恢复（PITR）。该功能在MariaDB的的版本“mysqlbinlog“可以实施。

使用本地mysqlbinlog工具，我们可以将二进制日志从二进制格式转换为文本格式。我们还可以使用mysqlbinlog实用工具生成确切的SQL语句。它取决于“ binlog_format＆binlog_rows_query_log_events ”参数。无论如何，该工具确实没有生成ROLLBACK语句的功能。

对于“ binlog_format = STATEMENT”格式，我们确实使用mysqlbinlog获取SQL。
使用参数“ binlog_rows_query_log_events = ON ”，我们确实使用mysqlbinlog获得了SQL事件和binlog事件。

Mysqlbinlog与Binlog2sql

在进入binlog2sql工具之前，我想比较Mysqlbinlog和bilog2sql的输出。在本节中，我将通过各种选项显示每个工具的输出格式。作为参考，我使用了以下查询来生成binlog事件。

mysql> insert into binlog2sqlVSmysqlbinlog (name) values ('hercules7sakthi');
Query OK, 1 row affected (0.00 sec)

Mysqlbinlog：

Mysqlbinlog（（binlog_format = ROW，binlog_rows_query_log_events = OFF），

[root@hercules7sakthi2 ~]# mysqlbinlog --base64-output=decode-rows -vv --start-position 313 --stop-position 449 /var/lib/mysql/percona-bin.000003  
... 
### INSERT INTO `percona`.`binlog2sqlVSmysqlbinlog` 
### SET 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='hercules7sakthi' /* VARSTRING(64) meta=64 nullable=1 is_null=0 */ 
...

输出：文本格式，不是真实的SQL。
回滚：该工具不具有生成回滚语句的功能。

带有（binlog_format = STATEMENT）的Mysqlbinlog，

[root@hercules7sakthi2 ~]# mysqlbinlog --base64-output=decode-rows -vv --start-position 323 --stop-position 507 /var/lib/mysql/percona-bin.000004 
...
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
insert into binlog2sqlVSmysqlbinlog (name) values ('hercules7sakthi')
/*!*/;
...

输出：SQL格式。
回滚：该工具不具有生成回滚语句的功能。

Mysqlbinlog（（binlog_format = ROW和binlog_rows_query_log_events = ON ），

[root@hercules7sakthi2 ~]# mysqlbinlog --base64-output=decode-rows -vv /var/lib/mysql/percona-bin.000006 --start-position 313
...
# insert into binlog2sqlVSmysqlbinlog (name) values ('hercules7sakthi')
...
### INSERT INTO `percona`.`binlog2sqlVSmysqlbinlog`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='hercules7sakthi' /* VARSTRING(64) meta=64 nullable=1 is_null=0 */
...

输出：SQL和文本格式。
回滚：该工具不具有生成回滚语句的功能。

Binlog2sql：

[root@hercules7sakthi2 ~]# ./binlog2sql/binlog2sql/binlog2sql.py --user percona -p --start-file percona-bin.000007
Password: 
INSERT INTO `percona`.`binlog2sqlVSmysqlbinlog`(`id`, `name`) VALUES (1, 'hercules7sakthi'); #start 4 end 542 time 2020-06-21 22:36:16

输出：SQL格式
回滚：可能与binlog2sql

什么是binlog2sql？

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

MySQL要求

#vi my.cnf
 
[mysqld]
#binlog
server-id = #id
log-bin
binlog_format = row
binlog_row_image = full

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

binlog2sql基于BINLOG_DUMP协议来获取binlog内容。
必须使用binlog2sql才能从服务器读取INFORMATION_SCHEMA.COLUMNS表，以获取该表的元数据。（具有很多表的服务器在查询INFORMATION_SCHEMA.COLUMNS表时可能会产生大量开销。）

安装

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

经过测试的环境

Python 2.7、3.4+
MySQL 5.6、5.7、8+

注意：确保需要“ pymysql – 0.9.3”才能获得MySQL 8+支持。

Binlog2sql选项

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.

如何从二进制日志中提取原始SQL？

为了进行测试，我安装了一个MySQL服务器并创建了以下数据：

mysql> use percona
Database changed
 
mysql> create table binlog2sql(id int primary key auto_increment, name varchar(16), status enum('A','NA'), up_date datetime default current_timestamp);
Query OK, 0 rows affected (0.16 sec)
 
mysql> insert into binlog2sql (name,status) values ('kani','A'),('ram','A'),('durai','A'),('asha','A'),('sakthi','A');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

使用binlog2sql解码二进制日志：

[root@hercules7sakthi2 binlog2sql]# ./binlog2sql.py -upercona -p --start-file percona-bin.000002 --start-position 321 --stop-position 510  | cut -f1 -d"#" 
Password:  
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 1, 'kani');  
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 2, 'ram');
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 3, 'durai');  
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 4, 'asha');  
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 5, 'sakthi');

binlog2sql如何支持PITR？

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

案例1 – DELETE的PITR：

为了测试这种情况，我从测试表“ binlog2sql”中删除了两个记录 “ id in（4,5） ”。

mysql> delete from binlog2sql where id in (4,5);
Query OK, 2 rows affected (0.03 sec)
 
mysql> select * from binlog2sql;
+----+-------+--------+---------------------+
| id | name  | status | up_date             |
+----+-------+--------+---------------------+
|  1 | kani  | A      | 2020-06-20 14:54:51 |
|  2 | ram   | A      | 2020-06-20 14:54:51 |
|  3 | durai | A      | 2020-06-20 14:54:51 |
+----+-------+--------+---------------------+
3 rows in set (0.00 sec)

作为参考，我正在使用binlog2sql生成原始SQL。

[root@hercules7sakthi2 binlog2sql]# ./binlog2sql.py -upercona -pxxxxxxx --start-file percona-bin.000002 --start-position 1172 --stop-position 1344  | cut -f1 -d'#'
DELETE FROM `percona`.`binlog2sql` WHERE `status`='A' AND `up_date`='2020-06-20 14:54:51' AND `id`=4 AND `name`='asha' LIMIT 1; 
DELETE FROM `percona`.`binlog2sql` WHERE `status`='A' AND `up_date`='2020-06-20 14:54:51' AND `id`=5 AND `name`='sakthi' LIMIT 1;

上面的DELETE是精确的语句，执行这些语句以删除记录。现在，我将使用“ –flashback”选项生成ROLLBACK语句，如下所示，

[root@hercules7sakthi2 binlog2sql]# ./binlog2sql.py -upercona -pxxxxxx --start-file percona-bin.000002 --start-position 1172 --stop-position 1344 --flashback  | cut -f1 -d'#'
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 5, 'sakthi'); 
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 4, 'asha');

您可以看到那些DELETE语句现在已转换为INSERT语句。现在，我可以将回滚语句加载到服务器中，

[root@hercules7sakthi2 binlog2sql]# ./binlog2sql.py -upercona -pxxxxxx --start-file percona-bin.000002 --start-position 1172 --stop-position 1344 --flashback  | cut -f1 -d'#' | mysql -vvv 
--------------
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 5, 'sakthi')
--------------
Query OK, 1 row affected (0.03 sec)
 
--------------
INSERT INTO `percona`.`binlog2sql`(`status`, `up_date`, `id`, `name`) VALUES ('A', '2020-06-20 14:54:51', 4, 'asha')
--------------
Query OK, 1 row affected (0.01 sec)
Bye
 
[root@hercules7sakthi2 binlog2sql]# mysql -e "select * from percona.binlog2sql"
+----+--------+--------+---------------------+
| id | name   | status | up_date             |
+----+--------+--------+---------------------+
|  1 | kani   | A      | 2020-06-20 14:54:51 |
|  2 | ram    | A      | 2020-06-20 14:54:51 |
|  3 | durai  | A      | 2020-06-20 14:54:51 |
|  4 | asha   | A      | 2020-06-20 14:54:51 |
|  5 | sakthi | A      | 2020-06-20 14:54:51 |
+----+--------+--------+---------------------+

从DELETE中恢复了数据。

案例2 – UPDATE的PITR：

在这种情况下，我将更新两行。我将“（4,5）中的id”的状态从“ A”更改为“ NA”。

mysql> update binlog2sql set status='NA' where id in (4,5);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
 
mysql> select * from binlog2sql;
+----+--------+--------+---------------------+
| id | name   | status | up_date             |
+----+--------+--------+---------------------+
|  1 | kani   | A      | 2020-06-20 14:54:51 |
|  2 | ram    | A      | 2020-06-20 14:54:51 |
|  3 | durai  | A      | 2020-06-20 14:54:51 |
|  4 | asha   | NA     | 2020-06-20 14:54:51 |
|  5 | sakthi | NA     | 2020-06-20 14:54:51 |
+----+--------+--------+---------------------+
5 rows in set (0.00 sec)

作为参考，我正在使用binlog2sql生成执行的UPDATE。

[root@hercules7sakthi2 binlog2sql]# ./binlog2sql.py -upercona -pxxxxxxx --start-file percona-bin.000002 --start-position 2136 --stop-position 2343  | cut -f1 -d'#'
UPDATE `percona`.`binlog2sql` SET `status`='NA', `up_date`='2020-06-20 14:54:51', `id`=4, `name`='asha' WHERE `status`='A' AND `up_date`='2020-06-20 14:54:51' AND `id`=4 AND `name`='asha' LIMIT 1; 
UPDATE `percona`.`binlog2sql` SET `status`='NA', `up_date`='2020-06-20 14:54:51', `id`=5, `name`='sakthi' WHERE `status`='A' AND `up_date`='2020-06-20 14:54:51' AND `id`=5 AND `name`='sakthi' LIMIT 1;

生成ROLLBACK语句。

[root@hercules7sakthi2 binlog2sql]# ./binlog2sql.py -upercona -pxxxxxx --start-file percona-bin.000002 --start-position 2136 --stop-position 2343 --flashback  | cut -f1 -d'#'
UPDATE `percona`.`binlog2sql` SET `status`='A', `up_date`='2020-06-20 14:54:51', `id`=5, `name`='sakthi' WHERE `status`='NA' AND `up_date`='2020-06-20 14:54:51' AND `id`=5 AND `name`='sakthi' LIMIT 1; 
UPDATE `percona`.`binlog2sql` SET `status`='A', `up_date`='2020-06-20 14:54:51', `id`=4, `name`='asha' WHERE `status`='NA' AND `up_date`='2020-06-20 14:54:51' AND `id`=4 AND `name`='asha' LIMIT 1;

将回滚语句加载到服务器中。

[root@hercules7sakthi2 binlog2sql]# ./binlog2sql.py -upercona -pxxxxxxx --start-file percona-bin.000002 --start-position 2136 --stop-position 2343 --flashback  | cut -f1 -d'#' | mysql -vvv
--------------
UPDATE `percona`.`binlog2sql` SET `status`='A', `up_date`='2020-06-20 14:54:51', `id`=5, `name`='sakthi' WHERE `status`='NA' AND `up_date`='2020-06-20 14:54:51' AND `id`=5 AND `name`='sakthi' LIMIT 1
--------------
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
--------------
UPDATE `percona`.`binlog2sql` SET `status`='A', `up_date`='2020-06-20 14:54:51', `id`=4, `name`='asha' WHERE `status`='NA' AND `up_date`='2020-06-20 14:54:51' AND `id`=4 AND `name`='asha' LIMIT 1
--------------
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Bye
 
[root@hercules7sakthi2 binlog2sql]# mysql -e "select * from percona.binlog2sql"
+----+--------+--------+---------------------+
| id | name   | status | up_date             |
+----+--------+--------+---------------------+
|  1 | kani   | A      | 2020-06-20 14:54:51 |
|  2 | ram    | A      | 2020-06-20 14:54:51 |
|  3 | durai  | A      | 2020-06-20 14:54:51 |
|  4 | asha   | A      | 2020-06-20 14:54:51 |
|  5 | sakthi | A      | 2020-06-20 14:54:51 |
+----+--------+--------+---------------------+

所做的更改已回滚。

结论

如前所述，该工具仅支持DELETE / UPDATE的回滚。
该工具将不支持DDL，因为DDL语句不会在二进制日志中记录任何实际数据。
该工具不适用于加密/压缩的二进制日志。
该工具已经在MySQL 5.6和MySQL 5.7环境中进行了测试。它支持带有“ pymysql – 0.9.3”的MySQL 8。

作者：Sri Sakthivel
文章来源：https://www.percona.com/blog/2020/07/09/binlog2sql-binlog-to-raw-sql-conversion-and-point-in-time-recovery/

mysql 闪回 sql sql提取

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者