MyFlash初体验
验证binlog2sql 是否支持blob转换
1 | `--w_blob.py 写入blob import sys import pymysql import os path = "./" fp = open("/alidata/PG01.jpg", 'rb') img = fp.read() fp.close() database = pymysql.connect(host="localhost", user="root", passwd="root", db="qinxi") cursor = database.cursor() sql = "INSERT INTO tb_test (nameBlob) VALUES (%s);" args = (img) cursor.execute(sql, args) database.commit() cursor.close() database.close() print("============") print("Done! ") --r_blob.py 读取blob import pymysql as mdb import sys conn = mdb.connect(host='localhost',user='root',passwd='root',db='qinxi') cursor = conn.cursor() cursor.execute("SELECT nameBlob FROM tb_test LIMIT 1") fout = open('PG333.jpg','wb') fout.write(cursor.fetchone()[0]) fout.close() cursor.close() conn.close() python binlog2sql.py -uroot -h10.200.6.53 -proot -B --start-file=mybinlog.000033 > 33.sql` |
MyFlash测试
https://github.com/Meituan-Dianping/MyFlash
安装
1 | `yum install libgnomeui-devel gcc -w -g `pkg-config --cflags glib-2.0` source/binlogParseGlib.c -o binary/flashback /usr/lib64/libglib-2.0.a -lrt yum install install glib2*` |
测试环境准备
1 | https://github.com/Meituan-Dianping/MyFlash/blob/master/doc/TestCase.md |
回滚命令生成
1 | `./MyFlash/binary/flashback --binlogFileNames=/alidata/mysql/mybinlog.000022 --outBinlogFileNameBase=22delete.flashback` |
扩展
阿里云RDS实例,用户没有Super权限,导入带有set @@信息,会提示ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPERprivilege(s) for this operation报错。
本地5.7版本,测试去除set @@GTID_NEXT,当GTID模式开启状态,导入实例会提示报错:
1 | ERROR 1782 (HY000) at line 19: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. |
只有将 GTID模式关闭,才可以正常导入。
https://bugs.mysql.com/bug.php?id=85480
1 | `mysqlbinlog -v -v --skip-gtids 22delete.flashback | sed 's@\/\*.*\*\/@@' |mysql -uroot -proot` |
初步结论:云上RDS 目前无法使用该工具,由于没有super权限。