mysql| Data | Database Ideally, MySQL has been running smoothly since the first installation. Sometimes, however, problems can occur for a variety of reasons, ranging from power outages to hardware failures to abnormally shutting down MySQL servers (such as terminating a server or machine crash with kill-9). Most of these things are beyond your control, causing the destruction of database tables, especially if the table is modified and not fully written.
The focus of this chapter is on detecting and resolving table problems, regardless of how the problem is caused. For table checks and fixes, the MySQL administrator's best friend is the Myisamchk and Isamchk utilities. The two programs have several functions, and we discuss how to use them to perform the distribution analysis of index keys and the release and activation of indexes. You can also use them to check tables and fix problematic tables. This allows you to correct the failure before the table becomes bad (before the table can be used).
The list of all options provided by Myisamchk and Isamchk is in Appendix E. For additional backgrounds, see the "Maintaining MySQL Installation" chapter of the MySQL Reference guide.
The general process of fault detection and correction for a table is as follows:
1 Check the wrong table. If the table checks through, the task completes, or it must be repaired.
2 Copy the table file before starting the repair, just in case.
3 Try to repair the table.
4 If the repair operation fails, restore the table from the database backup and update log.
The last step in the preceding procedure assumes that you have performed a database backup and that the update log is valid. If this is not the case, the system will be in danger. Refer to the 11th chapter to find out how to use mysqlaump and how to turn on the update log. You certainly don't want to lose a table irrevocably, so try to make a backup.
Before you use Myisamchk or isamchk to check or fix a table, you should meet some preliminary requirements:
Establish a regular database backup process and allow the log to be updated in case things get worse and the damage to the table cannot be repaired. Does the writer seem to have reminded this before?
Read the contents of this chapter carefully before you begin the experiment. In particular, you should not do this until you read the "Avoid interaction with MySQL server" because it will discuss the problem that the server is using when you try to perform a check or repair process on a table. It also discusses how to prevent those problems from occurring when the server is running.
When running a table check or repair, you should be registered under the account running MySQL because you need to read and write access to the table file.
Invocation syntax for MYISAMCHK and ISAMCHK
MySQL's myisamchk and Isamchk utilities are similar, and most of the time they can be used in the same way. The main differences between them are the types of tables they use. For the MyISAM table, the I i s a M C h K is used, and for the ISAM table, I s a M C h k is used. You can tell the table which storage format to use by using the extension of the index file for the table. Extension ". M Y I "indicates to be a MyISAM table, and". I S M "indicates to be ISAM table.
In order to use either utility, you should indicate the table you want to examine or fix, and the options that indicate the type of action you want to perform:
% Myisamchk Options Tbl_name ...
% Isamchk Options Tbl_name ...
The Tbl_name parameter can be either a table name or an indexed file name for the table. If you specify more than one table, you can easily use the file name pattern to pick up all the appropriate files in the directory:
% myisamchk options *. Myi
% isamchk options *. Ism
The table is not corrupted because it tells the wrong program to check a table, but the program does nothing except to post a warning message. For example, the first statement below checks all MyISAM tables in the current directory, and the second statement displays only one warning message:
% Myisamchk *. Myi Right
% Myisamchk *. ISM Incorrect-file type is wrong
Both Myisamchk and isamchk do not make any judgments about where the table is located, so you should either run the program in the directory containing the table file or specify the path name of the table. This allows you to copy the table file to another directory and use the copy to manipulate it.
Check table
Myisamchk and Isamchk provide table-checking methods that differ in the degree to which the table is thoroughly examined. It is usually sufficient to use the standard method. If the standard check report finds no errors and you still suspect damage (perhaps because the query does not work properly), you may want to perform a more thorough check. To perform a standard table check with any utility, you can call it directly without any options:
% Myisamchk Tbl_name
% Isamchk Tbl_name
To perform an extended check, use the--extend-check option. This option is very slow, but the check is extremely thorough. For each record in the data file of the table, the key to each index in the index file is checked to make sure it actually points to the correct record. Myisamchk also has an intermediate option--m e d i um-c h e C K, which is not as thorough as the extended check, but fast.
If you do not report errors for--extend-check checks, you can be sure that the table is good. If you still feel the table has a problem, that reason is certainly in other places. Any query that appears to be problematic should be reviewed to verify that the query is written correctly. If you think the problem may be the cause of the MySQL server, consider organizing a fault report or upgrading to a new version.
If Myisamchk or Isamchk report tables have errors, apply the instructions in the following section to fix them.
Repair table
The repair of a table is a terrible task, and it is more difficult to carry out if the specific problem is unique. However, there are some conventional guidelines and processes that can be followed to increase the chance of revising the table. Usually, you can start with the fastest fix to see if you can fix the failure. If not, you can step up to a more thorough (but slower) fix until the fault is repaired or you cannot continue the upgrade (in fact, most problems can be corrected without a larger and slower approach). If the table cannot be repaired, the table is recovered from the backup. Guidelines for recovery using backup files and update logs are given in chapter 11th.
1. Perform standard table repair
To fix a table, perform the following steps:
1 try to fix the table with the--recover option, but you can also use the--quick option to try to recover only from the contents of the index file. This will not touch the data file:
% myisamchk--recover--quick tbl_name
% isamchk--recover--quick tbl_name
2 If the problem persists, try the previous command, but ignore the--quick option to allow my i s a M c h K or isamchk to advance and modify the data file:
% Myisamchk--recover tbl_name
% Isamchk--recover tbl_name
3 If you do not work, try--safe-recover repair method. This method is slower than normal recovery methods, but can fix several problems that the-recover method cannot fix:
% Myisamchk--safe-recover tbl_name
% Isamchk--safe-recover tbl_name
If Myisamchk or Isamchk stops in any step due to an error message "C a n ' t create new temp File:file_name", you should repeat this command and increase the--FORCE option to force the purge of the temporary file. This temporary file may have been left over from the last failed fix.
Copy them before you fix the table. A general precaution to follow before performing a table repair is to make a new copy of the table. This may not be the case, but if it happens, you can make a new copy of the table from the copy file and try another recovery method.
2. What to do when a standard table repair method fails
If the standard repair process fails to repair the table, the index file may be lost or destroyed during repair. Although it may not be possible, it is possible to lose the description file for the table. In either case, you need to replace the affected file, and then try the standard repair process again.
In order to regenerate the index file, you can use the following procedure:
1 Locate the database directory that contains the crash table.
2 Move the data file of the table to a safe place.
3 call MySQL and recreate the new empty table by executing the following statement, which uses the table's description file tbl_name.frm to start generating the new data and index files again:
Mysql> DELETE from Tbl_name;
4 exit MySQL, move the original data file back to the database directory and replace the new empty file you just created.
5 Try the standard table repair method again.
To restore the description of the table, you can recover from the backup file before you try the standard fix method. If you do not have a backup for some reason, but you know the CREATE TABLE statement that establishes the tables, you can still recover the file:
1 Locate the database directory that contains the crash table.
2 Move the data file of the table to a safe place. If you want to use an index, you also need to remove the index file.
3 call MySQL and publish the CREATE TABLE statement to create the table.
4 exit MySQL, move the original data file back to the database directory, replace the newly created data file. If you moved the index file in step 2, you also want to move it back to the database directory.
5 Try the standard table repair method again.
Avoid interacting with MySQL server
When you are running the check/repair utility for a table, you may not want the MySQL server and utility to access a table at the same time. If two of programs write data to a table, it's obviously a bad thing, but it's not good to read another program while it's writing. If the table is being written by a program, another program that is being read will be
Messing.
If you shut down the server, you can ensure that there is no interaction between the server and the Myisamchk or Isamchk. However, the administrator is extremely reluctant to make the server completely offline because it makes the database and tables that have no failures available. The procedures discussed in this section will help you avoid interactions between servers and Myisamchk or ISAMCHK.
The server has two types of locking methods. It uses internal locking to prevent client requests from interfering with each other-for example, to avoid the client's select query being interfered with by another client's update query. The server also uses an external lock (file-level lock) to prevent other programs from modifying the table's files while the server is using the table. Typically, the server uses external locking in combination with Myisamchk or isamchk in a table check operation. However, external locking is disabled on some systems because it does not work reliably. The process you select for running Myisamchk and Isamchk depends on whether the server can use external locking. If you do not use it, you must use an internal locking protocol.
If the server is running with the--skip-locking option, external locking is disabled. This option is default on some systems, such as L i n UX. You can determine whether the server can use an external lock by running the mysqladmin variables command. Check the value of the skip_locking variable and follow the following methods:
If Skip_locking is O FF, external locking is valid. You can continue and run any utility to check the table. The server and utility will collaborate to access the table. However, you should refresh the cache of the table with Mysqladmin Flush-tables before running any of the utilities. To fix the table, you should use the Repair lockout protocol for the table.
If Skip_locking is o n, external locking is disabled, but the server does not know when myisamchk or isamchk checks or repairs a table, preferably shutting down the server. If you insist on keeping the server open, you need to make sure that you do not have a client to access it when you use this table. You must use the appropriate locking protocol to tell the server to leave the table alone and block the client from accessing it.
The locking protocol described here uses the internal locking mechanism of the server to prevent the server from accessing the table when you work with my i s a M c h K or isamchk. The usual approach is to call MySQL and publish the L O C K table statement for the table to be inspected or repaired. Then, when MySQL is idle (that is, run, but not to do anything except to keep the table locked), run Myisamchk or I s a M c h K. After the end of Myisamchk or ISAMCHK, you can switch to a MySQL session and release the lock to tell the server that the program is finished and that the table can be used again.
There is a difference between checking and fixing the locking protocol. For checks, you only need to get read locks. In this case, you can only read the table, but you cannot modify it, so it also allows other clients to read it. Read locks are enough to prevent other clients from modifying the table. For repairs, you must obtain a write lock to prevent any client from modifying the table while you are working on it.
The locking protocol obtains and releases the lock using the lock table and the Unlock table statement. The protocol also uses F L U S H tables to tell the server to refresh any pending changes on the disk and reopen the table after modifying the table through the table repair utility. You must execute all L O C K, FLUSH, and unlock statements from a single MySQL session. If you lock a table and then exit MySQL, the lock will be released, and running Myisamchk or ISAMCHK will no longer be safe!
If you keep the state of two windows open and one runs MySQL and the other runs Myisamchk or I s a M c h K, it will be easy to run the lockdown process. This allows you to easily switch between programs. If you are not running in a Windows environment, when running Myisamchk or ISAMCHK, you will need to use the Shell's job control tool to pause and restore MySQL. The following instructions show a command for MYISAMCHK or isamchk that can be used to correspond to the table you are using.
1. Lock table for check operation
This procedure is only for table checks and not for table repairs. In Window 1, call MySQL and publish the following statement:
% Mysqldb_name
Mysql>lock TABLE tbl_name READ;
Mysql>flush TABLES;
This lock prevents other clients from writing to the table and modifying the table while it is being examined. The FLUSH statement causes the server to close the table's files, and it refreshes any unsaved changes that are still in the cache.
When MySQL is idle, switch to window 2 and check the table:
% Myisamchk Tbl_name
% Isamchk Tbl_name
When Myisamchk or isamchk ends, switch back to the MySQL session in Window 1 and release the table lock:
Mysql>unlock TABLE;
If Myisamchk or isamchk indicates a problem with discovering the table, you will need to perform a table repair.
2. Lock table for repair operation
Fixing a table's locking process is similar to checking the table's process, but there are two differences. First, you must get a write lock instead of a read lock. Because you are about to modify the table, the client is not allowed to access it at all. Second, the Flush TABLE statement must be published after the repair is performed because Myisamchk and Isamchk have created the new index file unless the refresh
The table's cache or the server will not notice it:
% Mysqldb_name
Mysql>lock TABLE tbl_name WRITE;
Mysql>flush TABLES;
Using MySQL's Idle switch to Window 2, make a copy of the database file for the table, and then run Myisamchk or I s a M c h K:
% CP tbl_name.* |some|other|directory
% Myisamchk--recover tbl_name
% Isamchk--recover tbl_name
The--recover option is only set for installation. The choice of these special options will depend on the type of repair you are performing. After the Myisamchk or isamchk is finished, switch back to the MySQL session in Window 1, refresh the cache of the table again and release the table lock:
Mysql>flush TABLES;
Mysql>unlock TABLE;
Fast running Myisamchk and I s a M c h k
Myisamchk and Isamchk may take a long time to run, especially if you are working on a large table or using a broader review or repair method. By telling these programs to use more memory at run time, they can improve their speed. All two utilities have several operational parameters that can be set. The most important of these is the buffer used by the control program
Variable for area size:
| Variable |
Meaning |
| Key _ buffer _ s I z E |
Buffer size for storing index blocks |
| R e A D _ buffer _ s I z E |
Buffer size used for read operations |
| Sort _ buffer _ s I z E |
Size of buffer used for sorting |
| W r i t e _ buffer _ s I z E |
Buffer size for write operations |
To see the default values for these variables that are used by any program, you can run the program with the--HELP option. To specify a different value, you can use--set-variable variable=value or-o variable=value on the command. You can simplify the name of a variable to key, r e A D, sort, and W r i t e. For example, you can tell Myisamchk to use the 16MB sort buffer and 1MB read-write buffers, which are called as follows:
% myisamchk-0 sort=16m-0 read=1m write=1m ...
Sort _ Buffer_size can only use the--recover option (instead of using-s a f e _ r e ver), in which case key _ buffer cannot be used.
Reduce server Downtime
Another way to prevent a server from accessing the table (which you are working on) is to use a copy of the table file outside the data directory. This does not eliminate the interaction problem, because the server must still be prevented from accessing (and possibly modifying) the table being copied. However, if you do not want to take the server offline, the route may be a way to minimize server downtime, which is appealing to you. Shut down the server when you copy the table's files to another directory, and then restore the server.
Myisamchk's future plans
The Myisamchk table check and Repair feature is intended to be merged into the server at some point in the MySQL3.23 version series. If this is the intended implementation, it will be easier to check and fix the table because the interaction between the server and my i s a M c h K will no longer occur.
Similarly, you can tell the server to check the table at startup, so you will not need to set any special commands to execute during boot time before starting the server. The program does not operate on the ISAM table, so consider converting the ISAM table into a MyISAM table when the server obtains a check-repair capability for the table. Check out the MySQL reference guide for the new release to see what's new in this area. You can use the ALTER TABLE statement to convert a table's type:
ALTER TABLE tbl_name Type=myisam
