MySQL provides three options to repair corrupt MySQL database tables:
- The REPAIR TABLE
- The mysqlcheck command-line utility
- The myisamchk command-line utility
Repairing a table with REPAIR TABLE
You can repair corrupt MySQL table by using REPAIR TABLE command. You can only recover MyISAM tables by using following command:
REPAIR TABLE tablename[,tablename1...] [options]
The available options in REPAIR TABLE command are:
The quickest, as the data file is not modified.
Use as a last option & attempt to recover every possible data row file, which can result in garbage rows.
To be used if the .MYI file is missing or Uses the .frm file definitions to rebuild the indexes.
Repairing tables with mysqlcheck
The mysqlcheck command-line utility also used to repair corrupt MySQL database tables. It can be used while the server is running, the syntax is:
mysqlcheck [options] db_name [tbl_name...]
The available options are:
Used to check all tables in all databases
Used to analyze the tables
If table is corrupted, the command automatically fix it
The directory where character sets are installed
Check the tables for any corruption & other errors
Used to check only tables that have changed since the last check
Invoke CHECK TABLE with the FOR UPGRADE option
Compress all information sent between the client and the server
Process all tables in the named databases
Write a debugging log
Use charset_name as the default character set
Check and repair tables
Check only tables that have not been closed properly
Continue even if an SQL error occurs
Display help message and exit
Connect to the MySQL server on the given host
Do a check that is faster than an --extended operation
Optimize the tables
The password to use when connecting to the server
On Windows, connect to server using a named pipe
The TCP/IP port number to use for the connection
The connection protocol to use
The fastest method of checking
Perform a repair that can fix almost anything except unique keys that are not unique
For connections to localhost
The path to a file that contains a list of trusted SSL CAs
The path to a directory that contains trusted SSL CA certificates in PEM format
The name of the SSL certificate file to use for establishing a secure connection
A list of allowable ciphers to use for SSL encryption
The name of the SSL key file to use for establishing a secure connection
The server's Common Name value in its certificate is verified against the host name used when connecting to the server
Overrides the --databases or -B option
For repair operations on MyISAM tables
The MySQL user name to use when connecting to the server
Display version information and exit
Repairing tables with myisamchk
Similar to mysqlcheck, the difference only that it is used when server is not running & works only for MyISAM tables. The syntax is:
myisamchk [options[ [tablenames].
The available options are:
Makes a .BAK backup of the table before repairing it
Corrects the checksum
--data-file-length=#, -D #
Specifies the maximum length of the data file, when recreating
Attempts to recover every possible row from the data file. This option should not be used except as a last resort, as it may produce garbage rows.
Overwrites old temporary .TMD files instead of aborting if it encounters a pre-existing one.
keys-used=#, -k #
Can make the process faster by specifying which keys to use. Each binary bit stands for one key starting at 0 for the first key.
The most commonly used option, which repairs most corruption. If you have enough memory, increase the sort_buffer_size to make the recover go more quickly. Will not recover from the rare form of corruption where a unique key is not unique.
More thorough, yet slower repair option than -r, usually only used only if -r fails. Reads through all rows and rebuilds the indexes based on the rows. This also uses slightly less disk space than a -r repair since a sort buffer is not created. You should increase the key_buffer_size value to improve repair speed if there is available memory.
MySQL uses sorting to resolve the indexes, even if the resulting temporary files are very large.
The directory containing the character sets
Specifies a new character set for the index
Passes a new path for storing temporary files if you dont want to use the contents of the TMPDIR environment variable
The fastest repair, since the data file is not modified. A second -q will modify the data file if there are duplicate keys. Also uses much less disk space since the data file is not modified.
Unpacks a file that has been packed with the myisampack utility.