分布式数据库各回滚恢复工具学习(binlog2sql,MyFlash,my2sql ...)
1、背景:DBA或开发人员,有时会误删或者误更新数据,如果是线上环境并且影响较大,就需要能快速回滚。传统恢复方法是利用备份重搭实例,再应用去除错误sql后的binlog来恢复数据。此法费时费力,甚至需要停机维护,并不适合快速回滚。也有团队利用LVM快照来缩短恢复时间,但快照的缺点是会影响mysql的性能
2、闪回原理:
MySQL binlog以event的形式,记录了MySQL server从启用binlog以来所有的变更信息,能够帮助重现这之间的所有变化。
MySQL引入binlog主要有两个目的:一是为了主从复制;二是某些备份还原操作后需要重新应用binlog
binlog以event形式记录了所有的变更信息,那么我们把需要回滚的event,从后往前回滚回去即可。
有三种可选的binlog格式,各有优缺点:
* statement:基于SQL语句的模式,binlog数据量小,但是某些语句和函数在复制过程可能导致数据不一致甚至出错;
* row:基于行的模式,记录的是行的完整变化。很安全,但是binlog会比其他两种模式大很多;
* mixed:混合模式,根据语句来选用是statement还是row模式;
利用binlog闪回,**需要将binlog格式设置为row**。row模式下,一条使用innodb的insert会产生如下格式的binlog:
2、binlog2sql学习:
2.1.安装
tar xvf binlog2sql.tar.gz
cd binlog2sql/binlog2sql_dependencies/
tar xvf setuptools-0.6c11.tar.gz
cd setuptools-0.6c11
python setup.py install
cd …
tar xvf pip-9.0.1.tar.gz
cd pip-9.0.1
python setup.py install
cd …
pip install *.whl mysql-replication-0.9.tar.gz
2.2 参数理解
*解析模式**
--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。可选
2.3 测试操作:
2.3.1以表`t_wf_dcasemsg_arc` 为例
2.3.2删除之前查看数据:
2.3.3删除十条数据:
2.3.4 依据 删除 时间, 或者用mysqlbinlog先解析出来,查看删除具体的时间和位置来确定回滚的时间及位置
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/167af768-8270-44a2-9c74-07f7f6cd77a8.png)
2.3.5依据位置确定回滚语句及恢复
3、MyFlash 学习:
3.1安装:
yum install libgnomeui-devel
cd 到解压目录
动态编译链接
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
3.2 参数理解
下面的这些参数是可以任意组合的。
- 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文件切割,防止单次应用的binlog尺寸过大,对线上造成压力*
- 9.binlogFileNames
指定需要回滚的binlog文件,目前只支持单个文件,后续会增加多个文件支持
- 10.outBinlogFileNameBase
指定输出的binlog文件前缀,如不指定,则默认为binlog_output_base.flashback
- 11.logLevel
仅供开发者使用,默认级别为error级别。在生产环境中不要修改这个级别,否则输出过多
- 12.include-gtids
指定需要回滚的gtid,支持gtid的单个和范围两种形式。
- 13.exclude-gtids
指定不需要回滚的gtid,用法同include-gtids
3.3 恢复测试操作
3.3.1以表`t_wf_dcasemsg_arc` 为例(2.3.1-2.3.4步骤一样略)
3.3.2依据位置确定回滚语句及恢复
3.3.3 根据文件mysqlbinlog进行恢复(上传至其他环境恢复)
2、闪回原理:
MySQL binlog以event的形式,记录了MySQL server从启用binlog以来所有的变更信息,能够帮助重现这之间的所有变化。
MySQL引入binlog主要有两个目的:一是为了主从复制;二是某些备份还原操作后需要重新应用binlog
binlog以event形式记录了所有的变更信息,那么我们把需要回滚的event,从后往前回滚回去即可。
有三种可选的binlog格式,各有优缺点:
* statement:基于SQL语句的模式,binlog数据量小,但是某些语句和函数在复制过程可能导致数据不一致甚至出错;
* row:基于行的模式,记录的是行的完整变化。很安全,但是binlog会比其他两种模式大很多;
* mixed:混合模式,根据语句来选用是statement还是row模式;
利用binlog闪回,**需要将binlog格式设置为row**。row模式下,一条使用innodb的insert会产生如下格式的binlog:
2、binlog2sql学习:
2.1.安装
tar xvf binlog2sql.tar.gz
cd binlog2sql/binlog2sql_dependencies/
tar xvf setuptools-0.6c11.tar.gz
cd setuptools-0.6c11
python setup.py install
cd …
tar xvf pip-9.0.1.tar.gz
cd pip-9.0.1
python setup.py install
cd …
pip install *.whl mysql-replication-0.9.tar.gz
2.2 参数理解
*解析模式**
--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。可选
2.3 测试操作:
2.3.1以表`t_wf_dcasemsg_arc` 为例
2.3.2删除之前查看数据:
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/3519cd90-455c-4554-b1bc-435e7a3e3cf6.png)
2.3.3删除十条数据:
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/72be40bd-604e-443a-ab18-56c9ae303b7a.png)
2.3.4 依据 删除 时间, 或者用mysqlbinlog先解析出来,查看删除具体的时间和位置来确定回滚的时间及位置
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/167af768-8270-44a2-9c74-07f7f6cd77a8.png)
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/b7594e25-a277-4b3a-a25f-a147e280aa86.png)
2.3.5依据位置确定回滚语句及恢复
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/25ea9c63-6bc5-410c-996a-e475bc9b33da.png)
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/db1e3557-436b-4bd5-bf32-a4a975b1c06a.png)
3、MyFlash 学习:
3.1安装:
yum install libgnomeui-devel
cd 到解压目录
动态编译链接
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
3.2 参数理解
下面的这些参数是可以任意组合的。
- 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文件切割,防止单次应用的binlog尺寸过大,对线上造成压力*
- 9.binlogFileNames
指定需要回滚的binlog文件,目前只支持单个文件,后续会增加多个文件支持
- 10.outBinlogFileNameBase
指定输出的binlog文件前缀,如不指定,则默认为binlog_output_base.flashback
- 11.logLevel
仅供开发者使用,默认级别为error级别。在生产环境中不要修改这个级别,否则输出过多
- 12.include-gtids
指定需要回滚的gtid,支持gtid的单个和范围两种形式。
- 13.exclude-gtids
指定不需要回滚的gtid,用法同include-gtids
3.3 恢复测试操作
3.3.1以表`t_wf_dcasemsg_arc` 为例(2.3.1-2.3.4步骤一样略)
3.3.2依据位置确定回滚语句及恢复
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/f241e0aa-57d6-4ed5-be15-d3a40fc133fc.png)
3.3.3 根据文件mysqlbinlog进行恢复(上传至其他环境恢复)
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/f77985d8-6bbe-4a1a-aae1-a6841096b88f.png)
![](https://media.teamshub.com/10000/tm/2022/08/30/a8d13564-ab95-42a5-9c40-dfcef5575083/9c2d8949-17f5-4043-80a7-51f210894615.png)