Use Myisamchk for MySQL Data Table Health Check

Welcome to the Linux community forum and interact with 2 million technical staff in the MySQL database. There 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 table to determine whether the table has some problems. It's like every one of us

Welcome to the Linux community forum and interact with 2 million technical staff> In the MySQL database, there are hundreds of data tables, and the database administrator cannot have so much time and energy to check the effectiveness of the table in sequence, therefore, they urgently need a tool to perform a health check on the relevant data table to determine whether the table has some problems. It's like every one of us

Welcome to the Linux community forum and interact with 2 million technicians>

In a MySQL database, there are hundreds of data tables, and the database administrator cannot spend so much time and energy checking the effectiveness of the table in sequence. Therefore, they urgently need a tool, perform a health check on the relevant data table 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 is Myisamchk, which is used 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 M. 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 data table of 2 GB, the required space should be at least 2 GB. That is, you need to increase the data file size to double the original size. 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.

[1] [2]