使用python腳本實現mysql誤操作
- 2020 年 1 月 7 日
- 筆記
1.簡介 在oracle資料庫中，當一個誤操作被提交後，我們可以通過oracle提供的閃回功能將表閃回至誤操作之前的狀態。mysql中沒有原生的flushback功能，DBA誤操作時，傳統的恢復方式是利用全備+二進位日誌前滾進行恢復。 今天給大家介紹一種使用python腳本在mysql中實現類似oracle中flushback table的閃回功能，相比於傳統的全備+增備，本方法更為快速、簡單。
2.閃回原理 原理：調用mysql_rollback.py（腳本在我的另一篇blog 閃回腳本：mysql_rollback.py）對rows格式的binlog進行逆向操作，delete反向生成insert、update生成反向的update、insert反向生成delete。
3.說明 0、需安裝python及MySQLdb模組 1、binlog的格式必須為row 2、要恢復的表操作前後表結構沒有發生變更，否則腳本無法解析 3、只生成DML(insert/update/delete)的rollback語句，DDL語句不可回滾 4、最終生成的SQL是逆序的，所以最新的DML會生成在輸入文件的最前面，並且帶上了時間戳和偏移點，方便查找目標 5、需要提供一個連接MySQL的只讀用戶，主要是為了獲取表結構 6、如果binlog過大，建議帶上時間範圍，也可以指定只恢復某個庫的SQL 7、SQL生成後，請務必在測試環境上測試恢復後再應用到線上
4.實戰 step1.登陸mysql查看錶資訊
mysql> use db1 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +------+-------+------+---------+----------+ | id | name | age | country | city | +------+-------+------+---------+----------+ | 1 | alex | 26 | china | shanghai | | 2 | bob | 25 | britain | london | | 3 | simon | 24 | france | paris | +------+-------+------+---------+----------+ 3 rows in set (0.00 sec)
step2.模擬誤操作（update）
mysql> update test set country='europe' where name='bob'; --bob的國家被改為europe Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +------+-------+------+---------+----------+ | id | name | age | country | city | +------+-------+------+---------+----------+ | 1 | alex | 26 | china | shanghai | | 2 | bob | 25 | europe | london | | 3 | simon | 24 | france | paris | +------+-------+------+---------+----------+ 3 rows in set (0.00 sec) mysql> exit; Bye
step3.分析binlog並生成反向語句 找到最新的binlog
SZD-L0087668:gzz3306:Master > ll -rw-rw---- 1 mysql mysql 167 May 2 14:30 mysql-bin.000001 -rw-rw---- 1 mysql mysql 11400402 May 2 19:28 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1807 May 2 19:49 mysql-bin.000003 -rw-rw---- 1 mysql mysql 660 May 2 20:10 mysql-bin.000004 -rw-rw---- 1 mysql mysql 403 May 2 20:10 mysql-bin.000005 -rw-rw---- 1 mysql mysql 584 May 3 10:45 mysql-bin.000006 -rw-rw---- 1 mysql mysql 417 May 3 10:53 mysql-bin.000007 -rw-rw---- 1 mysql mysql 1973 May 3 13:28 mysql-bin.000008 -rw-rw---- 1 mysql mysql 2604 May 3 14:13 **mysql-bin.000009** -rw-rw---- 1 mysql mysql 369 May 3 13:28 mysql-bin.index -rw-r--r-- 1 root root 12222 Apr 13 2017 mysql_rollback.py
根據關鍵詞europe查找binlog中的誤操作sql，並輸出europe前後30行（行數視具體情況而定，一定要輸出語句對應的BEGIN和COMMIT部分）
SZD-L0087668:gzz3306:Master > mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS --set-charset=utf8 mysql-bin.000009 | grep -C 30 'europe' ... BEGIN /*!*/; # at 2426 #180503 14:13:36 server id 1 end_log_pos 2482 CRC32 0xe79b9612 Table_map: `db1`.`test` mapped to number 76 # at 2482 #180503 14:13:36 server id 1 end_log_pos 2573 CRC32 0xacd94a0b Update_rows: table id 76 flags: STMT_END_F ### UPDATE `db1`.`test` ### WHERE ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='bob' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### @3=25 /* INT meta=0 nullable=1 is_null=0 */ ### @4='britain' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### @5='london' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='bob' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### @3=25 /* INT meta=0 nullable=1 is_null=0 */ ### @4='europe' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### @5='london' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ # at 2573 #180503 14:13:36 server id 1 end_log_pos 2604 CRC32 0x63b3d5fa Xid = 118 COMMIT/*!*/; ...
選取2426和2604作為分析binlog的起始位置
SZD-L0087668:gzz3306:Master > python2.7 mysql_rollback.py -f mysql-bin.000009 -o rollback.sql -h127.0.0.1 -P3306 -uroot -p123456 --start-position='2426' --stop-position='2604' -d db1 正在獲取參數..... 正在解析binlog..... 正在初始化列名..... 正在開始拼湊sql..... done!
查看rollback.sql中誤操作的逆向語句
SZD-L0087668:gzz3306:Master > cat rollback.sql ## at 2482 ##180503 14:13:36 server id 1 end_log_pos 2573 CRC32 0xacd94a0b Update_rows: table id 76 flags: STMT_END_F UPDATE `db1`.`test` SET id=2 ,name='bob' ,age=25 ,country='britain' ,city='london' WHERE id=2 AND name='bob' AND age=25 AND country='europe' AND city='london';
step4.回滾
SZD-L0087668:gzz3306:Master > mysql -uroot -p <rollback.sql Enter password: SZD-L0087668:gzz3306:Master > mysql -uroot -p -e 'select * from db1.test'; Enter password: +------+-------+------+---------+----------+ | id | name | age | country | city | +------+-------+------+---------+----------+ | 1 | alex | 26 | china | shanghai | | 2 | bob | 25 | britain | london | | 3 | simon | 24 | france | paris | +------+-------+------+---------+----------+
test表已回滾。
