Mysql数据闪回操作备忘

准备工作

1. 更新mysql客户端到5.7版本

shell> wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
shell> yum -y install mysql57-community-release-el7-11.noarch.rpm
shell> yum -y install mysql-community-server
# 检查mysqlbinlog版本是否3.4
shell> mysqlbinlog --version

2. 安装MyFlash

# 下载
shell> git clone https://github.com/Meituan-Dianping/MyFlash.git

安装

shell> cd MyFlash
shell> gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback
# 如果报No package 'glib-2.0' found，先安装glib依赖
shell> yum install libgnomeui-devel

3. 安装binlog2sql

shell> git clone https://github.com/danfengcao/binlog2sql.git
shell> cd binlog2sql
shell> pip install -r requirements.txt

闪回操作

注意：利用MyFlash回滚阿里云RDS备份恢复页面中下载到ECS本地的binlog（RDS下载的binlog无法解析）

# 下载阿里云RDS控制台备份恢复页面中相关binlog
# 下载地址需要加'' 用-O(大写)指定输出文件名
shell> wget -c 'dowload url' -O mysql-bin.xxxx
# 生成回滚binlog
shell> binary/flashback --binlogFileNames=mysql-bin.xxxx --start-datetime="2020-01-01 12:00:00" --stop-datetime="2020-01-01 13:00:00" --databaseNames=xxx --tableNames=xxx --sqlTypes='UPDATE','DELETE' --outBinlogFileNameBase=xxx
# 查看回滚sql
shell> mysqlbinlog --no-defaults --base64-output=decode-rows -vv xxx.flashback 
# 利用mysqlbinlog回滚
shell> mysqlbinlog --no-defaults xxx.flashback |mysql -hxxx -uxxx -p 

利用binlog2sql回滚最新binlog

# 找到最新binlog
mysql> show binary logs;
# 或者手动生成最新binlog
mysql> flush logs;
# 生成回滚sql -d 指定数据库 -t 指定表 -B 生成回滚sql
shell> python binlog2sql/binlog2sql.py -hxxxx -P3306 -uxxx -p -dxxx -txxx --start-file='mysql-bin.xxxx' --start-datetime='2020-01-01 22:30:00' --stop-datetime='2020-01-01 22:50:00' -B > /tmp/raw.sql
# 人工筛选后执行sql完成回滚