MySQL [Delete error] uses binlog to quickly roll back (python script is not required)

Locate binlog position according to misoperation time

In specific usage scenarios, binlog may store hundreds of thousands or more pieces of data, and the amount of data reaches the size of G. therefore, determining the location of binlog needs to be filtered according to the misoperation time. Therefore, it is best to remember the misoperation time, otherwise it is time-consuming and laborious to find and locate binlog. Use the mysqlbinlog command to filter all operations within this time interval and the position of binlog corresponding to the operation through – start datetime and – stop datetime.
The path of the log file may be different from the version of MySQL. I use mysql8, and the binlog file is under / var/lib/mysql.
Enter the directory where the file is located and execute the following command to query the relevant data.

mysqlbinlog --no-defaults --database=xxx -vv binlog.000007 --start-datetime="2022-02-19 8:06:34" --stop-datetime="2022-02-19 8:06:35"  | more

However, if there are many logs in this process, it is difficult to determine the position through the above command. You can filter it in combination with "| head -1000" and "| tail -1000", and only look at the first N lines or the last N lines of binlog output to find the start and end positions, which can greatly save time. If there is no memory about the time and other information of the misoperation, the process of locating the misoperation can be filtered in a variety of ways, such as adding the SED command to the command to select the specified log to print, "| sed -n '/ #### delete from' test '.' tet3 '/, / COMMIT / P'", and printing the contents from "#### delete from 'test'. 'tet3' to the end of" COMMIT ", Similar methods can be found and tried by yourself.

mysqlbinlog --no-defaults --database=xxx -vv binlog.000007 --start-datetime="2022-02-19 8:06:34" --stop-datetime="2022-02-19 8:06:35" |tail -300   | more

Extract misoperation log

The position can be confirmed by querying the "at 123" log in binlog. The start and end positions of the entire deletion operation can be known by confirming the BEGIN and COMMIT positions of the deletion operation. The DELETE operation log is extracted according to the two location points. The format is as follows:

### DELETE FROM `test`.`me_info`
### WHERE
###   @1=2165974 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1984:03:17' /* DATE meta=0 nullable=1 is_null=0 */
###   @3=NULL /* DATE meta=765 nullable=1 is_null=1 */
###   @4=2012-10-25 00:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @6=0 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @7='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */
###   @9=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */

Extract the logs of all delete operations and output them to a file:

mysqlbinlog --no-defaults -vv binlog.000007 --start-position=10147088 --stop-position=109132975 | grep ^"###" >data_reload

Convert the delete statement in binlog into insert statement

The delete statement is delete from 'dbname'` Tablename ` where @ 1 = XXXX, @ 1 indicates the first field.
The insert statement is insert into 'dbname'` tablename` values(value1, value2, ……).
Convert delete statement to insert statement:

cat data_reload | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/g;' |sed -r 's/(@9.*),/\1);/g' | sed 's/@[1-9]=//g' >data_reload.sql

Command parameter description:

• sed -n '/ #### / p': print lines beginning with '###'.
• sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/g;':
  s/### //g;s/\/\*.*/,/g; Remove '####' and / *... * /; s/DELETE FROM/INSERT INTO/g; Replace delete from with insert into; s/WHERE/VALUES(/g); replace where with values(.
• sed -r ‘s/(@9.*),/‘);/g ': - R is a regular expression, add one at the end of the line at the beginning of @ 9), (after the last field here), that is, values(v1,..., v9).
• sed's/@[1-9]=//g ': remove @ 1 - @ 9.

Note: this command is executed according to different database table structures based on specific analysis.

Execute the insert statement script to import the data into the database

mysql -uroot -p < data_reload.sql