轻松了解各种云产品、服务及动态、提供各种云服务。

MySQL Update误操作回滚

135
文章目录
  1. 背景
  2. 恢复方法
  3. 操作步骤
  4. 更多内容请关注公众号

MySQL Update误操作回滚

背景

今天系统上线了一个很小的功能,涉及到数据库更新操作,但是在执行SQL语句之前没有备份相关的数据库表,在上线验证完成后,发现程序员写的SQL更新语句限制条件不够,多更新了一些记录,所以需要对多更新的语句进行恢复。

恢复方法

1. 书写SQL语句进行恢复
该方法需要程序员通过各种联合查询,把多更新的记录和字段找出来,已经更新之前的字段状态,从而进行恢复。但是查找出来的记录是否齐全以及状态是否正确都很难保证,所以排出了该方法。
** 2. 创建临时实例 **
公司使用的数据库是阿里云RDS,RDS有一个很牛的功能,就是能够指定时间来创建临时实例,只要你记得你更新时间即可,但是出问题的数据库目前拥有接近400G的数据,如果使用该方法,可能要等一天的时间才能把临时实例创建出来。
3. 通过binglog恢复
MySQL的binglog记录了所有的更新,删除,插入操作,如果Binglog模式是row模式,那么binglog文件里面将会记录着更新前每个字段的值,只要我们找出当时更新的记录,把where字段和set字段进行颠倒,即可恢复到更新前的状态。该方法,能够很快找出当时的更新语句,且全面,所以小编选择该方法。

操作步骤

**1. 停止表相关业务 **
由于本次更新的表是2张,只涉及了一个功能,该功能的使用人数很少,所以当时我们就直接电话给使用者,告诉他们不再适用相关功能。

2. 获取binglog日志
根据我们更新语句时的操作时间,获取binglog文件,通过命令:

mysqlbinlog --no-defaults  --start-datetime='2018-04-12 16:30:00' --stop-datetime='2018-04-12 16:45:00' -vv -d deal_report 1047140219_3306-bin.000009 > log.txt

3. 通过binglog找到更新语句
通过操作SQL语句查找到log.txt更新语句,删除其他与错误操作没有关系的语句,获取的原始数据如下:

