mysqlbinlog 闪回功能

mysqlbinlog 新增特性

  • 支持过滤指定表名的 row events
  • 支持根据 @2 aaa 的格式过滤binlog里面的记录,结果可直接应用
  • 指定 --flashback 选项,上述过滤相当于回档指定记录
  • 可以指定如何处理 query events(DDL),报错、忽略、保留等
  • 支持转换 update_event 为 write_event

下载地址: https://github.com/Tencent/TenDBCluster-TenDB/releases ,与 TenDB 发布包一起。该特性需 TenDB 版本 >=3.3.1

使用注意

  1. 如果要执行 flashback binlog 导入,可能需要配合幂等模式 slave_exec_mode=IDEMPOTENT 使用
  2. 同样,可能需要调大 max_allowed_packet
  3. 对于 int 类型,mysqlbinlog 的输出始终会输出一个 signed 和一个unsigned,因为mysqlbinlog不能准确知道字段的类型。所以在处理负数时,记得带:signed
  4. 建议是 binlog_row_image=FULL。如果是MINIMAL,给定的字段条件一定要在主键中
  5. flashback 闪回不支持ddl(包括库表级和记录级flashback)
  6. 支持 mysql 5.5, 5.6, 5.7,以及 tendb binlog 压缩事件

选项说明

相比原生 mysqlbinlog 命令增加了以下选项:

  -L, --databases=name 
                      List entries for these databases (local log only).Give
                      the database names in a comma separated list.
  --tables=name       List entries for these tables (local log only).Give the
                      tables names in a comma separated list.
  -B, --flashback     Flashback feature can rollback you committed data to a
                      special time point.
  --flashback-databases=name 
                      List entries for these flashback databases (local log
                      only).Give the database names in a comma separated list.
  --flashback-tables=name 
                      List entries for these flashback tables (local log
                      only).Give the tables names in a comma separated list.
  --flashback-databases-ignore=name 
                      List entries for ignoring these databases to flashback
                      whole tables (local log only).Give the database names in
                      a comma separated list.
  --flashback-tables-ignore=name 
                      List entries for ignoring these tables to flashback. not
                      work with --filter-rows (local log only).Give the tables
                      names in a comma separated list.
  --conv-event-update-to-write 
                      Whether convert the update_event's after image to
                      write_event.It's useful to work with imdepotent mode
  --filter-rows=name  Filter string or file to filter rows from event. (local
                      log only).Format: '@1,@2 100,aaa'. Must work with
                      --tables or --flashback-tables. You can use @2:hex format 
                      to tell mysqlbinlog its' varchar/varbinary/blob value is a hex. 
                      Also you can use @1:signed to mark it as a signed int/
                      tinyint/smallint/mediumint/bigint
  --query-event-handler=name 
                      Decide how to handle the query events like statement or
                      ddl.  Only error|keep|ignore|safe allowed. (error: exit
                      when encountered any query event. keep or ignore all
                      query event. safe: work with --filter-statement-match-error
                      and --filter-statement-match-ignore)
  --filter-statement-match-error=name 
                      Exit when this string is matched in query event. Comma
                      separated. Only work when
                      query-event-handler=keep|ignore|safe
  --filter-statement-match-ignore=name 
                      Ignore the query event when this string is matched. Comma
                      separated. Only work when query-event-handler=error|safe
  --filter-lines-terminated-by=name 
                      Lines in the filter file are terminated by the given
                      string.
  --filter-fields-terminated-by=name 
                      Fields in the filter file are terminated by the given
                      string.
  --filter-fields-enclosed-by=name 
                      Fields in the filter file are enclosed by the given
                      character.

