應用innodb_force_recovery處理MySQL瓦解沒法重啟成績。本站提示廣大學習愛好者：（應用innodb_force_recovery處理MySQL瓦解沒法重啟成績）文章只能為提供參考，不一定能成為您想要的結果。以下是應用innodb_force_recovery處理MySQL瓦解沒法重啟成績正文
一 配景
某一創業的同伙的主機由於磁盤陣列破壞機械crash,重啟MySQL辦事時 報以下毛病:
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 9120034833
150125 16:12:51 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 150125 16:12:51 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 5.5.37-MariaDB-log
key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=0
max_threads=1002
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2332093 K bytes of memory
41 Hope that.
二 剖析
重要存眷 mysqld got signal 11 的成績,從日記內容剖析來看,數據庫在機械crash 招致日記文件破壞,重啟以後沒法正常恢復,更沒法正常對外供給辦事。
三 處理
由於日記曾經破壞，這裡采取異常規手腕,起首修正innodb_force_recovery參數，使mysqld跳過恢復步調，將mysqld 啟動,將數據導出來然後重建數據庫。
innodb_force_recovery可以設置為1-6,年夜的數字包括後面一切數字的影響。
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):不履行前滾的操作。
留意
a 當設置參數值年夜於0後，可以對表停止select,create,drop操作,但insert,update或許delete這類操作是不許可的。
b 當innodb_purge_threads 和 innodb_force_recovery一路設置會湧現一種loop景象:
150125 17:07:42 InnoDB: Waiting for the background threads to start
150125 17:07:43 InnoDB: Waiting for the background threads to start
150125 17:07:44 InnoDB: Waiting for the background threads to start
150125 17:07:45 InnoDB: Waiting for the background threads to start
150125 17:07:46 InnoDB: Waiting for the background threads to start
150125 17:07:47 InnoDB: Waiting for the background threads to start
在my.cnf中修正以下兩個參數
innodb_force_recovery=6
innodb_purge_thread=0
重啟MySQL
150125 17:10:47 [Note] Crash recovery finished.
150125 17:10:47 [Note] Server socket created on IP: '0.0.0.0'.
150125 17:10:47 [Note] Event Scheduler: Loaded 0 events
150125 17:10:47 [Note] /vdata/webserver/mysql/bin/mysqld: ready for connections.
Version: '5.5.37-MariaDB-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
立刻對數據庫做邏輯導出 ，完成以後將innodb_force_recovery設置為0 ，innodb_purge_thread=1 ,然後重建數據庫 。
別的 MySQL 版本 5.5和之前 ,當innodb_purge_threads =1，innodb_force_recovery >1 的情形會湧現上文提到的輪回報warning 成績（=1 沒有成績），
緣由：
MySQL 的源代碼中顯示 當innodb_purge_threads 和 innodb_force_recovery一路設置會湧現loop輪回
while (srv_shutdown_state == SRV_SHUTDOWN_NONE) {
if (srv_thread_has_reserved_slot(SRV_MASTER) == ULINT_UNDEFINED
|| (srv_n_purge_threads == 1
&& srv_thread_has_reserved_slot(SRV_WORKER)
== ULINT_UNDEFINED)) {
ut_print_timestamp(stderr);
fprintf(stderr, " InnoDB: Waiting for the background threads to start\n");
os_thread_sleep(1000000);
} else {
break;
}
}
所以當須要設置innodb_force_recovery>1的時刻須要封閉 innodb_purge_threads，設置為0（默許）。
四 小結
MySQL crash 或許 MySQL 數據庫辦事器 crash 會招致各類各樣的成績 ，好比主備之間的error 1594 (5.6 版本開啟crash-safe ，會最年夜水平上防止 error 1594的成績，今後會寫5.6新特征引見該功效 )，error 1236， 日記破壞，數據文件破壞 ，等等，本案例只是個中的一種，仔細從日記中找的相干毛病提醒，慢慢處理便可。