### UPDATE `deal_report`.`sh_report_distribute_com`
### WHERE
###   @1='0001bb0dd8ab33e9aadff2c65953da7e' /* STRING(96) meta=65120 nullable=0 is_null=0 */
###   @2='abded7834001340fa2bf62fb4ec5665c' /* STRING(96) meta=65120 nullable=0 is_null=0 */
###   @3=5355 /* INT meta=0 nullable=0 is_null=0 */
###   @4=9849 /* INT meta=0 nullable=1 is_null=0 */
###   @5='xxxxx' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @6=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @7=1581 /* INT meta=0 nullable=1 is_null=0 */
###   @8='万科学府七组' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @9=1926 /* INT meta=0 nullable=1 is_null=0 */
###   @10='A2初级置业顾问' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @11=7554 /* INT meta=0 nullable=1 is_null=0 */
###   @12='xxxxxx' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @13=1581 /* INT meta=0 nullable=1 is_null=0 */
###   @14='万科学府七组' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @15=3962 /* INT meta=0 nullable=1 is_null=0 */
###   @16='xxxxx' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @17=978 /* INT meta=0 nullable=1 is_null=0 */
###   @18='xxxxx区' /* VARSTRING(189) meta=189 nullable=1 is_null=0 */
###   @19=1966 /* INT meta=0 nullable=1 is_null=0 */
###   @20='xxxxx' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @21=398 /* INT meta=0 nullable=1 is_null=0 */
###   @22='xxxxx区' /* VARSTRING(189) meta=189 nullable=1 is_null=0 */
###   @23=38880.00 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
###   @24=0.1500 /* DECIMAL(10,4) meta=2564 nullable=0 is_null=0 */
###   @25=0.1485 /* DECIMAL(10,4) meta=2564 nullable=0 is_null=0 */
###   @26=5773.68 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
###   @27=0.2800 /* DECIMAL(10,4) meta=2564 nullable=0 is_null=0 */
###   @28=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @29=9120 /* INT meta=0 nullable=0 is_null=0 */
###   @30=1518431210 /* INT meta=0 nullable=0 is_null=0 */
###   @31=1519352161 /* INT meta=0 nullable=1 is_null=0 */
###   @32=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @33=8074 /* INT meta=0 nullable=1 is_null=0 */
###   @34='新M1经理' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @35=8004 /* INT meta=0 nullable=1 is_null=0 */
###   @36='R2项目经理' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @37=2457 /* INT meta=0 nullable=1 is_null=0 */
###   @38='D2高级区域总监' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
### SET
###   @1='0001bb0dd8ab33e9aadff2c65953da7e' /* STRING(96) meta=65120 nullable=0 is_null=0 */
###   @2='abded7834001340fa2bf62fb4ec5665c' /* STRING(96) meta=65120 nullable=0 is_null=0 */
###   @3=5355 /* INT meta=0 nullable=0 is_null=0 */
###   @4=9849 /* INT meta=0 nullable=1 is_null=0 */
###   @5='xxxxx' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @6=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @7=1581 /* INT meta=0 nullable=1 is_null=0 */
###   @8='万科学府七组' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @9=1678 /* INT meta=0 nullable=1 is_null=0 */
###   @10='A3中级置业顾问' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @11=7554 /* INT meta=0 nullable=1 is_null=0 */
###   @12='xxxxx' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @13=1581 /* INT meta=0 nullable=1 is_null=0 */
###   @14='万科学府七组' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @15=3962 /* INT meta=0 nullable=1 is_null=0 */
###   @16='xxxxx' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @17=978 /* INT meta=0 nullable=1 is_null=0 */
###   @18='xxxxx区' /* VARSTRING(189) meta=189 nullable=1 is_null=0 */
###   @19=1966 /* INT meta=0 nullable=1 is_null=0 */
###   @20='xxxxx' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @21=398 /* INT meta=0 nullable=1 is_null=0 */
###   @22='xxxxx区' /* VARSTRING(189) meta=189 nullable=1 is_null=0 */
###   @23=38880.00 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
###   @24=0.1500 /* DECIMAL(10,4) meta=2564 nullable=0 is_null=0 */
###   @25=0.1485 /* DECIMAL(10,4) meta=2564 nullable=0 is_null=0 */
###   @26=5773.68 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */
###   @27=0.2800 /* DECIMAL(10,4) meta=2564 nullable=0 is_null=0 */
###   @28=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @29=9120 /* INT meta=0 nullable=0 is_null=0 */
###   @30=1518431210 /* INT meta=0 nullable=0 is_null=0 */
###   @31=1519352161 /* INT meta=0 nullable=1 is_null=0 */
###   @32=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @33=8074 /* INT meta=0 nullable=1 is_null=0 */
###   @34='新M1经理' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */
###   @35=8004 /* INT meta=0 nullable=1 is_null=0 */
###   @36='R2项目经理' /* VARSTRING(93) meta=93 nullable=1 is_null=0 */

4. 通过脚本把Update操作中where和set对调并生成恢复SQL语句
Row格式的binlog记录的格式如上面所示,需要做的工作就是把Update的操作的WHERE好SET对调,上面的都是有一定规律的,并且需要注意的是:
* 需要把@表示的“虚列”换成“实列”。
* 更新NULL值的时候,WHERE 后面的字段有NULL的,不能用“=”号,需要用“is”。
* 字段类型 DATETIME 日期。在日志中保存的格式为 @4=2012-10-25 00:00:00,需要将2012-10-25 00:00:00加上引号
* 负数。在日志中保存的格式为 @1=-1 (4294967295), -2(4294967294),-3(4294967293),需要将()里面的数据去掉,只保留@1=-1。
* 转义字符集。如:’s,\,等

