Repair of some data corruption in MySQL database

The following articles mainly describe the actual recovery process of some data corruption in the MySQL database. A few days ago, due to data corruption in the MySQL database, I tried the actual data restoration operation, sort out the following documents for your reference.

Environment: Windows2003

Database: MySQL (best combination with PHP)

Corrupted data file name: function_products

Import the physical file of the MySQL database content directly to MySQL (the best combination with PHP) \ data. Each table has three files, respectively:

. Frm. myd. myi. First of all, I first thought of searching online, searching for similar tools, and trying to restore corrupted files through tools. So I searched on google, find a MySQL (the best combination of PHP and MySQL) recovery tool. After installation, I use it for restoration. Unfortunately, the effect is too bad, and dozens of MB of data files are large, after the restoration, it prompts that I only have dozens of KB.

I also thought that MySQL (the best combination with PHP) should have its own repair program, and so on, so I want to use it for recovery, I thought it should not be too bad, after searching for information on the Internet, the following message is displayed: due to temporary power failure, kill-9 is used to stop the MySQL (the best combination with PHP) service process, or MySQL (the best combination with PHP) force backup during high-speed operation, and so on, all of which may destroy MySQL (the best combination with PHP) data files. If the service is changing the file when it is disturbed, the file may be in an incorrect or inconsistent state. This destruction is sometimes not easy to find. It may be a long time before you find this mistake.

So when you find this problem, maybe all the backups have the same error. I think the problem I encountered may be that the backup data is damaged, so it cannot be fully run, realize that the myisamchk program should be unique in accessing MySQL (the best combination with PHP) data files used for checking and modifying.

If the MySQL (best combination with PHP) service is using a certain file and modifying the file myisamchk is checking, myisamchk will mistakenly think that an error has occurred, and will try to fix it-this will cause MySQL (the best combination with PHP) service to crash! In this way, to avoid this situation, we usually need to disable the MySQL (the best combination with PHP) service at work.

You can also temporarily disable the service to make a copy of a file and then work on the copy. After you have finished, close the service again and replace the original file with the new file (You may also need to use the change log during the period ).

MySQL (the best combination with PHP) data directory is not too hard to understand. Each MySQL database corresponds to a sub-directory, and each sub-directory contains files corresponding to the data tables in the database. Each data table corresponds to three files with the same name but different extensions.

The tblname. frm file is a table definition that stores the content and type of data columns contained in the table. The tblname. myd file contains the table data. The tblname. myi file contains the table index (for example, it may contain a lookup table to help improve the query of the table's primary key columns ). To check for a table error, you only need to run myisamchk (in the bin directory of MySQL (the best combination with PHP) and provide the file location and table name, or the index file name of the table:

 
 

  
  
% Myisamchk/usr/local/MySQL (best combination with PHP)/var/dbname/tblname
  
  
% Myisamchk/usr/local/MySQL (the best combination with PHP)/var/dbname/tblname. myi
 
 

You can run the preceding two commands to check the specified table. To check all tables in the MySQL database, you can use wildcards:

 
 

  
  
% Myisamchk/usr/local/MySQL (the best combination with PHP)/var/dbname/*. myi
 
 

To check all tables in all MySQL databases, you can use two wildcards:

 
 

  
  
% Myisamchk/usr/local/MySQL (the best combination with PHP)/var/*. myi
 
 

Without any options, myisamchk performs a normal check on the table file. If you have doubts about a table, but normal checks cannot find any errors, you can perform a more thorough check (but it is also slower !), This requires the -- extend-check option:

 
 

  
  
% myisamchk --extend-check /path/to/tblname  
 
 

The check for errors is not destructive, which means you don't have to worry about executing the check on your data files will make the existing problems worse. On the other hand, the repair option is usually secure, but its changes to your data files cannot be undone.

For this reason, we strongly recommend that you first make a backup when trying to repair a corrupted table file and ensure that your MySQL (the best combination with PHP) is used before the backup is made) the service is disabled.
At the command prompt in Win2003, the above content is an introduction to the restoration process of some data corruption in the MySQL database. I hope you will gain some benefits.