Reason analysis and repair method of MySQL data table damage

1. Table Damage Reason analysis
The following are common causes of MySQL table corruption:

1, the sudden loss of power to the server caused data file corruption.

2, forced shutdown, did not first shut down the MySQL service.

3. The mysqld process was killed while writing the table.

4, the use of myisamchk at the same time, Mysqld also in the operating table.

5, disk failure.

6, the server crashed.

7, the MySQL itself bug.

2. Symptom of table damage
A typical symptom of a damaged table is as follows:

1. When selecting data from a table, you get the following error:

Incorrect key file for table: ' ... '. Try to repair it

2. The query cannot find rows in the table or return incomplete data.

3. Error:table ' P ' is marked as crashed and should to be repaired.

4, open the table failed: Can ' t open file: ' xxx. Myi ' (errno:145).

5,

3. Prevent MySQL Table damage
The following means can be used to prevent MySQL table damage:

1. Periodically use MYISAMCHK to check the MyISAM table (note that you want to turn off mysqld), it is recommended to check the table (without closing the mysqld) by using check table.

2, after a large number of updates or deletions, it is recommended to use optimize table to optimize tables, so that both reduce file fragmentation, but also reduce the probability of table damage.

3, before shutting down the server, first close the mysqld (normal shutdown service, do not use kill-9 to kill the process).

4, the use of UPS power to avoid sudden power failure.

5, using the latest stable release version of MySQL, reduce the MySQL itself bug caused the table damage.

6. For the InnoDB engine, you can use Innodb_tablespace_monitor to check the integrity of the file space management within the tablespace file.

7, raid on the disk, reduce disk errors and improve performance.

8, the database server best run only mysqld and necessary other services, do not run other business services, so as to reduce the crash caused the possibility of table damage.

9, not afraid of in case, afraid of accidents, usually do a good backup is to prevent table damage effective means.

4. MySQL Table Damage Repair
The MyISAM table can be repaired using the following steps:

1, use Reapair table or Myisamchk to repair.

2. If the above method repair is invalid, use the Backup recovery table.

You can refer to the following procedure:

Phase 1: Check your watch

If you have a lot of time, run Myisamchk *. Myi or MYISAMCHK-E *. Myi. Use the-S (silence) option to prohibit unnecessary information.

If the MYSQLD server is down, use the--update-state option to tell Myisamchk to mark the table as ' checked '.

You have to fix only those tables where the MYISAMCHK report errors. For such a table, continue to stage 2.

If in check, you get strange errors (such as out of memory error), or if Myisamchk crashes, to Stage 3.

Phase 2: Simple and secure repair

Note: If you want to repair faster, you should set the value of the sort_buffer_size and key_buffer_size variables to about 25% of the available memory when you run MYISAMCHK.

First, try Myisamchk-r-Q tbl_name (-r-q means "quick recovery mode"). This will attempt to repair the index file without touching the data file. This should work if the data file contains everything it should and a delete connection to the correct location in the data file, and the table can be repaired. Begin to repair the next table. Otherwise, perform the following procedures:

Back up the data file before continuing.

Using Myisamchk-r tbl_name (-R means "recovery mode"). This deletes the incorrect records and deleted records from the data file and rebuilds the index files.

If the previous step fails, use Myisamchk--safe-recover tbl_name. The security recovery model uses an old recovery method to handle a small number of cases (but more slowly) that do not work with the normal recovery model.

If you get a strange error (for example out of memory error), or if the myisamchk crashes, to Stage 3.

Stage 3: Difficult fixes

Only the first 16K block of the index file is corrupted, or contains incorrect information, or if the index file is lost, you should be at this stage. In this case, you need to create a new index file. Do the following steps:

Move the data file to a safe place.

To create a new (empty) data file and index file by using a table description file:

shell> MySQL db_name

Mysql> SET autocommit=1;

mysql> TRUNCATE TABLE tbl_name;

Mysql> quit

If your MySQL version has no truncate TABLE, use the delete from Tbl_name.

Copy old data files to the newly created data file. (Don't just move old files back into new files; you need to keep a copy in case something goes wrong.) ）

Back to Stage 2. Now Myisamchk-r-Q should be working. (This should not be an infinite loop).

You can also use the repair TABLE tbl_name use_frm to automatically execute the entire program.

Stage 4: Very difficult to repair

only. frm The description file is destroyed, you should get to this stage. This should never have happened because the description file does not change after the table is created.

Restore the description file from one backup and then back to Phase 3. You can also recover the index file and then return to Stage 2. For the latter, you should start with Myisamchk-r.

If you don't have a backup but know exactly how the table was created, create a copy of the table in another database. Delete the new data file and move the profile and index files from other databases to the corrupted database. This provides a new description and index file, but lets. The myd data file was left alone. Go back to Stage 2 and try to rebuild the index file.

The InnoDB table can be repaired in the following ways:

If the database page is corrupted, you may want to dump your table from the database with SELECT INTO OutFile, and most of the data obtained in this way is intact. Even so, corruption can cause a select * from Tbl_name or InnoDB background operation to crash or assert, or even make the InnoDB roll back crash. However, you can use it to force the InnoDB storage engine to start and prevent the background operation from running so that you can dump your tables. For example, you can add the following line to the [mysqld] section of the options file before restarting the server:
    [mysqld]innodb_force_recovery = 4innodb_force_ Recovery is allowed with a value of not 0 as follows. A larger number contains preventive measures of all smaller numbers. If you can dump your table with an option value that is mostly 4, you are safe, and only some of the data on the corrupted individual page is lost. A value of 6 is exaggerated because the database page is left in a stale state, which in turn can cause more damage to the B-tree and other database structures.
    1 (srv_force_ignore_corrupt)
Let the server run even if a corrupted page is detected by the server; try to have the select * from tbl_name skip corrupted index records and pages , which helps to dump the table.

2 (Srv_force_no_background)
Prevents the main thread from running, which will block if the crash may occur during the purge operation.
3 (Srv_force_no_trx_undo)
Transaction rollback does not run after recovery.
4 (Srv_force_no_ibuf_merge)
The Insert Buffer merge operation is also blocked. If you could cause a crash. It is best not to do these operations, do not calculate table tables.
5 (Srv_force_no_undo_log_scan)
Do not view the unfinished log at the start of the database: InnoDB treats the unfinished transaction as committed.
6 (Srv_force_no_log_redo)
Do not roll the log forward in the recovery connection.
The database cannot be used separately with the options allowed in these options. As a security measure, when Innodb_force_recovery is set to a value greater than 0, InnoDB prevents the user from performing an insert, UPDATE, or delete operation.
Even if forced recovery is used, you can also drop or create tables. If you know that a given table is causing a rollback crash, you can remove it. You can also use this to stop runaway rollback caused by failed bulk imports or failed alter TABLE. You can kill the MYSQLD process and set the Innodb_force_recovery to 3, leaving the database suspended without the need to roll back, and then discarding the table that caused the runaway rollback.