#!/bin/env python
# -*- encoding: utf-8 -*-
#-------------------------------------------------------------------------------
# Name:        restore_update.py
# Purpose:     通过Binlog恢复Update误操作数据
# Author:      zhoujy
# Created:     2012-12-26
# update:      2012-12-26
# Copyright:   (c) Mablevi 2012
# Licence:     zjy
# Usage:       python restore_update.py binlog.txt tablename
#-------------------------------------------------------------------------------
import MySQLdb
import sys
reload(sys)
sys.setdefaultencoding("utf-8")
def get_column(conn,tbname):                                                                                  #从库中取字段"实名",代替@字段名。
query = "select group_concat(COLUMN_NAME) from information_schema.COLUMNS where TABLE_NAME='%s'" %tbname
cursor = conn.cursor()
cursor.execute(query)
items = cursor.fetchone()
return items
def read_binlog(file,tbname,column):
f = open(file)
columns = column.split(',')
num = '@'+str(len(columns))                                                                               #取字段数
while True:
lines = f.readline()
if lines.strip()[0:3] == '###':
lines=lines.split(' ',3)
if lines[1].strip() == 'WHERE':                                                                    #SET和WHERE对调。
lines[1] = "SET"
sep1 = ','
sep2 = ''                                                                                      #如果是SET 后面的字段,则用这些定义的分隔符。
sep3 = ' = '
lines[-1] = lines[-1].strip()
elif lines[1].strip() == 'SET':                                                                    #SET和WHERE对调。
lines[1] = "WHERE"
lines[-1] = lines[-1].strip()
sep1 = '\nand'
sep2 = ';\n'                                                                                   #WHERE 后面的字段,则用这些定义的分隔符。
sep3 = ' is '
else:
lines[-1] = lines[-1].strip()
if  ''.join(lines).find('@') <> -1 and lines[3].split('=',1)[0] <> num:
c = int(lines[3].split('=',1)[0].split('@')[1])
col = columns[c-1]                                                                             #取实名字段。
lines[3] = lines[3].split('=',1)[-1].strip()
if lines[3].strip('\'').strip().find('\'') <> -1:
lines[3] = lines[3].split('/*')[0].strip('\'').strip().strip('\'').replace('\\','').replace('\'','\\\'')
lines[3] = col + " = " + '\'' + lines[3] + '\'' + sep1
elif lines[3].find('INT meta') <> -1 and lines[3].find('NULL') == -1:
lines[3] = lines[3].split('/*')[0].strip()
lines[3] = col + " = " + lines[3].split()[0] + sep1
elif lines[3].find('NULL') <> -1:                                                              #和Delete脚本一样,不一样的是分隔符。
lines[3] = lines[3].split('/*')[0].strip()
lines[3] = col + sep3 + lines[3] + sep1
else:
lines[3] = lines[3].split('/*')[0].strip('\'').strip().strip('\'').replace('\\','').replace('\'','\\\'')
lines[3] = col + " = " + '\'' + lines[3].strip('\''' ') + '\'' + sep1
if  ''.join(lines).find('@') <> -1 and lines[3].split('=',1)[0] == num:
c = int(lines[3].split('=',1)[0].split('@')[1])
col = columns[c-1]
lines[3] = lines[3].split('=',1)[-1].strip()
if lines[3].strip('\'').strip().find('\'') <> -1:
lines[3] = lines[3].split('/*')[0].strip('\'').strip().strip('\'').replace('\\','').replace('\'','\\\'')
lines[3] = col + " = " + '\'' + lines[3] + '\'' + sep2
elif lines[3].find('INT meta') <> -1 and lines[3].find('NULL') == -1:
lines[3] = lines[3].split('/*')[0].strip()
lines[3] = col + " = " + lines[3].split()[0] + sep2
elif lines[3].find('NULL') <> -1:                                                              #和Delete脚本一样,不一样的是分隔符。
lines[3] = lines[3].split('/*')[0].strip()
lines[3] = col + sep3 + lines[3] + sep2
else:
lines[3] = lines[3].split('/*')[0].strip('\'').strip().strip('\'').replace('\\','').replace('\'','\\\'')
lines[3] = col + " = " + '\'' + lines[3].strip('\''' ') + '\'' + sep2
print ' '.join(lines[1:])
if lines == '':
break
if __name__=='__main__':
conn = MySQLdb.connect(host='localhost',user='root',passwd='123456',charset='utf8',db='test')
col = get_column(conn,sys.argv[2])
for column in col:
read_binlog(sys.argv[1],sys.argv[2],column)

