MySQL database table bug fix summary

MySQL Database bad table repair

Radish cabbage, each their own, can work, can repair the table is the King!!!

remember before repairing: back up the database first (after the backup is complete, the following repair operation)

You can mysqldump-a > all.sql for a full library backup (mysqldump export error when you can omit the wrong table to export additional data add option--ignore-table=table_name) can also go to/usr/l ocal/shell/perform mysql_backup.sh backup database neither of the two ways can be backed up into the/data/mysql/3306 directory. Copy one of the following file information

Repair method for MyISAM table

1. Description:

The database can enter MySQL program normally, only when the log is thrown with tables error, use the following fix:

mysql> REPAIR Table Name

3. Description:

Use when the database fails to enter and the database program does not start properly

Mysqlcheck-aor (This command is executed out of the database, fixed for all MyISAM database tables-a= all,-O,--optimize for index optimization "REORGANIZE index"-R,--repair fix)

shell# Mysqlcheck-aor throws an error when executing (all InnoDB tables will throw errors)

You can use the following command to fix a condition that knows that the table is wrong (you can add the-F option to force a forced repair, and use this option with caution)

shell# myisamchk-oar tables. MYI

Repair method for InnoDB table

1. Description:

The database can enter MySQL program normally, only when the log is thrown with tables error, use the following fix:

1, through mysqldump the wrong table to export (only export data can), to determine the export no problem!

2. Enter MySQL TRUNCATE table name

3, through the MySQL command to the database data just exported to fill it can be done

2. Description:

Refer to Http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

The database does not start correctly, there is a table error in the log, and the index error message is fixed:

1, add in the My.cnf file

[Mysqld]

Innodb_force_recovery = 1 (1 level lowest usually just as skip table error normal start MySQL service)

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

1 (srv_force_ignore_corrupt): Ignores the corrupt page that is checked.

2 (Srv_force_no_background): Prevents the main thread from running, as the main thread needs to perform full purge operations, which can cause crash.

3 (Srv_force_no_trx_undo): Transaction rollback operation is not performed.

4 (Srv_force_no_ibuf_merge): Do not perform insert buffer merge operation.

5 (Srv_force_no_undo_log_scan): Do not look for the log, InnoDB storage engine will treat uncommitted transactions as committed.

6 (Srv_force_no_log_redo): Do not roll forward operations.

2, can start the MySQL normally, the upper application does not have to start, prevents writes the data to appear the error

Enter MySQL to make changes to the error table information

Mysql> ALTER TABLE table_name ENGINE=MYISAM;

Repair with MyISAM after modification is complete

After the repair is complete, change the table to InnoDB, my.cnf file Innodb_force_recovery = 1 Comment out and restart MySQL Note that the log is wrong

3, through the database backup plus binlog log for recovery, too many steps, not write

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

for recovery

This article is from the "Yu Hogeng" blog, make sure to keep this source http://yhgcenter.blog.51cto.com/2623981/1903984

MySQL database table bug fix summary