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
