Restoration of some data corruption in MySQL database (1)

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, solve the problem as soon as possible. Environment: Windows2003 Database: MySQL corrupted data file name: function_products import the physical file of the database content directly to mysql \ data. Each table has three files, respectively :. frm. myd. myi. First of all, I first thought about searching online, searching for similar tools, and trying to restore corrupted files. So I searched for a tool named mysqlrecovery on google, after the installation, I use it for recovery, but unfortunately the effect is too bad. After the restoration, it prompts that I only have dozens of KB. 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 the information on the Internet and prompted: due to temporary power failure, kill-9 is used to stop the MySQL service process, or when MySQL is performing a forced backup operation at high speed, all of which may destroy MySQL 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 data files used for checking and modifying. If MySQL is using a 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 the MySQL service to crash! In this way, to avoid this situation, we usually need to close the MySQL 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 ). The MySQL data directory is not hard to understand. Each 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 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
The two commands above % myisamchk/usr/local/mysql/var/dbname/tblname. myi can check the specified table. To check all tables in the database, use the wildcard: % myisamchk/usr/local/mysql/var/dbname /*. to check all tables in all databases, use two wildcards: % myisamchk/usr/local/mysql/var /*/*. if myi does not have 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 that the -- extend-check option: % myisamchk -- extend-check/path/to/tblname is not destructive to the error check, this means that you don't have to worry about performing a 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 ents and settings \ administrator> c: C: \> cd MySQL C: \ mysql> cd data C: \ mysql \ data> cd hw_ice ice C: \ mysql \ data \ hw_ice ice> myisamchk function_products.frm 'Myisamchk' is not an internal or external command, nor a program or batch processing file that can be run. 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 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 if table is locked. check options (check is the default action for myisamchk ): -C, -- check table for errors -E, -- extend-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 checkedrepair 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 file packed with myisampack. other actions: -A, -- 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_ice ice \ function_products.frm Myisamchk: error: 'C: \ mysql \ data \ hw_policice \ function_products.frm 'is not a m Yisam-tablec: \ mysql \ bin> myisamchk c: \ mysql \ data \ hw_ice ice \ 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"