执行脚本获取恢复语句(脚本执行格式: python 脚本名 binglog文本 表名)

 python aaa.py   log.txt sh_report_distribute_com > sh_report_distribute_com.sql

获取的恢复语句效果:

UPDATE `deal_report`.`sh_report_distribute_com`
SET
report_distribute_com_id = '0001bb0dd8ab33e9aadff2c65953da7e',
deal_report_id = 'abded7834001340fa2bf62fb4ec5665c',
com_person_type = 5355,
broker_id = 9849,
broker_name = 'xxxxx',
broker_type = 1,
broker_dept_id = 1581,
broker_dept_name = '万科学府七组',
broker_position_id = 1926,
broker_position_name = 'A2初级置业顾问',
business_manager_id = 7554,
business_manager_name = 'xxxxx',
business_manager_dept_id = 1581,
business_manager_dept_name = '万科学府七组',
area_manager_id = 3962,
area_manager_name = 'xxxxx',
area_manager_dept_id = 978,
area_manager_dept_name = 'xxxxx区',
majordomo_id = 1966,
majordomo_name = 'xxxxxxx',
majordomo_dept_id = 398,
majordomo_dept_name = 'xxxx区',
total_turnover = '38880.00',
business_distribute_rate = '0.1500',
distribute_rate = '0.1485',
expect_achievement = '5773.68',
com_rate = '0.2800',
is_broker_money = 1,
create_id = 9120,
created_at = 1518431210,
updated_at = 1519352161,
deleted_at = NULL,
business_manager_position_id = 8074,
business_manager_position_name = '新M1经理',
area_manager_position_id = 8004,
area_manager_position_name = 'R2项目经理',
majordomo_position_id = 2457,
majordomo_position_name = 'D2高级区域总监'
WHERE
report_distribute_com_id = '0001bb0dd8ab33e9aadff2c65953da7e'
and
deal_report_id = 'abded7834001340fa2bf62fb4ec5665c'
and
com_person_type = 5355
and
broker_id = 9849
and
broker_name = 'xxxxxxxx'
and
broker_type = 1
and
broker_dept_id = 1581
and
broker_dept_name = '万科学府七组'
and
broker_position_id = 1678
and
broker_position_name = 'A3中级置业顾问'
and
business_manager_id = 7554
and
business_manager_name = 'xxxxxxx'
and
business_manager_dept_id = 1581
and
business_manager_dept_name = '万科学府七组'
and
area_manager_id = 3962
and
area_manager_name = 'xxxxxxxx'
and
area_manager_dept_id = 978
and
area_manager_dept_name = 'xxxxxx区'
and
majordomo_id = 1966
and
majordomo_name = 'xxxxx'
and
majordomo_dept_id = 398
and
majordomo_dept_name = 'xxx片区'
and
total_turnover = '38880.00'
and
business_distribute_rate = '0.1500'
and
distribute_rate = '0.1485'
and
expect_achievement = '5773.68'
and
com_rate = '0.2800'
and
is_broker_money = 1
and
create_id = 9120
and
created_at = 1518431210
and
updated_at = 1519352161
and
deleted_at is NULL
and
business_manager_position_id = 8074
and
business_manager_position_name = '新M1经理'
and
area_manager_position_id = 8004
and
area_manager_position_name = 'R2项目经理'
and
majordomo_position_id = 2457
and
majordomo_position_name = 'D2高级区域总监';

5. 恢复数据
执行恢复SQL语句时,一定要注意字符集编码,要不然可能出现乱码,从而导致2次事故,恢复命令:

 mysql -u用户名 -h数据库IP --default-character-set=utf8 -p 数据库 < a.sql sh_report_distribute_com.sql

参考文献

  1. MySQL【Delete误操作】回滚
  2. MySQL【Update误操作】回滚

更多内容请关注公众号


来源:本文由云小秘原创撰写,欢迎分享本文,转载请保留出处和链接!

抢沙发

昵称*

邮箱*

网址