MySQL INNODB 损坏恢复指南

首要步骤是关闭MYSQL实例和备份数据目录

/etc/init.d/mysqld stop

mkdir /root/myql_backup 
tar -jcvf /root/myql_backup/mysql_data.tar.bz2  /var/lib/mysql





尝试启动 mysql 实例

/etc/init.d/mysqld start

如果实例不崩溃那么 用mysqldump做逻辑备份

/etc/init.d/mysql start
mysqldump --single-transaction -AER > /root/dump_wtrans.sql
mysqldump -AER > /root/dump.sql 




如果 MYSQL 实例崩溃则 尝试设置 innodb_force_recovery 参数 


例如

mode=1; sed -i "/^\[mysqld\]/{N;s/$/\ninnodb_force_recovery=$mode/}" /etc/my.cnf


注意这些的mode=1 设置了 innodb_force_recovery 参数的级别

innodb_force_recovery 的参数级别含义为：

Mode 1 当发现一个损坏的数据页时不让MYSQL实例自动崩溃
Mode 2 不启用后台操作
Mode 3 不尝试 回滚事务
Mode 4 不计算状态也不应用buffer change
Mode 5 在启动过程中不去关心undo log
Mode 6 在启动过程中不关心重做日志ib_logfiles， 不去做前滚


以上 mode 2 会包含 mode 1, mode 3会包含 mode 2和 mode 1 ，依此类推


同时从 MYSQL 5.6.15 开始 mode 4-6 会让mysql 以只读模式运行

若已经设置了innodb_force_recovery =6 还是有问题 ，那么需要进一步诊断



检查日志 

tail -200 /var/lib/mysql/`hostname`.err
tail -f /var/log/mysqld.log



如发现

    InnoDB: Database page corruption on disk or a failed
    InnoDB: file read of page 515891.

