誤刪數據包含如下四種情況
(2)使用 drop table 或者 truncate table 語句誤刪數據表;
(3)使用 drop database 語句誤刪數據庫;
(4)使用 rm 命令誤刪整個 MySQL 實例;
使用delete語句誤刪數據
恢復方式:使用binlog,利用Flashback工具恢復,Flashback的原理是修改binlog拿到原庫里回放,這個方案的前提是binlog_format=row 并且binlog_row_image=full
單個事務的處理:
1.insert 對應的 write_row event 改成delete_row event
2.delete 對應的 delete_row event 改成write_row event
3.update binlog中記錄了修改前和修改后的值,對掉位置就可以了
多個事務的處理注意調整事務的順序,
預防:
1.將sql_safe_updates設置為on,這樣delete和update中無where子句的語句就會報錯,生產如果要執行整表刪除可以用truncate或者where 1=1。
2.上線,必須做sql審計,至少也要在測試環境完成驗證。
將測試庫中的某個表刪除

解析binlog查看,先看下統計中,這個刪除在不在這個binlog中,確認確實是在這個binlog日志中
根據binlog統計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
解析出binlog

將這部分的delete_row event 改成write_row event
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' 生產insert語句就是delete掉的
binlog2sql的使用
安裝
shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt
-B, --flashback 生成回滾SQL
解析范圍控制
--start-file 起始解析文件,只需文件名,無需全路徑 。必須。
--start-position/--start-pos 起始解析位置。可選。默認為start-file的起始位置。
--stop-file/--end-file 終止解析文件。可選。默認為start-file同一個文件。若解析模式為stop-never,此選項失效。
--stop-position/--end-pos 終止解析位置。可選。默認為stop-file的最末位置;若解析模式為stop-never,此選項失效。
--start-datetime 起始解析時間,格式'%Y-%m-%d %H:%M:%S'。可選。默認不過濾。
--stop-datetime 終止解析時間,格式'%Y-%m-%d %H:%M:%S'。可選。默認不過濾。
對象過濾
-d, --databases 只解析目標db的sql,多個庫用空格隔開,如-d db1 db2。可選。默認為空。
-t, --tables 只解析目標table的sql,多張表用空格隔開,如-t tbl1 tbl2。可選。默認為空。
--only-dml 只解析dml,忽略ddl。可選。默認False。
--sql-type 只解析指定類型,支持INSERT, UPDATE, DELETE。多個類型用空格隔開,如--sql-type INSERT DELETE。可選。默認為增刪改都解析。用了此參數但沒填任何類型,則三者都不解析。誤刪庫test下的表test
根據刪除時間,確認binlog位置
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]#
可以得到binlog的position位置
[root@VM-0-9-centos binlog]# mysqlbinlog --start-position=1709 --stop-position=1740 -v mysql-bin.000013
也可以根據這個解析出sql來確認出具體pos位置
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語句即為誤刪的數據
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




