Summary of restoration of corrupted data in mysql database _ MySQL

Summary of the restoration process of corrupted data in the mysql database this document is a summary of the original practice of Hewei, a master colleague. The process is really wonderful. Finally, the repair of damaged databases has achieved a satisfactory result, A good article worth adding to your favorites

Summary of restoration of corrupted data in the mysql database

(This document is a summary of the original practices of Hewei, a master colleague. it is a wonderful process. it is worth a good article to fix and damage the database)

A few days ago, due to some data corruption in the mysql database, I tried to recover the data and then sorted out the following documents for your reference,
If you have similar problems in the future, you can avoid detours and solve the problems as soon as possible.

Environment: windows2003
Database: mysql
Corrupted data File name: function_products

Import the physical files of the database content directly to mysql/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 tool named MySQLRecovery and use it for restoration after installation. Unfortunately, the effect is too bad, and the data files of dozens of MB are restored.
Then it prompts me that I only have a few dozen K, so I can vomit blood...
I also thought that mysql should have its own repair program and so on, so I want to use it for recovery. I thought it would not be too bad. I found it online.
Materials, tips:
Due to temporary power failure, kill-9 is used to stop the MySQL service process, or force backup is performed when mysql is running at high speed,
All of these may destroy MySQL data files. If the service is changing the file when it is disturbed, the file may leave an error
Or inconsistent status. 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 data files used for checking and modifying. If the MySQL service is using
A file and modify the file myisamchk is checking. myisamchk will mistakenly think that an error has occurred and will try to fix it --
This will cause the MySQL service to crash! In this way, to avoid this situation, we usually need to close the MySQL service at work. Select,
You can also temporarily close the service to make a copy of a file and then work on it. After you have finished, close the service again and
Replace the original file with a new file (you may need to use the change log during the period ).
The MySQL data directory is not hard to understand. Each database corresponds to a sub-directory, and each sub-directory contains
Data table file. Each data table corresponds to three files with the same name but different extensions. The tblName. frm file is
Table definition, which stores the content and type of data columns contained in the table. The tblName. MYD file contains the table data. TblName. MYI file
Contains the index of the table (for example, it may contain a lookup table to help improve the query of the primary key columns of the table ).
To check the error of a table, you only need to run myisamchk (in the bin directory of MySQL) and provide the file location and table name, or the index file name of the table:
% Myisamchk/usr/local/mysql/var/dbName/tblName
% Myisamchk/usr/local/mysql/var/dbName/tblName. MYI