以上报错说明mysql innodb 引擎发现有数据页损坏 进一步诊断：

  #!/bin/bash
    for i in $(ls /var/lib/mysql/*/*.ibd)
    do
    innochecksum $i
    done


or

DATADIR=/var/lib/mysql; find $DATADIR -type f -name *.ibd -o -name ibdata* | xargs -I{} innochecksum {}





如发现

    mysql: 120901  9:43:55  InnoDB: Error: page 70944 log sequence number 8 1483471899
    mysql: InnoDB: is in the future! Current system log sequence number 5 612394935.
    mysql: InnoDB: Your database may be corrupt or you may have copied the InnoDB
    mysql: InnoDB: tablespace but not the InnoDB log files. See
    mysql: InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.2 Starting InnoDB on a Corrupted Database[/url]


以上日志为LSN 不同步问题， 其一般可以通过 设置 innodb_force_recovery解决的


如发现 

    [ERROR] Table ./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!

    InnoDB: Error: table 'database/table'
    InnoDB: in InnoDB data dictionary has tablespace id 423,
    InnoDB: but tablespace with that id or name does not exist. Have
    InnoDB: you deleted or moved .ibd files?

    [ERROR] Cannot find or open table database/table from
    the internal data dictionary of InnoDB though the .frm file for the
    table exists. Maybe you have deleted and recreated InnoDB data
    files but have forgotten to delete the corresponding .frm files
    of InnoDB tables, or you have moved .frm files to another database?
    or, the table contains indexes that this version of the engine
    doesn't support.


可能丢失了表的.frm 或 .ibd文件  ,找回这些文件



当实例可以启动 那么如何检测表是否存在讹误？


可以使用 check table 命令

mysql> CHECK TABLE roundcube.users;
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| roundcube.users | check | status   | OK       |
+-----------------+-------+----------+----------+
1 row in set (0.13 sec)

mysql> CHECK TABLE roundcube.dictionary;
+----------------------+-------+----------+----------------------------------------------------------------+
| Table                | Op    | Msg_type | Msg_text                                                       |
+----------------------+-------+----------+----------------------------------------------------------------+
| roundcube.dictionary | check | Warning  | InnoDB: Tablespace is missing for table 'roundcube/dictionary' |
| roundcube.dictionary | check | Error    | Table 'roundcube.dictionary' doesn't exist                     |
| roundcube.dictionary | check | status   | Operation failed                                               |
+----------------------+-------+----------+----------------------------------------------------------------+
3 rows in set (0.00 sec)


如果以上检测发现讹误，则可能导致mysql崩溃 ，那么可能需要使用 innodb_force_recovery=1 来启动实例 否则可能启动时实例崩溃


可以使用 mysqlcheck 来检测一个数据库 

[root@ocp ~]# mysqlcheck -A -uroot -p
Enter password: 
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
recovery.testeasy                                  OK
sys.sys_config                                     OK



如何从frm文件中恢复create table建表语句

可以从 https://downloads.mysql.com/archives/utilities/ 下载 MySQL Utilities 

tar xvzf mysql-utilities*


    cd mysql-utilities-1.4.3
    chmod +x setup.py
    ./setup.py build
    ./setup.py install


mysqlfrm 会自己建一个mysql实例 需要单独的端口

mysqlfrm --basedir=/usr --user=mysql --port=3308 /var/lib/mysql/testdb/staff.frm


# Spawning server with --user=mysql.
# Starting the spawned server on port 3308 ... done.
# Reading .frm files
#
# Reading the staff.frm file.
#
# CREATE statement for staff.frm:
#

CREATE TABLE `staff` (
  `staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `address_id` smallint(5) unsigned NOT NULL,
  `picture` blob,
  `email` varchar(50) DEFAULT NULL,
  `store_id` tinyint(3) unsigned NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `username` varchar(16) NOT NULL,
  `password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`staff_id`),
  KEY `idx_fk_store_id` (`store_id`),
  KEY `idx_fk_address_id` (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#...done.



有时需要 SET FOREIGN_KEY_CHECKS=0; 才能建表成功




在实例 没崩溃情况下 如何重建有问题的表


mysql -u root -p

USE dbname;
 CREATE TABLE tablename_recovered LIKE tablename;
 INSERT INTO tablename_recovered SELECT * FROM tablename;


这里 先见表 后插入

插入如果 遇到问题页面 ，那么可以用limit 

insert ignore into tablename_recovered select * from tablename limit 10;
insert ignore into tablename_recovered select * from tablename limit 50;
insert ignore into tablename_recovered select * from tablename limit 100;
insert ignore into tablename_recovered select * from tablename limit 200;
...

测试 下插入到哪个位置 才报错

如果成功复制表 那么把原表删除 新表重命名

DROP dbname.tablename;
RENAME TABLE dbname.tablename_recovered TO dbname.tablename;




还原 所有数据库和 重置 ibdata/ib_log files

首先要有 有效的备份， 做这些操作前 确认这个备份可用！！

  mysqldump -AER > /root/recovery_dump.sql
  

干掉所有 有问题的db 

   mysql> SET FOREIGN_KEY_CHECKS=0;
    mysql> DROP DATABASE db1;
    mysql> DROP DATABASE db2;
    ...


重启mysql 实例

  mysql -e "SET GLOBAL innodb_fast_shutdown = 0"
    /etc/init.d/mysql stop

 sed -i '/innodb_force_recovery/d' /etc/my.cnf

 nohup /etc/init.d/mysql start & tail -f /var/lib/mysql/`hostname`.err


恢复 数据

mysql < /root/recovery_dump.sql




对于 Log Sequence Number Mismatched/In Future


可以考虑重建整个数据库


也可以重建重做日志

mysql -e "SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql stop
cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
/etc/init.d/mysql start




或者 临时交换存储引起 为myisam 然后换回来


    mysql -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'"

 /etc/init.d/mysql stop
    mkdir -p /root/innodb.bak
    mv ib* /root/innodb.bak/
    /etc/init.d/mysql start

mysql -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'MyISAM'"


如何合适的删除.ibd文件  可以使用如下命令

SET FOREIGN_KEY_CHECKS=0;
mysql -e "ALTER TABLE roundcube.staff DISCARD TABLESPACE"



如果误删除了 .ibd文件 如何 基于备份恢复前的清理工作

[ERROR] MySQL is trying to open a table handle but the .ibd file for table dbname/tblname does not exist.

ALTER TABLE dbname.tblname DISCARD TABLESPACE;
   InnoDB: Warning: cannot delete tablespace 251 in DISCARD TABLESPACE.
    InnoDB: But let us remove the insert buffer entries for this tablespace.

  DROP TABLE dbname.tblname;
  
    InnoDB: We removed now the InnoDB internal data dictionary entry
    InnoDB: of table `dbname/tblname`.


如上完成后 再恢复重建整个表


.frm 文件在 但数据字典里没这个表了

先备份.frm 文件 然后执行 create table重建表


丢失.frm文件 但字典里有这个表


InnoDB: Error: table dbname/tblname already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? ...

InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.


可以在别的db下重建这个表获得.frm 文件 然后拷贝过去

# mysql
mysql> CREATE DATABASE test2;
mysql> CREATE TABLE ... CHARSET=utf8;
mysql> quit
# cp /var/lib/mysql/test2/test.frm /var/lib/mysql/test/
# mysql
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP TABLE test.test;