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:
- Change the write_row event corresponding to insert to delete_row event
- The delete_row event corresponding to delete is changed to write_row event
- 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.
prevention:
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
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/sqlParse 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
Install
shell> git clone https://github.com/danfengcao/binlog2sql.git && 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
BEGIN
/*!*/;
# 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
BINLOG '
RMycYRM6KiAiPgAAAOYEAAAAAIYAAAAAAAEABHRlc3QABHRlc3QABgMPDw8PDwoeAB4AHgAeAB4A
Pm47Qa4=
RMycYSA6KiAixwEAAK0GAAAAAIYAAAAAAAEAAgAG/8ABAAAAAmExAmEyAmEzAmE0AmE1wAIAAAAC
YjECYjICYjMCYjQCYjXAAwAAAAJjMQJjMgJjMwJjNAJjNcAEAAAAAmQxAmQyAmQzAmQ0AmQ1wAUA
AAACZTECZTICZTMCZTQCZTXABgAAAAJmMQJmMgJmMwJmNAJmNcAHAAAAATEBMQExATEBMcAIAAAA
ATIBMQExATEBMcAJAAAAATIBMgExATEBMcAKAAAAATMBMgExATEBMcALAAAAATEBMgExATEBMcAM
AAAAATMBMgExATEBMcANAAAAATMBMgExATEBMMAOAAAAATQBMgExATEBMMAPAAAAATUBMQEwATEB
MMAQAAAAATEBMQEwATEBMMARAAAAATYBMQEwATEBMMASAAAAATYBMQEwATEBMMATAAAAATgBMQEw
ATEBMMAUAAAAATkBMQEwATEBMMAVAAAAATgBMQEwATEBMMAWAAAAATcBMQEwATEBMMAXAAAAATEB
MQEwATEBMMAYAAAAATgBMQEwATEBMMAZAAAAATkBMQEwATEBMMAaAAAAATUBMQEwATEBMEkJxkQ=
'/*!*/;
### DELETE FROM `test`.`test`
### WHERE
### @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
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[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 binlog2sql.py -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
我们得到了误操作sql的准确位置在1709-1740之间，再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确(注：真实环境下，此步经常会进一步筛选出需要的sql。结合grep、编辑器等)
执行
python binlog2sql.py -h127.0.0.1 -P3306 -ucjr -p'cjr' -dtest -ttest --start-file='mysql-bin.000013' --start-position=1709 --stop-position=1740 -B > rollback.sql
输出insert语句即为误删的数据
Using MyFlash
下载安装
git clone https://github.com/Meituan-Dianping/MyFlash.git
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
生成闪回binlog文件
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
English