MySQL数据库表空间损坏如何修复

呈现的现象：

连不上 MySQL 数据库，一读取某一个数据库表的时候，MySQL 服务就宕掉了，重启服务重启不了。

导致原因：

MYSQL 服务没有正常停止，包括服务器断电、拉闸等，导致数据库表空间损坏，无法正常启动。

解决办法：

首先，将 MYSQL 数据库进行备份，切记：一定要备份数据库，以防数据再次损坏，常见的备份方法有俩种：

• 一种方法是将 MYSQL 目录下的 data 文件整体拷贝出来；（这种方法简单，还原较为方便，但可能会导致备份不完整或者备份文件无法正常恢复的情况）

描述：

• data 文件：用来存储数据库实际数据的文件，data 目录下的各个文件夹就是各个对应的库文件；
• ibdata1 文件：是 InnoDB 存储引擎的共享表空间文件，存储系统表空间和未显式指定表空间的表数据和索引；
• .frm 文件：是表定义文件，包含了表的结构信息，如列名、数据类型等；
• .ibd 文件：是 InnoDB 存储引擎特有的数据文件，存储了 InnoDB 表的数据和索引。

• 另一种方法是使用 mysqldump 命令工具来进行备份，这种方法的前提是 MYSQL 服务必须是启动的，因此可在配置文件 my.ini 中尝试使用innodb_force_recovery参数启动 MySQL 服务，具体命令如下：

innodb_force_recovery = 1    #1-6依次测试，看哪个数值可以使 MYSQL 服务正常启动（建议从小数值开始）

描述：

• innodb_force_recovery 是 MySQL 中 InnoDB 存储引擎的一个参数。该参数可以在数据库发生严重故障时启动数据库并允许备份数据；
• 这个参数的值可以从1到 6，每个取值对应不同的恢复级别。值越高，尝试恢复的程度就越大，但是也可能会导致数据丢失或无法恢复。

注意：【在正常情况下，除非你知道自己在做什么，并且明白可能会造成数据损坏的风险，否则不建议随意修改 innodb_force_recovery 参数！！！】

在启动成功后，使用如下命令完成数据库的备份：

mysqldump --force -u[username] -p[password] -P [port] --all-databases >backup.sql

描述：

• –force 选项可以让 mysqldump 命令忽略不存在的表继续备份；
• [username] 是 MySQL 用户名；
• [password] 是 MySQL 密码；
• [port] 是 MySQL 端口号；
• backup.sql 是将备份结果输出到 backup.sql 文件中。

注意：【如果在备份过程中 MYSQL 服务挂了，则重新更改 innodb_force_recovery 参数，重新备份，直到将所有的数据都备份下来！！！】

备份完成后，使用 mysqlcheck 工具检查并定位是哪个库或者哪个表有问题导致服务宕掉，具体命令如下

mysqlcheck -u[username] -p[password] -P [port] --auto-repair --all-databases

描述：

• [username] 是 MySQL 用户名；
• [password] 是 MySQL 密码；
• [port] 是 MySQL 端口号；
• –auto-repair: 表示在检查表时，发现任何问题时都尝试自动修复;
• –all-databases: 表示对所有数据库执行检查和修复操作。

如果过程被中断，根据提示在 Navicat 可视化工具中定位是哪个表或者哪个库，将其对应的 data 文件拷贝出来，重启 MYSQL 服务，重新 mysqlcheck 检查，直到找出所有损坏的表，之后删除innodb_force_recovery参数，保证 MYSQL 启动正常。

注意：【这里建议将损坏的表单独备份一下（包括数据和结构），这样恢复的时候不用还原所有的数据，只需还原损坏表的数据即可。】

然后，从新的 MYSQL 数据库中拷贝上述查询到的损坏的表对应的 data 文件，删除其对应的表空间，这里不建议直接删除损坏的 .ibd 文件，这样有可能只是删除了物理文件，但在数据库中还存在其元数据信息，导致创建表时仍会提示表空间存在，具体的操作如下：

mysql -u[username] -p[password] -P [port]														#连接数据库服务器
 - USE database_name;																			#选择要操作的数据库
 - SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name';		#获取该数据库中所有表的名称
 - ALTER TABLE table1_name DISCARD TABLESPACE;													#针对每个表进行表空间的删除

描述：

• [username] 是 MySQL 用户名；
• [password] 是 MySQL 密码；
• [port] 是 MySQL 端口号；
• database_name 是包含要删除表空间的表的数据库名称；
• table1_name 是想要删除表空间的表的名称。
• 要想一键删除一个库中所有表的表空间，可以参考.bat 脚本来实现

最后将备份出来的损坏表的数据再次导入即可。至此，MYSQL 数据库表空间修复完成！！！

拓展：

1、如果表结构损坏了，尝试使用mysqlfrm工具来重建损坏的表结构，首先先安装了 MySQL Utilities，然后执行如下命令：

mysqlfrm --diagnostic path/xxx.frm >user-frm.sql

描述：

• MySQL Utilities 是一组用于管理和维护 MySQL 数据库的命令行工具集合，包括数据库备份、复制设置、数据迁移、查询执行等；
• mysqlfrm 工具是 MySQL 提供的一个用于解析 .frm 文件并生成 CREATE TABLE 语句的工具；
• –diagnostic 是 mysqlfrm 工具的一个参数，可以获取关于表结构的更多信息；
• path/xxx.frm 是要修复的损坏表的 .frm 文件路径；
• user-frm.sql 是将所得到的结果输出到 user-frm.sql 文件中。

2、在 MySQL 中查询所有数据库中是否存在某个表，你可以使用以下 SQL 查询语句：

select table_schema, table_name from information_schema.tables where table_name = 'xxx';

描述：

• table_schema 是 MySQL 数据库中，表所属的数据库名称；
• table_name 是在 MySQL 数据库中的表的名称；
• information_schema.tables 是系统视图或信息模式中的一部分，是一个数据库，包含了数据库中所有表的详细信息，如表名、表类型、存储引擎等。

第二种情况：

任务中启动 mysql 服务不报错，重新刷新后，服务挂掉

导致原因：

data 目录下的 ibdata1 表文件损坏

解决办法：

将好的 ibdata1 共享表空间表文件进行替换，重新启动mysql服务。
注意一点：这是基于所有的表或者库都有单独的表空间文件，不占用这个共享表空间文件，也就说这个表空间文件不存任何实际使用数据才可以替换。