MySQL資料庫InnoDB引擎資料表的恢複樣本

保持資料的完整性和一致性（Integrity and consistency）是資料庫在商務應用中的核心內容，MySQL資料庫使用InnoDB引擎來實現交易處理（transaction），因此針對使用 InnoDB 類型引擎的資料表就需要有有更多檢查和限制。而相應地，這也就更容易出現因為資料一致性和完整性而導致無法正常讀取表中部分資料甚至全部記錄的問題，因此在實際應用之中，您有可能需要比較多地面對如何恢複 InnoDB 資料表的問題。

本文筆記一次 InnoDB 資料表恢複的過程。

另外，值得一提的是，從MySQL 5.5.5 版本開始，預設引擎已經改為InnoDB了。

http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB.

（1） Get backup files(取得相關備份檔案)：

Data File:  /var/lib/mysql/ibdata1
Logs File:  /var/lib/mysql/ib_logfile0
Logs File:  /var/lib/mysql/ib_logfile1

Database:  bizness
Table Name:  transaction
Table File:  /var/lib/mysql/bizness/transaction.frm

（2） Setup temporary mysql service for recover（設定臨時資料庫以供恢複操作)

Create database and InnoDB table with same name engine(not matter the field name):

mysql> CREATE DATABASE bizness;
mysql> CREATE TABLE transaction(a INT) ENGINE=InnoDB;

Stop the temporary mysql service, and copy all the backup files to replace the
current databases files.(ibdata1,ib_logfile0,ib_logfile1,transaction.frm)

# cp -p ibdata1 /var/lib/mysql/
# cp -p ib_logfile0 /var/lib/mysql/
# cp -p ib_logfile1 /var/lib/mysql/
# cp -p transaction.frm /var/lib/mysql/bizness/
# chown mysql:mysql /var/lib/mysql/ibdata
# chown mysql:mysql /var/lib/mysql/ib_logfile0
# chown mysql:mysql /var/lib/mysql/ib_logfile1
# chown mysql:mysql /var/lib/mysql/transaction.frm

(3) Check the old logfiles size:

# /bin/ls -l -b /var/lib/mysql/ib_logfile*;
------------------------------------------------------------------------------
-rw-rw---- 1 mysql mysql  5242880  9月  2 13:29 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880  7月 30 13:46 ib_logfile1
------------------------------------------------------------------------------
Note: the size is 5242880 bytes.

(4) Now start up mysql in rescue mode

# /usr/libexec/mysqld --innodb-log-file-size=5242880 --innodb-force-recovery=6
------------------------------------------------------------------------------
InnoDB: Initializing buffer pool, size = 8.0M
InnoDB: Completed initialization of buffer pool
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
InnoDB: Started; log sequence number 0 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
[Note] Event Scheduler: Loaded 0 events
[Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.61'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
------------------------------------------------------------------------------

And then dump the database to SQL file with mysqldump command:

# mysqldump -u root -p bizness > bizness.sql
或者：
# mysqldump -u root -p bizness transaction > bizness.transaction.sql

如果成功匯出了資料文本，那麼就可以它用來進行恢複了，根據您所遇到的實際情況，
您或許有可能需要現刪除已經崩潰的資料表，然後再匯入恢複出來的SQL文本。例如：

# mysql -u root -p bizness < bizness.sql
或者：
# mysql -u root -p bizness < bizness.transaction.sql

Note: mysql importing do not need to indicate the table name .

如有需要，可查看協助說明：

# /usr/libexec/mysqld --verbose --help

--innodb-log-file-size=#
  Size of each log file in a log group.

--innodb-force-recovery=#
  Helps to save your data in case the disk image of the database becomes corrupt.
  0 by default (normal startup without forced recovery)

官方網站的參考文檔：
http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

1 (SRV_FORCE_IGNORE_CORRUPT)
  即使發現了損壞頁也繼續讓服務運行，這個選項對於備份或者轉存當前資料尤為有用。
  Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name
  jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)
  防止主線程和任何清除線程運行，如果清除操作會導致伺服器掛掉，此選項有助於防止它。
  Prevents the master thread and any purge threads from running. If a crash
  would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)
  恢複後不復原事務。
  Does not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)
  如果插入到緩衝區的合併作業會導致系統崩潰，那麼插入將不會被執行。
  Prevents insert buffer merge operations. If they would cause a crash,
  does not do them. Does not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
  啟動資料庫時，忽略撤消日誌，InnoDB將未完成事務當作已經完成。
  Does not look at undo logs when starting the database:
  InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)
  啟動資料庫時，忽略與恢複相關的前滾日誌。
  Does not do the redo log roll-forward in connection with recovery.

使用上述選項值時，您只能進行基本的SELECT查詢，不能進行其他查詢。
With this value, you might not be able to do queries other than a basic
SELECT * FROM t, with no WHERE, ORDER BY, or other clauses. More complex
queries could encounter corrupted data structures and fail.

If corruption within the table data prevents you from dumping the entire table
contents, a query with an ORDER BY primary_key DESC clause might be able to dump
the portion of the table after the corrupted part.