【赵渝强老师】MySQL的闪回
MySQL DBA或开发人员,有时会误删或者误更新数据,如果是线上环境并且影响较大,就需要能快速回滚。传统恢复方法是利用备份重搭实例,再应用去除错误sql后的binlog来恢复数据。此法费时费力,甚至需要停机维护,并不适合快速回滚。也有团队利用LVM快照来缩短恢复时间,但快照的缺点是会影响mysql的性能。MySQL闪回(flashback)利用binlog直接进行回滚,能快速恢复且不用停机。
闪回的原理
MySQL binlog以event的形式,记录了MySQL server从启用binlog以来所有的变更信息,能够帮助重现这之间的所有变化。MySQL引入binlog主要有两个目的:一是为了主从复制;二是某些备份还原操作后需要重新应用binlog。有三种可选的binlog格式,各有优缺点:
- statement:基于SQL语句的模式,binlog数据量小,但是某些语句和函数在复制过程可能导致数据不一致甚至出错;
- row:基于行的模式,记录的是行的完整变化。很安全,但是binlog会比其他两种模式大很多;
- mixed:混合模式,根据语句来选用是statement还是row模式;
利用binlog闪回,需要将binlog格式设置为row。利用下面的语句可以查看当前binlog的模式。
1 | show global variables like "%binlog_format%" ; |
闪回的实战
真实的闪回场景中,最关键的是能快速筛选出真正需要回滚的SQL。我们使用开源工具binlog2sql来进行实战演练。binlog2sql由美团点评DBA团队(上海)出品,多次在线上环境做快速回滚。
① 安装binlog2sql工具
1 2 3 4 5 6 7 | 首先安装Python工具管理表pip yum -y install epel-release yum -y install python-pip 安装binlog2sql git clone https: //github .com /danfengcao/binlog2sql .git && cd binlog2sql pip install -r requirements.txt |
② 闪回案例实战
1、我们使用之前的员工表数据,单独建立一个数据库
1 2 3 | create database testflashback; use testflashback; source /root/tools/scott.sql |
2、误操作,执行下面的事务。
1 2 3 4 5 | start transaction ; delete from emp where sal>3000; update emp set sal=6000; delete from emp where job= 'CLERK' ; commit ; |
3、查看目前的binlog文件
1 | show master logs; |
4、最新的binlog文件是mysql-binlog.000001。我们的目标是筛选出需要回滚的SQL,由于误操作人只知道大致的误操作时间,我们首先根据时间做一次过滤。只需要解析testflashback库emp表。(注:如果有多个sql误操作,则生成的binlog可能分布在多个文件,需解析多个文件)
1 2 | python binlog2sql /binlog2sql .py -uroot -pWelcome_1 \ --start- file = 'mysql-binlog.000001' > /root/tools/raw .sql |
上面的语句将列车emp表的所有binlog日志。如果能够确定大致的时间范围,可以使用参数--start-datetime和--stop-datetime进行过滤。例如:
1 | --start-datetime= '2016-12-26 11:44:00' --stop-datetime= '2016-12-26 11:50:00' |
解析处理的binlog如下:
5、根据位置信息,我们确定了误操作sql来自同一个事务,准确位置在14956-16791之间(binlog2sql对于同一个事务会输出同样的start position)。再根据位置过滤,使用 -B 选项生成回滚sql,检查回滚sql是否正确。(注:真实场景下,生成的回滚SQL经常会需要进一步筛选。结合grep、编辑器等)
1 2 | python binlog2sql /binlog2sql .py -uroot -pWelcome_1 --start- file = 'mysql-binlog.000001' \ --start-position=14956 --stop-position=16791 -B > /root/tools/rollback .sql |
下面是生成的闪回语句:
6、与业务方确认回滚sql没问题,执行回滚语句。登录mysql,确认回滚成功。
1 | mysql -uroot -pWelcome_1 < /root/tools/rollback .sql |
7、检查数据是否恢复
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 趁着过年的时候手搓了一个低代码框架
· 推荐一个DeepSeek 大模型的免费 API 项目!兼容OpenAI接口!