GO IT WORLD | IT TECH | IT NEWS

A regular part of a database administrator’s job is to do preventative maintenance, as well as to repair things when they go wrong. In spite of the best efforts, data errors can occur, such as in the case of a power failure that interrupts a write. Usually you can correct these fairly painlessly.

There are four main tasks involved in checking and repairing:

• Optimizing tables

• Analyzing tables (analyzes and stores the key distribution for MyISAM and BDB tables)

• Checking tables (checks the tables for errors, and, for MyISAM tables, updates the key statistics)

• Repairing tables (repairs corrupted MyISAM tables)

Optimizing Tables

Tables that contain BLOB and VARCHAR fields will, over time, become less optimized. Because these field types are variable in length, when records are updated, inserted, or deleted, they will not always take the same amount of space, the records will start to become fragmented, and empty spaces will remain. Just like with a fragmented disk, this situation will slow performance, so to keep MySQL in tiptop shape, you should regularly defragment it. The way to do this is to optimize the table, which can be done in a number of ways. There’s the OPTIMIZE TABLE statement, the mysqlcheck utility (if the server is running), or the myisamchk utility (if the server is not running or there is no interaction with the table).

Optimizing currently works only with MyISAM and partially with BDB tables. With MyISAM tables, optimizing does the following:

• Defragments tables where rows are split or have been deleted

• Sorts the indexes if they have not been already

• Updates the index statistics if they have not been already

With BDB tables, optimizing analyzes the key distribution (the same as ANALYZE TABLE; see the "Analyzing Tables with ANALYZE TABLE" section later in this chapter).

Optimizing Tables with the OPTIMIZE Statement

The OPTIMIZE statement is a SQL statement used when connected to a MySQL database. The syntax is as follows:

OPTIMIZE TABLE tablename

You can also optimize many tables at once, separating each with a comma:

mysql> OPTIMIZE TABLE customer,sales;
+------------------+----------+----------+-----------------------------+
| Table            | Op       | Msg_type | Msg_text                    |
+------------------+----------+----------+-----------------------------+
| firstdb.customer | optimize | status   | Table is already up to date |
| firstdb.sales    | optimize | status   | OK                          |
+------------------+----------+----------+-----------------------------+
2 rows in set (0.02 sec)

The customer table in this instance has already been optimized.

Optimizing Tables with mysqlcheck

mysqlcheck is a command-line utility that can perform numerous checking and repairing tasks besides optimization. A full description of all the mysqlcheck features follows later in the chapter in the section titled "Using mysqlcheck." The server must be running for you to use mysqlcheck. To optimize the customer table from the firstdb database, use the -o mysqlcheck option, as follows:

% mysqlcheck -o firstdb customer  -uroot -pg00r002b
firstdb.customer           Table is already up to date

mysqlcheck allows you to optimize more than one table at a time by listing all the tables after the database name:

% mysqlcheck -o firstdb customer sales  -uroot -pg00r002b
firstdb.customer           Table is already up to date
firstdb.sales              Table is already up to date

You could also optimize the entire database by leaving out any table references, with the following:

% mysqlcheck -o firstdb -uroot -pg00r002b

Optimizing Tables with myisamchk

Finally, you can use the myisamchk command-line utility when the server is down or not interacting with the server. (Flush the tables before running this statement if the server is up with mysqladmin flush-tables. You still need to make sure the server is not interacting with the table, though, or else corruption may result.) This is the oldest way of checking tables. You have to run myisamchk from the exact location of the table or specify the path leading to the table. A full description of all the myisamchk features follows later in this chapter in the section titled "Using myisamchk."

The equivalent of an optimize statement is as follows:

myisamchk --quick --check-only-changed --sort-index --analyze tablename

or as follows:

myisamchk -q -C -S -a tablename

For example:

% myisamchk --quick --check-only-changed --sort-index --analyze customer
- check key delete-chain
- check record delete-chain
- Sorting index for MyISAM-table 'customer'

The -r option repairs the table, but also eliminates wasted space:

% myisamchk -r sales   
- recovering (with sort) MyISAM-table 'sales'
Data records: 8
- Fixing index 1
- Fixing index 2

If you do not specify the path to the table index file, and you’re not in the right directory, you’ll get the following error:

% myisamchk -r customer
myisamchk: error: File 'customer' doesn't exist

Specifying the full path to the .MYI file corrects this:

% myisamchk -r /usr/local/mysql/data/firstdb/customer
- recovering (with keycache) MyISAM-table '/usr/local/mysql/data/firstdb/customer'
Data records: 0

Warning

Tables are locked during the optimization, so don’t run this during peak hours! Also, make sure you have a reasonable amount of free space on the system when running OPTIMIZE TABLE. If you try to run it when your system has almost or already run out of disk space, MySQL may not be able to complete the optimization, leaving your table unusable.

Optimizing is an important part of any administrative routine for databases that contain MyISAM tables and should be performed regularly.

Analyzing Tables

Analyzing tables improves performance by updating the index information for a table so that MySQL can make a better decision on how to join tables. The distribution of the various index elements is stored for later usage. (Analyzing currently only works with MyISAM and BDB tables.)

There are three ways to analyze a table:

• When connected to MySQL, with the ANALYZE TABLE statement

• With the mysqlcheck command-line utility

• With the myisamcheck command-line utility

Regular analysis of tables can help performance and should be a regular part of any maintenance routine.

Analyzing Tables with ANALYZE TABLE

ANALYZE TABLE is a statement used when connected to a database on the server. The syntax is as follows:

ANALYZE TABLE tablename

For example:

mysql> ANALYZE TABLE sales;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| firstdb.sales | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.00 sec)

The Msg_type (message type) is one of status, error, info, or warning. Here’s what would happen if the index file was missing altogether and you tried to analyze the table:

mysql> ANALYZE TABLE zz;
+------------+---------+----------+----------------------------------+
| Table      | Op      | Msg_type | Msg_text                         |
+------------+---------+----------+----------------------------------+
| firstdb.zz | analyze | error    | Table 'firstdb.zz' doesn't exist |
+------------+---------+----------+----------------------------------+
1 row in set (0.00 sec)

The table will only be analyzed again if it has changed since the last time it was analyzed:

mysql> ANALYZE TABLE sales;
+---------------+---------+----------+-----------------------------+
| Table         | Op      | Msg_type | Msg_text                    |
+---------------+---------+----------+-----------------------------+
| firstdb.sales | analyze | status   | Table is already up to date |
+---------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

Analyzing Tables with mysqlcheck

The mysqlcheck command-line utility is discussed fully later in this chapter in the section titled "Using mysqlcheck." The server needs to be running for you to use mysqlcheck, and it works only with MyISAM tables. To use it to analyze tables, you use the -a option:

% mysqlcheck -a firstdb sales -uroot -pg00r002b
firstdb.sales                                      OK

You can also analyze more than one table from a database by listing the tables after the database name:

% mysqlcheck -a firstdb sales customer -uroot -pg00r002b
firstdb.sales                 Table is already up to date
firstdb.customer              Table is already up to date

If you tried to analyze a table that does not support analysis (such as an InnoDB table), no harm would be done and the operation would just fail. For example:

% mysqlcheck -a firstdb innotest -uroot -pg00r002b
firstdb.innotest
error    : The handler for the table doesn't support check/repair

You could also analyze all tables in the database with by leaving out any table names:

% mysqlcheck -a firstdb innotest -uroot -pg00r002b

Analyzing Tables with myisamchk

The myisamchk command-line utility is discussed fully later in this chapter in the section titled "Using myisamchk." The server should either not be running, or you must be sure that there is no interaction with the tables with which you’re working. If the –skip-external-locking option is not on, you can safely use myisamchk to analyze tables, even if the server is running. The tables will be locked, affecting access, but there will be no erroneous reports. If –skip-external-locking is used, you’ll need to flush the tables before starting the analysis (with mysqladmin flush-tables) and ensure that there is no access. You may get invalid results if mysqld or anything else accesses the table while myisamchk is running. To analyze tables, use the -a option:

% myisamchk -a /usr/local/mysql/data/firstdb/sales   
Checking MyISAM file: /usr/local/mysql/data/firstdb/sales
Data records:       9   Deleted blocks:       0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2

Checking Tables

