Three Options to Repair Corrupt MySQL Database Tables

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:

QUICK
The quickest, as the data file is not modified.

 
EXTENDED
Use as a last option & attempt to recover every possible data row file, which can result in garbage rows.

 
USE_FRM
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:

--all-databases
Used to check all tables in all databases 

 
--analyze
Used to analyze the tables 

 
--auto-repair
If table is corrupted, the command automatically fix it 

 
--character-sets-dir=path
The directory where character sets are installed 

 
--check
Check the tables for any corruption & other errors 

 
--check-only-changed
Used to check only tables that have changed since the last check 

 
--check-upgrade
Invoke CHECK TABLE with the FOR UPGRADE option 

 
--compress
Compress all information sent between the client and the server 

 
--databases
Process all tables in the named databases 

 
--debug[=debug_options]
Write a debugging log 

 
--default-character-set=charset_name
Use charset_name as the default character set 

 
--extended
Check and repair tables 

 
--fast
Check only tables that have not been closed properly 

 
--force
Continue even if an SQL error occurs 

 
--help
Display help message and exit 

 
--host=host_name
Connect to the MySQL server on the given host 

 
--medium-check
Do a check that is faster than an --extended operation 

 
--optimize
Optimize the tables 

 
--password[=password]
The password to use when connecting to the server 

 
--pipe
On Windows, connect to server using a named pipe 

 
--port=port_num
The TCP/IP port number to use for the connection 

 
--protocol=type
The connection protocol to use 

 
--quick
The fastest method of checking 

 
--repair
Perform a repair that can fix almost anything except unique keys that are not unique 

 
--silent
Silent mode 

 
--socket=path
For connections to localhost 

 
--ssl-ca=file_name
The path to a file that contains a list of trusted SSL CAs 

 
--ssl-capath=dir_name
The path to a directory that contains trusted SSL CA certificates in PEM format 

 
--ssl-cert=file_name
The name of the SSL certificate file to use for establishing a secure connection 

 
--ssl-cipher=cipher_list
A list of allowable ciphers to use for SSL encryption 

 
--ssl-key=file_name
The name of the SSL key file to use for establishing a secure connection 

 
--ssl-verify-server-cert
The server's Common Name value in its certificate is verified against the host name used when connecting to the server 

 
--tables
Overrides the --databases or -B option 

 
--use-frm
For repair operations on MyISAM tables 

 
--user=user_name,
The MySQL user name to use when connecting to the server 

 
--verbose
Verbose mode 

 
--version
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

 
--correct-checksum
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.

 
--character-sets-dir=...
The directory containing the character sets

 
--set-character-set=name
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.