使用说明

  • --tables 选项用于binlog解析时,只输出指定表的变更记录。多个表以英文 , 分隔,指定多个表时字段位置代表的字段,必须相同 只针对 delete/insert/update 这样的 row event 有效。

  • --filter-rows 选项可以用于根据字段值,过滤binlog里面的记录。要求必须指定 --tables(正向) 或者 --flashback-tables(逆向),过滤后的结果可直接应用到 mysql-server 上。 有两种方式指定:

    • --filter-rows="@2,@3 aaa,10" 直接指定值,其中@2代表字段位置,多个字段条件使用 , 分隔。默认通过空格 ` 来区分多行 字段位置根据show create table xxx顺序往下从 1 开始,或者information_schema.COLUMNS表的ORDINAL_POSITION` 里有显示。 对于 update_rows event,只匹配 before_image 的字段 对于 update_rows event,只匹配 before_image 的字段。
    • --filter-rows=keyfilter_back.txt 指定csv格式的文件,在需要过滤数量较多的条件时可使用,格式:
      @2,@3
      aaa,100
      bbb,200
      
    • 针对 varchar、varbinary, char, binary, blob 等类型,可以指定十六进制格式,例如--filter-rows="@2:hex 0x64646464",对应的字符串为select unhex('64646464')dddd
    • 针对 tinyint、smallint、mediumint、int、bigint 类型,默认当做无符号 unsigned 处理。当给定的值包括负数时,请指定 @3:signed,否则过滤的结果可能有遗漏。(见下文示例)
    • 如果过滤条件行较多时,建议将 过滤度 比较高的列字段放在第1列,会提高过滤性能。(不要求是主键)
    • 请勿将可能修改的字段作为条件,可能导致过滤的结果有遗漏
  • --filter-lines-terminated-by 只在--filter-rows指定时有效,指定行分隔符。指定文件时,默认\n,直接命令行输入时,默认空格

  • --filter-fields-terminated-by 只在--filter-rows指定时有效,指定字段分隔符。默认,

  • --filter-fields-enclosed-by 只在--filter-rows指定时有效,指定字段包裹分隔符。默认'

  • --filter-statement-match-error query_event 精确匹配了子字符串,退出。多个字符串使用逗号 , 分隔

  • --filter-statement-match-ignore query_event 精确匹配了子字符串,忽略,记录到注释。退出。多个字符串使用逗号 , 分隔 --filter-statement-match-error 优先匹配生效。

  • --query-event-handler 如何处理 query_event 。像一些 ddl, statement 语句(pt-table-checksum产生的)会记录为 query event,不支持这类语句的过滤和flashback。 允许四个值:error|keep|ignore|safe

    1. error: 遇到 query_event 立马报错退出。但如果同时指定了 --filter-statement-match-ignore,会忽略并记录到注释
    2. keep: 保留 query_event。默认为keep,但如果同时指定了 --filter-statement-match-error,匹配指定字符串后还是会报错退出
    3. ignore: 忽略 query_event,会记录在注释里。如果同时指定了 --filter-statement-match-error,匹配指定字符串后还是会报错退出
    4. safe: 安全模式,配合 --filter-statement-match-error--filter-statement-match-ignore 一起使用 如果匹配了filter-statement-match-error则退出,否则如果匹配了filter-statement-match-ignore则忽略并记录到注释,都没匹配,退出
  • conv-event-update-to-write

    是否将 UPDATE事件的 after image 转换成 WRITE事件。在 mysqld IDEMPOTENT 模式下,应用 binlog 时如果记录在目标实例不存在,update_event 不会将数据写入目标实例。使用此选项会将事件改写成 write_event,在一个异地恢复数据时可能有用。

    在 flashback 闪回模式下,会将 before image 转换成 WRITE事件。

示例

过滤示例(正向):

#  按表来过滤binlog,可以不指定 --databases
$ mysqlbinlog binlog20000.000025 -vv \
  --tables filter_row_test  > filter_row_test.sql

$ mysqlbinlog binlog20000.000025 -vv \
  --tables filter_row_test \
  --filter-rows="@1,@2 200,aaa"  > filter_row_test.aaa.sql

内容会被裁剪只保留了符合条件 id=200 and username='aaa' 的row。

指定字段条件过滤binlog,多行:

$ mysqlbinlog binlog20000.000025 -vv \
  --tables filter_row_test \
  --filter-rows="@1 100 500" > 100.sql

# 也可以指定csv文件作为过滤条件
$ cat key.txt
@2
'ee e'
aaa
ddd

$ mysqlbinlog binlog20000.000025 -vv 
  --tables filter_row_test  \
  --filter-rows=keyback.txt > 100.sql

# 过滤值为为负数的列
$ mysqlbinlog -v binlog20000.000044 \
  --flashback \
  --flashback-tables filter_row_test \
  --filter-rows="@1 -1"
这个无法过滤出 id=-1 的数据,必须指定 "@1:signed -1",或者 "@1 4294967295"

结合 --query-event-handler 过滤危险 query event:

-- alter table roll_test add column c2 int;
-- alter table filter_row_test add column c2 int;
-- alter table roll_test drop column c2;
-- alter table filter_row_test drop column c2;

$ mysqlbinlog binlog20000.000041 -v binlog20000.000041 --query_event_handler=ignore > binlog20000.000041.sql
会记录到注释:
# ignore query_log_event
# alter table filter_row_test add column c2 int

下面是一个典型组合

