Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to use Myisamchk to carry out physical examination on MySQL data sheet". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, large tables should increase the capacity of memory.

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

Typically, the-O parameter determines the amount of memory that can be used by the Myisamchk tool when it runs. When running Myisamchk, the memory allocated for its use cannot exceed the size specified by this parameter. If the database administrator needs to use the Myisamchk tool for each large table, he often needs to first determine the size of the data table and then determine the amount of memory required. By default, the memory size that can be used for recovery is only 3m. This is not enough memory for large tables. 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-Osort=8M and so on. In general, this value can be set to 16m. It doesn't 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 table in the database. If you have a large datasheet, you can filter it out first (in the form of wildcards, for example). Then resize the memory and check these large tables separately. This is a good way to improve the efficiency of Myisamchk operation.

Second, using Myisamchk tools to recover data requires a lot of hard disk space.

When using Myisamchk to check data tables, if you find that there is a problem with some tables, you can also use Myisamchk as a tool to restore them. However, when recovering, you need to make sure that there is enough hard disk space. Otherwise, it may cause the data table recovery to fail.

Typically, the hard disk space required is twice the size of the data table. That is, if you need to restore the 2G data table, then the remaining space needed should be at least 2G. That is, you need to double the size of the data file. What if there is not enough hard disk space? at this point, the database administrator can consider using the-quick option. After using this option, you don't need so much space to fix it. It is important to note, however, that at this time the database system only creates an index file.

In some cases, the index file needs to be recreated when it is repaired. At this time, the new index file to replace the old index file also needs to take up a certain amount of disk space. Although at the beginning of the repair work, the database system will delete the index files. However, for the sake of safety, the author still recommends that enough hard disk space be reserved 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 adopts this option, you can fix almost everything. Note, however, that there is a keyword almost. That is to say, most data table problems can be solved through this option. But there are some exceptions. For example, when there is a problem that the unique key is not unique, there is no way to use this option immediately. In general, database administrators can try this option first when resuming work. When the system reports that this option is not available, try another recovery method. It is important to note, however, that sort buffer space is required when using this option. The approximate size is generally twice the size of the data.

To sum up, when you use the Myisamchk tool to restore a table, you need to ensure that it has enough disk space. The author's suggestion is to have at least twice the disk space of the data file size.

Third, gradually repair damaged data sheets

If unfortunately your data table is damaged, how to repair it? the author thinks that it should be repaired step by step. However, there are two necessary steps to do before performing the repair operation. 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 safe data checking tool, it is still a necessary security measure to back up the data files before restoring the data. When recovering the data, you can usually operate in the following order.

One is to check the data tables to determine which data tables have a problem, and the size of the problem. For the sake of security and efficiency, it is common to repair only those data tables that have problems. When inspecting datasheets, you can use the-update-state option to tell the Myisamchk tool which datasheets are checked and to identify problematic tables. When you then fix it, you can specify that the Myisamchk tool fixes only those tables that report errors.

The second is to carry out simple and safe repair. When you first start to repair data, it is best 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 points to the correct delete connection in the data file, the corrupted data table can be repaired by using the fast recovery model at this time, even with a simple and secure repair. it is best to back up the data table before the repair begins. Then use the Myisamchk-r table name to restore the specified data table. After running this command, incorrect records and deleted records are deleted from the data file, and the index file is recreated. If the data file cannot be repaired with this model, the following recovery model is used.

The third is the recovery model in the case of index file destruction. If the first 16K block of the index file of the database table is destroyed, or contains incorrect information, or if the entire index file is missing, in this case, you need to create a new index file. The recovery model discussed below needs to be used at this point. At this point, the database administrator needs to 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 use the command Myisamchk-r-q to restore the data table. If there is only a problem with the index file, you can use this command to recover the data.

The fourth is the most extreme case, that is, the table description file is also damaged. The above recovery model cannot be used at this time. Because table description files can no longer be used to create new data files and index files. At this time, the author suggests that we should use the way of database recovery to recover all the data files. In the MySQL database, you can set up a full backup or an incremental backup as needed. If the table description file is also corrupted, then database recovery is undoubtedly the safest way.

Of course, the Myisamchk tool does more than that. There is a lot of interesting content below it. If necessary, repair a specified data table. You can also use wildcards to check files in batches. You can even check independent data files, and so on. However, it is important to note that either way, you need to make the necessary backups of the database first. Although Myisamchk is a relatively safe tool. However, it is still a good habit of database management to backup the original data before any inspection and repair.

It is important to note, however, that Myisamchk tools are not everything. For some extreme damage, such as table description file corruption, this tool does not have the opportunity to use its talents. For this reason, the database administrator still needs the best daily backup of the database. Database administrators must do a good job in this area to ensure that the database is foolproof.

This is the end of the content of "how to use Myisamchk for physical examination of MySQL data sheet". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.