Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use myisamchk and mysqlcheck tools to repair damaged MySQL database files. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

Because the server database hard disk space is full, a "Duplicate entry '' for key 'username'" error occurs due to a large number of write failures.

If there is such a mysql database error, it is likely that there is a problem with the mysql database index. What is a MySQL database index?

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

This usually happens when writing to a database, such as Discuz! 4.1 The forum program requires that the username of all members must be unique, that is, the index of username is unique. In this case, if you forcibly insert an existing username record into the cdb_members table, you will send this error, or update the username of a record to an existing username.

For example, a netizen's dedecms website has a problem, visit a look, sure enough full-screen error, check mysql log, error message is:

Table .dedecmsv4dede_archives is marked as crashed and should be repaired

Tip says cms article table dede_archives is marked as faulty and needs to be fixed.

So quickly restore historical data, online to find out why. Eventually the problem will be solved.

The solution is as follows:

Find the bin/myisamchk tool in the mysql installation directory and type:

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

The myisamchk tool will then help you recover the index of the data table. Restart mysql, problem solved.

Then, repair mysql database generally can myisamchk tool or mysqlcheck tool with these two methods:

1. myisamchk tool

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

# service mysql stop (stop MySQL );

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

# service mysql start

Myisamchk automatically checks and fixes index errors in the data table.

2. mysqlcheck tool

Use mysqlcheck without stopping MySQL, hot fixes can be performed. The procedure is as follows:

# mysqlcheck -r discuz.*

# service mysql stop (stop MySQL );

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

# service mysql start

Myisamchk automatically checks and fixes index errors in the data table.

Note: Whether it is myisamchk or mysqlcheck, do not use-f to force repair under normal circumstances. The-f parameter will delete some error data to try to repair when the general repair fails. Don't use-f unless you have to.

The following is a supplement from other netizens

Check to repair all databases:

# mysqlcheck -A -o -r -p

Enter password:

db1 OK

db2 OK

db3 OK

db4 OK

......

......

......

Repair specified database with

# mysqlcheck -o -r Database_NAME -p

can

Command Details:

The mysqlcheck client can check and repair MyISAM tables. It can also optimize and analyze tables.

mysqlcheck functions like myisamchk, but works differently. The main difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk applies 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 users with a convenient way to use 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 call mysqlcheck:

shell> mysqlcheck[options] db_name [tables]

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

shell> mysqlcheck[options] --all--database

If no tables are specified or if the---database or--all--database options are used, the entire database is checked.

Mysqlcheck has one special feature compared to other clients. Renaming binaries changes the default behavior of checklists (--check). If you want a tool that fixes tables by default, simply copy mysqlcheck back to mysqlrepair, or use a symbolic link to mysqlrepair to link mysqlcheck. If mysqlrepair is called, follow the command to repair the table.

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 help messages and exit.

· --all--database，-A

Check all tables in all databases. As with the---database option, name all databases on the command line.

· --all-in-1，-1

Instead of issuing one statement for each table, execute one statement for each database that names all the tables to be processed in the database.

· --analyze，-a

Analysis sheet.

· --auto-repair

If one of the checked tables is corrupted, repair it automatically. After checking all tables, automatically perform all required repairs.

· --character-sets-dir=path

The installation directory for the character set. See Section 5.10.1,"Character Sets for Data and Sorting."

· --check，-c

Errors in the checklist.

· --check-only-changed，-C

Check only tables that have changed since the last check or that have not been closed correctly.

· --compress

Compress all information sent between client and server (if both support compression).

· ---database，-B

Process all named tables in the database. With this option, all character name parameters are treated as database names, not table names.

· ---debug[=debug_options]，-# [debug_options]

Write debug logs. The debug_options string is usually'd:t:o,file_name'.

· --default-character-set=charset

Use charsetas default character set. See Section 5.10.1,"Character Sets for Data and Sorting."

· --extended，-e

If you are checking tables using this option, you can make sure they are 100% consistent, but it takes a long time.

If you are using this option to repair tables, running extended repair will not only take a long time to perform, but will also generate a lot of garbage rows!

· --fast，-F

Check only tables that are not closed correctly.

· --force，-f

Continue even if SQL errors occur.

· --host=host_name，-h host_name

Connect MySQL servers on a given host.

· --medium-check，-m

Performs faster checks than the--extended operation. Only 99.99% of errors can be found, which is sufficient in most cases.

· --optimize，-o

Optimize the table.

· --password[=password]，-p[password]

Password used when connecting to the server. If you use the short option form (-p), there must be no spaces between the option and the password. If there is no password value after the--password or-p option on the command line, prompt for a password.

· --port=port_num，-P port_num

TCP/IP port number used for connection.

· --protocol={TCP | SOCKET | PIPE | MEMORY}

Connection protocol used.

· --quick，-q

If you are using this option in a checklist, it prevents scanning rows to check for incorrect links. This is the fastest way to check.

If you are using this option to repair tables, it attempts to repair only index trees. This is the fastest way to repair.

· --repair，-r

Perform fixes that fix most issues, but not unique values.

· --silent，-s

Silent mode. Print only error messages.

· --socket=path，-S path

Socket file for connection.

· --tables

Overrides the---database or-B option. All arguments following the option are treated as table names.

· --user=user_name，-u user_name

MySQL username to use when connecting to the server.

· --verbose，-v

Verbose mode. Prints information about the operation of the program at each stage.

· --version，-V

Display version information and exit.

About "how to repair damaged MySQL database files using myisamchk and mysqlcheck tools" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.