-
在误操作数据以后,如果没有备份,还可以使用binlog的方式对数据进行还原,并不是执行了回滚操作,而是对执行的记录进行提取,将提取的数据再插入到表中,实现数据的还原,这个还原是有一定条件的,我们要理解实施原理:
通常对这类操作有两种类型:
- 通过生成的sql语句将数据还原,这种方式是有一定的条件限制,比如我们将数据库恢复到某一个点,这个点之后到误操作之前的所有sql重新跑一遍,如果备份的时间点与误操作时间点中间有binlog未记录上的操作,则这种方式可能会执行不成功
- 通过生成的文件提取误操作的内容,然后生成批量插入或者更新语句,将数据进行还原
-
具体操作的方式
在执行了误操作以后,立马停止其他操作,进行binlog还原
首先是确定binlog的格式,binlog有三种格式
- Statement格式,此种格式会记录执行的sql,并不会记录每一行的变化,极大的减少了日志量
- row模式,会记录执行的sql以及每一行的数据变化,如果binlog是这种模式,肯定可以对数据进行恢复
- Mixed模式,是statement和row模式的混合体,由系统判断使用哪种模式
-
首先确定一下我们是否开启了binlog,执行结果第一行是否开启binlog,第二行binlog的位置
show variables like '%log_bin%';
-
查询使用哪种binlog格式
show variables like '%binlog_format%';
-
查询所有的binlog文件
show binary logs
-
查询一下当前使用的哪个binlog
show master status;
-
找到这个binlog文件以后可以查看详细的操作内容(windows下),在dos中执行此命令,通常此种方式是用来分析执行的语句,以及执行的内容,方便定位使用
mysqlbinlog --no-defaults -vv --base64-output=decode-rows binlog.000001
通常数据量较大,将内容传入文本文件中
mysqlbinlog --no-defaults -vv --base64-output=decode-rows binlog.000001 >aaa.txt
-
如果时第二种情况则需要不需要继续操作,直接使用手搓或者正则提取(正则提取是有可能不准确,但是大数据量的情况下,用正则比较人道,此链接为正则提取教程),将提取出来的数据转成sql的insert语句,执行插入操作,如果是第一种情况,则继续往下执行即可
如果在文件中确定了数据范围,就可以使用如下语句进行提取,找到节点执行的开始和终止节点,或者使用时间节点生成sql文件,(生成语句不能使用--base-output=decode-rows参数,否则生成的sql文件执行会报错,如果在windows下查看的时候乱码,将windows系统的编码改成和mysql一致即可,一般都是utf-8)
可以通过下面命令生成sql文件,此处生成的文件名称为aaa,路径在D盘下,也可以更改文件生成的位置
-
生成sql文件有两种方式,一种是通过时间进行生成,另外一种是通过pos节点生成,通过时间方式只需要从binlog中找到sql执行时间,将开始时间和结束时间添加即可生成
mysqlbinlog --no-defaults -vv --start-datetime="2023-05-27 12:02:10" --stop-datetime="2023-05-27 12:02:10" binlog.000084 >D:/aaa.sql
-
如果使用pos生成,则需要找到完整的起始节点和结束节点,下述图片是一个完整的执行流程,节点也要完整
执行此命令即可生成sql文件
mysqlbinlog --no-defaults -vv --start-position=2483 --stop-position=2824 binlog.000084>D:/aaa.sql
-
进入mysql,也可以在-p后输入密码
mysql -uroot -p
-
打开要操作的数据库 ,此处我们打开的project
mysql> use project
-
执行sql文件(注意此命令不可以在navicat等工具中执行,只能在Dos或者linux上登录的mysql中执行)
mysql> source D:/aaa.sql