$ mysqlbinlog binlog20000.000041 -v binlog20000.000041 \
--tables=filter_row_test \
--filter-rows=xxx.txt \
--query-event-handler=safe \
--filter-statement-match-ignore="FLUSH ,db_infobase,create table ,CREATE TABLE " \
--filter-statement-match-error="filter_row_test" \
--start-datetime='2020-12-18 02:08:01'\
--stop-datetime='2020-12-22 16:13:42' \
> binlog20000.000041.sql
Exit when query event occurs: alter table filter_row_test add column c2 int

效果:想要回滚 filter_row_test 表的数据,但回滚期间该表不能有ddl,其它表上有 ddl 或者 query event 不影响回滚。 这里演示的会解析失败退出,因为 filter_row_test 上有 ddl ,符合预期。

结合--flashback(反向)回滚示例:

上面的普通正向过滤语句,都可以加 --flashback --flashback-tables xxx 转换成回滚语句。 回滚 id in (2000,2001) 两条insert:

$ mysqlbinlog binlog20000.000025 -vv \
  --flashback \
  --flashback-tables filter_row_test \
  --filter-rows='@1 2000 2001' > 2000.back.sql
## 导入 mysqld
$ cat 2000.back.sql | mysql

mysql> select id,username,nickname,age,gender,hight,weight,birthday,create_ts from filter_row_test;
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+
| id   | username | nickname | age | gender | hight  | weight  | birthday            | create_ts           |
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+
...
|  500 | ddd      | 㾄        |  76 | F      |  101.1 | 200.100 | 2020-12-02 00:00:05 | 2020-12-03 15:43:08 |
|  600 | ddd      | 㾄        |  76 | F      |  101.1 | 200.100 | 2020-12-02 00:00:06 | 2020-12-03 15:43:08 |
| 1002 | ee ,e    | ee ,ee   |  20 | F      | 300.01 | 200.100 | 2020-12-02 00:00:07 | 2020-12-02 00:00:07 |
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+
6 rows in set (0.00 sec)

回滚 username in ('ee e','ee "e') 两条delete,考虑到改字段有特殊字符,转成十六进制:

$ mysqlbinlog binlog20000.000025 -vv \
 --flashback \
 --flashback-tables filter_row_test \
 --filter-rows='@2:hex 0x65652065 0x6565202265' > eee.back.sql

# 注意:字段里有空格,命令行默认以空格分行,如果不转成十六进制,需要指定分行符(与上等价)
$ mysqlbinlog binlog20000.000025 -vv \
  --flashback \
  --flashback-tables filter_row_test  \
  --filter-rows='@2\nee e\nee "e' \
  --filter-lines-terminated-by='\n'

$ cat eee.back.sql | mysql

mysql> select id,username,nickname,age,gender,hight,weight,birthday,create_ts from filter_row_test;
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+
| id   | username | nickname | age | gender | hight  | weight  | birthday            | create_ts           |
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+
...
| 1000 | ee e     | ee ee    |  20 | F      | 300.01 | 200.100 | 2020-12-02 00:00:07 | 2020-12-02 00:00:07 |
| 1001 | ee "e    | ee "ee   |  20 | F      | 300.01 | 200.100 | 2020-12-02 00:00:07 | 2020-12-02 00:00:07 |
| 1002 | ee ,e    | ee ,ee   |  20 | F      | 300.01 | 200.100 | 2020-12-02 00:00:07 | 2020-12-02 00:00:07 |
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+

把 username=ddd 的 update 也回滚下,这次使用文件输入:

$ cat ddd_1.txt
@1,@2
500,ddd

$ mysqlbinlog binlog20000.000025 -vv \
  --flashback \
  --flashback-tables filter_row_test  \
  --filter-rows=ddd_1.txt  > ddd1.back.sql

## 导入 mysqld
cat ddd1.back.sql | mysql

mysql> select id,username,nickname,age,gender,hight,weight,birthday,create_ts from filter_row_test;
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+
| id   | username | nickname | age | gender | hight  | weight  | birthday            | create_ts           |
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+
...
|  500 | ddd      | 㾄        |  50 | F      |  200.1 | 200.100 | 2020-12-02 00:00:05 | 2020-12-02 00:00:05 |
|  600 | ddd      | 㾄        |  76 | F      |  101.1 | 200.100 | 2020-12-02 00:00:06 | 2020-12-03 15:43:08 |
...
+------+----------+----------+-----+--------+--------+---------+---------------------+---------------------+
# 这条记录有被update两次,结果符合预期

第3个字段是varbinary,使用十六进制:

$ cat ddd_2.txt
@3:hex
0xE48004
0x61616161

$ mysqlbinlog binlog20000.000025 -vv --flashback --flashback-tables filter_row_test  \
  --filter-rows=ddd_2.txt  > ddd2.back.sql | mysql

通过以上各种条件回滚,表的数据与之前一样了。

results matching ""

    No results matching ""