首要步骤是关闭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;
