0%

记一次阿里云更新回滚操作

由于执行更新update语句时,没有选中where语句,导致全表更新,万幸的是该表只是操作记录表,数据量也不大。

数据库日志格式

通过日志恢复数据的日志格式必须是Row。

下载binlog

通过阿里云管理界面下载binlog时间段内的文件

解析binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysqlbinlog -vv --base64-output=decode-rows mysql-bin.004489 | grep -B 15 '此处替换成要搜索的关键字'

参数说明:
-v, --verbose
用于输出基于row模式的binlog日志,-vv为列数据类型添加注释
--base64-output=decode-rows
解码binlog里经过base64编码的内容

-A 15 : 输出符合查询条件的日志后的15行

-B 15 : 输出符合查询条件的日志前的15行


mysqlbinlog -vv --base64-output=decode-rows mysql-bin.004489 | sed -n '/# at 244950283/,/COMMIT/p' > update.log

注:

如果通过mysqlbinlog解析binlog时遇到类似如下提示:

1
2
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 151, event_type: 35
ERROR: Could not read entry at offset 120: Error in log format or read error.

请用户检查使用的mysqlbinlog是否版本较低,比如使用3.3版本会遇到上述错误提示无法正常解析binlog日志,使用较高版本,如3.4版本可以正常查看,这种情况下用户可以使用较高版本的mysqlbinlog

将binlog转换成SQL语句

1
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 3.log | sed 's/### //g;s/\/\*.*/,/g' | sed  /@10/s/,//g | sed '/WHERE/{:a;N;/@10/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d'  >  rollback.sql

注:

@3 为最后一个变量。如果有20个变量,则此处为@20。
@2 此处#与@之前空格数为3个,不能多不能少。

分段解析

第一个sed 命令:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' update.sql

功能:将where 和set未知对调

/WHERE/ #包含WHERE

:a; #创建一个labela;

N; #追加下一个输入行到读取行的末尾,读入到模式空间

/SET/!ba; # 如果不是/SET/,返回a,也就是重复读,一直读到/SET/之前(buffer的内容是WHERE\n.......\nSET)

s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/ 这块可以分三部分来读

第1步:
s #替换命令,例如s/a/b 将a替换为b

第2步:
\([^\n]*\)\n\(.*\)\n\(.*\)

\ #转义字符

[^\n]* == buffer中的where

(.*\) #单符号(.)匹配除换行符以外的单个字符,*同上;

[^\n]*\ #代表非换行符(回车)开头,*表示匹配零或多个字符

\n #换行

第3步:
\3\n\2\n\1

\3 == 内存中的set,第三个括号中的内容

\2 == 内存中原来where与set之间的内容,第二个括号中的内容

\1 == 内存中的where,第一个括号中的内容

第二个sed 命令:

1
2
3
4
5
6
7
8
9
sed 's/### //g;s/\/\*.*/,/g'

功能:这句做了两个事情1.把字符串### 替换成 空格 2.把/*往后的内容 替换成,

s/### //g #将### 替换成空串,

\ #转义字符

\/\*.* #匹配/*之后出换行符外所有内容

第三个sed 命令:

1
2
3
4
5
6
7
8
9
sed  /@10/s/,//g

功能:这句把字符串包含@3的行中的全部(,)换成空格

/@10/ #匹配包含@10的行

s/,// #将,替换为空串

g #全部替换

第四个sed 命令:

1
2
3
4
5
6
7
8
9
sed '/WHERE/{:a;N;/@3/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g'

功能:这句做了三件事 1.就是把WHERE 至@3之间的所有逗号,替换成分号 AND 2.#.* 就是把#在的行替换为空格 3.就是把匹配到的COMMIT, 替换为空格

/@1/!ba;s/,/;/g #将@1及之前的行尾的(,)替换为(;)

s/#.*//g #将#号开头的整行字符替换为空串。

s/COMMIT,//g #将(COMMIT,)替换为空行;

第五个sed 命令:

1
2
3
4
5
6
7
8
9
10
sed '/^$/d' > rollback.sql

功能:删除缓存中所有的空行。

/^$/ #查找缓存内容中所有的空行

d #删除

> rollback.sql #输出缓存中的内容到rollback.sql

将rollback.sql中的where语句后加(;)

如果后执行这句请将@10换成对应的列名即可

1
sed  -i -r  '/WHERE/{:a;N;/@3/!ba;s/(@3=.*)/\1\;/g}' rollback.sql

将@1,@2,@3…列转换为对应的列名

1
sed -i 's/@1/f_id/g;s/@2/f_outing_id/g;s/@3/f_outing_date_id/;s/@4/f_order_id/;s/@5/f_order_member_id/;s/@6/f_operater_type/;s/@7/f_user_id/;s/@8/f_system_user_id/;s/@9/f_remark/;s/@10/f_create_time/g' rollback.sql

数据格式化

将所有的换行替换成空格,此处用tr命令,因我的数据量比较大,tr执行效率相对较高,也可以用sed命令sed -i ‘:label;N;s/\n/ /;b label’ rollback.sql,效果都是一样的。

1
2
cat rollback.sql | tr  "\n" " " > new_rollback.sql