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.
