侧边栏壁纸
博主头像
落叶人生博主等级

走进秋风,寻找秋天的落叶

  • 累计撰写 143987 篇文章
  • 累计创建 28 个标签
  • 累计收到 9 条评论
标签搜索

目 录CONTENT

文章目录

mysqlbinlog结合sed命令恢复数据

2024-04-03 星期三 / 0 评论 / 0 点赞 / 4 阅读 / 6891 字

1、环境说明使用mysqlbinlog搭配sed命令完美还原MySQL版本号:5.6.X及5.7.X;mysql必须开启binlog,并且mysql的binlog最好是Row模式;mysql数据库指定

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命令恢复数据库数据介绍完毕。

广告 广告

评论区