How to quickly repair corrupted MySQL database files using the myisamchk and mysqlcheck tools

Because the server's database hard disk space was full, a large amount of data writing failed, resulting in the error "Duplicate entry '' for key 'username'".

If such a MySQL database error occurs, it is likely that there is a problem with the MySQL database index. So, what is a MySQL database index?

Analysis: If the index is primary or unique, then the field corresponding to the data in the data table must ensure the uniqueness of each record. Otherwise this error will occur.

This usually occurs when writing to the database. For example, the Discuz! 4.1 forum program requires that all members' usernames must be unique, that is, the username index is unique. At this time, if you forcibly insert an existing username record into the cdb_members table, this error will be displayed, or if you update the username of a record to an existing username.

For example, a netizen's dedecms website had a problem. When he visited it, he saw an error message on the full screen. He checked the MySQL log and found the error message was:

Table .dedecmsv4dede_archives is marked as crashed and should be repaired

It prompts that the cms article table dede_archives is marked as problematic and needs to be repaired.

So I quickly restored the historical data and searched the Internet for the cause. Finally the problem was solved.

The solution is as follows:

Find the bin/myisamchk tool in the mysql installation directory and enter it in the command line:

myisamchk -c -r ../data/dedecmsv4/dede_archives.MYI

Then the myisamchk tool will help you restore the index of the data table. Restart mysql and the problem is solved.

Then, to repair the MySQL database, you can generally use the myisamchk tool or the mysqlcheck tool in these two ways:

1. myisamchk tool

To use myisamchk you must temporarily stop the MySQL server . For example, we want to overhaul the discuz database. Do the following:

# service mysql stop (Stop MySQL);

# myisamchk -r /absolute path of database file/*MYI

# service mysql start

myisamchk automatically checks and repairs index errors in data tables.

2. mysqlcheck tool

Using mysqlcheck does not require stopping MySQL and allows hot fixes . The steps are as follows:

# mysqlcheck -r discuz.*

# service mysql stop (Stop MySQL);

# myisamchk -r /absolute path of database file/*MYI

# service mysql start

myisamchk automatically checks and repairs index errors in data tables.

Note: For both myisamchk and mysqlcheck, do not use -f to force a repair. The -f parameter will delete some erroneous data in an attempt to repair the problem when general repairs fail. Therefore, do not use -f unless absolutely necessary.

The following are the additions from other netizens

Check and repair all databases:

# mysqlcheck -A -o -r -p
Enter password:
db1 OK
db2 OK
db3 OK
db4 OK
......
......
......

Repair the specified database

# mysqlcheck -o -r Database_NAME -p

You can

Command details:

The mysqlcheck client can check and repair MyISAM tables. It can also optimize and analyze tables.
mysqlcheck functions similarly to myisamchk, but works differently. The main difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when the server is not running. The advantage of using mysqlcheck is that you do not need to stop the server to check or repair tables.
MySQLcheck provides a convenient way for users to use the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE. It determines which statement to use in the operation to be performed and then sends the statement to the server to be executed.
There are three ways to invoke mysqlcheck:

shell> mysqlcheck [options] db_name [tables]
shell> mysqlcheck [options] ---database DB1 [DB2 DB3...]
shell> mysqlcheck [options] --all --database

If no table is specified or the --database or --all-database option is used, the entire database is checked.
Compared to other clients, mysqlcheck has a special feature. Renaming the binary can change the default behavior of the checklist (--check). If you want a tool that can repair tables by default, just copy mysqlcheck back to mysqlrepair, or use a symbolic link mysqlrepair to link mysqlcheck. If you call mysqlrepair, you can repair the table according to the command.

The following names can be used to change the default behavior of mysqlcheck:

mysqlrepair

The default option is --repair

mysqlanalyze
The default option is --analyze

mysqloptimize
The default option is --optimize

mysqlcheck supports the following options:
---help, -?
Display a help message and exit.
--all--database, -A
Check all tables in all databases. Same as using the --database option, except that all databases are named on the command line.
--all-in-1, -1
Instead of issuing one statement for each table, one statement is executed for each database that names all tables in the database to be processed.
--analyze, -a
Analysis table.
--auto-repair
If a checked table is corrupted, it is automatically repaired. After checking all tables, any necessary repairs are automatically performed.
--character-sets-dir=path
The directory where the character sets are installed. See Section 5.10.1, “Character Sets for Data and Sorting”.
--check, -c
Check the table for errors.
--check-only-changed, -C
Only tables that have been changed since the last check or that were not closed properly are checked.
--compress
Compresses all information sent between the client and server (if both support compression).
---database, -B
Processes all tables named in the database. With this option, all string name arguments are treated as database names rather than table names.
---debug[=debug_options], -#[debug_options]
Write debug log. The debug_options string is typically 'd:t:o,file_name'.
--default-character-set=charset
Use charsetas the default character set. See Section 5.10.1, “Character Sets for Data and Sorting”.
--extended, -e
If you are using this option to check tables, you can ensure that they are 100% consistent, but it may take a long time.
If you are using this option to repair a table, running an extended repair will not only take a long time to execute, but will also generate a large number of garbage rows!
--fast, -F
Only tables that were not closed properly are checked.
--force, -f
Continue even if SQL errors occur.
--host=host_name, -h host_name
Connect to the MySQL server on the given host.
--medium-check, -m
Performs a faster check than --extended. Only 99.99% of errors are found, which is good enough in most cases.
--optimize, -o
Optimize the table.
--password[=password], -p[password]
The password to use when connecting to the server. If the short option form (-p) is used, there must be no space between the option and the password. If no password value follows the --password or -p option on the command line, you are prompted for a password.
--port=port_num, -P port_num
The TCP/IP port number to use for the connection.
--protocol={TCP | SOCKET | PIPE | MEMORY}
The connection protocol to use.
--quick, -q
If you are using this option on a check table, it prevents the check from scanning rows to check for incorrect links. This is the quickest way to check.
If you are repairing a table using this option, it attempts to repair only the index tree. This is the quickest fix.
--repair, -r
Perform a fix that fixes most issues except when the unique values ​​are not unique.
--silent, -s
Silent mode. Only error messages are printed.
--socket=path, -S path
The socket file to use for the connection.
--tables
Overrides the --database or -B option. All arguments following the option are treated as table names.
--user=user_name, -u user_name
The MySQL user name to use when connecting to the server.
--verbose, -v
Verbose mode. Prints information about each stage of program operation.
--version, -V
Display version information and exit.