www.tecpages.com

mysqlcheck and myisamchk are used to check and repair tables (MYISAM Tables). They can help you keep your tables free from errors. If any errors occurred to MySQL database due to a server crash or by an unexpected MySQL service issue, those commands can be used to recover or repair your valuable MySQL databases. So what is the difference between mysqlcheck and myisamchk . . . mysqlcheck is a client program that communicate with the MySQL server over a network connection. To use mysqlcheck the MySQL server should be online. This also means that you can use mysqlcheck to repair a remote database. myisamcheck isn't a client program. It is a utility program that works directly on the files that represent MyISAM tables (.MYI files, inside MySQL database directory). You must need read write privileges on these files to use myisamchk.

When using myisamcheck on tables inside a database, the tables must not be accessed during the check. The safest method is to bring the server down during a myisamchk. Otherwise it will not give the results you are expecting.

Now the theory part is over … ;). Lets do some MySQL database repairing . . .

BEFORE PERFORMING OPERATIONS SPECIFIED IN THIS TUTORIAL PLEASE TAKE A BACKUP OF YOUR DATABASE

First mysqlcheck . . . By default if we doesn't provide any options mysqlcheck takes the first argument as the database name and check all the tables inside it. Example :- 

mysqlcheck my_database

If you specify a second argument after "my_database", it will be considered as a table name inside "my_database" and mysqlcheck check that table only.

mysqlcheck my_database personal_table

You can specify any number of table names after the database name.

mysqlcheck my_database personal_table super_table

If you want to check more than one databases, use –databases or -B option. Now all arguments after it become databases not table names.

mysqlcheck –databases my_database jobs_database power_database

To check all databases, use –all-databases or -A.

mysqlcheck –all-databases

To recover the database, you have to use some more additional options. I will be telling that after explaining myisamchk. The usage of myisamchk is different from mysqlcheck. These are the steps to follow before issuing a myisamchk command,

A. Stop the MySQL server or service.
B. Change the location to the database directory. The default location of MySQL databases are in "/var/lib/mysql".
C. Now issue myisamchk followed by options then the table name/names to be checked and recovered.

Following example will give you a clearer image

cd /var/lib/mysql/
cd my_database
myisamchk dating_table
OR
myisamchk dating_table.MYI (Name of the table's index file. It should be name of the table followed by .MYI)

The default operation for myisamchk is to check the table for errors. It won't recover the table until you specify the recovery methods. This is also applicable for mysqlcheck also. Now lets do some action. Suppose you are seeing some .MYI errors in your browser regarding your database, that means the index file corresponding to that table is corrupted. To correct this error use the following,

myisamchk –recover corrupted_table.MYI
TIP :- To fix all the tables of database,
myisamchk –recover *.MYI
Please note you should be inside the database directory before issuing these commands.

If that doesn't fix the issue use mysqlcheck with these options,

mysqlcheck –auto-repair –databases datbase_name

Now some common options that are applicable for myisamchk and mysqlcheck

–analyse or -a
Analyze the distribution of key values in the table. This can improve performance of queries by speeding up index-based lookups.
–check or -c
Default action if no other options are specified. –check-only-changed or -C Skip table checking except for tables that have been changed since they were last checked or tables that haven't been properly closed. The latter condition might occur if the server crashes while a table is open.
–fast or -F
Skip table checking except for tables that haven't been properly closed.
–extended (for mysqlcheck), –extend-check (for myisamchk), or -e (for both programs) This will perform a through check and recovery, if used along with -r option for both programs. –medium-check or -m Run a medium table check. –repair (for mysqlcheck), –recover (for myisamchk), or -r (for both programs) Table/Data recover/repair options for mysqlcheck and myisamchk. This has been mentioned earlier.

Thats all . . . Don't forget to post your comments.