生產環境下，MySQL大事務操作導致的回滾解決方案

• 2019 年 10 月 23 日
• 筆記

如果mysql中有正在執行的大事務DML語句，此時不能直接將該進程kill，否則會引發回滾，非常消耗資料庫資源和性能，生產環境下會導致重大生產事故。

如果事務操作的語句非常之多，並且沒有辦法等待那麼久，可以採取以後操作：

1. 在資料庫中的配置文件中新增：innodb_force_recovery = 3。

innodb_force_recovery影響整個InnoDB存儲引擎的恢復狀況。默認為0，表示當需要恢復時執行所有的innodb_force_recovery可以設置為1-6,大的數字包含前面所有數字的影響。當設置參數值大於0後，可以對錶進行select,create,drop操作,但insert,update或者delete這類操作是不允許的。

1(SRV_FORCE_IGNORE_CORRUPT):忽略檢查到的corrupt頁。
2(SRV_FORCE_NO_BACKGROUND):阻止主執行緒的運行，如主執行緒需要執行full purge操作，會導致crash。
3(SRV_FORCE_NO_TRX_UNDO):不執行事務回滾操作。
4(SRV_FORCE_NO_IBUF_MERGE):不執行插入緩衝的合併操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日誌，InnoDB存儲引擎會將未提交的事務視為已提交。
6(SRV_FORCE_NO_LOG_REDO):不執行前滾的操作。

2. 重啟msql，drop掉導致回滾的表，再將配置文件恢復成默認值（即不需要在配置文件中設置innodb_force_recovery參數），再次重啟mysql。

　重啟操作會因為數據量非常大，導致mysql恢復緩慢，此時需要等待mysql進行崩潰恢復。根據數據量的不同，等待的時間也不同。

註：

　　如果需要導入導出資料庫數據，可以使用以下命令：

　　1. 導出：mysqldump  -uroot -p –opt -R -E –default-character-set=utf8 –databases device2> /mysql/device_20191022.sql

　　　　　　mysql5.7以後可以使用pump命令，調用多執行緒：

　　　　　　　　/mysql/bin/mysqlpump -uroot -p –default-character-set=utf8 –compress-output=LZ4 –default-parallelism=4 –databases device2 > /mysql/device_20191022.sql.LZ4

　　2. 導入：mysql -uroot -p < /opt/device_20191022.sql

　　　　　　如果使用了pump導出，需要先解壓：

　　　　　　　　lz4_decompress /tmp/backup_kevin.sql /tmp/kevin.sql

　　　　　　　　mysql < source /tmp/kevin.sql;