binlog2sql与Myflash

偏执的太偏执、 2022-04-10 06:28 271阅读 0赞

# 一、场景 #

一般情况下，数据表被误操作，通常都会使用 备份+binlog 进行恢复。那如果误操作为dml而不是ddl，并且操作的语句不多，这样的话使用该方式会极大的影响业务，这就造成一个痛点：少量数据不恢复不行，恢复又极大的影响线上业务。这时binlog2sql应运而生，但是使用binlog2sql恢复会比较慢，这时Myflash 也就随之出现。具体应该使用哪一个下文会进行优缺点分析。

# 二、闪回原理 #

在此就不说明binlog是什么，怎么工作，内容的含义了（因为这是写给我自己看的-<->-）

每个工具实现闪回的方式都不同，但是有一点是一样的：

insert ----> delete ; delete -----> insert; update---->将set 和 where置换

binlog2sql是作为通过python-mysql-replication将自己作为伪slave读取binlog并将dml转换之后读取系统表TABLES，从而转换成标准的SQL语句。

myflash是直接解析binlog文件并将其转换，所以速度要快得多，使用此工具pos一定要找准。

# 三、工具介绍 #

到现在为止一般会有4种方法对binlog进行处理：

1.mysqlbinlog 工具配合 sed、awk
        此方法比较简单，但是做起来还是比较复杂的，尤其是字段类型比较大的时候
    2.给数据库源码打patch
        mysql5.5的mysqlbinlog应该有flashback选项。这种方法需要对数据库源码非常熟悉并且善于编程的大佬。这种方法升级版本的话就不适用了，所以运维成本非常大。
    3.binlog2sql
        对SQL进行构造，稳定性好，上手难度低，效率也低。
    4.myflash

# 四、binlog2sql 安装 #

前提：

> MySQL server 必须设置以下参数：
> 
> \[mysqld\]
> 
> server\_id=1
> 
> log\_bin=/wx/mydata/mysql-bin.log
> 
> max\_binlog\_size=1G
> 
> binlog\_format=row
> 
> binlog\_row\_image=full

1.版本
    python2.7  ,  python 3.4+ 
    mysql 5.6 、 5.7
    2.安装python（2.7建议）
        # wget https://www.python.org/downloads/release/python-371/Python-3.7.1.tgz
        yum install libffi-devel  openssl openssl-devel(去网上下载rpm包)
        安装pip：略
    3.下载binlog2sql
        git clone https://github.com/danfengcao/binlog2sql.git
    4.安装binlog2sql
        sudo /usr/local/bin/pip  install --no-index  --find-links="./packages/" -r requirements.txt
        packages可在https://download.csdn.net/download/wangxin3618/10883140 下载；也可自己制作
        连网环境：pip install -r requirements.txt

pip安装：
        python2.7 安装pip
    1 先安装setuptools
       下载地址：https://pypi.python.org/pypi/setuptools#downloads
    　将下载后的tar文件解压，用CMD模式进入到解压后的文件所在的目录执行命令：python setup.py install
    2 安装pip 
    　下载地址：https://pypi.python.org/pypi/pip#downloads
       将下载后的tar文件解压，用CMD模式进入到解压后的文件所在的目录执行命令:python setup.py install 
     安装完成

# 五、binlog使用测试 #

使用：

1.DDL （truncate）语句，在整个过程中都是无法输出的
    2.表删了，就不能解析出dml语句了，需将表创建后才能使用
    3.文件可以是动态的（正在被追加的文件）
    4.可解析大文件，不受内存限制。是否可以将一个binlog文件分成多个小文件处理？？？NO
    5.mysql server 必须开启，离线（关闭）模式下不能解析
    6.参数binlog_row_image 必须为full，暂不支持MINIMAL
    7.解析速度不理想，不如myflash
    8.纯python开发，安装使用很简单
    9.自带flashback、no-primary-key解析模式，无需安装补丁
    10.如果只回滚某张表，并且该表有关联表，关联表不会被回滚

用途：

1.数据快速回滚（闪回）
    2.主从切换后新master丢数据的修复
    3.从binlog生成标准SQL

binlog2sql解析原理：

通过binlog_dump 协议来获取binlog内容，需要读取information_schema.columns表来获取元信息，这样才能结合binlog拼接成SQL语句。所以给出用户的最小权限：GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%';
    权限说明：
    select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
    super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
    replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
    用python-mysql-replication实现了MySQL的复制协议，该服务伪装成mysql slave，来获取主的binlog和event。
    如果在高并发环境上解析binlog，相当于多了一个同步线程在主上读取数据，这就加大了主的负担。

