MySQL的Innodb表修复

MySQL的Innodb表修复

前一段时间遇到这样一个问题，突然有一天在做数据库备份的时候出现了#2013 - Lost connection to MySQL server at 'reading initial communication packet', system error: 0，注意我的标红部分。因为不同的错误代码可能原因是不同的。即使是我这个问题，网上找到的原因也有几种。
到网站去搜，刚开始的结果是说linux的hosts.allow里面应该加一行mysqld:ALL:ALLOW。意思就是，linux的远程服务没有把mysql加到里面。但问题就来了，以前我运行都好好的呢。死马且当活马医吧。加上试试，不行。在my.ini里面加上skip-name-resolve？没用！my.ini里面有个bind-address=127.0.0.1？没有啊！
后来还是着急备份数据，只能试试其他表行不行。其他表都没问题，唯独一张表，也就是文章表，备份出现问题。问google（以前都是问baidu，但还是google靠谱啊），说是表出现问题了，这个问题后来得知或是因为数据量过大，或是因为mysql的bug。但是怎么修复呢，checktable？那是对myisam，如果对innodb的数据库，会显示一个当前表的storeage（数据库引擎）不支持当前操作。其实网上搜如果修复innodb的表有很多。但最权威的还是mysql官网上的。里面这样写的，英文不好直接看我翻译。
13.2.6.2. Forcing InnoDB Recovery

If there is database page corruption, you may want to dump your tables from the database with SELECT ... INTO OUTFILE. Usually, most of the data obtained in this way is intact. However, it is possible that the corruption might cause SELECT * FROM tbl_name statements or InnoDB background operations to crash or assert, or even cause InnoDB roll-forward recovery to crash. In such cases, you can use the innodb_force_recovery option to force the InnoDB storage engine to start up while preventing background operations from running, so that you are able to dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server:

[mysqld]
innodb_force_recovery = 4

innodb_force_recovery is 0 by default (normal startup without forced recovery) The permissible nonzero values for innodb_force_recovery follow. A larger number includes all precautions of smaller numbers. If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 6 is more drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

    1 (SRV_FORCE_IGNORE_CORRUPT)

    Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

    2 (SRV_FORCE_NO_BACKGROUND)

    Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

    3 (SRV_FORCE_NO_TRX_UNDO)

    Do not run transaction rollbacks after recovery.

    4 (SRV_FORCE_NO_IBUF_MERGE)

    Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

    5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

    Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

    6 (SRV_FORCE_NO_LOG_REDO)

    Do not do the log roll-forward in connection with recovery.

The database must not otherwise be used with any nonzero value of innodb_force_recovery. As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

You can SELECT from tables to dump them, or DROP or CREATE tables even if forced recovery is used. If you know that a given table is causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a failing mass import or ALTER TABLE. You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.
我的翻译
13.2.6.2. 强制执行innodb恢复
如果你想从数据库中备份你的数据。通常，大多数获得的数据是完整的。但是，不排除你在执行SELECT * FROM tbl_name或者执行InnoDB后台操作的时候碰上了崩溃、断言。这样，就会让InnoDB前滚恢复的时候崩溃。在这种情况下，你就要用到innodb_force_recovery（innodb强制恢复）操作去强制InnoDB 数据存储引擎在防止后台操作运行的情况下启动。这样，你就能够备份你的数据库表了。例如，你可以在[mysqld]选项的下面加入如下行，然后重启服务（这里说一下RedHat系下面mysql的重启，redhat是用rpm包管理的，可以直接输入命令行service mysql restart即可。网上说什么mysqld。反正我的机器上是不行）。
[mysqld]
innodb_force_recovery = 4

innodb_force_recovery默认是0（平常启动不带强制恢复）。大点的数字包含所有小的数的预防措施（也就是说，例如2包含1的所有功能）。如果你备份你的表用4，只有一些崩溃表的数据会丢失，相对来说比较安全。6是最极端的，因为所有的数据库表都处在一种转入B-tree和其他数据结构的废弃状态

有下面非0的选项：
    1 （强制忽略崩溃）
    在备份的时候，让服务遇到崩溃，跳过崩溃的记录或者表。

    2 （强制没有后台）

    Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

    3 (强制不回滚事务)
    在恢复之后，不回滚事务

    4 (SRV_FORCE_NO_IBUF_MERGE)

    Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

    5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

    Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

    6 (SRV_FORCE_NO_LOG_REDO)

    Do not do the log roll-forward in connection with recovery.

数据库在innodb_force_recovery这个参数中一定要使用以上非零数字。作为安全的考量，当数字大于0的时候innodb不让用户使用插入、更新、删除操作。但你可以从表中select数据，然后备份他们，或者做一些DROP表或者CREATE表的操作。
如果你知道那张表出了问题，你可以drop掉。你当然也可以停止一个由于失败的导入或者 ALTER TABLE操作所导致的runaway rollback。You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

翻译的比较烂，明白大概意思就行。当然这种完成之后，只能保证你能够顺利select。反正我这边备份还是有问题，还好我之前有备份的sql文件。把整个表drop掉，然后再导入数据，记住，前面说了，导入之前你必须把innodb_force_recovery改回来，因为innodb_force_recovery=1不支持增删改操作。期间还有问题，总觉得不是整张表有问题，而是个别记录出了问题。只能一条一条导入再试试。非常的麻烦，不知道各位谁有好的办法。