InnoDB Table Maintenance
If a table check indicates problems, restore the table to a consistent state by dumping it with mysqldump, dropping it, and re-creating it from the dump file. In the event of a crash of the MySQL server or the host on which it runs, some InnoDB tables might be in an inconsistent state. InnoDB performs auto-recovery as part of its startup sequence.
Use CHECK TABLE or a client program to find inconsistencies, incompatibilities, and other problems. Restore a table by dumping it with mysqldump:
shell> mysqldump [db_name] [table_name] > [dump_file]
Then drop it and re-create it from the dump file.
shell> mysql [db_name] < [dump_file]
Rarely, the server does not start due to the failure of auto-recovery. If that happens, use the following procedure:
- Restart the server with the --innodb_force_recovery option set to a value from 1 to 6. These values indicate increasing levels of caution in avoiding a crash, and increasing levels of tolerance for possible inconsistency in the recovered tables. A good value to start with is 4, which prevents insert buffer merge operations.
- When the server is started with --innodb_force_recovery set to a non-zero value, InnoDB prevents INSERT, UPDATE, or DELETE operations. Therefore, you should dump the InnoDB tables and then drop them while the option is in effect. Then restart the server without the --innodb_force_recovery option. When the server comes up, recover the InnoDB tables from the dump files.
- If the preceding steps fail, restore the tables from a previous backup.
MyISAM Table Maintenance
The default CHECK TABLE check type is MEDIUM for both dynamic and static format tables. If a static-format table type is set to CHANGED or FAST, the default is QUICK. The row scan is skipped for CHANGED and FAST because the rows are very seldom corrupted.
shell> myisamchk --medium-check [table_name]
CHECK TABLE changes the table if the table is marked as "corrupted" or "not closed properly". If it does not find any problems in the table, it marks the table status as “up to date.” If a table is corrupted, the problem is most likely in the indexes and not in the data. The --myisam-recover option value can consist of a comma-separated list of one or more of the following values:
- DEFAULT: Default checking
- BACKUP: Tells the server to make a backup of any table that it must change
- FORCE: Performs table recovery even when causing the loss of more than one row of data
- QUICK: Performs a quick recovery. The recovery skips tables that have no gaps (known as “holes”) between rows resulting from deletes or updates.
For example, to tell the server to perform a forced recovery of MyISAM tables found to have problems but to make a backup of any table that it changes, add the following to your option file:
[mysqld] myisam-recover=FORCE,BACKUP
MEMORY Table Maintenance
MEMORY tables do not release memory when rows are deleted using a DELETE...WHERE statement. To release memory, you must perform a null ALTER TABLE operation.
ARCHIVE Table Maintenance
ARCHIVE compression issues:
- Table rows are compressed as they are inserted.
- On retrieval, rows are uncompressed on demand.
- Some SELECT statements can deteriorate the compression.
Use of OPTIMIZE TABLE or REPAIR TABLE can achieve better compression. OPTIMIZE TABLE works when the table is not being accessed (by read or write).