mysql 非正常关闭_MySQL运维之记录一次因数据库非正常关闭导致MySQL数据库不可用修复(一)...

问题描述

因MySQL数据库所在数据目录磁盘出现问题导致数据库表出现如下问题,导致表找不到对应对应表空间

数据库版本 mysql 5.7

show tables;   -- 能查询到表存在

desc table_name;   -- 提示表不存在

在数据库目录下   ibd 与 frm文件都存在

错误日志

[ERROR] InnoDB: Cannot read first page of '.db_test_2/user_test.ibd' I/O error

[ERROR] Innodb: Cannot read first page in datafile: ./db_test_2/user_test.ibd, Space ID:18445744073709551615, Flags: 33. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

[ERROR] InnoDB: Operating system error number 2 in a file operation.

[ERROR] InnoDB: The error means the system cannot find the path specified.

[ERROR] InnoDB: If you are instaling InnoDB, remember that you must create directories yourself, InnoDB desc not create them.

[ERROR] InnoDB: Cloud not find a valid tablespace file for `db_test_2/user_test`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

[warning] InnoDB: Ignoring tablespace `db_test_2/user_test` bacause it could not be opened

解决步骤

新库或者新实例上操作

1、在新实例上建表

mysql> create database db_name1;

Query OK, 1 row affected (0.00 sec)

mysql> use db_name1;

Database changed

mysql> CREATE TABLE `user_test` (

->   `id` int(11) NOT NULL,

->   `name` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

->   `age` int(8) NULL DEFAULT NULL,

->   `comment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

->   PRIMARY KEY (`id`) USING BTREE

-> ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

Query OK, 0 rows affected (0.03 sec)

2、discard新建表的表空间

mysql> alter table db_name1.user_test discard tablespace;

Query OK, 0 rows affected (0.01 sec)

旧实例上操作

3、拷贝旧实例该表的ibd文件到新实例对应的位置

cp /data/mysql/data/db_test2/user_test.ibd /data/mysql/data/db_name1/

#scp /data/mysql/data/db_test2/user_test.ibd user@ip:/data/mysql/data/db_name1/

新库或者新实例上操作

4、确保新实例下拷贝的ibd文件 权限与属主正确

chown mysql:mysql /data/mysql/data/db_name1/user_test.ibd

5、导入拷贝过来的ibd文件

mysql> alter table db_name1.user_test import tablespace;

Query OK, 0 rows affected, 1 warning (0.01 sec)

6、检查

mysql> select * from db_name1.user_test limit 1;

+----+--------+------+---------+

| id | name   | age | comment |

+----+--------+------+---------+

|  1 | 小一   |   22 | 学生   |

+----+--------+------+---------+

1 row in set (0.00 sec)

mysql> select count(1) from db_name1.user_test;

+----------+

| count(1) |

+----------+

|        5 |

+----------+

1 row in set (0.00 sec)

7、导出user_test 库并拷贝备份文件至旧实例 --备份

mysqldump -uroot -p12345678 db_name1 user_test >/data/backup/user_test.sql

head -n 100 /data/backup/user_test.sql # 检查备份文件

scp /data/backup/user_test.sql user@ip:/data/backup

旧实例上操作--恢复

8、删除对应表的ibd文件

rm -rf /data/mysql/data/db_test_2/user_test.ibd

9、导入库

mysql -uroot -p1234567 db_test_2 < /data/backup/user_test.sql

10、检查

mysql> show tables;

+---------------------+

| Tables_in_db_test_2 |

+---------------------+

| user |

| user_test |

+---------------------+

2 rows in set (0.00 sec)

mysql> select * from user_test limit 1;

+----+--------+------+---------+

| id | name | age | comment |

+----+--------+------+---------+

| 1 | 小一 | 22 | 学生 |

+----+--------+------+---------+

1 row in set (0.00 sec)

mysql> select count(1) from user_test;

+----------+

| count(1) |

+----------+

| 5 |

+----------+

1 row in set (0.00 sec)

如对小编的文章感兴趣，关注小编公众号咱们一起学习开发运维相关知识：