binlog2sql参数：

解析模式
    --stop-never : 持续解析binlog。可选。默认false，同步至执行命令时最新的binlog位置，如果不ctrl+c就不会退出。
    --no-primariy-key , -K : 对insert语句去除主键。可选。默认false
    --flashback,-B : 生成回滚SQL，可解析大文件，不受内存限制。可选。默认false。与stop-never或no-primary-key 不能同时使用。
    --back-interval -B 模式下，每打印一千行回滚SQL，加一句sleep多少秒，如果不想加sleep，请设为0.
    解析范围控制
    --start-file : 起始解析文件，只需要文件名，无需全路径。必须。
    --stop-file :  终止解析文件，可选。默认和stat-file同一个文件。若解析模式为stop-server ，则该选项失效。
    --start-position / --start-pos : 起始解析位置。可选，默认为start-file的起始位置
    --stop-position / --stop-pos : 终止解析位置。可选。默认为stop-file的最末位置。若解析模式为stop-server ，则该选项失效。
    --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 table1 table2 。可选默认为空
    --only-dml  只解析dml，忽略ddl。可选默认false。在打印误删语句的时候会将ddl语句也打印出来，这时加上该选项则只打印dml。在flashback的时候不管加没加这个参数，都只打印dml。
    --sql-type 只解析指定类型，支持insert，update，delete。多个类型用空格隔开：--sql-type insert delete .可选。默认dml都解析。用了此参数但没有填任何类型，则三者都不解析。在打印误操作语句的时候指定该参数则打印指定的类型如：指定insert就打印insert；在打印回滚语句的时候指定该参数则打印与之相反的类型如：指定insert，则打印delete；

使用方法：

> 首先判断误删语句所在的binlog文件：show master status; or 去binlog dir ll 查看binlog file的最后修改时间来确定
> 
> 其次确定误删语句执行的大致时间：询问执行该语句的人
> 
> 然后指定文件与时间和对应的库表做一次大致的筛选，然后再通过position筛选出需要回滚的数据（也可以查找binlog文件直接确定误操作语句的准确pos）
> 
>     sudo python /wx/binlog2sql/binlog2sql/binlog2sql.py  -uroot -h10.124.202.125  -p -P3306 --sql-type delete  --only-dml   -dtest -t table1 table2 --start-file='mysql-bin.000008'
>     sudo python /wx/binlog2sql/binlog2sql/binlog2sql.py --flashback  -uroot -h10.124.202.125  -p -P3306  --sql-type delete  --only-dml  -dtest -t table1 table2 --start-file='mysql-bin.000008'
>     sudo python /wx/binlog2sql/binlog2sql/binlog2sql.py --flashback  -uroot -h10.124.202.125  -p -P3306 --sql-type delete  --only-dml  -dtest -t table1 table2 --start-file='mysql-bin.000008'| mysql -uroot -h10.124.202.125  -p -P3306

# 六、myflash安装 #

安装依赖包
        yum install gcc*  pkg-config glib2 libgnomeui-devel -y
    下载
        git clone https://github.com/Meituan-Dianping/MyFlash.git
    动态编译
        gcc -w -g `pkg-config --cflags  glib-2.0` source/binlogParseGlib.c   -o    binary/flashback /lib64/libglib-2.0.so.0
    步骤：
        1.首先flush logs；使误操作的binlog所在的log文件成为静态的
        2.根据误操作的时间点和语句，通过mysqlbinlog |grep -i <dml>  -C20 分析出误操作确定    的pos ;
        3.然后生成回滚后的binlog  
        4.再将binlog导入到mysql server中
    测试
        mysql> flush logs;
        shell> mysqlbinlog  -vv --base64-output=decode-rows --start-datetime='2018-12-26 10:00:00' --database=test   mysql-bin.000008|grep -i -C20 delete 
        shell> sudo ../binary/flashback  --binlogFileNames=/wx/mydata/data1/binlog/mysql-bin.000008 --databaseNames=test  --tableNames=table3  --sqlTypes='delete'  --start-position=7955  --stop-position=8177  
        shell> sudo mysqlbinlog    --skip-gtids     binlog_output_base.flashback  |/wx/mysql/bin/mysql -uroot -p -S /wx/mydata/data1/mysql.sock
    
    测试问题：
    1.在开启gtid的MySQL server上，应用flashback报错，错误为：ERROR 1782 (HY000) at line 16: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. 
        答：在导入时加入--skip-gtids mysqlbinlog --skip-gtids | mysql -uxxx -pxxx
    2.如果回滚后的binlog日志尺寸超过20M，在导入时，很耗时。如何处理?
        答：参考 使用 ,搜索maxSplitSize。使用该参数可以对文件进行切片
    3.加入 sqlTypes  ，会将原delete语句反转成insert语句

