首页 > 代码库 > MySQL【Update误操作】回滚（转)

MySQL【Update误操作】回滚（转)

2024-08-18 17:32:54   222人阅读

前言：
      继上一篇MySQL【Delete误操作】回滚之后，现在介绍下Update回滚，操作数据库时候难免会因为“大意”而误操作，需要快速恢复的话通过备份来恢复是不太可能的，因为需要还原和binlog差来恢复，等不了，很费时。这里说明因为Update 操作的恢复方法：主要还是通过binlog来进行恢复，前提是binlog_format必须是Row格式，否则只能通过备份来恢复数据了。和上一篇的条件一样。
方法：
     条件：开启Binlog，Format为Row。
     步骤：
1.通过MySQL自带工具mysqlbinlog 指定导出操作的记录：
表结构和记录数:

技术分享
root@localhost : test 10:06:16>select count(*) from me_info;+----------+| count(*) |+----------+|    84183 |+----------+1 row in set (0.00 sec)root@localhost : test 10:12:14>select id,realName,contactAddress from me_info limit 3;+---------+--------------------------+--------------------+| id      | realName                 | contactAddress     |+---------+--------------------------+--------------------+| 2123269 | 数据库管理员             | 浙江杭州滨江       || 2123270 | 中级数据库管理员         | 浙江杭州西湖       || 2123271 | 高级数据库管理员         | 浙江杭州余杭       |+---------+--------------------------+--------------------+root@localhost : test 10:12:18>desc me_info;+-----------------+--------------+------+-----+---------+-------+| Field           | Type         | Null | Key | Default | Extra |+-----------------+--------------+------+-----+---------+-------+| id              | int(11)      | NO   | MUL | 0       |       || birthTime       | date         | YES  |     | NULL    |       || enName          | varchar(255) | YES  |     | NULL    |       || gender          | tinyint(2)   | YES  |     | 0       |       || identity        | varchar(255) | YES  |     |         |       || identitylType   | tinyint(2)   | YES  |     | 0       |       || interest        | varchar(255) | YES  |     |         |       || jobYear         | int(11)      | YES  |     | 0       |       || livePlace       | mediumint(6) | YES  |     | 0       |       || location        | mediumint(6) | YES  |     | 0       |       || married         | tinyint(2)   | YES  |     | 0       |       || mdCerti         | tinyint(2)   | YES  |     | 0       |       || mdCertiNum      | varchar(255) | YES  |     |         |       || photo           | varchar(255) | YES  |     |         |       || posit           | mediumint(6) | YES  |     | NULL    |       || graduateMajor   | int(11)      | YES  |     | 0       |       || realName        | varchar(255) | YES  |     | NULL    |       || userPublic      | tinyint(2)   | YES  |     | NULL    |       || email           | varchar(255) | YES  |     | NULL    |       || contactCell     | varchar(255) | YES  |     | NULL    |       || contactPhone    | varchar(255) | YES  |     | NULL    |       || contactZip      | varchar(6)   | YES  |     | NULL    |       || contactWebsite  | varchar(255) | YES  |     | NULL    |       || contactLocation | mediumint(6) | YES  |     | 0       |       || contactAddress  | varchar(255) | YES  |     | NULL    |       || userName        | varchar(50)  | YES  |     | NULL    |       || education       | int(11)      | YES  |     | 0       |       || workName        | varchar(255) | YES  |     | NULL    |       || workCategory    | varchar(255) | YES  |     | NULL    |       || nowSalary       | int(11)      | YES  |     | 0       |       || grade           | int(11)      | YES  |     | 0       |       || userId          | int(11)      | YES  |     | 0       |       || jobApplyStatus  | tinyint(4)   | YES  |     | NULL    |       || source          | tinyint(4)   | YES  |     | NULL    |       || englishLevel    | tinyint(4)   | YES  |     | 0       |       || modifyTime      | datetime     | YES  |     | NULL    |       |+-----------------+--------------+------+-----+---------+-------+

更新表:

技术分享
root@localhost : test 10:15:09>update me_info set realName=‘周吴郑王‘,contactAddress=‘浙江vv杭州vv北京‘;Query OK, 84183 rows affected (1.56 sec)Rows matched: 84183  Changed: 84183  Warnings: 0root@localhost : test 11:11:08>select id,realName,contactAddress from me_info limit 3;+---------+--------------+------------------------+| id      | realName     | contactAddress         |+---------+--------------+------------------------+| 2123269 | 周吴郑王     | 浙江vv杭州vv北京       || 2123270 | 周吴郑王     | 浙江vv杭州vv北京       || 2123271 | 周吴郑王     | 浙江vv杭州vv北京       |+---------+--------------+------------------------+3 rows in set (0.00 sec)

