请选择 进入手机版 | 继续访问电脑版
搜索
房产
装修
汽车
婚嫁
健康
理财
旅游
美食
跳蚤
二手房
租房
招聘
二手车
教育
茶座
我要买房
买东西
装修家居
交友
职场
生活
网购
亲子
情感
龙城车友
找美食
谈婚论嫁
美女
兴趣
八卦
宠物
手机

「删库跑路」使用Binlog日志恢复误删的MySQL数据

[复制链接]
查看: 19|回复: 0

5440

主题

4872

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
14646
发表于 2020-1-14 01:00 | 显示全部楼层 |阅读模式
我的关键词 「删库跑路」使用Binlog日志恢复误删的MySQL数据  qq签名


前言

“删库跑路”是步伐员经常谈起的话题,本日,我就要教大家怎样删!库!跑!路!
我的关键词 「删库跑路」使用Binlog日志恢复误删的MySQL数据  qq签名


开个玩笑,本日文章的主题是怎样操纵Mysql内置的Binlog日志对误删的数据举行规复,读完本文,你可以也许了解到:

  • MySQL的binlog日志是什么?凡是是用来干什么的?
  • 模拟一次误删数据的操纵,而且操纵binlog日志规复误删的数据。
写这篇文章的初衷,是有一次我真的几乎把测试数据库的一张表给删除了,那时吓出一身冷汗。原因原由是由于Spring JPA的设备中,有一个spring.jpa.properties.hibernate.hbm2ddl.auto=create-drop,其用处是每次加载hibernate时按照model类天生表,可是sessionFactory一封闭,表就自动删除。,这个可不能肆意设备上去,间接就把你本来存在的表给drop了!
好了,回归正题,这篇文章就是想让大家安心,MySQL就算举行了误删操纵,也底子都可以也许救济归来。特别是至公司内,数据可不是你想删就能删掉的,有无数权限/备份劝止着你。
正文

Binlog先容

binlog是记载全数数据库表结构变更(例如CREATE、ALTER TABLE…)以及表数据点窜(INSERT、UPDATE、DELETE…)的二进制日志。
binlog不会记载SELECT和SHOW这类操纵,由于这类操为难刁难数据自己并没有点窜,但你可以经过查询通用日志来检察MySQL尝试过的全数语句。
看了上面binlog的界说,大家也应当能大略推理出binlog的三大用处:

  • 规复数据:本日要说的重点
  • 数据库复制:主从数据库是经过将binlog传给从库,从库有两个线程,一个I/O线程,一个SQL线程,I/O线程读取主库传过来的binlog内容并写入到relay log,SQL线程从relay log里面读取内容,写入从库的数据库。
  • 审计:用户可以经过二进制日志中的信息来举行审计,判定能否有对数据库举行注入进犯。
所以说,想要可以也许规复数据,首先,你得翻开Mysql的binlog,在平常你自己安装的单机Mysql中,默许情况下不会开启。下面就一步步地理论下怎样开启你办事器上的Binlog日志。
在MySQL中开启Binlog

