MySQL意外斷電，InnoDB資料庫恢複

標籤：

客戶資料庫在運行中突然斷電，當伺服器重啟發現MySQL無法啟動，查看日誌，報錯如下：

151105 11:24:52 mysqld_safe Starting mysqld daemon with databases from /data/mysql/datafile151105 11:24:52 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.151105 11:24:52 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.151105 11:24:52 InnoDB: The InnoDB memory heap is disabled151105 11:24:52 InnoDB: Mutexes and rw_locks use GCC atomic builtins151105 11:24:52 InnoDB: Compressed tables use zlib 1.2.3151105 11:24:52 InnoDB: Initializing buffer pool, size = 512.0M151105 11:24:52 InnoDB: Completed initialization of buffer pool151105 11:24:52 InnoDB: highest supported file format is Barracuda.InnoDB: The log sequence number in ibdata files does not matchInnoDB: the log sequence number in the ib_logfiles!151105 11:24:52  InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Restoring possible half-written data pages from the doublewriteInnoDB: buffer...151105 11:24:52  InnoDB: ERROR: We were only able to scan the log up toInnoDB: 2735615488, but a checkpoint was at 2735615806.InnoDB: It is possible that the database is now corrupt!InnoDB: 3 transaction(s) which must be rolled back or cleaned upInnoDB: in total 3 row operations to undoInnoDB: Trx id counter is 6D84900InnoDB: Last MySQL binlog file position 0 446116929, file name /data/mysql/binlog/mysql-bin.001288InnoDB: Cleaning up trx with id 6D7C116InnoDB: Cleaning up trx with id 6D74419InnoDB: Cleaning up trx with id 6D6F27E151105 11:24:52  InnoDB: Waiting for the background threads to start151105 11:24:52  InnoDB: Assertion failure in thread 140108568532736 in file trx0purge.c line 848InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_noInnoDB: We intentionally generate a memory trap.InnoDB: Submit a detailed bug report to http://bugs.mysql.com.InnoDB: If you get repeated assertion failures or crashes, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.htmlInnoDB: about forcing recovery.03:24:52 UTC - mysqld got signal 6 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully helpdiagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.Please help us make Percona Server better by reporting anybugs at http://bugs.percona.com/key_buffer_size=402653184read_buffer_size=4194304max_used_connections=0max_threads=2002thread_count=0connection_count=0It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 16818378 K  bytes of memoryHope that's ok; if not, decrease some variables in the equation.Thread pointer: 0x0Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0 thread_stack 0x40000/usr/local/percona-server-5.5.38-35.2/bin/mysqld(my_print_stacktrace+0x2e)[0x7785ae]/usr/local/percona-server-5.5.38-35.2/bin/mysqld(handle_fatal_signal+0x43a)[0x66a1da]/lib64/libpthread.so.0[0x379c20f710]/lib64/libc.so.6(gsignal+0x35)[0x379be32925]/lib64/libc.so.6(abort+0x175)[0x379be34105]/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x814fb0]/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x8153f7]/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x8e318f]/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x8d98d5]/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x813c4e]/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x809d2c]/lib64/libpthread.so.0[0x379c2079d1]/lib64/libc.so.6(clone+0x6d)[0x379bee8b5d]You may download the Percona Server operations manual by visitinghttp://www.percona.com/software/percona-server/. You may find informationin the manual which will help you identify the cause of the crash.151105 11:24:52 mysqld_safe mysqld from pid file /data/mysql/datafile/mysql.pid ended

分析日誌後發現，資料庫無法重啟的原因是因為ibdata1檔案損壞，重啟後無法正常恢複。

現在我們就需要跳過恢複步驟，修改my.cnf檔案，在my.cnf中的[mysqld]中添加

innodb_force_recovery = 6innodb_purge_threads = 0

innodbforcerecovery可以設定為1-6,大的數字包含前面所有數位影響。
1. (SRVFORCEIGNORECORRUPT):忽略檢查到的corrupt頁。
2. (SRVFORCENOBACKGROUND):阻止主線程的運行，如主線程需要執行full purge操作，會導致crash。
3. (SRVFORCENOTRXUNDO):不執行交易回復操作。
4. (SRVFORCENOIBUFMERGE):不執行插入緩衝的合併作業。
5. (SRVFORCENOUNDOLOGSCAN):不查看重做日誌，InnoDB儲存引擎會將未提交的事務視為已提交。
6. (SRVFORCENOLOG_REDO):不執行前滾的操作。

再次啟動，如果還無法啟動則刪除資料目錄datafile下的 ibdata1,ib_logfile*等檔案。

啟動後匯出MySQL資料庫，重新恢複即可。

MySQL意外斷電，InnoDB資料庫恢複