背景:
- 常在河边走哪有不湿鞋,经常大量操作生产数据数据库,总有手一抖、没看清的原因,导致sql 执行错误或者库被删除,利用binlog 恢复就不用跑路了
- 一般进行删表、删库的操作行为较少,删除行的数据也比较少,新增数据对业务没有影响,所以绝大部分出数据都是更改数据(UPDATE),最常见的就是条件出错导致全表更改
- 结合一次生产数据库被误修改,表 credit_query_record 中的字段gmt_create 设置了 根据当前时间更新属性(不应该设置),在初始化user_id 字段时,导致所有的gmt_create 被更改为当前时间,从而无法查询用户支付记录创建时间,业务异常,最终通过binlog 恢复
恢复操作步骤
1、获取binlog
发现生产sql执行导致数据问题,如果紧急需要立即恢复,首先联系运维获取刚产生的binlog,或者从数据部门otter 获取,如果不是很紧急可以从数据库面版下载oss备份的binlog,一般达到500M就会上传至oss
2、解析binlog
由于binlog是二进制文件,需要借助专业的工具命令,最常用的mysql自带的命令 mysqlbinlog,如果知道具体的位点使用位点(精确),如果不是很清楚可使用大致的时间范围, grep 我们需要的
参考命令
./mysqlbinlog --start-datetime='2018-08-06 21:00:00' --stop-datetime='2018-08-06 21:20:10' -d open_platform /Users/user/Downloads/mysql-bin.000589 -v|grep -E '###|# at' > /Users/user/Downloads/old.sql
old.sql 内容
3、解析old.sql ,转换自己需要恢复数据的sql语句
pthon解析代码参考
__author__ = 'test'
#encoding:utf-8
import json
import time, datetime
f = open('/Users/test/PycharmProjects/untitled/sqlresult', 'w')
result = ''
updatesql=''
i=0
with open('old.sql') as stream:
for (index,line) in enumerate(stream,1):
if index%1000==0:
print index
if(i>0):
i=i+1
if i==3 :
id=line[9:len(line)]
updatesql=' where id= '+id
if i==14:
timelong=line[10:len(line)];
time_local = time.localtime(int(timelong))
otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
updatesql=' update credit_query_record_bak set gmt_create= '+"'"+otherStyleTime+"'"+ updatesql.strip('\n')+";"
f.write(updatesql)
f.write("\n")
print updatesql
i=0;
updatesql=''
if line.startswith('at'):
continue
if ('UPDATE' in line and 'credit_query_record' in line):
i=1
print line
print result
f.close()
sqlresult 文件语句如下,放在服务器器上执行即可恢复误操作的数据