You can run the preceding two commands to check the specified table. To check all tables in the database, you can use wildcards:
% Myisamchk/usr/local/mysql/var/dbName/*. MYI

To check all tables in all databases, you can use two wildcards:
% Myisamchk/usr/local/mysql/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 service is disabled before the backup is made.

At the command prompt in win2003, enter:

Note: This is the whole process of my operations at that time.

D:/Documents and Settings/Administrator> c:

C:/> cd mysql

C:/mysql> cd data

C:/mysql/data> cd hw_ice ice

C:/mysql/data/hw_enterprice> myisamchk function_products.frm
'Myisamchk' is neither an internal or external command nor a program that can be run.
Or batch files.

C:/mysql/data/hw_ice Ice> cd/

C:/> cd mysql

C:/mysql> cd bin

Note: view the help information of myisamchk.

C:/mysql/bin> myisamchk
Myisamchk Ver 2.6 for Win95/Win98 at i32
By Monty, for your professional use
This software comes with no warranty: see the PUBLIC for details.

Description, check and repair of ISAM tables.
Used without options all tables on the command will be checked for errors
Usage: myisamchk [OPTIONS] tables [. MYI]

Global options:
-#, -- Debug =... Output debug log. Often this is 'd: t: o, filename'
-?, -- Help Display this help and exit.
-O, -- set-variable var = option
Change the value of a variable. Please note that
This option is deprecated; you can set variables
Directly with '-- variable-name = value '.
-T, -- tmpdir = path Path for temporary files
-S, -- silent Only print errors. One can use two-s to make
Myisamchk very silent
-V, -- verbose Print more information. This can be used
-- Description and -- check. Use limit-v for more verbosity!

-V, -- version Print version and exit.
-W, -- wait Wait if table is locked.

Check options (check is the default action for myisamchk ):
-C, -- check table for errors
-E, -- extend-check Check the table VERY throughly. Only use this in
Extreme cases as myisamchk shoshould normally be able
Find out if the table is OK even without this switch
-F, -- fast Check only tables that haven'tbeen closed properly
-C, -- check-only-changed
Check only tables that have changed since last check
-F, -- force Restart with '-r' if there are any errors in the table.
States will be updated as with '-- update-state'
-I, -- information Print statistics information about table that is checked
-M, -- medium-check Faster than extend-check, but only finds 99.99%
All errors. shocould be good enough for most cases
-U -- update-state Mark tables as crashed if you find any errors
-T, -- read-only Don't mark table as checked

Repair options (When using '-R' or'-O ')
-B, -- backup Make a backup of the. MYD file as 'filename-time. bak'
-- Correct-checksum Correct checksum information for table.
-D, -- data-file-length = # Max length of data file (when recreating data
File when it's full)
-E, -- extend-check Try to recover every possible row from the data file
Normally this will also find a lot of garbage rows;
Don't use this option if you are not totally desperate.
-F, -- force Overwrite old temporary files.
-K, -- keys-used = # Tell MyISAM to update only some specific keys. # is
Bit mask of which keys to use. This can be used
Get faster inserts!
-R, -- recover Can fix almost anything indexes t unique keys that aren't
Unique.
-N, -- sort-recover Forces recovering with sorting even if the temporary
File wocould be very big.
-P, -- parallel-recover
Uses the same technique as '-R' and'-N', but creates
All the keys in parallel, in different threads.
This is alpha code. use at your own risk!
-O, -- safe-recover Uses old recovery method; Slower than '-R' but can
Handle a couple of cases where '-R' reports that it
Can't fix the data file.
-- Character-sets-dir =...
Directory where character sets are
-- Set-character-set = name
Change the character set used by the index
-Q, -- quick Faster repair by not modifying the data file.
One can give a second '-Q' to force myisamchk
Modify the original datafile in case of duplicate keys
-U, -- unpack Unpack file packed with myisampack.

Other actions:
-A, -- analyze Analyze distribution of keys. Will make some joins in
MySQL faster. You can check the calculated distribution
By using '-- description -- verbose table_name '.
-D, -- description Prints some information about table.
-A, -- set-auto-increment [= value]
Force auto_increment to start at this or higher value
If no value is given, then sets the next auto_increment
Value to the highest used value for the auto key + 1.
-S, -- sort-index Sort index blocks. This speeds up 'read-Next' in
Applications
-R, -- sort-records = #
Sort records according to an index. This makes your
Data much more localized and may speed up things
C:/mysql/bin> myisamchk c:/mysql/data/hw_enterprice/function_products.frm
Myisamchk: error: 'C:/mysql/data/hw_ice ice/function_products.frm 'is not a M
YISAM-table

C:/mysql/bin> myisamchk c:/mysql/data/hw_enterprice/function_products.myi
Checking MyISAM file: c:/mysql/data/hw_ice ice/function_products.myi
Data records: 85207 Deleted blocks: 39
Myisamchk: warning: Table is marked as crashed
Myisamchk: warning: 1 clients is using or hasn' t closed the table properly
-Check file-size
-Check key delete-chain
-Check record delete-chain
Myisamchk: error: record delete-link-chain upted
-Check index reference
-Check data record references index: 1
-Check data record references index: 2
-Check data record references index: 3
-Check record links
Myisamchk: error: Wrong bytesec: 0-195-171 at linkstart: 841908
MyISAM-table 'C:/mysql/data/hw_ice ice/function_products.myi' is already upted
Fix it using switch "-r" or "-o"

Continue:

C:/mysql/bin> myisamchk -- recover -- quick c:/mysql/data/hw_ice ice/function_p
Roducts. myi
-Check key delete-chain
-Check record delete-chain
Myisamchk: error: record delete-link-chain upted
Myisamchk: error: Quick-recover aborted; Run recovery without switch 'Q'
Updating MyISAM file: c:/mysql/data/hw_ice ice/function_products.myi
MyISAM-table 'C:/mysql/data/hw_ice ice/function_products.myi' is not fixed be
Cause of errors
Try fixing it by using the -- safe-recover (-o) or the -- force (-f) option

The system prompts me to use -- safe-recover (-o) or the -- force (-f) option for the repair operation, so

C:/mysql/bin> myisamchk -- safe-recover c:/mysql/data/hw_ice ice/function_prod
Ucts. myi
-Recovering (with keycache) MyISAM-table 'C:/mysql/data/hw_ice ice/function _
Products. myi'
Data records 85207
Wrong bytesec: 0-195-171 at 841908; Skipped
Data records 85215

Copy the repaired physical file to mysql/data and access it through phpMyAdmin. OK is normal!
This data repair operation was successful, and the data has been properly recovered, totaling 85215 records, of which 85207 were recovered.

Summarize this experience and find the materials as follows:

There are three repair types when you try to fix a broken table. If you get an error message indicating that a temporary file cannot be created, delete the file indicated by the information and try again-this is typically left behind by the last repair operation.
The three methods are as follows:
% Myisamchk -- recover -- quick/path/to/tblName
% Myisamchk -- recover/path/to/tblName
% Myisamchk -- safe-recover/path/to/tblName

The first is the fastest, which is used to fix the most common problems, and the last is the slowest, which is used to fix problems that cannot be fixed by other methods.

Check and repair MySQL data files
If the above method cannot repair a damaged table, you can try the following two tips before giving up:
If you suspect that the index file of the table (*. MYI) an irreparable error occurs, or even this file is lost. you can use the data file (*. MYD) and data format file (*. frm. Create a copy of the data file (tblName. MYD. Restart your MySQL service and connect to the service. run the following command to delete the table content:
Mysql> delete from tblName;
When deleting the table content, a new index file is created. Log out and close the service again, and overwrite the new (empty) data file with the data file (tblName. MYD) you just saved. Finally, use myisamchk to execute the standard repair (the second method above) and regenerate the index data based on the table data content and the table format file.

If your table format file (tblName. frm) is lost or an irreparable error occurs, but you know how to use the corresponding create table statement to regenerate the TABLE, you can regenerate a new one. frm files are used together with your data files and index files (if there is a problem with the index file, use the above method to recreate a new one. Create a copy of the data and index file, and then delete the original file (delete all records related to the table in the data directory ).

Start MySQL and use the original create table file to CREATE a new TABLE. The new. frm file should work properly, but you 'd better execute the standard fix (the second method above ).

If you have a similar problem, we recommend that you analyze the root cause of the problem, search for information, and solve the problem by yourself. you can not only learn more knowledge and skills, but more importantly, I also got happy while solving the problem.