MySQLBINLOG restores misuse data

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'

share picture

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

share picture

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

Share the picture

Then go to see where the deletion started

show binlog events in 'mysql-bin.000211'

Share a picture

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.

Share a picture

< 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

Open, resource.sql is the SQL statement that many of us are familiar with. . The adjustment and execution is very simple

share picture

/p>

Summary

   The above can only be effective for the wrong operation of delete, and the binlog is in line mode. If it is caused by the truncate statement, you can only pray for a backup file.

Reference

https://yq.aliyun.com/articles/664444

show variables like 'log_bin'

show master status

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

show binlog events in 'mysql-bin.000211'

mysqlbinlog -vv --start-position=956859551- -stop-position=956863056 mysql-bin.000211 |grep ^"###" >bin_1448

< p>

cat bin_1448 | sed -n '/###/p' < span style="color: #808080;">| 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

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 2955 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC

Leave a Comment

Your email address will not be published.