MySQL闪回工具调研

发表于 | 分类于 |

刚入职2个月左右的时候，就遇到了业务误操作，将对测试环境的delete操作，到线上执行了。。。(至于业务为什么有delete权限的账号，俺也不知道)
由于公司所有实例都部署在阿里云上，所以只能依赖于阿里云的备份恢复系统来进行数据恢复。但是非常慢，当时是大概花了20分钟才完全恢复（-_-||）。
于是乎~工作量又被增加了。领导让我调研数据闪回工具。。。

在工具调研测试过程中，发现了这几个工具存在部分BUG问题，所以给记录一下。避免再次踩坑。

MyFlash

MyFlash是由美团点评公司技术工程部开发维护的一个回滚DML操作的工具。该工具通过解析v4版本的binlog，完成回滚操作。相对已有的回滚工具，其增加了更多的过滤选项，让回滚更加容易。

通过解析binlog来生成回滚binlog文件。

优缺点

优点：

  • 多种过滤条件，能够按照需求实现精准过滤
  • 支持离线解析。不会对运行实例造成影响

缺点：

  • binlog格式必须为row，并且binlog_row_image=full。
  • 只支持5.6和5.7
  • 只支持DML，不支持DDL
  • MyFlash不能解析阿里云RDS的binlog

安装

1
2
3
4
5
6
# 下载源代码
git clone https://github.com/Meituan-Dianping/MyFlash.git
 
# 编译
cd MyFlash
sh build.sh

使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@node001 binary]# ./flashback --help
Usage:
flashback [OPTION...]
 
Help Options:
-h, --help Show help options
 
Application Options:
--databaseNames databaseName to apply. if multiple, seperate by comma(,) 库名，多个库之间用“，”分割
--tableNames tableName to apply. if multiple, seperate by comma(,) 表名，多个表之间用“，”分割
--start-position start position 开始的位点。如不指定，从文件的开始处回滚。
--stop-position stop position 结束的位点。如不指定，回滚到文件结尾。
--start-datetime start time (format %Y-%m-%d %H:%M:%S) 开始的时间点。如不指定，则不限定时间
--stop-datetime stop time (format %Y-%m-%d %H:%M:%S) 结束的时间点。如不指定，则不限定时间
--sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,) 需要回滚的SQL类型，只支持INSERT, UPDATE,DELETE。多个类型之间以“，”分割
--maxSplitSize max file size after split, the uint is M 生成文件分割大小。以M为单位
--binlogFileNames binlog files to process. if multiple, seperate by comma(,) binlog文件名，支持多个文件
--outBinlogFileNameBase output binlog file name base 输出文件名前缀。文件名后缀为：.flashback
--logLevel log level, available option is debug,warning,error
--include-gtids gtids to process 生成的语句包含gtid
--exclude-gtids gtids to skip 跳过gtid

binlog2sql

通过模拟从库解析数据来生成回滚SQL。

优缺点

优点：

  • 直接生成回滚语句，可读性较好。可根据需求进行选择性回滚
  • python实现，安装、使用、可扩展性较好
  • 可指定位点、时间、语句类型
  • 支持阿里云、自建实例

缺点：

  • 需要连接数据库读取binlog，会对线上环境造成一定负载
  • 依赖binlog。如果binlog被清理则无法生成回滚语句
  • 不支持离线解析
  • 解析速度较慢

安装使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt

用户授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

使用
mysql连接配置
-h host; -P port; -u user; -p password
 
解析模式
--stop-never 持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。默认False
-B, --flashback 生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。
--back-interval -B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。
 
解析范围控制
--start-file 起始解析文件，只需文件名，无需全路径 。必须。
--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
 
对象过滤
-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。
-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。
--only-dml 只解析dml，忽略ddl。可选。默认False。
--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

发现的问题

datetime类型恢复错误

问题描述：

  • 存在字段：o_update_time datetime NOT NULL COMMENT
  • 存在值：o_update_time: 0000-00-00 00:00:00
  • 问题一：删除后，binlog2sql生成的insert语句，会将该字段值给设置为NULL。导致插入报错失败。
  • 问题二：阿里云恢复后，数据不一致。

删除前数据：

binlog2sql生成语句：

varbinary类型字段恢复错误

现数据库中存在经过加密函数加密后的字段。并存在几条数据。然后删除其中一条语句来测试工具生成回滚语句。
pwd varbinary(255) NOT NULL COMMENT ‘密码’,

使用binlog2sql生成回滚语句时出错

