MySQL table damage prevention and repair _ MySQL-mysql tutorial

Prevention and repair of MySQL table corruption 1. analysis of causes of table corruption

The following are common causes of mysql table destruction:

1. data files are damaged due to a sudden power failure on the server.

2. force shutdown without shutting down the mysql service first.

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

4. when myisamchk is used, mysqld is also operating the table.

5. disk faults.

6. the server crashes.

7. mysql bug.

2. Symptoms of table corruption

Typical symptoms of a damaged table are 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 shoshould be retried red.

4. failed to open the table: Can't open file: '×××. myi' (errno: 145 ).

5,

3. prevent MySQL table corruption

You can use the following methods to prevent mysql table corruption:

1. use myisamchk regularly to check the MyISAM table (note that mysqld should be disabled). check table is recommended (do not disable mysqld ).

2. after a large number of update or delete operations, we recommend that you use optimize table to OPTIMIZE the TABLE. this reduces file fragments and the probability of TABLE corruption.

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

4. use the ups power to avoid sudden power failure.

5. use the latest stable mysql version to reduce table corruption caused by mysql Bugs.

6. for the InnoDB engine, you can use innodb_tablespace_monitor to check the integrity of file space management in Space files.

7. perform raid on the disk to reduce disk errors and improve performance.

8. it is best for the database server to run only mysqld and other necessary services, instead of other business services, so as to reduce the possibility of table damage caused by crashes.

9. Don't be afraid of accidents. backing up tables is an effective way to prevent table damage.

4. repair of MySQL table corruption

To fix the MyISAM table, follow these steps::

1. use reapair table or myisamchk to fix the issue.

2. if the above method does not work, use backup to restore the table.

For details, refer to the following practices:

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 disable unnecessary information.

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

You must fix only tables with errors reported by myisamchk. For such a table, continue to stage 2.

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

Phase 2: simple and secure repair

Note: If you want to fix it faster, you should set the values of the sort_buffer_size and Key_buffer_size variables to about 25% of the available memory when running myisamchk.

First, try myisamchk-r-q tbl_name (-r-q means "quick recovery mode "). This will try to repair the index file without contacting the data file. If the data file contains everything it should have and the deletion link pointing to the correct location in the data file, this should work and the table can be repaired. Start to repair the next table. Otherwise, execute the following process:

Back up data files before continuing.

Use myisamchk-r tbl_name (-r means "recovery mode "). This will delete incorrect records and deleted records from the data file and recreate the index file.

If the previous step fails, use myisamchk -- safe-recover tbl_name. The security recovery mode uses an old recovery method to handle a few cases (but slower) where the conventional recovery mode does not work ).

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

Phase 3: difficult repairs

Only when the first 16 K block of the index file is damaged, 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. Perform the following operations:

Move data files 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, delete from tbl_name is used.

Copy the old data file to the newly created data file. (Do not just move the old file back to the new file; keep a copy to prevent some errors .)

Return to Stage 2. Now myisamchk-r-q should work. (This should not be an infinite loop ).

You can also use repair table tbl_name USE_FRM to automatically execute the entire program.

Phase 4: Very difficult repairs

Only when the. frm description file is damaged should you reach this stage. This should never happen, because after the table is created, the description file will not change.

Recover the description file from a backup and return 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 have a backup, but you know exactly how the table is created, create a copy of the table in another database. Delete the new data file and move the description file and index file from other databases to the damaged database. This provides a new description and index file, but leaves the. MYD data file alone. Go back to stage 2 and try to recreate the index file.

The InnoDB table can be repaired using the following methods:

If the database page is damaged, you may want to use select into outfile to dump your table from the database. Generally, most of the data obtained in this way is intact. Even so, the damage may cause the SELECT * FROM tbl_name or InnoDB background operation to crash or assert, or even cause the InnoDB roll-back recovery to crash. However, you can use it to force the InnoDB storage engine to start and prevent background operations from running, so that you can dump your tables. For example, you can add the following lines in the [mysqld] section of the option file before restarting the server:
[Mysqld] innodb_force_recovery = 4innodb_force_recovery the following non-zero values are allowed. A larger number contains all smaller numbers. If you can use an option value of 4 to dump your table, you are safe, and only some data on damaged individual pages will be lost. A value of 6 is even more exaggerated because the database page is left in an old state, which in turn can cause more damage to the B tree and other database structures.
1 (srv_force_ignore_0000upt)
Even if the server detects a corrupted page, it also causes the server to run. try to let SELECT * FROM tbl_name skip the damaged index records and pages, which will help dump the table.

2 (SRV_FORCE_NO_BACKGROUND)
Prevents the main thread from running. if the crash may occur during the cleaning operation, this will block it.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollback after recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
It also prevents insert buffer merge operations. If you may cause a crash. We recommend that you do not perform these operations or calculate statistical tables.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
When starting the database, do not view unfinished logs: InnoDB regards unfinished transactions as committed.
6 (SRV_FORCE_NO_LOG_REDO)
Do not roll logs before restoring the connection.
The database cannot be used with the allowed options in these options. As a security measure, when innodb_force_recovery is set to a value greater than 0, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations.
Even if force recovery is used, you can DROP or CREATE a table. If you know that a given table is causing a rollback crash, you can remove it. You can also use this to stop the out-of-control rollback caused by failed large imports or failed ALTER tables. You can kill the mysqld process and set innodb_force_recovery to 3, so that the database is suspended and does not need to be rolled back. then, you can discard the tables that lead to out-of-control rollback.