myflash 静态编译和动态编译：

动态编译链接
        gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback
        然而用户不想每次去重新编译，可以选择使用静态链接，但是该方法需要知道glib库的版本和位置，因此对于每台机器略有不同，请谨慎使用
    
    静态编译链接
        为了保证在一台机器上编译后，可以在其它机器上使用，需要满足以下两个条件 
            a) 将glib做成静态链接 
            b）在编译的那台机器的glibc版本（查看方法为ldd --version）要小于等于要运行该软件的那台机器glibc版本 因此需要你在一台glibc版本较低的机器上运行如下命令即可。
        gcc -w -g `pkg-config --cflags  glib-2.0` source/binlogParseGlib.c   -o binary/flashback /usr/lib64/libglib-2.0.a -lrt

# 七、myflash使用 #

使用：

1.解析的binlog文件需为静态的（解析过程中binlog不能再写入）：尽量 flush logs 之后再分析
    2.可以处理离线的binlog文件（该binlog文件对应的mysql server 可以是关闭的）

限制：

binlog格式必须为row,且binlog_row_image=full
    仅支持5.6与5.7
    只能回滚DML（增、删、改）

参数说明：

1.databaseNames 
    指定需要回滚的数据库名。多个可用‘，’隔开。不指定该参数则指定所有的库
    2.tableNames
    指定需要回滚的表名。多个可用‘，’隔开。不指定该参数则指定所有的表
    3.start-position
    指定回滚开始的位置，如不指定则从文件的开始处回滚
    4.stop-position 
    指定回滚结束的位置，如不指定则回滚到文件结尾
    5.start-datetime
    指定回滚的开始时间，格式为 %Y-%m-%d %H:%M:%S。 如不指定，则不限定时间
    6.stop-datetime
    指定回滚的结束时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定，则不限定时间
    7.sqlTypes
    指定需要回滚的sql类型。目前支持的过滤类型是INSERT, UPDATE ,DELETE。多个类型可以用“,”隔开。
    8.maxSplitSize
    一旦指定该参数，对文件进行固定尺寸的分割（单位为M）。防止单次应用的binlog尺寸过大。指定一个文件多少兆。
    9.loglevel 	
    仅开发者使用，默认级别为error级别。在生产环境中不要修改这个级别，窦泽会输出过多
    10.include-gtids
    指定需要回滚的gtid,支持gtid的单个和范围两种形式。
    11.exclude-gtids
    指定不需要回滚的gtid，用法同include-gtids

解析的不同：

与binlog2sql的解析方法不同：
    binlog2sql：
        1.binlog2sql 是先根据过滤条件将 操作的可视化语句 显示出来（每条语句后面包含该语句执行的时间与pos）
        2.再根据pos将确定的语句回滚，将回滚后的语句导入到一个SQL文件，然后导入进mysql
    flashback：
        1.根据条件直接将binlog反转：可能反转出的不仅仅是误删的语句，这就需要查看原binlog文件确定误操作语句的准确pos。如果binlog文件很大，则查找很费力。但是binlog2sql解析很慢。

# 八、两种工具性能对比 #

表：

CREATE TABLE `flash_test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `nameShort` varchar(20) DEFAULT NULL,
      `nameLong` varchar(260) DEFAULT NULL,
      `amount` decimal(19,9) DEFAULT NULL,
      `amountFloat` float DEFAULT NULL,
      `amountDouble` double DEFAULT NULL,
      `createDatetime6` datetime(6) DEFAULT NULL,
      `nameText` text,
      `nameBlob` blob,
      `nameMedium` mediumtext,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=876527 DEFAULT CHARSET=utf8

rows：100万； binlog文件大小：400M

生成反转语句：

myflash:binlog2sql = 1:8

将反转语句导入到mysql：

myflash:binlog2sql = 1:15

总结：从恢复速度上面 myflash 远优于 binlog2sql

myflash可处理离线binlog文件，并支持拆分，但是文件需是静态的

binlog2sql 可处理动态文件，但是server须存活