mysql 闪回_Mysql数据闪回操作备忘

准备工作

更新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

安装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

安装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完成回滚