Errors can occur when the indexes are not synchronized with the data. System crashes or power failures can all cause situations where the tables have become corrupted. Corruption of the data is fairly rare; in most cases, the corruption is of the index files. These can be hard to spot, though you may notice information being returned slowly or data not being found that should be there. Checking tables should be the first thing you do when you suspect an error. Some of the symptoms of corrupted tables include errors such as the following:

• Unexpected end of file.

• Record file is crashed.

• tablename.frm is locked against change.

• Can’t find file tablename.MYI (Errcode: ###).

• Got error ### from table handler. The perror utility gives more information about the error number. Just run perror (which is stored in the same directory as the other binaries such as mysqladmin) and the error number. For example:

  % perror 126
  126 = Index file is crashed / Wrong file format

Some of the other more common errors include:

126 = Index file is crashed / Wrong file format
127 = Record-file is crashed
132 = Old database file
134 = Record was already deleted (or record file crashed)
135 = No more room in record file
136 = No more room in index file
141 = Duplicate unique key or constraint on write or update
144 = Table is crashed and last repair failed
145 = Table was marked as crashed and should be repaired

Once connected to the MySQL server, you can issue a CHECK TABLE command, make use of the mysqlcheck utility (when the server is running), or use the myisamchk utility when the server has been stopped. Checking updates the index statistics and checks for errors.

If any errors are found, the table will need to be repaired (see the "Repairing Tables" section later in this chapter). Serious errors mark the table as corrupt, in which case it can no longer be used until it is repaired.

Tip

Always check tables after a power failure or a system crash. You can usually fix any corruption that has occurred before users notice any problems.

Checking Tables with CHECK TABLES

The syntax for CHECK TABLE is as follows:

CHECK TABLE tablename [option]

For example:

mysql> CHECK TABLE customer;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| firstdb.customer | check | status   | OK       |
+------------------+-------+----------+----------+
1 row in set (0.01 sec)

CHECK can check only MyISAM and InnoDB tables.

There are five options depending on the level of checking you want to do, as shown in Table 4

Table 4: CHECK TABLE Options

Option	Description
QUICK	This is the quickest check and does not scan the rows to check for wrong links.
FAST	Only checks tables that haven’t been closed properly.
CHANGED	Only checks tables that haven’t been closed properly or have been changed since last check.
MEDIUM	The default option. It scans rows to check that deleted links are correct. It also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
EXTENDED	This is the slowest method, but it checks the table for complete consistency by doing a full key lookup for every index associated with each row.

 

The QUICK option is useful for checking tables where you don’t suspect any errors.

If an error or warning is returned, you should try and repair the table.

You can check more than one table at a time by listing the tables one after another,

for example:

mysql> CHECK TABLE sales,customer;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| firstdb.sales    | check | status   | OK       |
| firstdb.customer | check | status   | OK       |
+------------------+-------+----------+----------+
2 rows in set (0.01 sec)

Checking Tables with mysqlcheck

The mysqlcheck command-line utility can be used when the server is running and works only with MyISAM tables. It is described fully later in this chapter in the section titled "Using mysqlcheck." Table 5 lists the options.

The syntax is as follows:

mysqlcheck [options] databasename tablename[s]

For example:

% mysqlcheck -c firstdb customer -uroot -pg00r002b
firstdb.customer                               OK

Table 5: The mysqlcheck Options That Apply to Table Checking

Option	Description
–auto-repair	Used in conjunction with one of the check options, it will automatically beginto repair corrupted tables after the checks have completed.
-c, –check	Checks tables.
-C, –check-only-changed	Checks tables that have changed since the last check or were not closed properly.
-F, –fast	Checks tables that haven’t been closed properly.
-e, –extended	This is the slowest form for checking, but it will make sure the table is completely consistent. You can also use this option to repair, though itisusually not necessary.
-m, –medium-check	This is much faster than the extended check, and it finds the vast majority oferrors.
-q, –quick	The fastest check, this does not check table rows when checking. When repairing, it only repairs the index tree.

 

You can check more than one table by listing a number of tables after the database name:

% mysqlcheck -c firstdb sales customer -uroot -pg00r002b
firstdb.sales                                      OK
firstdb.customer                                   OK

You can check all tables in the database by just specifying the name of the database.

% mysqlcheck -c firstdb -uroot -pg00r002b

Checking Tables with myisamchk

When the server is shut down or there is no interaction with the tables you’re checking, you can use the myisamchk command-line option (described fully in the "Using myisamchk" section later in this chapter). If the –skip-external-locking option is not on, you can safely use myisamchk to check tables, even if the server is running. The tables will be locked, affecting access, but there will be no erroneous reports. If –skip-external-locking is used, you’ll need to flush the tables before starting the check (with mysqladmin flush-tables) and ensure that there is no access. You may get wrong results (with tables being marked as corrupted even when they are not) if mysqld or anything else accesses the table while myisamchk is running.

The syntax is as follows:

mysiamchk [options] tablename

The equivalent to the CHECK TABLE statement is the medium option:

myisamchk -m table_name 

The default for myisamchk is the ordinary check option (-c). There is also the fast check (-F), which only checks tables that haven’t been closed properly. This is not the same as the lowercase -f option, which is the force option, meaning the check continues even if errors occur. There is also the medium check (-m), slightly slower and more complete. The most extreme option is the -e option (that performs an extended check), which is the most thorough and slowest option. It’s also usually a sign of desperation; use this only when all other options have failed. Increasing the key_buffer_size variable can speed up the extended check (if you have enough memory). See Table 6 for the checking options.

Table 6: myisamchk Checking Options

Option	Description
-c, –check	Ordinary check and the default option.
-e, –extend-check	Slowest and most thorough form of check. If you are using –extended-check and have much memory, you should increase the value of key_buffer_size a lot!
-F, –fast	Fast check, which only checks tables that haven’t been closed properly.
-C, –check-only-changed	Checks only the tables that have been changed since the last check.
-f, –force	This runs the repair option if any errors are found in the table.
-i, –information	Displays statistics about the table that is checked.
-m, –medium-check	Medium check, faster than an extended check, and good enough for most cases.
-U, –update-state	Keeps information about when the table was checked and whether the table has crashed, which is useful for the -C option. Should not be used when the table is being used and the –skip-external-locking option is active.
-T, –read-only	Does not mark the table as checked (useful for running myisamchk when the server is active and the –skip-external-locking option is in use).

 

The following is a sample myisamchk output when errors are found:

% myisamchk largetable.MYI
Checking MyISAM file: Hits.MYI
Data records: 2960032   Deleted blocks:       0
myisamchk: warning: 1 clients is using or hasn't closed the table
properly
- check file-size
myisamchk: warning: Size of datafile is: 469968400 Should be: 469909252
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
myisamchk: error: Found 2959989 keys of 2960032
- check record links
myisamchk: error: Record-count is not ok; is 2960394 Should be: 2960032
myisamchk: warning: Found    2960394 parts   Should be: 2960032 parts

Repairing Tables

If you have checked the tables and errors have been found, you’ll need to repair them. There are various repair options available, depending on which method you use, but you may not have success. If the disk has failed, or if none of them work, the only option is to restore from your backup. Repairing a table can take up significant resources, both disk and memory:

• Generally, repairing a table takes up twice as much disk space as the original data file (on the same disk). A quick repair (see the options in the following sections) is an exception because the data file is not modified.

• Some space for the new index file (on the same disk as the original). The old index is deleted at the start, so this is usually not significant, but it will be if the disk is close to full.

• With the standard and –sort-recover options, a sort buffer is created. This takes up the following amount of space (largest_key + row_pointer_length) * number_of_rows * 2. You can move some or all of this to memory (and increase the speed of the process) by increasing the size of the mysqld variable sort_buffer_size if you have the available memory. Otherwise, it is created as specified by the TMPDIR environment variable or the -t myisamchk option.

• Memory usage is determined by the mysqld variables or the options set in the myisamchk command line (see the section titled "Using myisamchk").

If the error is caused by the table running out of space and the table type is InnoDB, you will have to enlarge the InnoDB tablespace. MyISAM tables have a huge theoretical size limit (eight million terabytes), but by default pointers are only allocated for 4GB. If the table reaches this limit, you can extend it by using the MAX_ROWS and AVG_ROW_LENGTH ALTER TABLE parameters. To prepare the table called limited for great things (currently it only has three records), you use the following:

mysql> ALTER TABLE limited MAX_ROWS=999999999999 AVG_ROW_LENGTH=100;
Query OK, 3 rows affected (0.28 sec)
Records: 3  Duplicates: 0  Warnings: 0

This allocates pointers for a much greater number of records. The AVG_ROW_LENGTH is used when BLOB and TEXT fields are present, and it gives MySQL an idea of the average size of a record, which it can then use for optimization purposes.

Repairing Non-MyISAM Table Types

The three methods of repairing discussed in the following sections work only with MyISAM tables. Some of the options have been reported to work occasionally with BDB tables, but they were not designed for this. Currently, the only way to repair corrupted BDB and InnoDB tables is to restore from backup.

Repairing Tables with REPAIR TABLE

You can run the REPAIR TABLE statement when connected to the MySQL server. It currently only works with MyISAM tables. See Table 7 for the options.

The syntax is as follows:

Table 10: Available REPAIR TABLE Options

Option	Description
QUICK	Fastest repair because the data file is not modified. It uses much less disk space as well because the data file is not modified.
EXTENDED	Attempts to recover every possible row from the data file. This option should not be used unless as a last resort because it may produce garbage rows.
USE_FRM	This is the option to use if the .MYI file is missing or has a corrupted header. It will rebuild the indexes from the definitions found in the .frm table definition file.

 

The following is an example of using REPAIR in the case of a missing .MYI file. Let’s delete the .MYI file of an existing table, t4.

% ls -l t4.*
-rw-rw----  1 mysql  mysql    10 Jun 14 02:00 t4.MYD
-rw-rw----  1 mysql  mysql  4096 Jun 14 02:00 t4.MYI
-rw-rw----  1 mysql  mysql  8550 Jun  8 10:46 t4.frm
% rm t4.MYI
% mysql -uguru2b -pg00r002b firstdb

A normal REPAIR does not work:

mysql> REPAIR TABLE t4;
+------------+--------+----------+--------------------------------------+
| Table      | Op     | Msg_type | Msg_text                             |
+------------+--------+----------+--------------------------------------+
| firstdb.t4 | repair | error    | Can't find file: 't4.MYD' (errno: 2) |
+------------+--------+----------+--------------------------------------+
1 row in set (0.47 sec)

The current error message (4.0.3) indicates that the .MYD file cannot be found, when it’s actually the .MYI file that’s missing. The error message is likely to have been clarified by the time you read this. To repair the table in this instance, you need to use the USE_FRM option, which, as the name suggests, uses the .frm definition file to re-create the .MYI index file:

mysql> REPAIR TABLE t4 USE_FRM;
+------------+--------+----------+------------------------------------+
| Table      | Op     | Msg_type | Msg_text                           |
+------------+--------+----------+------------------------------------+
| firstdb.t4 | repair | warning  | Number of rows changed from 0 to 2 |
| firstdb.t4 | repair | status   | OK                                 |
+------------+--------+----------+------------------------------------+
2 rows in set (0.46 sec)

Repairing Tables with mysqlcheck

The mysqlcheck command-line utility is used while the server is still running and works only with MyISAM tables. It is described fully later in this chapter in the "Using mysqlcheck" section. To repair tables, you use the -r option:

% mysqlcheck -r firstdb customer -uroot -pg00r002b
firstdb.customer                                   OK

You can repair multiple tables at the same time by listing the table names after the

database name:

% mysqlcheck -r firstdb customer sales -uroot -pg00r002b
firstdb.customer                                   OK
firstdb.sales                                      OK

If for some reason all tables in a database are corrupt, you can repair them all by just supplying the database name:

% mysqlcheck -r firstdb -uroot -pg00r002b

Repairing Tables with myisamchk

You can use the myisamchk command-line utility (described fully later in this chapter in the "Using myisamchk" section) repair tables (see Table 10.8).

Table 8: Repairing Tables with myisamchk

Option	Description
-D #, –data-file-length=#	Specifies the maximum length of the data file when re-creating it.
-e, –extend-check	Attempts to recover every possible row from the data file. This option should not be used unless as a last resort because it may produce garbage rows.
-f, –force	Overwrites old temporary files (that have an extension of .TMD) instead of aborting if it encounters a preexisting one.
-k #, keys-used=#	Specifies which keys to use, which can make the process faster. Each binary bit stands for one key starting at 0 for the first key.
-r, –recover	Repairs most corruption and should be the first option attempted. You can increase thesort_buffer_size to make the recovery go more quickly if you have the memory. This option will not recover from the rare form of corruption where a unique key is not unique.
-o, –safe-recover	A more thorough, yet slower repair option than -r that should be used only if -r fails. This reads through all rows and rebuilds the indexes based on the rows. It also uses less disk space than -r because a sort buffer is not created. You can increase the size of key_ buffer_size to improve repair speed.
-n, –sort-recover	Forces MySQL to use sorting to resolve the indexes, even if the resulting temporary files are large.
–character-sets-dir=…	The directory containing the character sets.
–set-character-set=name	Specifies a new character set for the index.
-t, –tmpdir=path	Specifies a new path for storing temporary files if you don’t want to use whatever the TMPDIR environment variable specifies.
-q, –quick	Fastest repair because the data file is not modified. Specifying the q twice (-q -q) will modify the data file if there are duplicate keys. Uses much less disk space as well because the data file is not modified.
-u, –unpack	Unpacks a file that has been packed with the myisampack utility.

 

The server should either not be running, or you must be sure there is no interaction with the tables with which you’re working, such as when you start MySQL with the –skip-external-locking option. If the –skip-external-locking option is not on, you can only safely use myisamchk to repair tables if you are sure there will be no simultaneous access. Whether –skip-external-locking is used or not, you’ll need to flush the tables before starting the repair (with mysqladmin flush-tables) and ensure that there is no access. You may get wrong results (with tables being marked as corrupted even when they are not) if mysqld or anything else accesses the table while myisamchk is running.

The syntax is as follows:

myisamchk [options] [tablenames]

You must run myisamchk from the directory containing the .MYI files or supply the path. The following examples show a repair in action, with MySQL deciding whether to use sorting or a keycache:

% myisamchk -r customer
- recovering (with keycache) MyISAM-table 'customer.MYI'
Data records: 0
% myisamchk -r sales   
- recovering (with sort) MyISAM-table 'sales.MYI'
Data records: 9
- Fixing index 1
- Fixing index 2

If you have lots of memory, besides increasing the size of sort_buffer_size and key_buffer_size as described previously, you can also set some other variables to make myisamchk perform more snappily. See the full myisamchk description later in this chapter in the "Using myisamchk" section.

Using mysqlcheck

The mysqlcheck utility is a boon to more recent users of MySQL because, beforehand, much of the repairing and checking functionality could only be used when the server was shut down. Luckily this limitation is a thing of the past with the mysqlcheck utility.

mysqlcheck uses the CHECK, REPAIR, ANALYZE, and OPTIMIZE statements to perform these tasks from the command line, which is useful for automated maintenance of your databases (see Table 9).

The syntax is as follows:

mysqlcheck [options] databasename [tablenames]

or as follows:

mysqlcheck [options] --databases databasename1 [databasename2 databasename 3 ...]

or as follows:

Table 9: Mysqlcheck Options

Option	Description
-A, –all-databases	Checks all available databases.
-1, –all-in-1	Combines queries for tables into one query per database (instead of one per table). Tables are in a comma-separated list.
-a, –analyze	Analyzes the listed tables.
–auto-repair	Automatically repairs tables if they are corrupted (after all tables in the query have been checked).
-#, –debug=…	Outputs a debug log.
–character-sets-dir=…	This specifies the directory where the character sets are.
-c, –check	Checks tables.
-C, –check-only-changed	Checks tables that have changed since the last check or were not closed properly.
–compress	Uses compression in the client/server protocol.
-?, –help	Displays the help message and exits.
-B, –databases	Lists a number of databases to check (all tables in the databases are checked).
–default-character-set=…	Sets the default character set.
-F, –fast	Checks tables that haven’t been closed properly.
-f, –force	Forces the process to continue even if it encounters an error.
-e, –extended	This is the slowest form for checking but will make sure the table is completely consistent. You can also use this option to repair, though it is usually not necessary.
-h, –host=…	Hostname to which to connect.
-m, –medium-check	Much faster than the extended check and finds the vast majority of errors.
-o, –optimize	Optimizes the tables.
-p, –password[=...]	The password with which to connect.
-P, –port=…	The port to use for connecting.
-q, –quick	The fastest check, this does not check table rows when checking. When repairing, it only repairs the index tree.
-r, –repair	Repairs most errors, except unique keys that somehow contain duplicates.
-s, –silent	Displays no output except for error messages.
-S, –socket=…	Specifies the socket file to use when connecting.
–tables	List of tables to check. With the -B option, this will take precedence.
-u, –user=#	Specifies the user to connect as.
-v, –verbose	Prints lots of output about the process.
-V, –version	Displays the version information and exits.

The mysqlcheck utility also has a feature that allows it to be run in different ways without specifying the options. By simply creating a copy of mysqlcheck with one of the following names, it will take that default behavior:

• mysqlrepair: The default option is -r.

• mysqlanalyze: The default option is -a.

• mysqloptimize: The default option is -o.

The default option when it is named mysqlcheck is -c. All of these renamed files can have the full mysqlcheck functionality—it’s just that their default behavior is changed.

Using myisamchk

The myisamchk utility is the older utility, available since the early days of MySQL. It is also used to analyze, check, and repair tables, but care needs to be taken if you want to use it when the server is running. Table 10 describes the general myisamchk options, Table 11 describes the check options, Table 12 describes the repair options, and Table 13 describes other options.

The server should either not be running, or you must be sure there is no interaction with the tables with which you’re working, such as when you start MySQL with the –skip-external-locking option. If the –skip-external-locking option is not on, you can only safely use myisamchk to repair tables if you are sure there will be no simultaneous access. Whether or not –skip-external-locking is used, you’ll need to flush the tables before starting the repair (with mysqladmin flush-tables) and ensure that there is no access.

I suggest you rather use one of the other options if the server is running.

The syntax is as follows:

myisamchk [options] tablename[s]

You must run myisamchk from the directory where the .MYI index files are located unless you specify the path to them; otherwise you’ll get the following error:

% myisamchk -r sales.MYI
myisamchk: error: File 'sales.MYI' doesn't exist

Specifying the path solves the problem:

% myisamchk -r /usr/local/mysql/data/firstdb/sales.MYI
- recovering (with sort) MyISAM-table '/usr/local/mysql/data/firstdb/sales.MYI'
Data records: 9
- Fixing index 1
- Fixing index 2

The table name can be specified with or without the .MYI extension.

% myisamchk -r sales
- recovering (with sort) MyISAM-table 'sales'
Data records: 9
- Fixing index 1
- Fixing index 2
% myisamchk -r sales.MYI
- recovering (with sort) MyISAM-table 'sales.MYI'
Data records: 9
- Fixing index 1
- Fixing index 2

Warning

A common mistake is to try run myisamchk on an .MYD data file. Always use the .MYI index file!

You can use wildcard character to search all tables in a database directory (*.MYI) or even all tables in all databases:

Table 10: General myisamchk Options

Option	Description
-#, –debug=debug_options	Outputs a debug log. A common debug_option string is d:t:o,filename.
-?, –help	Displays a help message and exits.
-O var=option, –set-variable var=option	Sets the value of a variable. The possible variables and their default values for myisamchk can be examined with myisamchk –help.
-s, –silent	Only outputs error messages. A second s can be used to completely silence myisamchk.
-v, –verbose	Displays more information than usual. As with silent, multiple v’s can be used to output more information (-vv or -vvv).
-V, –version	Displays the myisamchk version details and exits.
-w, –wait	If the table is locked, -w will wait for the table to be unlocked rather than exiting with an error. If mysqld was running with the –skip-external-locking option, the table can only be locked by another myisamchk command.

 

By running myisamchk –help, besides the general options, you can see what variables you can change with the -O option and what the current settings are:

% myisamchk --help
..
Possible variables for option --set-variable (-O) are:
key_buffer_size       current value: 520192
myisam_block_size     current value: 1024
read_buffer_size      current value: 262136
write_buffer_size     current value: 262136
sort_buffer_size      current value: 2097144
sort_key_blocks       current value: 16
decode_bits           current value: 9
ft_min_word_len       current value: 4
ft_max_word_len       current value: 254
ft_max_word_len_for_sort  current value: 20

The space allocated by the key_buffer_size is used when doing an extended check or when indexes are inserted one row at a time (using the safe-recover option). sort_buffer_size is used in the default repair, when indexes are sorted in the repair.

To achieve a faster repair, set the sort_buffer_size to about one-quarter of the total available memory. Only one of the two variables is used at a time, so you don’t need to worry about running out of memory by making both values large.

Note

Inside the my.cnf (or my.ini) file, there are separate sections for mysqld and myisamchk. You can quite easily set the sort_buffer_size to a high value for repairing, and keep it lower if your system has other requirements for day-to-day running.

Table 11: myisamchk Check Options

Option	Description
-c, –check	Ordinary check and the default option.
-e, –extend-check	Slowest and most thorough form of check. If you are using –extended-check and don’t have much memory, you should increase the value of key_buffer_size a lot!
-F, –fast	Fast check that only checks tables that haven’t been closed properly.
-C, –check-only-changed	Checks only the tables that have been changed since the last check.
-f, –force	This runs the repair option if any errors are found in the table.
-i, –information	Displays statistics about the table that is checked.
-m, –medium-check	Medium check, faster than an extended check and good enough for most cases.
-U, –update-state	Keeps information about when the table was checked and whether the table has crashed, which is useful for the -C option. Should not be used when the table is being used and the –skip-external-locking option is active.
-T, –read-only	Does not mark the table as checked (useful for running myisamchk when the server is active and the –skip-external-locking option is in use).

Table 12: myisamchk Repair Options

Option	Description
-D #, –data-file-length=#	Specifies the maximum length of the data file when re-creating it.
-e, –extend-check	Attempts to recover every possible row from the data file. This option should not be used unless as a last resort because it may produce garbage rows.
-f, –force	Overwrites old temporary files (that have an extension of .TMD) instead of aborting if it encounters a preexisting one.
-k #, keys-used=#	Specifies the keys to use, which can make the process faster. Each binary bit stands for one key starting at 0 for the first key (for example, 1 is the first index, 10 is the second index).
-r, –recover	Repairs most corruption and should be the first option attempted. You can increase the sort_buffer_size to make the recover go more quickly if you have the memory. This option will not recover from the rare form of corruption where a unique key is not unique.
-o, –safe-recover	A more thorough, slower repair option than -r, which should be used only if -r fails. This reads through all rows and rebuilds the indexes based on the rows. It also uses less disk space than -r because a sort buffer is not created. You can increase the size of key_ buffer_size to improve repair speed.
-n, –sort-recover	Forces MySQL to uses sorting to resolve the indexes, even if the resulting temporary files are large.
–character-sets-dir=…	The directory containing the character sets.
–set-character-set=name	Specifies a new character set for the index.
-t, –tmpdir=path	Specifies a new path for storing temporary files if you don’t want to use the contents of the TMPDIR environment variable.
-q, –quick	Fastest repair as the data file is not modified. Running this option a second time will modify the data file if there are duplicate keys. Uses much less disk space as well because the data file is not modified.
-u, –unpack	Unpacks a file that has been packed with the myisampack utility.

Table 13: Other myisamchk Options

Option	Description
-a, –analyze	Analyzing tables improves performance by updating the index information for atable so that MySQL can make a better decision on how to join tables. The distribution of the various index elements is stored for later usage. This option isthe same as ANALYZE TABLE.
-d, –description	Displays a description of the table.
-A, –set-auto-increment[=value]	Sets the AUTO_INCREMENT counter to the specified value (or increments it by one if no value is supplied).
-S, –sort-index	Sorts the index tree blocks in descending order, which improves the performance of seeks and table scanning by key.
-R, –sort-records=#	Sorts records according to the index specified (index numbers begin from 1; you can use SHOW INDEX to see an ordered list). This can speed up queries that are ordered on this index, as well as ranged selects. It will probably be very slow if you sort a large table for the first time.

Running myisamchk with the -d option produces the following kind of output:

% myisamchk -d customer
MyISAM file:         customer
Record format:       Packed
Character set:       latin1 (8)
Data records:        3  Deleted blocks:                 0
Recordlength:        75
table description:
Key Start Len Index   Type
1      2         4      unique   long