最后通过mysqlbinlog 取出:

root@zhoujy:/var/log/mysql# mysqlbinlog --no-defaults --start-datetime=‘2012-12-26 22:15:05‘ --stop-datetime=‘2012-12-26 22:17:00‘  -vv mysql-bin.000001 > /home/zhoujy/restore/me_info.txt

原始数据：

技术分享
### UPDATE test.me_info### WHERE###   @1=2123269 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘1990:11:12‘ /* DATE meta=0 nullable=1 is_null=0 */###   @3=NULL /* DATE meta=765 nullable=1 is_null=1 */###   @4=2 /* TINYINT meta=0 nullable=1 is_null=0 */###   @5=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @6=0 /* TINYINT meta=0 nullable=1 is_null=0 */###   @7=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */###   @9=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */###   @10=340800 /* MEDIUMINT meta=0 nullable=1 is_null=0 */###   @11=1 /* TINYINT meta=0 nullable=1 is_null=0 */###   @12=0 /* TINYINT meta=0 nullable=1 is_null=0 */###   @13=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @14=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @15=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */###   @16=32071 /* INT meta=0 nullable=1 is_null=0 */###   @17=‘数据库管理员‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @18=NULL /* VARSTRING(765) meta=0 nullable=1 is_null=1 */###   @19=‘123456@qq.comx0a‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @20=‘123456‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @21=‘0571-123456‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @22=NULL /* VARSTRING(765) meta=18 nullable=1 is_null=1 */###   @23=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @24=340100 /* MEDIUMINT meta=0 nullable=1 is_null=0 */###   @25=‘浙江杭州滨江‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @26=NULL /* VARSTRING(765) meta=150 nullable=1 is_null=1 */###   @27=1 /* INT meta=0 nullable=1 is_null=0 */###   @28=NULL /* INT meta=765 nullable=1 is_null=1 */###   @29=NULL /* INT meta=765 nullable=1 is_null=1 */###   @30=0 /* INT meta=0 nullable=1 is_null=0 */###   @31=0 /* INT meta=0 nullable=1 is_null=0 */###   @32=1700671 /* INT meta=0 nullable=1 is_null=0 */###   @33=NULL /* INT meta=0 nullable=1 is_null=1 */###   @34=3 /* TINYINT meta=0 nullable=1 is_null=0 */###   @35=0 /* TINYINT meta=0 nullable=1 is_null=0 */###   @36=NULL /* TINYINT meta=0 nullable=1 is_null=1 */### SET###   @1=2123269 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘1990:11:12‘ /* DATE meta=0 nullable=1 is_null=0 */###   @3=NULL /* DATE meta=765 nullable=1 is_null=1 */###   @4=2 /* TINYINT meta=0 nullable=1 is_null=0 */###   @5=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @6=0 /* TINYINT meta=0 nullable=1 is_null=0 */###   @7=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */###   @9=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */###   @10=340800 /* MEDIUMINT meta=0 nullable=1 is_null=0 */###   @11=1 /* TINYINT meta=0 nullable=1 is_null=0 */###   @12=0 /* TINYINT meta=0 nullable=1 is_null=0 */###   @13=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @14=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @15=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */###   @16=32071 /* INT meta=0 nullable=1 is_null=0 */###   @17=‘周吴郑王‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @18=NULL /* VARSTRING(765) meta=0 nullable=1 is_null=1 */###   @19=‘123456@qq.comx0a‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @20=‘123456‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @21=‘0571-123456‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @22=NULL /* VARSTRING(765) meta=18 nullable=1 is_null=1 */###   @23=‘‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @24=340100 /* MEDIUMINT meta=0 nullable=1 is_null=0 */###   @25=‘浙江vv杭州vv北京‘ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */###   @26=NULL /* VARSTRING(765) meta=150 nullable=1 is_null=1 */###   @27=1 /* INT meta=0 nullable=1 is_null=0 */###   @28=NULL /* INT meta=765 nullable=1 is_null=1 */###   @29=NULL /* INT meta=765 nullable=1 is_null=1 */###   @30=0 /* INT meta=0 nullable=1 is_null=0 */###   @31=0 /* INT meta=0 nullable=1 is_null=0 */###   @32=1700671 /* INT meta=0 nullable=1 is_null=0 */###   @33=NULL /* INT meta=0 nullable=1 is_null=1 */###   @34=3 /* TINYINT meta=0 nullable=1 is_null=0 */###   @35=0 /* TINYINT meta=0 nullable=1 is_null=0 */###   @36=NULL /* TINYINT meta=0 nullable=1 is_null=1 */

