How to recover MySQL accidentally deleted data (1) delete accidental deletion recovery

Accidentally deleted data includes the following four cases

(1) Use the delete statement to delete data rows by mistake;
(2) Use the drop table or truncate table statement to delete the data table by mistake;

(3) Use the drop database statement to delete the database by mistake;

(4) Use the rm command to delete the entire MySQL instance by mistake;

Delete data by mistake using the delete statement

Recovery method: use binlog, and use the Flashback tool to restore. The principle of Flashback is to modify the binlog and get it back to the original library. The premise of this scheme is that binlog_format=row and binlog_row_image=full

Processing of a single transaction:

  1. Change the write_row event corresponding to insert to delete_row event
  2. The delete_row event corresponding to delete is changed to write_row event
  3. The values before and after the modification are recorded in the update binlog, and the position is correct.

When processing multiple transactions, pay attention to adjusting the order of transactions.


Set sql_safe_updates to on, so that the statement without where clause in delete and update will report an error. If you want to delete the whole table in production, you can use truncate or where 1=1.

On-line, sql audit must be done, and at least verification must be completed in the test

Delete a table in the test library

mysql elete accidental

Parse the binlog view, first look at the statistics, this deletion is not in this binlog, confirm that it is indeed in this binlog log

According to binlog statistics ddl dml
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000007 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr
mysqlbinlog -v mysql-bin.000013 > /tmp/sql

Parse out binlog

 mysqlbinlog  --base64-output=DECODE-ROWS -vv   mysql-bin.000013 | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@17.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-1][0-7]=//g' 

The production insert statement is deleted

Use of binlog2sql


shell> git clone && cd binlog2sql

shell> pip install -r requirements.txt

-B, --flashback generate rollback SQL
Parse scope control
--start-file Start parsing the file, just the file name, not the full path. must.
--start-position/--start-pos Start parsing position. Optional. Defaults to the starting position of start-file.
--stop-file/--end-file Stop parsing file. Optional. Defaults to the same file as start-file. If the parsing mode is stop-never, this option has no effect.
--stop-position/--end-pos Stop resolving position. Optional. The default is the last position of stop-file; if the parsing mode is stop-never, this option is invalid.
--start-datetime Start parsing time, format '%Y-%m-%d %H:%M:%S'. Optional. No filtering by default.
--stop-datetime Stop parsing time, format '%Y-%m-%d %H:%M:%S'. Optional. No filtering by default.
Object filtering
-d, --databases Only parse the SQL of the target db, and separate multiple databases with spaces, such as -d db1 db2. Optional. Default is empty.
-t, --tables Only parse the SQL of the target table, and separate multiple tables with spaces, such as -t tbl1 tbl2. Optional. Default is empty.
--only-dml Parse only dml, ignore ddl. Optional. Default False.
--sql-type only parses the specified type, supports INSERT, UPDATE, DELETE. Multiple types are separated by spaces, such as --sql-type INSERT DELETE. Optional. The default is to parse additions, deletions and changes. If this parameter is used but no type is filled, the three will not be parsed.

Delete the table test under the library test by mistake

Confirm the binlog location according to the deletion time

mysqlbinlog --start-datetime='2021-11-23 19:00:00' --stop-datetime='2021-11-23 19:15:00' -v mysql-bin.000013
# at 1192
#211123 19:11:00 server id 572533306  end_log_pos 1254 CRC32 0xae413b6e         Table_map: `test`.`test` mapped to number 134
# at 1254
#211123 19:11:00 server id 572533306  end_log_pos 1709 CRC32 0x44c60949         Delete_rows: table id 134 flags: STMT_END_F
### DELETE FROM `test`.`test`
###   @1=1
###   @2='a1'
###   @3='a2'
###   @4='a3'
###   @5='a4'
###   @6='a5'
### DELETE FROM `test`.`test`
# at 1709
#211123 19:11:00 server id 572533306  end_log_pos 1740 CRC32 0x623ae51c         Xid = 182
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
# End of log file
[root@VM-0-9-centos binlog]# 
[root@VM-0-9-centos binlog]# 
[root@VM-0-9-centos binlog]# 

You can get the position of binlog

[root@VM-0-9-centos binlog]# mysqlbinlog --start-position=1709 --stop-position=1740  -v mysql-bin.000013

You can also parse out the sql based on this to confirm the specific pos location

python -h127.0.0.1 -P3306 -ucjr -p'cjr' -dtest -ttest --start-datetime='2021-11-23 19:00:00' --stop-datetime='2021-11-23 19:15:00
python -h127.0.0.1 -P3306 -ucjr -p'cjr' -dtest -ttest --start-file='mysql-bin.000013' --start-position=1709 --stop-position=1740 -B > rollback.sql

Using MyFlash

git clone
gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback

  --databaseNames             databaseName to apply. if multiple, seperate by comma(,)
  --tableNames                tableName to apply. if multiple, seperate by comma(,)
  --start-position            start position
  --stop-position             stop position
  --start-datetime            start time (format %Y-%m-%d %H:%M:%S)
  --stop-datetime             stop time (format %Y-%m-%d %H:%M:%S)
  --sqlTypes                  sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
  --maxSplitSize              max file size after split, the uint is M
  --binlogFileNames           binlog files to process. if multiple, seperate by comma(,)  
  --outBinlogFileNameBase     output binlog file name base
  --logLevel                  log level, available option is debug,warning,error
  --include-gtids             gtids to process
  --exclude-gtids             gtids to skip

binary/flashback --binlogFileNames=/root/mysql-bin.002712 --include-gtids='486bfa3b-d9f2-11ea-8b02-a01c8d40b01a:438640718' --databaseNames=workflow_1 --tableNames=sys_config --sqlTypes='UPDATE'  --outBinlogFileNameBase=test_tb
mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql -uroot -proot 

如果执行显示kill 查看操作系统日志 ,大部分是OOM原因
tail -500f /var/log/messages

Leave a Reply

Your email address will not be published. Required fields are marked *