Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
To investigate database page corruption, you might dump your tables from the database with SELECT ... INTO OUTFILE. Usually, most of the data obtained in this way is
intact. Serious corruption might cause SELECT * FROM
statements or tbl_nameInnoDB background operations to crash or assert, or even cause InnoDB roll-forward recovery to crash. In such cases, use the innodb_force_recovery option to force the InnoDB
storage engine to start up while preventing background operations from running, so that you can 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
innodb_force_recovery
is 0 by default (normal startup without forced recovery). The permissible nonzero values for innodb_force_recovery follow. A larger number includes all precautions of
smaller numbers. If you can 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)
Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM jump
over corrupt index records and pages, which helps in dumping tables. tbl_name
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)
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.
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 clause might be able to dump the portion of the table after the corrupted part.primary_key
DESC
The database must not otherwise be used with any nonzero value of innodb_force_recovery. As a safety measure, InnoDB
prevents INSERT,
UPDATE,
or DELETE operations when innodb_force_recovery is greater than 0.
You can SELECT from tables to dump them, or DROP or CREATE tables even if forced recovery is used. If you know that a given table is
causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a
failing mass import or ALTER TABLE: 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.