Using MySQL binary logs to recover deleted data method

This article mainly tells you about the method of using MySQL binary logs to recover deleted data. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article can bring you some practical help by using MySQL binary logs to recover deleted data.

Delete data from the MySQL table

Mysql > use sbtest;mysql > select count (*) from sbtest1;+-+ | count (*) | +-+ | 1000 | +-+ 1 row in set (0.00 sec) mysql > delete from sbtest1;Query OK, 1000 rows affected (0.21 sec) mysql > select count (*) from sbtest1 +-+ | count (*) | +-+ | 0 | +-+ 1 row in set (0.00 sec)

Confirm the time point and the current binary log file, and read the operation record from the binary log

Mysqlbinlog\-start-datetime= "2018-09-27 15:55:00"\-- stop-datetime= "2018-09-27 15:00:00"\-- base64-output=decode-rows\-- result-file=result.sql\-v / var/lib/mysql/binlog.000022

One of the records

Remove irrelevant content

Grep-A 5 "DELETE FROM\ `sbtest\'.\ `sbtest1\ `" result.sql > 1.txt

Extract data

Grep "=" 1.txt > 2.txtsed-I / G '2.txt

Use the following script to generate INSERT statements

[root@mysql03 tmp] # cat r2.sh #! / bin/bashvs= "" while read linedo n = `echo $line | awk-NF "="'{print $1}'`v = `echo $line | awk-NF "="'{print $2} '`if ["$n" = "@ 1"]; then vs= "INSERT INTO\ `echo\ ``sbtest1\ `VALUES (" $elif ["$n" = "@ 2"] Then vs=$vs "," $v elif ["$n" = "@ 3"]; then vs=$vs "," $v elif ["$n" = "@ 4"]; then vs=$vs "," $v ");" echo $vs > > 3.txt fidone "

< 2.txt[root@mysql03 tmp]# sh r2.sh 将数据导入到 MySQL 表中 [root@mysql03 tmp]# mysql < 3.txtmysql>

Select count (*) from sbtest1;+-+ | count (*) | +-+ | 1000 | +-+

1 row in set (0.00 sec)

Using the MySQL binary log method to recover deleted data, let's stop here. If you want to know about other related issues, you can continue to follow our industry information. Our section will capture some industry news and professional knowledge to share with you every day.