Make MySQL data sheets healthier with MYISAMCHK

Make MySQL datasheet healthier with myisamchk 2011-03-15 09:15 The water is too deep itpubfont Size:T | T

In order for the data tables in the MySQL database to be "healthier", they need to undergo regular medical examinations. Here the author recommends using the Myisamchk tool to check the data table irregularly. At the same time, the author gives some relevant considerations and the use of skills.

Ad:51cto Net + the first App innovation contest hot Start-----------super million resources for you to take!

In MySQL database, the data table hundreds, database administrators can not have so much time and effort to check the validity of the table, so they urgently need a tool to be able to check the relevant data table, to determine whether there are some problems with the table. It's like we need a physical examination every year, we find minor problems and fix them immediately so that we don't get sick. Here the author recommends using the Myisamchk tool to check the data table irregularly. In the use of this tool, I would like to highlight the relevant considerations and the use of skills.

First, large tables to increase the capacity of memory

The efficiency of the MYISAMCHK tool is mainly related to the size of the table. If the data table is larger, it will run slower. In this case, the database administrator may need to adjust the memory configuration.

Typically, the-o parameter determines the amount of memory that the Myisamchk tool can use when it runs. When you run Myisamchk, the memory allocated to it does not exceed the size specified by this parameter. If the database administrator needs to use the Myisamchk tool for each large table, it is often necessary to determine the size of the data table first, and then determine the size of the memory that needs to be consumed. By default, the amount of memory that can be recovered is only 3 m. For large tables, this memory is not enough. At this point the database administrator can resize the memory to make the Myisamchk tool run faster.

If necessary, you can use the-o parameter to adjust the memory to the appropriate size. such as-O sort=8m and so on. In general, this value is set to 16M. It does not need to be too big, otherwise it will affect the operation of other jobs. In summary, before running the Myisamchk tool, the database administrator needs to evaluate the size of each data table in the database. If you have a larger data table, you can filter it out first (such as through wildcards). Then adjust the size of the memory and examine the large tables separately. This is a good way to improve the efficiency of myisamchk operation.

Second, the use of MYISAMCHK tools to recover data requires a lot of hard disk space

When checking a data table with Myisamchk, you can also use the Myisamchk tool to recover some tables if you find problems with them. However, when recovering, you need to make sure that you have enough hard disk space. Otherwise, it may cause the data table recovery to fail.

Typically, the amount of hard disk space you need to use is one-fold of the data table. That is, if you need to restore a 2G data table, you should have at least 2G of the remaining space required. That is, you need to expand the size of the data file to the original one times. What if there is not enough hard disk space? The database administrator can now consider using the-quick option. With this option, there is no need for this amount of space to be repaired. However, it is important to note that the database system simply creates an index file.

In some cases, the index file needs to be recreated when repairing. A new index file that replaces the old index file also requires a certain amount of disk space. Although it is said that at the beginning of the repair work, the database system will be truncated on the index file. For security reasons, however, I recommend that you keep enough hard disk space for it. And the size of the storage space required on the file system is the same as the original data file.

In addition, the database administrator may use the-recover option when using the Myisamchk tool. If the database administrator takes this option, it can fix almost everything. But notice here there's a keyword almost. That is to say, most datasheets problems can be solved with this option. But there are some exceptions. If the unique key is not unique and other problems, the immediate use of this option is no way. In general, the database administrator can try this option first when resuming work. When the system reports that this option is not available, try using another recovery method. However, it is important to note that the sort buffer space is used when using this option. The approximate size is typically twice times the data.

In summary, when using the Myisamchk tool to restore the table, you need to ensure that it has enough disk space. The author's recommendation is to have at least twice times more disk space than the data file size.

Third, gradually repair the damaged data table

If your data sheet is damaged, how do you fix it? The author thinks that we should adopt the method of gradual repair. However, in performing the repair operation, there are two necessary steps that need to be done. One is to stop running the database server , and the other is to back up the relevant data files. Although the Myisamchk tool is a relatively secure data-checking tool, it is still a necessary security measure to make the necessary backup of the data files before the data is restored. When recovering data, you can usually do so in the following order.

The first is to check the data table to determine which data tables have problems and the size of the problem. for security and efficiency reasons, it is common to fix only those data tables that appear to be problematic. When checking a data table, you can use the-update-state option to tell the MYISAMCHK tool which data tables are checked and identify the problematic table. Then fix it, you can specify the Myisamchk tool to fix only those tables that report errors.

The second is to carry out simple and safe repair. When you start repairing data, it's a good idea to use the quick recovery model. in this mode, the database system does not touch the data files to repair the index files. If the data file contains everything it should have and the correct delete connection to the data file, using the quick recovery model can repair the damaged data table, and if it is a simple and secure fix, it is a good idea to make a backup of the data table before the repair begins. Then use the Myisamchk–r table name to restore the specified data table. After running this command, the system will remove the incorrect records from the data file and the records that have been deleted, and re-create the index files. If you are not able to repair the data file using this mode, the following recovery model is used.

The third is the recovery mode in the case of index file corruption. if the first 16K block of the index file for the database table is destroyed, or if it contains incorrect information, or if the entire index file is missing, then a new index file needs to be created. In this case, you need to use the recovery model mentioned below. At this point the database administrator needs to move the data files to a secure location, and then use the table description file to create new data files and index files. The old data file is then copied into the newly created data file. Then use the command myisamchk–r-q to recover the data table. If there is a problem with the index file, you can use this command to recover the data.

Four is the most extreme case where the table description file is also damaged. The above recovery model cannot be used at this time. Because the table description file cannot be used to create new data files and index files. At this point, I recommend that you use the database recovery method to recover all the data files. In the MySQL database, you can set up full or incremental backups as needed. If the table description file is also corrupted, it is undoubtedly the safest way to use database recovery.

Of course the Myisamchk tool does more than that. There are a lot of interesting things underneath it. If necessary, repair a specified data table. You can also use wildcard characters to check files in batches. Even independent data files can be checked and so on. It is important to note, however, that the necessary backup of the database is required, either way. Although it is relatively safe to say myisamchk this tool. But it is still a good database management habit to make a backup of the original data before any inspection and repair.

However, it is important to note that the Myisamchk tool is not omnipotent. For some extreme damage, such as table description file corruption, this tool is useless. For this reason, the database administrator still needs the best database for daily backup work.

Make MySQL data sheets healthier with MYISAMCHK