How to solve the problem of accidental deletion and rollback of MySQL database

One time I accidentally used delete from xxx to delete several important data. I found many methods on the Internet, but they were all scattered. I plan to record the process of data retrieval.
It is roughly divided into the following steps

1. Check whether binlog is turned on

# log_bin是ON，就说明打开了 OFF就是关闭状态，以下操作，只有为 ON 时有效。
show variables like 'log_bin';

2. Find the binlog file name

show master logs;

Run the above code, as shown in the figure below TS1-bin .000009 is the file name we are looking for

3. Check the binlog log location

show variables like '%datadir%';

4. Find TS1-bin based on the location obtained above .000009 file

5. Go to the bin directory of the mysql installation directory and execute the following command to export the TS1-bin.000009 file into a sql file based on the accidentally deleted time range

mysqlbinlog --base64-output=decode-rows -v --database=数据库名 --start-datetime="2022-06-29 15:35:00" --stop-datetime="2022-06-29 15:45:00" C:/Users/Administrator/Desktop/TS1-bin.000009 > C:/Users/Administrator/Desktop/mysqllog.sql

Here I am I copied the TS1-bin.000009 file to the desktop. Because the original storage path of the file contained spaces, the command execution failed and the path could not be found.
After getting the mysqllog.sql file, you can open it with Notepad, search for the DELETE keyword, and find the record of deleted data

6. Change the DELETE statement into an INSERT statement and use vbs to implement it under Windows. Copy and save the following code as: deleteToinsert.vbs file (must be a .vbs format file) in the same directory as mysqllog.sql, then double-click to run, the mysqllogOK.sql file will be generated, which is the INSERT statement we want

'========================== 
'用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("mysqllog.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 "mysqllogOK.sql", 2 'DELETE转成INSERT以后的新的SQL文件名
newStream.Close

7. Execute after getting the corresponding INSERT statement.

The above is the detailed content of How to solve the problem of accidental deletion and rollback of MySQL database. For more information, please follow other related articles on the PHP Chinese website!