Partial data corruption recovery process in MySQL database

A few days ago because of the MySQL database part of the data corruption reasons, I tried to recover the data, after the collation of the following documents for your reference, in case you colleagues have similar problems in the future, you can take less detours, as soon as possible to solve the problem.

Environment: Windows2003

Database: MySQL

Corrupted data file name: function_products

Import the database content physical files directly to Mysql\data, each table 3 files, respectively:. frm. myd. myi. First of all, my first thought was to search the Internet, looking for similar tools, trying to recover corrupted files through tools, so I looked up on Google and found a tool called mysqlrecovery, which I used to restore after installation, but unfortunately the effect was too bad, dozens of MB of data files, After the recovery, it prompts me to be only dozens of KB. I also thought that under MySQL should own the fix procedure and so on, so want to recover through it, thought should not be too bad bar, look up information on the internet, prompted: Due to temporary power outages, using kill-9 to stop the MySQL service process, or MySQL is running at a high speed when the forced backup operation, etc. , all of which could destroy MySQL's data files. If the service is changing the file while it is being disturbed, the file may leave a wrong or inconsistent state. Because this kind of destruction is sometimes not easy to find, when you find this error may be a long time later.

So, when you find this problem, maybe all the backups have the same error. I think the problem I'm having now is probably the problem, because the backed-up data is also partially corrupted, so it's not fully operational, realizing that the MYISAMCHK program's access to the MySQL data files used to check and modify should be unique. If the MySQL service is using a file and modifies the file that Myisamchk is checking, Myisamchk will mistakenly assume that an error has occurred and will attempt to fix it-which will cause the MySQL service to crash! In this way, to avoid this, we usually need to turn off the MySQL service while we work. As a choice, you can also temporarily shut down the service to make a copy of the file and then work on that copy. When you're done, shut down the service and replace the original file with the new file (you may also need to use the change log for the period).

MySQL Data directory is not too difficult to understand. Each database corresponds to a subdirectory, and each subdirectory contains a file corresponding to the data table in the database. Each data table corresponds to three files, which are the same as the table names, but have different extensions. The Tblname.frm file is a table definition that holds the contents and types of the data columns contained in the table. The Tblname.myd file contains the data in the table. The Tblname.myi file contains the index of the table (for example, it might contain a lookup table to help increase queries against the table's primary key columns). To check for errors in a table, you only need to run Myisamchk (in the MySQL bin directory) and provide the location and table name of the file, or the index file name of the table:

% Myisamchk/usr/local/mysql/var/dbname/tblname

% Myisamchk/usr/local/mysql/var/dbname/tblname.myi

All two of the above commands can perform a check on the specified table. To check all tables in the database, you can use wildcard characters:

% Myisamchk/usr/local/mysql/var/dbname/*.myi

To check all tables in all databases, you can use two wildcard characters:

% Myisamchk/usr/local/mysql/var/*/*.myi

If you do not have any options, MYISAMCHK will perform a normal check on the table file. If you have doubts about a table, but the normal check does not detect any errors, you can perform a more thorough check (but also slower!). ), which requires the use of the--extend-check option:

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

Checking for errors is not destructive, which means you don't have to worry that performing a check on your data files will make the existing problems worse. On the other hand, the fix option, while usually safe, does not undo changes to your data file. For this reason, we strongly recommend that you try to repair a corrupted table file first by making a backup and make sure that your MySQL service is off before making this backup.