MYSQL数据表损坏的原因分析和修复方法

1、mysql数据表损坏的原因分析和修复方法analysis of the cause of the breakdownthe following reasons are a common cause for the destruction of mysql tables:1, the server suddenly loses power and causes the data file to be damagedforced shutdown, didn't turn off mysql service firstthe mysqld process is killed when wr

2、iting a tableusing myisamchk, mysqld is also in the operation tabledisk failurethe server crashedthe bugs of mysql itself31ian (www 31ian. com)symptoms of table damagea typical symptom of a damaged watch is as follows:when selecting data from the table, you get the following error:incorrect key file

3、 for table: 'try to repair it'the query cannot find a row or return incomplete data in the table.error: table ' p' is marked as marked and should be repaired4, open the table failure: can，t open file: ' x * * myi， (errno: 145).5,prevent mysql table corruptionyou can prevent mysql

4、 table corruption by following these steps:regularly use myisamchk to check the myisam table (notice to close mysqld) and recommend the check table to check the table (not to close mysqld)after doing a lot of updates or deletions, you recommend using the optimize table to optimize the table so that

5、the file fragment is reduced and the probability of table corruption reducedbefore closing the server, close the mysqld (normal shutdown service, do not use the kill -9 to kill the process)use ups power to avoid sudden power outages.use the latest stable release of mysql to reduce the bugs in mysql

6、itself.for the innodb engine, you can use innodb_tablespace_monitor to check the integrity of the file space administration in the tablespace filedo raid on disk, reduce disk error and high performancethe database server is best to run only mysqld and other services that are necessary, not to run ot

7、her business services, thus reducing the risk of the crash.in case of accident,it is a good idea to make a backup.repair of the damageto the mysql tablethe myisam table can befixed with the following steps:use reapair table ormyisamchk to fix it.if the above method is repaired invalid, the backup re

8、covery table is adopted.here's what you can do:phase 1: check your watchif you have a lot of time, run myisamchk * my i or myisamchk 一 e *.myi use the -s (silent) option to disable unnecessary information.if the mysqld server is down state, use the -update-state option to tell myisamchk to mark

9、the table as' checked 'you have to only fix the myisamchk report with the wrong table for this table, proceed to phase 2.if you are checking, you get weird errors (such as out of memory errors), or if myisamchk crashes, to phase 3.stage 2: simple and secure fixesnote: if you want to repair f

10、aster, when running myisamchk, you should set the value of the sort_buffer_size andkey_buffer_size variable to about 25% of the available memory.first, try myisamchk-r-q tbl_name (-r-q means "quick recovery inode") this will try to fix the index file without touching the data file this sho

11、uld work and the table can be fixed if the data file contains all the content it should have and the delete connection to the correct location in the data file begin to fix the next table otherwise, execute the following procedure:backup the data files before proceedinguse myisamchk - r tbl_na.me (-

12、r means '"recovery mode") this will remove the incorrect record from the data file and the deleted record and rebuild the index fileif the previous steps fail, use myisamchk, safe - recover tbl name the secure recovery mode uses an old recovery method to handle a few (but slower) cases

13、 of regular recovery patterns.if you are fixed, you get a strange error (such as out of memory error), or if myisamchk crashes, to phase 3.stage 3: difficult repaironly if the first 16k block of the index file is destroyed, or contains incorrect information, or if the index file is missing, you shou

14、ld be at this stage in this case, you need to create a new index file follow these steps:move the data files to a secure location.use table description files to create new (empty) data files and index files:shell > mysql db_namemysql > set the autocommit mode 二 1;mysql > truncate table tbl_

15、name;mysql > quitif your mysql version does not have truncate table, use delete from tbl_name.copy the old data file into the newly created data file(don't just move the old files back into the new file; you want to keep a copy in case something goes wrong)let's go back to phase 2. now my

16、isamchk-r-q should work this should not be an infinite loopyou can also use repair table tbl_name use_frm to execute the entire program automatically.phase 4: very difficult fixonly if the frm description file is broken, you should arrive at this stage this should never happen because the descriptio

17、n of the file is no longer changed after the table is created.recover the description file from a backup and return to phase 3. you can also restore the index file and return to stage 2. for the latter, you should start with myisamchk-rif you don，t have a backup but you know exactly how the table is

18、 created, create a copy of the table in the other database delete the new data file and move the description files and index files from other databases to the corrupted database. this provides a new description and index file, but lets the myd data file stay on its own. go back to phase 2 and try to

19、 rebuild the index filethe innodb table can be fixed with the following method:if the database page is corrupted, you might want to use the select into outfile to dump your tables from the database, and most of the data that is normally obtained by this method is intact even so, the damage may resul

20、t in a select * from tbl_name or innodb background operation crash or assertion, or even a roll-back of innodb however, you can use it to force the innodb storage engine to start and stop the background operations so that you can dump your tables. for example, you can add the following lines to the

21、mysqld section of the options file before you restart the server:mysqld innodb_force_recovery 二 4innodb_force_recovery is allowed for non-zero values as follows. a larger number contains all the prevention measures for smaller numbers if you can use a majority of the option value of 4 to dump your t

22、able, then you are safe, only a few in the damage of the data on the separate page will be lost a value of 6 is more exaggerated, because the database page is left in a state of old, which in turn can trigger the b tree and other state database structure more damage1 (srv_force_ignore_corrupt)even i

23、f the server detects a broken page, it lets the server run; try to get the select * from tbl_name to skip the corrupted index record and page, which helps to dump the table2 (srv_force_no_background)prevent the main thread from running, and if the crash occurs during the cleansing process, this will

24、 stop it.3 (srv_force_no_trx_undo)not run the transaction rollback after recovery.4 (srv_force_no_ibuf_merge)also prevent insert buffer merge operations. if you can cause a crash it is best not to do these operations, not to calculate table statistics5 (srv_force_no_undo_log_scan)when you start the database, you