Summary of error repair of mysql database table

Repair of bad tables in mysql database

Radish and cabbage, each has his own love, can work, can repair the watch is the king!

Remember before repairing: back up the database first (after the backup is completed, do the following repair)

You can use mysqldump-A > all.sql for full library backup (you can omit the wrong table to export other data addition options-ignore-table=table_name when you have an error in mysqldump export), or you can enter / usr/local/shell/ to execute mysql_backup.sh to back up the database. Neither of the above two ways can be backed up. You can enter the / data/mysql/3306 directory to copy all the following file information.

Repair method for MyIsam table

1. Description:

When the database can enter the mysql program, it is normal to use the following fix when there is a tables error in the log:

Mysql > REPAIR TABLE table name

3. Description:

Use when the database cannot be accessed and the database program cannot be started normally

Mysqlcheck-Aor (this command is executed outside the database to repair all myisam database tables-A = all all,-o,-optimize to optimize the index [reorganize the index]-r,-- repair repair)

Shell# mysqlcheck-Aor throws an error when it is executed (any innodb table throws an error)

If you know which table error it is, you can use the following command to fix it (later, you can add the-f option force to force repair, use this option carefully)

Shell# myisamchk-oar tables.MYI

Repair method for Innodb table

1. Description:

The database can enter the mysql program normally. Only if there is a tables error in the log, use the following fix:

1. Export the wrong table through mysqldump (only export data), and make sure there is no problem with the export!

2. Enter the mysql truncate table table name

3. You can fill in the database data you just exported through mysql command.

two。 Description:

Please refer to http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

The database cannot be started properly, there is a table error in the log, and the index error message is fixed:

1. Add to the my.cnf file

[mysqld]

Innodb_force_recovery = 1 (the lowest level 1 usually starts the mysql service normally as a skip table error)

The higher the level, the more difficult it is to repair (1-6)

1 (SRV_FORCE_IGNORE_CORRUPT): ignore checked corrupt pages.

2 (SRV_FORCE_NO_BACKGROUND): blocking the running of the main thread, if the main thread needs to perform full purge operations, will result in crash.

3 (SRV_FORCE_NO_TRX_UNDO): no transaction rollback operation is performed.

4 (SRV_FORCE_NO_IBUF_MERGE): merge operations that insert buffers are not performed.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): without viewing the redo log, the InnoDB storage engine treats uncommitted transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO): roll forward is not performed.

2. After the mysql can be started normally, the upper application does not need to start to prevent errors in writing data.

Enter mysql to change the error table information

Mysql > alter table table_name ENGINE=myisam

After the modification is completed, use the repair method of Myisam to fix it.

After the repair is completed, change the table to innodb and restart mysql after the innodb_force_recovery = 1 of the my.cnf file is commented out. Pay attention to whether the log is thrown incorrectly.

3. Restore through database backup and binlog log. There are too many steps to write out.

You can still refer to http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Perform a recovery