精度问题

在数据类型为decimal、double、float等精度类型时，恢复时会由于精度问题导致恢复数据不一致。

1
2
3
4
5
6
7
8
9
10
11
mysql> show create table huzb_deci\G
*************************** 1. row ***************************
       Table: huzb_deci
Create Table: CREATE TABLE `huzb_deci` (
  `id` int(11) NOT NULL DEFAULT '0',
  `dec` decimal(5,2) DEFAULT NULL,
  `flo` float DEFAULT NULL,
  `dou` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

插入几条数据后，使用binlog2sql进行恢复。可以发现存在以下问题：

  1. double类型在插入时，由于精度问题，插入数据与原数据已经不一致。
  2. float类型数据在使用binlog2sql生成恢复语句时，转换成了double类型，小数点后多了几位精度。
  3. 生成的回滚语句执行后，恢复的double类型数据不一致。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> insert into huzb_deci values(1,1.1111111111111111111111111111111,2.22222222,3.333333333333333333333);
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1265 | Data truncated for column 'dec' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.01 sec)
 
mysql> select * from huzb_deci;
+----+------+---------+--------------------+
| id | dec  | flo     | dou                |
+----+------+---------+--------------------+
|  1 | 1.11 | 2.22222 | 3.3333333333333335 |
+----+------+---------+--------------------+
1 row in set (0.01 sec)
 
 
# 利用binlog2sql生成恢复语句并执行。
mysql> INSERT INTO `huzb`.`huzb_deci`(`id`, `dec`, `flo`, `dou`) VALUES (1, 1.11, 2.22222232818604, 3.33333333333333); #start 260805 end 260989 time 2019-10-30 19:58:48
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from huzb_deci;
+----+------+---------+------------------+
| id | dec  | flo     | dou              |
+----+------+---------+------------------+
|  1 | 1.11 | 2.22222 | 3.33333333333333 |
+----+------+---------+------------------+
1 row in set (0.01 sec)

问题修复

日期错误问题修复

  1. 按照https://github.com/noplay/python-mysql-replication/pull/228修改对应的python-mysql-replication包文件(需要修改：binlogstream.py和row_event.py两个文件)
  2. binlog2sql中修改。增加date_tostr参数
  3. 不能直接使用python-mysql-replication的最新依赖包。只能通过修改文件，可能会引起未知问题
  4. 如果无NULL值字段，可尝试使用文本替换方式来修改插入语句。
    1
    2
    3
    4
    binlog2sql.py 修改：
    stream = BinLogStreamReader(connection_settings=self.conn_setting, server_id=self.server_id,
                                log_file=self.start_file, log_pos=self.start_pos, only_schemas=self.only_schemas,
                                only_tables=self.only_tables, resume_stream=True, blocking=True, date_tostr=True)

二进制流数据导入导出错误问题修复

根据mysqldump导出二进制数据时的方式，所以有以下修复思路：

  1. 在生成sql文本时，将二进制流转换成十六进制。
  2. 在导入数据时，利用原生的unhex将十六进制转换成二进制。
    修改binlog2sql_util.py以下位置:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    # TODO 改动一：二进制流解析为十六进制
    def fix_object(value):
        """Fixes python objects so that they can be properly inserted into SQL queries"""
        if isinstance(value, set):
            value = ','.join(value)
        if PY3PLUS and isinstance(value, bytes):
            # return value.decode('utf-8', errors='ignore')
            # 将二进制数据转换成十六进制数据，并使用特殊符号包含起来。用于后面转换
            return "----" + bytes.hex(value) + "____"
        elif not PY3PLUS and isinstance(value, unicode):
            return value.encode('utf-8')
        else:
            return value
     
     
    # TODO 改动二：修改生成sql中的unhex串
    unhex_inex = str.find(sql, "'----")
    if unhex_inex != -1:
        sql = str.replace(sql, "'----", "unhex('")
        sql = str.replace(sql, "____'", "')")
    sql += ' #start %s end %s time %s' % (e_start_pos, binlog_event.packet.log_pos, time)

题外话：
小公司还是各种体系、规范不够完善，要是在上一家公司，早就被业务吊起来diss不知道多少遍了。
数据备份恢复是作为DBA最重要的技能点之一，但是由于云化的出现，导致很多人都只会点点点，太过于依赖平台的操作，而忽略了DBA的本质工作。这也是我接下来的工作重点：MySQL、Redis自动化备份恢复系统的建设工作（已完成）。