Принудительное восстановление InnoDB

Статья взята Forcing InnoDB Recovery

Если Ваша база данных повреждена и Вы захотите восстановить таблицы вашей базы данных с Выбором в файл (SELECT INTO OUTFILE). Обычно, восстановленные таким образом данные - не повреждены (intact). Но в данном режиме, повреждение базы может наступить даже, например, при выполнении выражения SELECT * FROM tbl_name или любых фоновых операций с базой данных. Начиная с MySQL 3.23.44, существует InnoDB переменная, которую Вы можете использовать, чтобы заставить стартовать InnoDB, но Вы должны предотвратить запуск любых фоновых операций с базой. После этого Вы сможете сделать дамп Ваших таблиц. Например, Вы можете добавить следующие строчки в секцию [mysqld] файла настроек перед перезапуском Вашего сервера:

[mysqld]
innodb_force_recovery = 4

Для версий MySQL 4.0, используйте следующие строки:

[mysqld]
set-variable = innodb_force_recovery=4

If there is database page corruption, you may want to dump your tables from the database with SELECT INTO OUTFILE. Usually, most of the data obtained in this way is intact. Even so, the corruption may cause SELECT * FROM tbl_name statements or InnoDB background operations to crash or assert, or even to cause InnoDB roll-forward recovery to crash. Starting from MySQL 3.23.44, there is an InnoDB variable that you can use to force the InnoDB storage engine to start up, and you can also prevent background operations from running, so that you are able to dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server:

[mysqld]
innodb_force_recovery = 4

Before MySQL 4.0, use this syntax instead:

[mysqld]
set-variable = innodb_force_recovery=4

The allowable non-zero values for innodb_force_recovery follow. A larger number includes all precautions of smaller numbers. If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 6 is more drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

1 (SRV_FORCE_IGNORE_CORRUPT)

Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Do not run transaction rollbacks after recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevent also insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)

Do not do the log roll-forward in connection with recovery.

Starting from MySQL 3.23.53 and 4.0.4, you can SELECT from tables to dump them, or DROP or CREATE a table even if forced recovery is used. If you know that a certain table is causing a crash in rollback, you can drop it. You can use this also to stop a runaway rollback caused by a failing mass import or ALTER TABLE. You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

The database must not otherwise be used with any non-zero value of innodb_force_recovery. As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.