Check and maintain the operation process of MySQL database tables

The following article describes how to correctly check and maintain the MySQL database table, as well as the specific descriptions of the matters worth your attention in the actual operations of checking and maintaining MySQL database tables, we hope you will gain some benefits after browsing.

Ideally, MySQL (the best combination with PHP) has been running smoothly since its first installation. But sometimes problems may occur for various reasons, from power failure to hardware failure to abnormal MySQL Shutdown (the best combination with PHP) if the server uses kill-9 to terminate the server or the server crashes ).

In most cases, this is beyond your control. They can cause database tables to be destroyed, especially when the table is modified and not completely written.

This chapter focuses on table detection and solving, regardless of the problem. For Table check and repair, the best friend of the MySQL (best combination with PHP) Administrator is the myisamchk and isamchk utilities. These two programs have several functions. We have discussed in chapter 4th how to use them to perform index Key Distribution Analysis and index release and activation. You can also use them to check tables and fix problematic tables. This allows you to correct the fault before the table becomes unusable.

The list of all options provided by myisamchk and isamchk is in appendix E. For other background information, see the "maintain MySQL (the best combination of PHP and PHP) installation" section in the Reference Guide.

The general process of fault detection and correction in the table is as follows:

1) Check the tables with errors. If the table passes the check, the task is completed; otherwise, 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, recover the table from the database backup and update log.

The last step of the above process assumes that you have performed a database backup and allowed the log update to be valid. If this is not the case, the system will be in danger. Refer to Chapter 11th to find out how to use MySQL (the best combination with PHP) aump and how to enable log update. You certainly do not want to irrecoverably lose a table. Therefore, you should make efforts to back up the table.

Before using myisamchk or isamchk to check or repair tables, you should meet some preliminary requirements:

Create a regular database backup process and allow log updates to prevent the destruction of tables from getting worse and worse. I seem to have warned this before?

Read the contents of this chapter carefully before starting the test. In particular, you should not perform operations before reading "Avoid interacting with the MySQL database table server, this is because it will discuss the problem that the server is using this table when you try to perform a check or repair on a table. It also discusses how to prevent problems when the server is running.

When you run table check or repair, you should be registered under the account that runs MySQL (the best combination with PHP), because you need to read and write table files.

Call Syntax of myisamchk and isamchk

The myisamchk of MySQL (the best combination with PHP) is similar to the isamchk utility. Most of the time they can be used in the same way. The main difference between them is the type of the table they use. For MyISAM tables, use my I s a m c h k, and for ISAM tables, use I s a m c h k. You can use the extension of the index file to tell which storage format the table uses. The extension ". m y I" indicates a MyISAM table, and ". I S M" indicates an ISAM table.

To use any utility, specify the table you want to check or repair, and specify options for the operation type to be executed:

% Myisamchk options tbl_name...

% Isamchk options tbl_name...

The tbl_name parameter can be either the table name or the index file name of the table. If multiple tables are specified, you can easily use the file name mode to pick up all the corresponding files in the directory:

% Myisamchk options *. MYI

% Isamchk options *. ISM

This table will not be destroyed because an incorrect program is told to check a table, but this program will not do anything except publish a warning message. For example, the following first statement checks all MyISAM tables in the current directory, while the second statement only displays one warning message:

% Myisamchk *. MYI is correct

% Myisamchk *. ISM is incorrect-the file type is incorrect.

Neither myisamchk nor isamchk can determine the location of the table. Therefore, you should run the program in the directory containing the table file or specify the table path name. This allows you to copy a table file to another directory and use this copy operation.

Checklist

Myisamchk and isamchk provide table check methods that differ in the extent to which a thorough checklist is performed. Generally, the standard method is enough. If no errors are found in the standard check report and you still suspect that the query may not work properly), perform a more thorough check. To use any utility to perform a standard table check, you can directly call it without any options:

% Myisamchk tbl_name

% Isamchk tbl_name

To perform the expansion 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 of each index in the index file is checked to ensure that it truly 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 fast as a full extension check.

If no error is reported for the -- extend-check, the table is correct. If you still feel that the table is faulty, the reason must be elsewhere. Any queries that appear to be faulty should be re-checked to verify that the queries are correctly written. If you think the problem may be caused by the MySQL database table server, you should organize a fault report or upgrade it to a new version.

If the myisamchk or isamchk report table has an error, apply the instructions in the next section to fix them.

Repair table

Table repair is a terrible task. If the problem is very unique, it is more difficult to fix it. However, there are some conventional guiding ideology and processes that can follow to increase the chance of correcting tables. Generally, you can use the fastest repair method at the beginning to check whether the fault can be corrected. If it is not found, you can gradually upgrade it to a more thorough but slower) repair method until the fault is repaired or you cannot continue the upgrade, most problems can be fixed without larger and slower methods ). If the table cannot be repaired, It is restored from the backup. Instructions on Restoring Using backup files and update logs are provided in Chapter 11th.

1. Execute standard table repair

To repair a table, perform the following steps:

1) try to use the -- recover option to correct the table, but you can also use the -- quick option to try to restore the table based only on the content 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 command in the previous step, but ignore the -- quick option to allow my s a m c h k or isamchk to move forward and modify the data file:

% Myisamchk -- recover tbl_name

% Isamchk -- recover tbl_name

3) if it does not work, try -- safe-recover. This method is slower than a common restoration method, but it can solve several problems that the-recover method cannot correct:

% Myisamchk -- safe-recover tbl_name

% Isamchk -- safe-recover tbl_name

If myisamchk or isamchk is stopped in any step due to an error message "C a n't create new temp file: file_name, repeat this command and add the -- force option to force the temporary file to be cleared. This temporary file may be left in the last failed repair.

Copy a table before repairing it. A common precaution that should be followed before performing the repair is to make a new copy of the table. This may not happen, but if so, you can copy the table from the copy file and try another restoration method.

2. What should I do when the standard table repair method fails?

If the table cannot be repaired during the standard restoration process, the index file may be lost or destroyed during restoration. Although not necessarily possible, it is still possible to lose the description file of the table. In either case, replace the affected files and try the standard repair process again.

To regenerate the index file, you can use the following process:

1) locate the database directory containing the crashed table.

2) Move the data file of the table to a safe place.

3) Call MySQL (the best combination with PHP) and re-create a new empty table by executing the following statement, this statement uses the table description file tbl_name.frm to start generating new data and index files again:

MySQL (best combination with PHP)> delete from tbl_name;

4) Exit MySQL (the best combination with PHP), move the original data file back to the database directory, and replace the newly created empty file.

5) Try the standard table repair method again.

To restore the description file of the table, you can recover it from the backup file first, and then try the standard restoration method again. If there is no backup for some reason, but you know the create table statement for creating a TABLE, you can still restore the file:

1) locate the database directory containing the crashed 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 (the best combination with PHP) and release the create table statement to CREATE the TABLE.

4) log out of MySQL (the best combination with PHP), move the raw data file back to the database directory, and replace the newly created data file. If you move the index file in step 2, you must also move it back to the database directory.

5) Try standard table repair again.

The above content is an introduction to checking and maintaining MySQL database tables. I hope you will get something better.