Monday, April 23, 2012

Three Options to Repair Corrupt MySQL Database Tables

MySQL provides three options to repair corrupt MySQL database tables:
  • 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 
Silent mode 
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 
Verbose mode 
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:

--backup, -B
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
--extend-check, -e
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.
--force, -f
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.
--recover, -r
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.
--safe-recover, -o
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.
--sort-recover, -n
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
--tmpdir=path, -t
Passes a new path for storing temporary files if you dont want to use the contents of the TMPDIR environment variable
--quick, -q
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.
--unpack, -u
Unpacks a file that has been packed with the myisampack utility.

No comments:

Post a Comment