Reason analysis and repair method of MySQL data table damage

1. Table Damage Reason analysis


The following reasons are common causes of MySQL table corruption:





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





2, forced shutdown, without first shutting down the MySQL service.





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





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





5, disk failure.





6, the server crashed.





7, the MySQL itself bug.











2. Symptom of table Damage


Typical symptoms of a damaged table are 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 are repaired.





4, Open 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, regular use of myisamchk check MyISAM table (note to close mysqld), it is recommended to use check table to check the table (do not close the mysqld).





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





3, before shutting down the server, first turn off 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, use the latest stable release version of MySQL, reduce the MySQL itself bugs cause table damage.





6, for the InnoDB engine, you can use Innodb_tablespace_monitor to check the integrity of the document space management in the Tablespace file.





7, Raid on disk, reduce disk errors and improve performance.





8, the database server is 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 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.





specific 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 that MYISAMCHK report errors. For such a table, continue to stage 2.





If you get a strange error (for example out of memory error), or if Myisamchk crashes, to stage 3 when checking.





Phase 2: Simple and safe repair





Note: If you want to fix it 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 procedure:





back up the data file before continuing.





uses 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 to repair





only if 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.





creates a new (empty) data file and index file 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.





copies 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, the entire program will be executed automatically.





Phase 4: Very difficult to repair





only. frm The description file is also damaged, you should reach 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 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 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 using the following method:





If the database page is corrupted, you may want to dump your table from the database using SELECT INTO OutFile, 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 in the [mysqld] section of the options file before restarting the server:


[mysqld]innodb_force_recovery = 4innodb_force_recovery is allowed for non 0 values 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)


even if a corrupted page is detected by the server, the server is running, and trying to have the select * from tbl_name skip corrupted index records and pages, which helps to dump the table.





2 (Srv_force_no_background)


blocks the main thread from running, which will block it if the crash may occur during the purge operation.


3 (Srv_force_no_trx_undo)


does not run transaction rollback after recovery.


4 (Srv_force_no_ibuf_merge)


also blocks the insert buffer merge operation. 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)


does not view the incomplete log when the database is started: 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.