Row格式的binlog记录的格式如上面所示，需要做的工作就是把Update的操作的WHERE好SET对调，上面的都是有一定规律的，并且需要注意的是：
除了MySQL【Delete误操作】回滚事项外，还有
①：需要把@表示的“虚列”换成“实列”。
②：更新NULL值的时候，WHERE 后面的字段有NULL的，不能用“=”号，需要用“is”。
清楚里之后，可以用脚本来还原刚才update的值：

技术分享
#!/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 MySQLdbimport sysreload(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 itemsdef 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 == ‘‘:            breakif __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 脚本名 binlog文本 表名

zhoujy@zhoujy:~/restore$ python restore_update.py me_info.txt me_info > me_info.sql

效果:

技术分享
PDATE test.me_infoSET  id = 2123269,  birthTime = ‘1990:11:12‘,  enName = NULL,  gender = 2,  identity = ‘‘,  identitylType = 0,  interest = ‘‘,  jobYear = -1,  livePlace = 0,  location = 340800,  married = 1,  mdCerti = 0,  mdCertiNum = ‘‘,  photo = ‘‘,  posit = 0,  graduateMajor = 32071,  realName = ‘数据库管理员‘,  userPublic = NULL,  email = ‘123456@qq.comx0a‘,  contactCell = ‘123456‘,  contactPhone = ‘0571-123456‘,  contactZip = NULL,  contactWebsite = ‘‘,  contactLocation = 340100,  contactAddress = ‘浙江杭州滨江‘,  userName = NULL,  education = 1,  workName = NULL,  workCategory = NULL,  nowSalary = 0,  grade = 0,  userId = 1700671,  jobApplyStatus = NULL,  source = 3,  englishLevel = 0,  modifyTime = NULLWHERE  id = 2123269and  birthTime = ‘1990:11:12‘and  enName is NULLand  gender = 2and  identity = ‘‘and  identitylType = 0and  interest = ‘‘and  jobYear = -1and  livePlace = 0and  location = 340800and  married = 1and  mdCerti = 0and  mdCertiNum = ‘‘and  photo = ‘‘and  posit = 0and  graduateMajor = 32071and  realName = ‘周吴郑王‘and  userPublic is NULLand  email = ‘123456@qq.comx0a‘andand  contactCell = ‘123456‘and  contactPhone = ‘0571-123456‘and  contactZip is NULLand  contactWebsite = ‘‘and  contactLocation = 340100and  contactAddress = ‘浙江vv杭州vv北京‘and  userName is NULLand  education = 1and  workName is NULLand  workCategory is NULLand  nowSalary = 0and  grade = 0and  userId = 1700671and  jobApplyStatus is NULLand  source = 3and  englishLevel = 0and  modifyTime is NULL;

还原:

zhoujy@zhoujy:~/restore$ mysql test < me_info.sql

结果，表结果和记录数：

技术分享
root@localhost : test 11:11:14>select count(*) from me_info;+----------+| count(*) |+----------+|    84183 |+----------+1 row in set (0.00 sec)root@localhost : test 11:12:36>select id,realName,contactAddress from me_info limit 3;+---------+--------------------------+--------------------+| id      | realName                 | contactAddress     |+---------+--------------------------+--------------------+| 2123269 | 数据库管理员             | 浙江杭州滨江       || 2123270 | 中级数据库管理员         | 浙江杭州西湖       || 2123271 | 高级数据库管理员         | 浙江杭州余杭       |+---------+--------------------------+--------------------+3 rows in set (0.00 sec)

总结:

【更新于 20160504】

开启Row模式的回滚操作，特别要注意表字符集的问题，保证表的字符集一致，否则出现乱码问题，如：

表结构：CREATE TABLE `tmp_1` (  `id` int(11) DEFAULT NULL,  `name` varchar(10) DEFAULT NULL,  `address` varchar(20) CHARACTER SET gbk DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8操作：insert into tmp_1 values(1,‘UTF8字符串‘, ‘GKB字符串‘);insert into tmp_1 values(2,‘我们a‘, ‘你们a‘);binlog记录：‘/*!*/;### INSERT INTO `dba_test`.`tmp_1`### SET###   @1=1 /* INT meta=0 nullable=1 is_null=0 */###   @2=‘UTF8字符串‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */###   @3=‘GKB?????‘ /* VARSTRING(40) meta=40 nullable=1 is_null=0 */   #出现乱码，复制是没有问题的，要是反转回滚就会出现乱码！

更多的信息见：

http://www.gpfeng.com/?p=259

MySQL【Update误操作】回滚（转)


联系
我们