一、故障描述
服务器频繁断电后，导致数据库无法启动，报错信息如下：
InnoDB: End of page dump2023-12-18T10:40:20.402321+08:00 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 1523906479, calculated checksums for field1: crc32 1766646306/2549004227, innodb 3409556651, none 3735928559, stored checksum in field2 1523906479, calculated checksums for field2: crc32 1766646306/2549004227, innodb 3663805432, none 3735928559, page LSN 14 3962870659, low 4 bytes of LSN at page end 3962870659, page number (if stored to page already) 147076, space id (if created with >= MySQL-4.1.1 and stored already) 1656InnoDB: Page may be an index page where index id is 47502023-12-18T10:40:20.402474+08:00 0 [Note] InnoDB: Index 4750 is `ACT_IDX_HI_PROCVAR_NAME_TYPE` in table `vehicle_s1`.`act_hi_varinst`2023-12-18T10:40:20.402534+08:00 0 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
初步判断：这是InnoDB引擎特有的错误信息，说明数据库遇到问题。通常与InnoDB的内部数据结构损坏有关。
二、处理思路
尝试操作：
1️⃣重启MySQL服务。重启MySQL服务让数据库重新加载，测试结果：不成功。
2️⃣ 使用`CHECK TABLE`命令。可以检查表是否有损坏，并尝试修复损坏的数据。
3️⃣ 重建损坏的表。 如果`CHECK TABLE`命令无法修复损坏的数据，可以尝试重建损坏的表。将创建一个新的表，并从备份或其他来源重新填充数据。
4️⃣ 强制InnoDB进行恢复。在尝试以上所有方法后仍然无法解决问题时使用。可以通过修改`innodb_force_recovery`变量来强制InnoDB进行恢复。
三、检查磁盘
[root@cheliangweb ~]# badblocks -s -v -o /data/sda.log /dev/sda正在检查从 0 到 1073741823的块Checking for bad blocks (read-only test): donePass completed, 0 bad blocks found. (0/0/0 errors)您在 /var/spool/mail/root 中有新邮件[root@cheliangweb mysql]# badblocks -s -v -o /data/sdb.log /dev/sdb正在检查从 0 到 2147483647的块Checking for bad blocks (read-only test): donePass completed, 0 bad blocks found. (0/0/0 errors)
结论：磁盘状态正常，该问题和磁盘无关。
四、修复措施
1、检查表损坏情况并尝试修复：
mysql> CHECK TABLE `vehicle_s1`.`act_hi_varinst`;+---------------------------+-------+----------+---------------------------------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+---------------------------+-------+----------+---------------------------------------------------------------------------------------------+| vehicle_s1.act_hi_varinst | check | Warning | InnoDB: Index 'ACT_IDX_HI_PROCVAR_PROC_INST' contains 10126618 entries, should be 10126664. || vehicle_s1.act_hi_varinst | check | Warning | InnoDB: The B-tree of index ACT_IDX_HI_PROCVAR_NAME_TYPE is corrupted. || vehicle_s1.act_hi_varinst | check | Warning | InnoDB: Index 'ACT_IDX_HI_PROCVAR_TASK_ID' contains 10126623 entries, should be 10126664. || vehicle_s1.act_hi_varinst | check | error | Corrupt |+---------------------------+-------+----------+---------------------------------------------------------------------------------------------+4 rows in set (30.91 sec)
故障分析：
1）索引`ACT_IDX_HI_PROCVAR_PROC_INST`的条目数不正确。应该有10126664个条目，但现在只有10126618个。
2）索引`ACT_IDX_HI_PROCVAR_NAME_TYPE`的B-tree已损坏；
3）索引`ACT_IDX_HI_PROCVAR_TASK_ID`的条目数也不正确。应该有10126664个条目，但现在只有10126623个；
尝试解决：
1）使用修复命令：REPAIR TABLE vehicle_s1.act_hi_varinst;
mysql> REPAIR TABLE vehicle_s1.act_hi_varinst;+---------------------------+--------+----------+---------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+---------------------------+--------+----------+---------------------------------------------------------+| vehicle_s1.act_hi_varinst | repair | note | The storage engine for the table doesn't support repair |+---------------------------+--------+----------+---------------------------------------------------------+1 row in set (0.01 sec)
结论：无法解决。存储引擎不支持修复操作。可能是因为表已经损坏无法修复。
2）重建损坏的表。创建一个新表，并从备份或其他来源重新填充数据。
查询数据文件位置：SHOW VARIABLES LIKE 'datadir';
mysql> SHOW VARIABLES LIKE 'datadir';+---------------+---------------------------+| Variable_name | Value |+---------------+---------------------------+| datadir | /home/Yinling/mysql/data/ |+---------------+---------------------------+1 row in set, 1 warning (0.01 sec)
删除之前表名对应物理文件：
mv act_hi_varinst.ibd act_hi_varinst.ibd.bakmv act_hi_varinst.frm act_hi_varinst.frm.bak
执行重启、查看日志：
systemctl restart YLMySQL
tail -f /home/Yinling/mysql/logs/mysqld.log
日志出现报错：
2023-12-18T17:03:37.443912+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.112023-12-18T17:03:37.443916+08:00 0 [Note] InnoDB: Using Linux native AIO2023-12-18T17:03:37.444581+08:00 0 [Note] InnoDB: Number of pools: 12023-12-18T17:03:37.444698+08:00 0 [Note] InnoDB: Using CPU crc32 instructions2023-12-18T17:03:37.446554+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M2023-12-18T17:03:37.532731+08:00 0 [Note] InnoDB: Completed initialization of buffer pool2023-12-18T17:03:37.544576+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().2023-12-18T17:03:37.556349+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda.2023-12-18T17:03:37.563370+08:00 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 640905696642023-12-18T17:03:37.604010+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 640925905922023-12-18T17:03:37.605061+08:00 0 [Note] InnoDB: Database was not shutdown normally!2023-12-18T17:03:37.605074+08:00 0 [Note] InnoDB: Starting crash recovery.2023-12-18T17:03:37.605193+08:00 0 [ERROR] InnoDB: Tablespace 1656 was not found at ./vehicle_s1/act_hi_varinst.ibd.2023-12-18T17:03:37.605201+08:00 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.2023-12-18T17:03:37.616729+08:00 0 [ERROR] InnoDB: Cannot continue operation.
故障分析：表明 InnoDB 存储引擎在启动时遇到了一个问题。指出一个表空间（tablespace）文件./vehicle_s1/act_hi_varinst.ibd 不存在。可能是由于文件丢失、损坏或被不当删除等原因导致。
尝试解决：将配置文件innodb_force_recovery = 1，重新启动，执行全库导出，无响应，继续报错，如下：
[root@cheliangweb vehicle_s1]# tail -f /home/Yinling/mysql/logs/mysqld.log2023-12-18T17:07:24.046650+08:00 0 [ERROR] InnoDB: Could not find a valid tablespace file for `vehicle_s1/act_hi_varinst`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.2023-12-18T17:07:24.068669+08:00 0 [Note] Failed to start slave threads for channel ''2023-12-18T17:07:24.077809+08:00 0 [Note] Event Scheduler: Loaded 0 events2023-12-18T17:07:24.078060+08:00 0 [Note] /home/Yinling/mysql/bin/mysqld: ready for connections.Version: '5.7.34-log' socket: '/home/Yinling/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)2023-12-18T17:07:24.287380+08:00 0 [Note] InnoDB: Rollback of trx with id 1303474 completed2023-12-18T17:07:24.287430+08:00 0 [Note] InnoDB: Rolling back trx with id 1303472, 4 rows to undo2023-12-18T17:07:24.418596+08:00 0 [Note] InnoDB: Rollback of trx with id 1303472 completed2023-12-18T17:07:24.418642+08:00 0 [Note] InnoDB: Rollback of non-prepared transactions completed2023-12-18T17:07:25.254905+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 231218 17:07:25
3）用正常表空间文件替换原表的ibd和frm文件
cp act_hi_varinst.ibd ../vehicle_s1cp act_hi_varinst.frm ../vehicle_s1
重新启动：
systemctl start YLMySQLsystemctl status YLMySQL
再次执行导出： mysqldump -u root -pxxx vehicle_s1 >s1.sql
查看日志，出现权限报错，日志详情如下：
2023-12-18T17:16:23.684879+08:00 0 [Note] Event Scheduler: Loaded 0 events2023-12-18T17:16:23.685018+08:00 0 [Note] /home/Yinling/mysql/bin/mysqld: ready for connections.Version: '5.7.34-log' socket: '/home/Yinling/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)2023-12-18T17:22:03.275223+08:00 4 [Warning] IP address '10.18.10.207' could not be resolved: Name or service not known2023-12-18T17:22:04.414457+08:00 4 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:04.423432+08:00 4 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:04.426539+08:00 4 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:04.467021+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.119643+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.139338+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.153370+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.154599+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.156006+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.157455+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.159139+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.161100+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.162593+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.166157+08:00 5 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)2023-12-18T17:22:24.174236+08:00 4 [ERROR] /home/Yinling/mysql/bin/mysqld: Can't find file: './vehicle_s1/act_hi_varinst.frm' (errno: 13 - Permission denied)
分析：表明MySQL无法找到或访问特定文件 act_hi_varinst.frm，并且是由于权限被拒绝（Permission denied）。
再次执行导出，可以发现命令行无法进行导出，原因是应该使用mysqldump
mysqldump -u root -pxxx -S /home/Yinling/mysql/mysql.sock vehicle_s1 >s1.sql 继续执行导出，发现出现报错：
[root@cheliangweb ~]# mysqldump -u root -pYinling_2021 -S /home/Yinling/mysql/mysql.sock vehicle_s1 >s1.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `act_hi_varinst`': Lost connection to MySQL server during query (2013)
五、整体处理思路
1、先解决MySQL数据库无法正常启动的问题；
如果存在报单表的表空间损坏，应该备份损坏的表空间，采用另一个正常的表替换源表，然后重新启动MySQL；
2、MySQL正常启动时，尝试全库导出；
全库导出后，还原到新安装的数据库，进行全库还原，再恢复丢失的对应表数据即可。
