sydomain

误删，误修改数据后，通常可以使用工具mysqlbinlog进行恢复

要使用mysqlbinlog对数据进行恢复，必须开启mysq1的binlog日志，同时保留误操作事故之前的全量备份文件

开启binlog日志

查看 binlog 是否开启，执行SQL:

show variables like '%log_bin%';

返回:

确保log_in 为ON，不是则代表没有开启，如果没有开启，需要修改mysql的配置文件my.cnf，务必存在参数:log_bin , server-id, expire_logs_days

[mysqld]
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days=10

场景

需要清除数据表yt_order 中id为1507的记录的goods_info 字段内容执行了SQL:

UPDATE yt_order SET buyer_name = '', buyer_mobile = '',goods_info = '', updated =1663295812 WHERE id = 1507

执行后，发现不小心把buyer_name和 buyer_mobile的值也清空了，需要把原来的buyer_name和buyer_mobile也找出来还原

查找日志文件以及结束点

查看binlog 日志文件

在开始要恢复数据的时候，要查看当前存在的binlog日志文件

执行SQL:

show master logs

返回的是目前存在的日志，最后一条就是当前正在使用的日志，可以看到当前使用的是mysql-bin.000011，如果不需要定位到太远，可以不用执行show master logs

查看最后一个日志以及它的结束点

执行SQL:

show master status

返回的字段Position为记录点，要记录这个字段的数字

刷新日志

执行SQL:

flush logs

在找到了当前正在使用的日志后，最好立即刷新日志，刷新日志会重新生成一个新的binlog日志文件，新的SQL日志记录会记录到新日志文件中

刷新日志的好处:

●生成并指向新日志文件，间接的缩小了数据的恢复范围

●旧日志文件立即停止读写，不会再继续变大，被调用时不会存在文件被占用不可读写的问题

再次查看 binlog日志文件，执行SQL:show master logs

会看到多了一个mysql-bin.000012，现在日志mysql-bin.000011停止增长，记录指向mysql-bin.000012

查找原始数据

现在已经知道了日志是mysql-bin.000011，也知道了最后的结束点是814709202，可以开始按范围来查找数据了

查找方式一:SQL命令 查找

show binlog events

使用 show binlog events的好处是可以通过SQL命令查找，不需要到文件里去翻

知道了日志是mysql-bin.000011，现在用命令:show binlog events in 'mysql-bin.000011'来查询，如果返回结果会非常慢，加一个 limit 来缩小范围:show binlog events in 'mysql-bin.000011’limit 0,100，返回的结果中Pos字段就代表结束点limit 的数字大了以后，记录起来会很麻烦，所以可以多加一个from来缩小范围

比如刚才查询到的最后一个结束点是114702701，那我下次搜索就用:show binlog events in 'mysql-bin.000011'from 114702701 limit 0, 100查询从114702701开始的100条日志，不停的改变from 后面的结束点和limit的值，一直查到自己想到的结果

如上可以发现，误操作的结束点是814703087，所以把数据恢复成结束点814703087之前的数据即可

查找方式二:mysqlbinlog导出SQL文件

知道被修改的id是1507，可以根据id1507来直接使用mysqlbinlog 来筛选:

mysqlbinlog /www/server/data/mysql-bin.000011 | grep 'id=1507'

返回:

UPDATE yt_order SET buyer_mobile ='****', updated =1663295279WHERE id=1507
UPDATE yt_order SET buyer_mobile =*****', updated =1663295289 WHERE id= 1507
UPDATE yt_order SET goods_info='测试一下'，updated =1663295298WHERE id=1507
UPDATE yt order SET buyer name = "', buyer mobile = "', goods info ="', updated=1663295724 WHERE id =1507

以上可以知道，修改的记录确实存在与这个mysql-bin.000011中，接下来进一步，直接讲日志中的SQL导出到文件中:

mysqlbinlog /www/server/data/mysql-bin.000011 >000011.sql

这个文件实际很大，可以缩小范围，只导出指定的库，比如库名是crm:

mysqlbinlog --database=crm /www/server/data/mysql-bin.000011>000011.sql

得到文件000011.sql后，进入文件中查找，可以发现在执行错误SQL之前的结束点，这个方式比执行SQL命令查找要快一些

恢复数据

现在知道了结束点是814703087，可以通过日志名称与结束点可以更好的指定范围:

mysqlbinlog --database=crm --stop-position=814703087/www/server/data/mysql-bin.000011 >000011.sql

因为是在结束点814703087进行的误操作，所以导出文件000011.sql后，要进入文件里查找结束点814703087前一个的结束点，也可以找到时间

找到前一个结束点是814702176，可以直接按结束点恢复，也可以按时间恢复，也可以导出SQL文件后，通过SQL文件恢复

按结束点恢复:

可以设置开始与结束的结束点

开始结束点:

--start-position=N

，结束结束点:

--stop-position=N

:

mysqlbinlog --database=crm--stop-position=814702176/www/server/data/mysql-bin.000011 | mysql -u root -p

按时间恢复:

开始时间:--start-datetime="2023-10-20 12:01:59”

结束时间:--start-datetime="2023-10-20 12:11:59”:

mysqlbinlog --database=crm --stop-datetime="2022-09-16 14:00:00"/www/server/data/mysql-bin.000013| mysql -u root -p

导出SQL文件进行恢复

导出SQL文件比较灵活，可以修改use库名来指向其他的库导入数据

mysqlbinlog --database=crm --stop-position=814702176/www/server/data/mysql-bin.000011>000022.sql

导出文件000022.sql后，如果我想测试一下，可以把use修改成指向我的测试库

有些表比较久，直接导出SQL，不会有create table语句，这种恢复不了

所以最好的恢复搭配就是:全量备份+指定时间的binlog文件