Use Myisamchk for MySQL Data Table Health Check

Guidance:InMySQL databaseThere are hundreds of data tables, and the database administrator cannot have so much time and effort to check the effectiveness of the table in sequence. Therefore, they urgently need a tool to perform a health check on the relevant data tables, to determine whether the table has any problems. This is like we need a health check every year. If we find small problems, we can fix them immediately to avoid getting sick. The recommended tool here isMyisamchk,Use this tool to perform irregular checks on data tables. When using this tool, I would like to emphasize the relevant precautions and usage skills.

I. Large tables need to increase the memory capacity

The efficiency of Myisamchk is mainly related to the table size. If the data table is large, it runs slowly. In this case, the database administrator may need to adjust the memory configuration.

In general, the-O parameter determines the memory size that can be used when the Myisamchk tool is running. When running Myisamchk, the memory allocated to it cannot exceed the size specified by this parameter. If the database administrator needs to use the Myisamchk tool for each large table, the database administrator must first determine the size of the data table and determine the size of memory occupied. By default, the memory size that can be used for restoration is only 3 MB. For large tables, this memory is not enough. In this case, the database administrator can adjust the memory size so that the Myisamchk tool runs faster.

If necessary, you can use the-O parameter to adjust the memory size. For example,-O sort = 8 m. Generally, this value is set to 16 Mb. It does not need to be too large. Otherwise, the operation of other jobs will be affected. In short, before running the Myisamchk tool, the database administrator needs to evaluate the size of each data table in the database. If a large data table exists, you can filter it out first (for example, using wildcards ). Then adjust the memory size and check the large tables separately. This is a good way to improve the running efficiency of Myisamchk.

2. Using Myisamchk to restore data requires a large amount of hard disk space

When you use Myisamchk to check a data table, you can use Myisamchk to restore it if you find any problems with the table. However, when restoring the disk, make sure that there is sufficient disk space. Otherwise, the data table may fail to be restored.

Generally, the disk space required is twice the size of the data table. That is to say, if you want to restore a 2 GB Data Table, the required space should be at least 2 GB. That is, you need to double the size of the data file. What should I do if the hard disk space is insufficient? In this case, the database administrator can consider using the-quick option. With this option, there is no need for so much space for restoration. Note that the database system only creates an index file.

In some cases, you need to re-create the index file during restoration. In this case, the new index file that replaces the old index file also occupies a certain amount of disk space. Although the database system will delete the index files at the beginning of the restoration work. However, for the sake of security, I suggest you keep enough hard disk space for it. In addition, the size of the bucket required on the file system is the same as that of the original data file.

In addition, the database administrator may use the-Recover option when using the Myisamchk tool. If the database administrator uses this option, it can fix almost all problems. Note that there is almost a keyword here. This means that most data table problems can be solved through this option. However, there are some exceptions. If the unique key is not unique, there is no way to use this option immediately. Generally, the database administrator can try this option before resuming work. When the system reports that this option is unavailable, try another recovery method. However, you must note that the sort buffer space is used when using this option. Generally, it is twice the data size.

To sum up, when using Myisamchk to restore a table, make sure that there is sufficient disk space. I suggest that you have at least two times the size of data files in disk space.

3. Gradually repair damaged data tables

If your data table is damaged, how can we fix it? In my opinion, we should adopt a step-by-step repair method. However, there are two necessary steps to perform the repair operation. One is to stop running the database server, and the other is to back up the relevant data files. Although Myisamchk is a relatively secure data check tool, it is still a necessary security measure to back up the data files before they are restored. Data can be recovered in the following order.

First, check the data table to determine which data tables have problems and the size of the data tables. For the sake of security and efficiency, only the problematic data tables are fixed during restoration. When checking the data table, you can use the-update-state option to tell Myisamchk which data tables have been checked and identify the problematic tables. Then, you can specify the Myisamchk tool to fix only the tables that report errors.

Second, simple and secure repair. It is best to adopt the "quick recovery mode" when restoring data at the beginning ". In this mode, the database system does not have access to data files to repair index files. If the data file contains everything it should have and the correct connection to the data file is deleted, the quick recovery mode can be used to repair the damaged data table. If simple and secure repair is adopted, it is best to back up the data table before the restoration starts. Then, use the Myisamchk-r table name to restore the specified data table. After running this command, the system will delete the incorrect records and deleted records from the data file, and re-create the index file. If the data file cannot be repaired in this mode, use the following recovery mode.

Third, the recovery mode when the index file is damaged. If the first 16 K index file of the database table is damaged, or contains incorrect information, or the entire index file is lost, in this case, you need to create a new index file. In this case, we need to use the recovery mode mentioned below. In this case, the database administrator needs to first transfer the data file to a safe place, and then use the table description file to create a new data file and index file. Then copy the old data file to the newly created data file. Then run Myisamchk-r-q to restore the data table. If there is a problem with the index file, you can use this command to restore the data.

Fourth, the most extreme case is that the table description file is damaged. In this case, the above recovery mode cannot be used. The table description file cannot be used to create new data files and index files. At this time, the author suggests that you use the database recovery method to restore all data files. In the MySQL database, you can set full backup or Incremental backup as needed. If the table description file is damaged, database recovery is undoubtedly the safest method.

Of course, the functions of the Myisamchk tool are not only described above. There are a lot of interesting content below. Fix a specified data table as needed. You can also use wildcards to check files in batches. You can even check independent data files. However, no matter which method is used, you must back up the database first. Although Myisamchk is relatively secure. However, it is still a good database management habit to back up the original data before any check and repair.

However, the Myisamchk tool is not omnipotent. This tool is useless for some extreme damage, such as damage to the table description file. For this reason, the database administrator still needs the best routine backup of the database. The database administrator must do a good job in this area to ensure that the database is safe.