binlog丢失情况 mysql mysql binlog数据恢复

项目场景：

最近项目上有遇到使用binlog恢复数据的场景，在家使用windows环境做了下测试。记录下来，其实和linux环境是差不多的，只不过指令不一样而已，linux的各位就自己转换一下就行。

---

恢复步骤

1.查看数据库是否开启了binlog

执行以下指令：

show variables like '%log_bin%'

可以看到，1 on表示已经打开，8.0版本是默认打开状态的。2. basename是binglog存放位置 3. index 是binlog存放的索引。打开后如下图所示。

记录的是所有的binlog的名字。

如果没打开，自己配置下重启sql就行了，这里就不展开了。

2. 执行delete语句

delete from a where a.id =1;

3. 查看binlog

2.1 一般我们要恢复的数据基本就是当天误删除的，直接把当天的那个binglog文件转换成可阅读的txt文档，看上面的binlog.index可以知道最后一个文件就是当天的binlog文件，这个文件默认大小是1g，如果数据量不是很大可以直接转换。

D:\javaTools\mysql-8.0.12-winx64\bin\mysqlbinlog --no-defaults -vv --base64-output=decode-rows binlog.000764>binlog1.txt

2.2 如果不确定是哪个binlog也可以通过时间去转换

D:\javaTools\mysql-8.0.12-winx64\bin\mysqlbinlog --no-defaults  --base64-output=decode-rows -v --database=test1  --start-datetime="2022-11-13 00:00:00" --stop-datetime="2022-11-14 00:00:00" binlog.000764 > binlog2.txt

2.3 打开文件后如下图，

可以看到binlog中的删除语句执行。

4. 数据恢复

4.1 恢复整个binlog文件数据

mysqlbinlog --no-defaults binlog.000001|mysql -u root -p test

报错如下：

'mysql' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

需要把mysql的bin添加到系统path就可以了。

4.2 按照起止行数恢复

经过2.1的步骤转换后的文件，要取begin之前的行数，可以看到delete语句的起止行数为，230-438
那么恢复的语句为;

D:\javaTools\mysql-8.0.12-winx64\bin\mysqlbinlog --no-defaults --start-position=230 --stop-position=438 binlog.000002|mysql -u root -p

报错如下，delete语句使用这两种方式恢复都不行，尝试了几种办法也没有 解决，如果有解决了的小伙伴可以留言告知。
4.3 既然上面的方式不行，就直接把delete语句转换成insert 语句去恢复就好了，使用另外的方式恢复数据。
在linux环境下，使用刚刚的txt文档执行以下指令。

cat binlog1.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/;INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@17.*),/\1;/g' | sed 's/@1=//g'| sed 's/@[1-9]=/,/g' | sed 's/@[1-9][0-9]=/,/g' > test.sql

转换后的文件内容如下，可以看到不是正常的sql指令，自己转换下就可以了。

;INSERT INTO `test1`.`a`
SELECT
  6 ,
  ,'小红1667188306698' ,
  ,1 ,
  ,12 ,
  ,'2022-10-31 11:51:47' ,

转换后的sql

insert
	into
	`test1`.`a`
select
	6,
	'小红1667188306698' ,
	
	1 ,
	
	12 ,
	
	'2022-10-31 11:51:47'

4.4 执行sql

总结：

可以通过binlog恢复数据，经过测试和查看文档，如果是drop table的应该是可以通过4.2的方式恢复数据，但是delete row的恢复不了，需要通过binlog日志转换成insert语句的方式去恢复数据。如果有可以直接使用
mysqlbinlog方式恢复了数据的小伙伴，可以告知下，共同进步。