MySQL【Update误操作】回滚
前言：
继上一篇MySQL【Delete误操作】回滚之后，现在介绍下Update回滚，操作数据库时候难免会因为“大意”而误操作，需要快速恢复的话通过备份来恢复是不太可能的，因为需要还原和binlog差来恢复，等不了，很费时。这里说明因为Update 操作的恢复方法：主要还是通过binlog来进行恢复，前提是binlog_format必须是Row格式，否则只能通过备份来恢复数据了。和上一篇的条件一样。
方法：
条件：开启Binlog，Format为Row。
步骤：
1.通过MySQL自带工具mysqlbinlog 指定导出操作的记录：
表结构和记录数:
View Code
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 | | +-----------------+--------------+------+-----+---------+-------+
更新表:
View Code
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: 0 root@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
原始数据：
View Code
### 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的值：
View Code
#!/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 脚本名 binlog文本 表名
zhoujy@zhoujy:~/restore$ python restore_update.py me_info.txt me_info > me_info.sql
效果:
View Code
PDATE test.me_info SET 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 = NULL WHERE id = 2123269 and birthTime = '1990:11:12' and enName is NULL and gender = 2 and identity = '' and identitylType = 0 and interest = '' and jobYear = -1 and livePlace = 0 and location = 340800 and married = 1 and mdCerti = 0 and mdCertiNum = '' and photo = '' and posit = 0 and graduateMajor = 32071 and realName = '周吴郑王' and userPublic is NULL and email = '123456@qq.comx0a' and and contactCell = '123456' and contactPhone = '0571-123456' and contactZip is NULL and contactWebsite = '' and contactLocation = 340100 and contactAddress = '浙江vv杭州vv北京' and userName is NULL and education = 1 and workName is NULL and workCategory is NULL and nowSalary = 0 and grade = 0 and userId = 1700671 and jobApplyStatus is NULL and source = 3 and englishLevel = 0 and modifyTime is NULL;
还原:
zhoujy@zhoujy:~/restore$ mysql test < me_info.sql
结果，表结果和记录数：
View Code
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 */ #出现乱码，复制是没有问题的，要是反转回滚就会出现乱码！
更多的信息见：
~~~~~~~~~~~~~~~
万物之中,希望至美
~~~~~~~~~~~~~~~
