MySQL Table damage prevention and repair

1. Cause Analysis of table damage

The following reasons are common causes of MySQL table corruption:

1, the server suddenly power loss caused data file corruption.

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

3. The mysqld process is killed when writing a table.

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

5, disk failure.

6, the server crashes.

7, the MySQL itself bug.

2. Symptoms of table corruption

A typical symptom of a damaged table is as follows:

1. When you select 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 be repaired.

4, Open Table failure: Can ' t open file: ' xxx. MYI ' (errno:145).

5.

3. Prevent MySQL table corruption

The following methods can be used to prevent MySQL table corruption:

1, regular use myisamchk check the MyISAM table (note to close mysqld), we recommend the use of Check table for checking tables (do not close mysqld).

2, after doing a large number of updates or deletions, it is recommended to use optimize table to optimize the table, which reduces the file fragmentation, but also reduces the probability of table corruption.

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

4, the use of UPS power supply, to avoid sudden power loss situation.

5, using the latest stable release version of MySQL, reduce the MySQL itself bugs caused by the table corruption.

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

7. Raid the disk to reduce disk errors and improve performance.

8, the database server is best only run mysqld and necessary other services, do not run other business services, so as to reduce the death of the table may cause damage.

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

4. Repair of MySQL table corruption

MyISAM the table can be repaired using the following steps :

1, use Reapair table or Myisamchk to repair.

2, if the above method repair invalid, take the backup to restore the table.

In particular, the following practices can be consulted:

Phase 1: Check your table

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

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

You must fix only those tables that MYISAMCHK report errors. For such a table, continue to stage 2.

If you get strange errors (such as Out of memory errors) when checking, or if myisamchk crashes, to Phase 3.

Phase 2: Simple and secure repair

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

First, try Myisamchk-r-Q tbl_name (-r-q means "fast recovery mode"). This will attempt to repair the index file without touching the data file. If the data file contains everything it deserves and a delete connection to the correct location within the data file, this should work and the table can be repaired. Start repairing the next table. Otherwise, perform the following procedure:

Back up the data files before continuing.

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

If the previous step fails, use Myisamchk--safe-recover tbl_name. The security recovery model uses an old recovery method that handles a few cases (but slower) that do not work with the normal recovery model.

If you get strange errors (such as Out of memory errors) when repairing, or if myisamchk crashes, to Stage 3.

Phase 3: Difficult fixes

Only the first 16K block of the index file is corrupted, or contains incorrect information, or if the index file is missing, you should go to this stage. In this case, you need to create a new index file. To do this, follow these steps:

Move the data file to a safe place.

Create a new (empty) data file and index file using the table description file:

shell> MySQL db_name

Mysql> SET autocommit=1;

mysql> TRUNCATE TABLE tbl_name;

Mysql> quit

If your MySQL version does not have a truncate TABLE, use the delete from Tbl_name.

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

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

You can also use the repair TABLE tbl_name use_frm, which automatically executes the entire program.

Phase 4: Very difficult to fix

Only the. frm description file is broken, and you should arrive at this stage. This should never have happened, because after the table is created, the description file will no longer change.

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

If you do not make 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 then move the profile and index files from the other database to the corrupted database. This provides a new description and index file, but lets. MYD data files are left alone. Go back to Phase 2 and try to rebuild the index file.

InnoDB The table can be fixed in the following ways:

    [mysqld]innodb_force_recovery = 4innodb_ force_recovery  is allowed for non-0 values as follows. A larger number contains all the precautions for smaller numbers. If you can dump your table with an option value that is mostly 4 , then you are more secure and only some of the data on the broken separate pages will be lost. A value for 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)  
If the server detects a corrupted page, let the server run; try to make select * from Tbl_ name  skips corrupted index records and pages, which helps to dump the table.

2 (Srv_force_no_background)
Prevents the main thread from running if the crash may occur during a cleansing operation, which will block it.
3 (Srv_force_no_trx_undo)
Transaction rollback is not run after recovery.
4 (Srv_force_no_ibuf_merge)
Also prevents the Insert buffer merge operation. If you can cause a crash. It is best not to do these operations, do not calculate table statistics.
5 (Srv_force_no_undo_log_scan)
Do not view incomplete logs when starting the database: InnoDB the unfinished transaction as committed.
6 (Srv_force_no_log_redo)
Do not roll forward logs in the recovery connection.
The database cannot be used in addition to 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 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 a failed bulk import or failed alter TABLE. You can kill the MYSQLD process and set the Innodb_force_recovery to 3 so that the database is suspended without having to roll back, and then discard the tables that cause runaway rollback.

Reprint: http://blog.csdn.net/forever_feng/article/details/5305070

MySQL Table damage prevention and repair