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

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崩溃，那么可能需要使用 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;

MySQL INNODB 损坏恢复指南

Comments

Leave a Reply Cancel reply