首先辈入数据库控制台,运转指令:
mysql>showvariableslike'log_bin%';+---------------------------------+-------+|Variable_name|Value|+---------------------------------+-------+|log_bin|OFF||log_bin_basename|||log_bin_index|||log_bin_trust_function_creators|OFF||log_bin_use_v1_row_events|OFF|+---------------------------------+-------+5rowsinset(0.00sec)可以看到我们的binlog是封闭的,都是OFF。接下来我们必要点窜Mysql设备文件,尝试命令:
sudovi/etc/mysql/mysql.conf.d/mysqld.cnf在文件末端增加:
log-bin=/var/lib/mysql/mysql-bin保存文件,重启mysql办事:
sudoservicemysqlrestart重启完成后,检察下mysql的状态:
systemctlstatusmysql.service这时,假如你的mysql版本在5.7或更高版本,就会报错:
Jan0615:49:58VM-0-11-ubuntumysqld[5930]:2020-01-06T07:49:58.190791Z0[Warning]Changedlimits:max_open_files:1024(requested5000)Jan0615:49:58VM-0-11-ubuntumysqld[5930]:2020-01-06T07:49:58.190839Z0[Warning]Changedlimits:table_open_cache:431(requested2000)Jan0615:49:58VM-0-11-ubuntumysqld[5930]:2020-01-06T07:49:58.359713Z0[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seJan0615:49:58VM-0-11-ubuntumysqld[5930]:2020-01-06T07:49:58.361395Z0[Note]/usr/sbin/mysqld(mysqld5.7.28-0ubuntu0.16.04.2-log)startingasprocess5930...Jan0615:49:58VM-0-11-ubuntumysqld[5930]:2020-01-06T07:49:58.363017Z0[ERROR]Youhaveenabledthebinarylog,butyouhaven'tprovidedthemandatoryserver-id.PleaserefertotheproperserverJan0615:49:58VM-0-11-ubuntumysqld[5930]:2020-01-06T07:49:58.363747Z0[ERROR]AbortingJan0615:49:58VM-0-11-ubuntumysqld[5930]:2020-01-06T07:49:58.363922Z0[Note]BinlogendJan0615:49:58VM-0-11-ubuntumysqld[5930]:2020-01-06T07:49:58.364108Z0[Note]/usr/sbin/mysqld:ShutdowncompleteJan0615:49:58VM-0-11-ubuntusystemd[1]:mysql.service:Mainprocessexited,code=exited,status=1/FAILUREYou have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server
之前我们的设备,对于5.7以下版本应当是可以的。但对于高版本,我们必要指定server-id。
假如你不是散布式的安排Mysql,这个server-id随机给个数字便可以。
server-id=123454模拟删除数据并规复


  • 首先新建数据库mytest,新建一张表table1,结构见下方SQL代码
CREATEDATABASE`test`;USE`test`;DROPTABLEIFEXISTS`table1`;CREATETABLE`table2`(`id`int(11)DEFAULTNULL,`name`varchar(20)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

  • 插入两条数据,别离是 (1,'A'),(2,'B')
INSERTINTO`table1`VALUES(1,'A'),(2,'B');

  • 我们看一下binlog日志的状态,操纵show master status
mysql>showmasterstatus->;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000001|690||||+------------------+----------+--------------+------------------+-------------------+1rowinsetbinlog日志特征:每当我们重启MySQL一次,会自动天生一个binlog文件,固然,我们也可以手动的来革新binlog文件,经过 flush logs,一样会新建立一个binlog文件。现实被骗办事器在重启时,也会挪用flush logs操纵。
上图代码中可以看到,现在我们正在操纵 mysql-bin.0000001 ,而且这个文件现在正在记载到690行。

  • 然后,操纵flush logs来自动革新一次binlog
mysql>flushlogs;QueryOK,0rowsaffectedmysql>showmasterstatus->;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000002|154||||+------------------+----------+--------------+------------------+-------------------+1rowinset可以看到,如本日志文件在 mysql-bin.000002 文件中,位置为154。也就是我们自动革新了一次binlog,天生了新的000002,而000001则已经归档了,不会再写入新的日志进去了。

  • 接下来我们在插入两条数据
insertintotable1values(3,'C');insertintotable1values(4,'D');mysql>select*fromtable1;+----+----+|id|name|+----+----+|1|A||2|B||3|C||4|D|+----+----+

  • 这时候我们已经有了四条数据,我们再次flush logs,把mysql-bin.000002日志存档,开启新的mysql-bin.000003日志,这样,每次我们插入的数据相互自力。现真相况下,binlog会比力复杂,这里也是做了简化,为了明白更方便。
mysql>flushlogs;QueryOK,0rowsaffectedmysql>showmasterstatus;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000003|154||||+------------------+----------+--------------+------------------+-------------------+1rowinset

  • 然后我们删除id为4的数据(4,D),而且再次革新binlog,如此一来,binlog.000003里面只要一条删除操纵。
mysql>deletefromtable1whereid=4;QueryOK,1rowaffectedmysql>showmasterstatus;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000003|423||||+------------------+----------+--------------+------------------+-------------------+1rowinsetmysql>flushlogs;QueryOK,0rowsaffectedmysql>showmasterstatus;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000004|154||||+------------------+----------+--------------+------------------+-------------------+1rowinset

  • 让我们来好好观察下mysql-bin.00002和mysql-bin00003两个binlog,操纵命令:show binlog events in 'mysql-bin.000003'
mysql>showbinlogeventsin'mysql-bin.000003';+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+|mysql-bin.000003|4|Format_desc|123456|123|Serverver:5.7.28-0ubuntu0.16.04.2-log,Binlogver:4||mysql-bin.000003|123|Previous_gtids|123456|154|||mysql-bin.000003|154|Anonymous_Gtid|123456|219|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000003|219|Query|123456|293|BEGIN||mysql-bin.000003|293|Table_map|123456|343|table_id:108(test.table1)||mysql-bin.000003|343|Delete_rows|123456|392|table_id:108flags:STMT_END_F||mysql-bin.000003|392|Xid|123456|423|COMMIT/*xid=39*/|+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+7rowsinsetmysql>showbinlogeventsin'mysql-bin.000002';+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+|mysql-bin.000002|4|Format_desc|123456|123|Serverver:5.7.28-0ubuntu0.16.04.2-log,Binlogver:4||mysql-bin.000002|123|Previous_gtids|123456|154|||mysql-bin.000002|154|Anonymous_Gtid|123456|219|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000002|219|Query|123456|293|BEGIN||mysql-bin.000002|293|Table_map|123456|343|table_id:108(test.table1)||mysql-bin.000002|343|Write_rows|123456|390|table_id:108flags:STMT_END_F||mysql-bin.000002|390|Xid|123456|421|COMMIT/*xid=34*/||mysql-bin.000002|421|Anonymous_Gtid|123456|486|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000002|486|Query|123456|560|BEGIN||mysql-bin.000002|560|Table_map|123456|610|table_id:108(test.table1)||mysql-bin.000002|610|Write_rows|123456|659|table_id:108flags:STMT_END_F||mysql-bin.000002|659|Xid|123456|690|COMMIT/*xid=35*/||mysql-bin.000002|690|Rotate|123456|737|mysql-bin.000003;pos=4|+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+13rowsinset固然有很多看似复杂的指令,可是照旧不丢脸出,在02里,有两条写操纵,03里有一条删除操纵。
一条插入操纵的完全日志是这样:
|mysql-bin.000002|154|Anonymous_Gtid|123456|219|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000002|219|Query|123456|293|BEGIN||mysql-bin.000002|293|Table_map|123456|343|table_id:108(test.table1)||mysql-bin.000002|343|Write_rows|123456|390|table_id:108flags:STMT_END_F||mysql-bin.000002|390|Xid|123456|421|COMMIT/*xid=34*/|

  • 我们的目标是规复误删的数据,实在就是将binlog.000002日志的两条插入记载重演一遍,而不必要取分析binlog.000003的操纵(由于删除是一个误操纵)
所以现在能明白为什么我们频仍革新binlog了吧,固然,在现实的线上情况中,我们必定必要将binlog导出后,细致挑选出误操纵,并将其断根,以后再运转binlog。
在本文中,我们只做一个规复两条插入语句的操纵,尝试语句:
sudomysqlbinlog/var/lib/mysql/mysql-bin.000002--start-position154--stop-position690|mysql-uroot-pmytest留意:这里填写的途径/var/lib/mysql/mysql-bin.000002必要具体到你的binlog目录,网上大部分文章只写到mysql-bin.000002,假如你不在目录里,mysqlbinlog命令并不会自动定位binlog地点途径。
参数描摹:
--start-datetime:从二进制日志中读取指定即是时候戳大要晚于当地盘算机的时候--stop-datetime:从二进制日志中读取指定小于时候戳大要即是当地盘算机的时候取值和上述一样--start-position:从二进制日志中读取指定position事变位置作为起头。--stop-position:从二进制日志中读取指定position事变位置作为事变停止尝试乐成后,再次检察表table1,可以看到两条新的id=3和4的数据被插入了进来。规复乐成了。
mysql>select*fromtable1;+----+----+|id|name|+----+----+|1|A||2|B||3|C||3|C||4|D|+----+----+6rowsinset延长思考

Binlog在什么情况下无律例复数据?
结语

删库跑路不用怕,其他开辟运维都等着规复你的数据呢,多好的练手机遇能否是。
固然,看完binlog日志规复数据的道理,渴望大家今后在定期备份数据库的剧本里,也可以也许加上革新binlog日志的命令,这样一旦某天丧失数据,可以将当天binlog数据零丁拿出来复原,做到清楚可辨,也加速规复服从。
参考

https://www.cnblogs.com/rjzheng/p/9721765.html
https://blog.csdn.net/king_kgh/article/details/74890381
https://www.jianshu.com/p/564fcc2b5e31
https://blog.csdn.net/king_kgh/article/details/74833539
关注我

我是一位后端开辟工程师。
垂危关注后端开辟,数据平安,爬虫,物联网,边沿盘算等偏向,接待交换。
各大平台都可以找到我


  • 微信公众号:后端技术漫谈
  • Github:@qqxx6661
  • CSDN:@后端技术漫谈
  • 知乎:@后端技术漫谈
  • 简书:@后端技术漫谈
  • 掘金:@后端技术漫谈
原创博客垂危内容


  • 后端开辟相关技术文章
  • Java口试常识点温习全手册
  • 计划形式/数据结构
  • Leetcode/剑指offer 算法题分析
  • SpringBoot/SpringCloud 入门实战系列
  • 爬虫相关技术文章
  • 逸闻趣事/好书分享/小我爱好
小我公众号:后端技术漫谈

我的关键词 「删库跑路」使用Binlog日志恢复误删的MySQL数据  qq签名


公众号:后端技术漫谈.jpg
假如文章对你有帮助,无妨收藏,投币,转发,在看起来~

免责声明:假如加害了您的权益,请联系站长,我们会实时删除侵权内容,感谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Copyright © 2006-2014 qq友乐园-q友乐园情侣头像-qq头像-男生头像-女生头像-网名-扣扣个性签名 版权所有 法律顾问:高律师 客服电话:0791-88289918
技术支持:迪恩网络科技公司  Powered by Discuz! X3.2
快速回复 返回顶部 返回列表