mysql通过binlog恢复数据

开启binlog

在my.ini中添加以下两行代码：

log-bin=mysql-bin

server-id=1

注意要写在[mysqld]范围内才会生效

查看binlog存放日志文件目录

show variables like '%datadir%';

查看binlog文件名

show master logs;

将binlog转换成sql

mysqlbinlog --no-defaults --base64-output=decode-rows --base64-output=decode-rows -v --database=test --start-datetime="2023-07-26 16:30:00" --stop-datetime="2023-07-26 17:00:00" D:\phpstudy_pro\Extensions\MySQL5.7.26\data\mysql-bin.000001 > D:\phpstudy_pro\Extensions\MySQL5.7.26\data\hsb.sql

创建test.vbs将binlog的delete转换成insert

function replaceregex(patern,str,tagstr)

dim regex,matches

set regex=new regExp

regex.pattern=patern

regex.IgnoreCase=true

regex.global=true

matches=regex.replace(str,tagstr)

replaceregex=matches

end function



'Mysql binlog DELETE转INSERT==========

'VBS打开文本文件

Set oldStream = CreateObject("ADODB.Stream")

oldStream.CharSet = "utf-8"

oldStream.Open

oldStream.LoadFromFile("hsb.sql") 'binLog生成的DELETE原日志文件

oldText = oldStream.ReadText()

newText=replace(oldText,"### DELETE FROM", ";INSERT INTO")

newText=replace(newText,"### WHERE", "SELECT")

newText=replace(newText,"###", "")

newText=replace(newText,"@1=", "")

newText=replaceregex("@[1-9]=",newText, ",")

newText=replaceregex("@[1-9][0-9]=",newText, ",")

oldStream.Close

'VBS保存文件

Set newStream = CreateObject("ADODB.Stream")

newStream.Type = 2 'Specify stream type - we want To save text/string data.

newStream.Charset = "utf-8" 'Specify charset For the source text data.

newStream.Open 'Open the stream And write binary data To the object

newStream.WriteText newText

newStream.SaveToFile "hsb_insert.sql", 2 'DELETE转成INSERT以后的新的SQL文件名

newStream.Close

双击执行test.vbs生成hsb_insert.sql

找到insert语句并执行