Checking and Repairing MySQL Tables
You might need to restore corrupted tables (or even an entire database) from your backups and use the update logs if a table gets damaged or deleted by accident. In case of relatively minor damage, however, MySQL provides several options for table repair. This next section deals with what you can do if this is the case.
Checking Tables for Errors
The first thing to do if you suspect something is wrong is to check the table for errors. The myisamchk utility is one way to check a table. To invoke this utility, execute the command myisamchk table-file.
Because myisamchk requires exclusive access to the tables, a good idea is to take the server offline before running it. This way, you needn’t worry about coordinating access between clients. In addition, you can run several options when you check a table for errors, as shown in Table 1.
The following example runs myisamchk with the extended option enabled. If you’re following along, don’t use a large table to see how this works because you’ll tie up your server for quite a while. If no errors are detected using the extended option, you can be certain the specified table isn’t the problem.
[root@production ~]# /usr/local/bin/myisamchk --extend- check /usr/local/mysql/data/db1/ airport.MYI Checking MyISAM file: /usr/ local/mysql/data/db1/airport. MYI Data records: 15 Deleted blocks: 0 myisamchk: warning: 1 client is using or hasn't closed the table properly - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check record links MyISAM-table '/usr/local/ mysql/data/db1/airport.MYI' is usable but should be fixed
The downside of myisamchk is this database-checking tool requires locking out clients while the diagnosis is performed. Moreover, no client can hold a lock on the table being checked while myisamchk is running. On a big table, where myisamchkcan take a few minutes to perform its checks, this can be a problem.
Table 1 Additional myisamchk Table Check Options
One alternative here is to set myisamchk to use large buffers (use myisamchk –helpto see the options for changing the various buffers). Another alternative is to use a different method to check your tables: the CHECK TABLE command.
The myisamchk utility requires exclusive access to the tables it’s checking because it works directly with the table files. The CHECK TABLE command, on the other hand, has the server check the tables. This means less work for you, as you don’t have to take the server down and remove all the locks from the table. Here’s an example of it in action:
mysql> CHECK TABLE airport;
1 row in set (0.08 sec)
In case you were wondering, you can also add the keywords FAST, MEDIUM, and EXTENDED to the CHECK TABLE command to perform the desired type of check.
Why not run CHECK TABLE all the time then, instead of myisamchk, you might ask? The main reason is this: The server does all the work when using CHECK TABLE. If your server is down, CHECK TABLE isn’t an option. On the other hand, myisamchkworks at the file level and, therefore, can work even if the server is down. Since CHECK TABLE is a SQL command that can only be sent via a client, the server must be running to accept it. If you have a choice, however, by all means let MySQL do the work.
CAUTION
myisamchk only works with the MyISAM storage engine. To check InnoDB tables, use the CHECK TABLE command instead.
Repairing Tables
If you find errors exist after checking a table, you must repair the table. The best practice is to make a copy of the table in question before you try to repair it. This gives you the option of trying a different way to recover it if your first solution doesn’t work.
The myisamchk tool discussed previously can also be used to repair a damaged table. Use the –recover option with the table filename to start this process. Here’s an example:
[root@host]# /usr/local/mysql/bin/myisamchk --recover /usr/local/mysql/data/db1/ airport.MYI - recovering (with sort) MyISAM-table '/usr/local/mysql/data/db1/ airport.MYI' Data records: 15 - Fixing index 1
If the –recover option fails to take care of the problem, the –safe-recover option attempts a slow recovery of the table. Other options are also available, and Table 2 explains what they mean.
Keep in mind that the myisamchk tool works at the file level and, therefore, requires that all locks be removed and all clients be excluded.
As when checking a table, you should try the fastest options first and move to the slower, more thorough, options only if needed. You might find many common problems are fixed without having to resort to the slower options. If you still have a problem after running even the most intensive repair possibilities, you’ll have to restore the table from your backups. Restoring is covered in detail in the section “Restoring Databases and Tables from Backup.”
The other option you have when repairing a table is the REPAIR TABLE command, coupled with the table name. Similar to myisamchk, you have the option of using the QUICK or EXTENDED keyword to set the type of repair. Simply add the option name to the end of the REPAIR TABLEstatement, as in the example shown:
mysql> REPAIR TABLE airportQUICK; +-------------+--------+------ ----+---------- + | Table | Op | Msg_ type | Msg_text | +-------------+--------+------ ----+---------- + | db1.airport | repair | status | OK | +-------------+--------+------ ----+---------- + 1 row in set (0.00 sec)
TIP
You can use either myisamchk or REPAIR TABLE to fix a damaged table, but remember (as discussed earlier in the context of the CHECK TABLE command), the server must be running in order to use REPAIR TABLE, while you must only usemyisamchk if the server is down.
TABLE 2 Additional myisamchk Table Repair Options
Optimizing Tables
There are a number of times when optimizing a table is a good idea. A common example is if a table gets considerable activity, especially many deletions. In such a situation, it can quickly get fragmented, resulting in performance degradation. Running the OPTIMIZE TABLE command flushes these deleted records and frees up space.
For example, the following command optimizes the route table:
mysql> OPTIMIZE TABLE route; +-----------+----------+----------+---------- + | Table | Op | Msg_ type | Msg_text | +-----------+----------+------ ----+---------- + | db1.route | optimize | status | OK | +-----------+----------+------ ----+---------- + 1 row in set (0.06 sec)
The OPTIMIZE TABLE command is like your mother coming in and tidying your room. In addition to getting rid of old, deleted files, it sorts indexed files, places the contents of variable table rows into contiguous spaces, and updates table statistics. Remember, though, that the table is locked and can’t be accessed by clients while it’s being serviced.
No comments:
Post a Comment