mysql sed_mysqlbinlog结合sed命令恢复数据

1、环境说明

使用mysqlbinlog搭配sed命令完美还原

MySQL版本号：5.6.X及5.7.X；

mysql必须开启binlog,并且mysql的binlog最好是Row模式;

mysql数据库指定字符集位utf8,同时表的字符集也得为utf8,否则在mysqlbinlog 解析出来的sql文件对于中文汉字的会出现乱码，导致最后恢复数据到线上的表中报错。

满足以上条件这样可以极大的保证数据恢复的几率。

当然把控好数据库的权限问题，禁止采用不加where条件的delete 和update语句，以及禁止采用drop,truncate才是从根源保证数据安全行之有效的办法。

查看当前的binlog文件：

show master status;

找到binllog文件路径下，再次之前需要确认一下你误操作的大概时间，因为我们要通过时间范围来搜索日志，执行命令如下：

mysqlbinlog  --base64-output=decode-rows  -v -v --start-datetime='2019-10-17 14:40:00' --stop-datetime='2019-10-17 14:50:00'  /data/mysql_data/binlog.000004 |grep -C 30 "UPDATE `test1`.`zx_scores`"

找到我们误操作的update 语句，记录下sql上面 # at 开头后面的数字4558 (这个标记应该是事务的行号)，OK，继续执行命令：

mysqlbinlog  --no-defaults  --base64-output=decode-rows  -v -v  /data/mysql_data/binlog.000004|sed -n '/# at 4558/,/COMMIT/p'  >/data/soft/update_test1.sql

将这串事务从# at 4558开始到COMMIT之间的行全部提取出来到/data/soft/update_test1.sql里。

到此处，我们已经拿到了需要还原的sql语句，根据导出的sql语句进行sed命令替换，还原到修改之前sql语句。

第一个sed命令作用：将where 和set位置对调

cp -a update_test1.sql update_test1_sed1.sql

sed -i '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' update_test1_sed1.sql

解释：

/WHERE/{:a;N;/SET/!ba;

: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.把/*往后的内容 替换成,

cp -a update_test1_sed1.sql update_test1_sed2.sql

sed -i 's/### //g;s/\/\*.*/,/g' update_test1_sed2.sql

解释：

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

\                #转义字符

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

第三个sed 命令作用: 把字符串包含@7的行中的全部(，)换成空格

cp -a update_test1_sed2.sql update_test1_sed3.sql

sed -i /@7/s/,//g update_test1_sed3.sql

解释：

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

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

g        #全部替换

第四个sed 命令作用: 1.把WHERE 至@7之间的所有逗号，替换成AND 2.#.* 就是把#在的行替换为空格 3.把匹配到的COMMIT, 替换为空格

cp -a update_test1_sed3.sql update_test1_sed4.sql

sed -i '/WHERE/{:a;N;/@7/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' update_test1_sed4.sql

解释：

/WHERE/{:a;N;/@7/!ba;s/,/AND/g}      #将WHERE至@7之间的行尾的(，)替换为(AND)

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

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

第五个sed 命令作用:删除所有的空行

cp -a update_test1_sed4.sql update_test1_sed5.sql

sed -i '/^$/d' update_test1_sed5.sql

解释：

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

d         #删除

第六个sed 命令作用:1.在where语句后@7最后一个字段加(;)2. 把@1，@2.....@6,@7替换为对应的zx_scores表的列名

cp -a update_test1_sed5.sql update_test1_sed6.sql

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

sed -i 's/@1/id/g;s/@2/titles/g;s/@3/icon/g;s/@4/integral/g;s/@5/isdefault/g;s/@6/create_time/g;s/@7/day/g' update_test1_sed6.sql

数据格式化

cat update_test1_sed6.sql |tr "\n" " "  >update_test1_tr1.sql

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

cp -a update_test1_tr1.sql update_test1_sed7.sql

sed -i 's/\;/ LIMIT 1\;\n/g' update_test1_sed7.sql

在每一个；前面加上 LIMIT 1，后面加上换行符

恢复到MySQL

到此处mysqlbinlog结合sed命令恢复数据库数据介绍完毕。