Overview
Code bug, executed DELETE FROM t_resource WHERE resource_id =? OR parent_id =? Because of the OR condition, all the data uploaded by the user is cleared.
show
see if log-bin backup is enabled
show variables like 'log_bin'
p>
Thankfully, the binary log backup has been turned on. Then it is much simpler, find this binary log, find this node, and restore it. Execute this command to see which file is the binary log being written
show master status
Of course, flush can also restart a file writing. Use this file name to search where this file is in the Linux global ==> find / -name mysql, it’s easy to find it for so long.
mysqlbinlog
mysqlbinlog -vv --start-datetime='2019-9-24 11:24:00' --stop-datetime='2019-9-24 11:25:20' mysql-bin.000211| grep "t_resource" | more
View the pos position where the delete operation is executed
Then go to see where the deletion started
show binlog events in ' span>mysql-bin.000211'
Know the beginning and end Node, the recovery of data is very fast, because logbin is a binary log, we make it understandable
mysqlbinlog -vv --start-positi on=956859551 --stop-position=956863056 mysql-bin.000211 |grep ^"###" >bin_1448
A bin_1448 file is generated. Let’s open it and take a look.
< p>
This is the thing that executes delete delete
INSERT
Next, turn it back into an insert statement and it will be OK
< div class="code">
cat bin_1448 | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed ' s/@[1-9][0-9]=//g' >resource.sql