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.

相关内容