代码改变世界

[美团] Myflash 的安装使用

2020-06-09 14:15  那个，我  阅读(995)  评论(0)  编辑  收藏  举报

目录

[美团] Myflash 的安装使用

GitHub：

https://github.com/Meituan-Dianping/MyFlash

Myflash 相对于binlog2sql 和 mysqlbinlog 来说恢复速度非常快。

实现原理可以参考：http://url.cn/5yVTfLY

该方式不像binlog2sql 一样转换binlog为易读的sql 语句，而是直接截取复制并修改二进制 binlog 文件实现SQL的反转，然后使用mysqlbinlog 命令读取新生成的二进制binlog闪回文件，将闪回操作导入数据库实现数据的恢复。

使用过程中需要特别注意的是binlog 文件的位置一定不能出错，注意相关参数的使用。

限制

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

安装

yum install -y gcc glib2 glib2-devel

unzip MyFlash-master.zip

cd MyFlash-master

gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback

cd binary

[root@mysql1 binary]# ./flashback -h
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(,)
  --maxSplitSize              max file size after split, the uint is M
  --binlogFileNames           binlog files to process. if multiple, seperate by comma(,)  
  --outBinlogFileNameBase     output binlog file name base
  --logLevel                  log level, available option is debug,warning,error
  --include-gtids             gtids to process
  --exclude-gtids             gtids to skip

测试案例

1. 建库建表

-- 建库
create database cym; use cym;
-- 建表
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB charset=utf8mb4;
-- 插入数据
flush logs;
insert into t1 values (1,'a'),(2,'b');
flush logs;
-- 获取Binlog位置
show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000022 |       504 |
| mysql-bin.000023 |       194 |
+------------------+-----------+
select @@log_bin_basename;
+-----------------------------+
| @@log_bin_basename          |
+-----------------------------+
| /mysqldata/binlog/mysql-bin |
+-----------------------------+

2. 测试闪回insert

# 生成闪回binlog文件
rm -rf binlog_output_base.flashback
./binary/flashback --binlogFileNames=/mysqldata/binlog/mysql-bin.000022

# 执行闪回
mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql -uroot -proot cym

# 检查验证
mysql -uroot -proot cym
select * from t1;
Empty set (0.00 sec)

3. 测试闪回delete

# 生成闪回binlog文件
rm -rf binlog_output_base.flashback
./binary/flashback --binlogFileNames=/mysqldata/binlog/mysql-bin.000023 --sqlTypes=DELETE

# 执行闪回
mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql -uroot -proot cym

# 检查验证
mysql -uroot -proot cym -e 'select * from t1;'
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+

4. 测试闪回update

-- 更新数据
mysql -uroot -proot cym
update t1 set name='c' where id=2;
select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | c    |
+----+------+
2 rows in set (0.00 sec)

# 生成闪回binlog文件
rm -rf binlog_output_base.flashback
./binary/flashback --binlogFileNames=/mysqldata/binlog/mysql-bin.000023 --sqlTypes=update

# 执行闪回
mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql -uroot -proot cym

# 检查验证
mysql -uroot -proot cym -e 'select * from t1;'
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+

5. 其他参数的测试使用

5.1 数据准备

flush logs;
create database cym1;use cym1;
create table t2 like cym.t1;
insert into t2 values(3,'c'),(4,'d'),(5,'e');

delete from t2 where id=4;				-- 需闪回的操作
insert into t2 values(6,'g');
insert into cym.t1 values(10,'test');
update t2 set name='f' where id=3;		-- 需闪回的操作

delete from t2 where id=5;
select * from cym.t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
| 10 | test |
+----+------+

select * from t2;
+----+------+
| id | name |
+----+------+
|  3 | f    |
|  6 | g    |
+----+------+

5.2 测试目标

  1. 恢复id=4的数据,
  2. 将id=3 恢复到修改前
  3. 不闪回其他操作，如对id=6 的insert和对cym.t1 表的变更。

目标:

select * from cym.t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
| 10 | test |
+----+------+

select * from t2;
+----+------+
| id | name |
+----+------+
|  3 | c    |
|  4 | d    |
|  6 | g    |
+----+------+

5.3 确认要恢复事务的GTID

show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000027 |     2084 |              |                  | fa9a20b5-831c-11ea-b919-080027a0316a:1-93 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

[root@mysql1 MyFlash-master]# mysqlbinlog -v /mysqldata/binlog/mysql-bin.000027 | egrep -i 'GTID_NEXT|UPDATE `cym1`.`t2`|DELETE FROM `cym1`.`t2`'
SET @@SESSION.GTID_NEXT= 'fa9a20b5-831c-11ea-b919-080027a0316a:86'/*!*/;
SET @@SESSION.GTID_NEXT= 'fa9a20b5-831c-11ea-b919-080027a0316a:87'/*!*/;
SET @@SESSION.GTID_NEXT= 'fa9a20b5-831c-11ea-b919-080027a0316a:88'/*!*/;
SET @@SESSION.GTID_NEXT= 'fa9a20b5-831c-11ea-b919-080027a0316a:89'/*!*/;
### DELETE FROM `cym1`.`t2`
SET @@SESSION.GTID_NEXT= 'fa9a20b5-831c-11ea-b919-080027a0316a:90'/*!*/;
SET @@SESSION.GTID_NEXT= 'fa9a20b5-831c-11ea-b919-080027a0316a:91'/*!*/;
SET @@SESSION.GTID_NEXT= 'fa9a20b5-831c-11ea-b919-080027a0316a:92'/*!*/;
### UPDATE `cym1`.`t2`
SET @@SESSION.GTID_NEXT= 'fa9a20b5-831c-11ea-b919-080027a0316a:93'/*!*/;
### DELETE FROM `cym1`.`t2`
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

要恢复的GTID 为: fa9a20b5-831c-11ea-b919-080027a0316a:89-92 只恢复其中对t2 表的delete和update 操作

5.4 生成闪回binlog文件

rm -rf binlog_output_base.flashback
./binary/flashback --binlogFileNames=/mysqldata/binlog/mysql-bin.000027 --databaseNames=cym1 --tableNames=t2 --sqlTypes=delete,update --include-gtids='fa9a20b5-831c-11ea-b919-080027a0316a:89-92'

5.5 恢复并验证

-- 恢复
mysqlbinlog --skip-gtids  binlog_output_base.flashback >flash.sql
mysql -uroot -proot
set sql_log_bin=0;
source flash.sql;
set sql_log_bin=1;

-- 验证
select * from cym.t1;select * from cym1.t2;'
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
| 10 | test |
+----+------+
+----+------+
| id | name |
+----+------+
|  3 | c    |
|  4 | d    |
|  6 | g    |
+----+------+

符合预期，恢复结束。

通过以上的验证，该恢复方式部署简单，效率高，且恢复可靠。