Checking and Repairing Tables


Checking tables for corruption is part of routine table maintenance and is also part of your disaster recovery routine, for example, in the event of a power failure.

MySQL allows us to check tables in three ways: using CHECK TABLE , using myisamchk (or isamchk ), and using mysqlcheck . We can then repair any problem tables using REPAIR TABLE or again with myisamchk (or isamchk ) or mysqlcheck .

There are a few factors you should take into account when determining which of these options to use. The CHECK and REPAIR commands can be used from inside MySQL, whereas the other techniques are used from the command line. CHECK and REPAIR can be used on both MyISAM and InnoDB tables. The isamchk script can be used on ISAM tables, whereas myisamchk and mysqlcheck can be used on MyISAM tables.

You should not use myisamchk or isamchk on tables that are currently in use. It is better if you take the server down before using these scripts, but you can resort to locking if needed. If you use these scripts on tables while they are being used by other MySQL threads, your data may become corrupted. CHECK , REPAIR , and mysqlcheck are all safe to use when the server is up and tables are in use.

We will look at the use of each of these tools.

Checking and Repairing Tables with CHECK and REPAIR

You can check a table with CHECK TABLE as in this example:

 
 check table department; 

CHECK TABLE works on MyISAM and InnoDB tables.

This should (all being well) give you a response like this:

 
 +---------------------+-------+----------+----------+  Table                Op     Msg_type  Msg_text  +---------------------+-------+----------+----------+  employee.department  check  status    OK        +---------------------+-------+----------+----------+ 1 row in set (0.00 sec) 

You may also get Table is already up to date , which also means everything is fine.

If you get any other message, you have a problem and should try to repair the table. You can do this with REPAIR TABLE (as long as it's a MyISAM table) as shown here:

 
 repair table t1; 

If the repair works (or if no repair was actually needed), you should get a result similar to this:

 
 +---------+--------+----------+----------+  Table    Op      Msg_type  Msg_text  +---------+--------+----------+----------+  test.t1  repair  status    OK        +---------+--------+----------+----------+ 1 row in set (0.03 sec) 

If you get any message other than OK , the REPAIR hasn't worked and you will need to resort to the more powerful myisamchk .

Checking and Repairing Tables with myisamchk

In this chapter we consider only myisamchk and ignore isamchk . If you have any ISAM tables, we suggest that you convert them to MyISAM (see Chapter 9, "Understanding MySQL's Table Types").

The myisamchk program is incredibly useful and will get you out of some unpleasant spots you might find yourself in. Again, remember that you should not use myisamchk when the server is in use. It is safest to stop the server.

The simplest way to invoke myisamchk is by typing

 
 myisamchk  table  

at the command prompt.

The table should be the path to a .MYI file that represents a MyISAM table.

This will report virtually all errors. If it doesn't seem to be finding your problem, you can try running it with the -m switch. The default behavior looks for corruption in the indexes; with this switch, the rows are scanned as well.

You can also repair errors with myisamchk . The vast majority of table errors you will encounter with MyISAM tables can be fixed in this way. You can invoke myisamchk with the -q -r options for quick recovery, as shown here:

 
 myisamchk -q -r  table  

If this doesn't work, you can back up the data file and then try a full recovery:

 
 myisamchk -r  table  

If that doesn't work, you can try the --safe-recover option, which will fix some errors not fixed by the -r option:

 
 myisamchk --safe-recover  table  

The myisamchk program has a large number of options, which you can review by typing myisamchk at the command prompt with no parameters.

Checking and Repairing Tables with mysqlcheck

The mysqlcheck program can be used to check MyISAM and InnoDB tables and to repair MyISAM tables safely while the server is up and running.

To check database tables with mysqlcheck , you can invoke it as in the following example:

 
 mysqlcheck -u  username  -p employee 

You can follow this with a list of tables you would like checked, but by default it will check all the tables in the specified database (a nice feature). If all is well, you should see output similar to the following:

 
 employee.assignment                                OK employee.client                                    OK employee.department                                OK employee.employee                                  OK employee.employeeSkills                            OK 

You can also use the --databases switch to specify a list of databases to check or the --all-databases option to check all the databases on the server.

You can use mysqlcheck with the -r option to repair any corrupted MyISAM tables that it encounters.



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net