当前位置：首页 > article >正文

mysql 日志恢复

article 2024/9/23 16:15:18

一:查看是否开启binlog日志

show variables like 'log_%';

二：查看日志存放目录：

SHOW VARIABLES LIKE '%datadir%';

三：查看日志列表

show binary logs; #获取binlog文件列表 
show master status； #查看当前正在写入的binlog文件

四：导出指定binlog日志

#把bin.00032日志转成temp.sql（时间是2024-09-11 8:54:40，2024-09-11 12:59:10范围内的）
mysqlbinlog  --no-defaults  -d test   --start-datetime='2024-09-11 8:54:40' --stop-datetime='2024-09-11 12:59:10'  bin.000032 >temp.sql

#恢复sql
source temp.sql; 

#指定位置（远程登录日志并且恢复指定日志的数据）
mysqlbinlog --start-position=312 --stop-position=647 --base64-output=decode-rows -vv  bin.000032 >temp.sql

五：binlog日志转成可读日志（把binlog日志下载下来放到本地，本地转成可读的）

mysqlbinlog --no-defaults -vv --base64-output=decode-rows D:\data.000501 > D:\temp.sql

六：把delte语句转成insert语句

#linux环境
cat temp.sql | 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' > mysqllogOK.sql

windows环境
'========================== 
'用VBS实现 MYSQL binglog 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("temp.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 "temp1.sql", 2 'DELETE转成INSERT以后的新的SQL文件名
newStream.Close