MySQL断电重启后导致数据库无法启动

Myth丶恋晨 2022-11-29 01:00 915阅读 0赞

MySQL断电重启后导致数据库无法启动

报错信息如下：

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.  
InnoDB: If you get repeated assertion failures or crashes, even  
InnoDB: immediately after the mysqld startup, there may be  
InnoDB: corruption in the InnoDB tablespace. Please refer to  
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html  
InnoDB: about forcing recovery.  
02:37:04 UTC - mysqld got signal 6 ;  
This could be because you hit a bug. It is also possible that this binary  
or one of the libraries it was linked against is corrupt, improperly built,  
or misconfigured. This error can also be caused by malfunctioning hardware.  
We will try our best to scrape up some info that will hopefully help  
diagnose the problem, but since we have already crashed,  
something is definitely wrong and this may fail.

key\_buffer\_size=8388608  
read\_buffer\_size=131072  
max\_used\_connections=0  
max\_threads=1500  
thread\_count=0  
connection\_count=0  
It is possible that mysqld could use up to  
key\_buffer\_size + (read\_buffer\_size + sort\_buffer\_size)\*max\_threads = 603387 K  bytes of memory  
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0  
Attempting backtrace. You can use the following information to find out  
where mysqld died. If you see no messages after this, something went  
terribly wrong...  
stack\_bottom = 0 thread\_stack 0x40000  
/usr/sbin/mysqld(my\_print\_stacktrace+0x3b)\[0x8e4a6b\]  
/usr/sbin/mysqld(handle\_fatal\_signal+0x491)\[0x673ab1\]  
/lib64/libpthread.so.0(+0xf5e0)\[0x7f4daa69c5e0\]  
/lib64/libc.so.6(gsignal+0x37)\[0x7f4da92961f7\]  
/lib64/libc.so.6(abort+0x148)\[0x7f4da92978e8\]  
"/var/lib/mysql/localhost.localdomain.err" 273L, 313101C                                                                                                 53,1          14%  
InnoDB: If you get repeated assertion failures or crashes, even  
InnoDB: immediately after the mysqld startup, there may be  
InnoDB: corruption in the InnoDB tablespace. Please refer to  
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html  
InnoDB: about forcing recovery.  
02:39:22 UTC - mysqld got signal 6 ;  
This could be because you hit a bug. It is also possible that this binary  
or one of the libraries it was linked against is corrupt, improperly built,  
or misconfigured. This error can also be caused by malfunctioning hardware.  
We will try our best to scrape up some info that will hopefully help  
diagnose the problem, but since we have already crashed,  
something is definitely wrong and this may fail.

vim /etc/my.cnf  
添加参数 innodb\_force\_recovery = 1 从1~4尝试知道能重启mysql成功为止  
\[mysqld\]  
innodb\_force\_recovery = 1

innodb\_force\_recovery 参数：

0  默认为0（没有强制恢复的正常启动）

1 (SRV\_FORCE\_IGNORE\_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服务运行。一般设置为该值即可，然后 dump 出库表进行重建。

2 (SRV\_FORCE\_NO\_BACKGROUND): 阻止主线程的运行，如主线程需要执行 full purge 操作，会导致 crash。 阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用该值。

3 (SRV\_FORCE\_NO\_TRX\_UNDO): 不执行事务回滚操作。如果你能以innodb\_force\_recovery为3或更低值转储你的表，那么你是比较安全的，只有在损坏的个人页的一些数据会丢失。

4 (SRV\_FORCE\_NO\_IBUF\_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。不要进行统计操作。该值可能永久损坏数据文件。若使用了该值，则将来要删除和重建辅助索引。4或更大的值被认为是危险的，因为数据文件可以被永久地损坏。

5 (SRV\_FORCE\_NO\_UNDO\_LOG\_SCAN): 不查看重做日志，InnoDB 存储引擎会将未提交的事务视为已提交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。

6 (SRV\_FORCE\_NO\_LOG\_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。值6被认为是严重的，数据库页被留在一个陈旧的状态，这反过来又可能带给B-trees和其它数据库结构更多的损坏。

InnoDB 在innodb\_force\_recovery大于0时阻止INSERT，UPDATE或DELETE操作。对于MySQL 5.6.15，将innodb\_force\_recovery设为4或更高会让InnoDB处于只读模式。

我从1~4都尝试过了，还是无法启动mysql。

最后我按照日志中的提示，设置了 innodb\_force\_recovery = 6 后启动mysql成功。查看日志，虽然启动成功但还是有相关的Error：

2020-08-18 12:21:09 0 \[Note\] /usr/sbin/mysqld (mysqld 5.6.32-log) starting as process 16513 ...  
2020-08-18 12:21:09 16513 \[Note\] Plugin 'FEDERATED' is disabled.  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Using atomics to ref count buffer pool pages  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: The InnoDB memory heap is disabled  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Mutexes and rw\_locks use GCC atomic builtins  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Memory barrier is not used  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Compressed tables use zlib 1.2.3  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Using Linux native AIO  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Using CPU crc32 instructions  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Initializing buffer pool, size = 128.0M  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Completed initialization of buffer pool  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: Highest supported file format is Barracuda.  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: The user has set SRV\_FORCE\_NO\_LOG\_REDO on, skipping log redo  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: 128 rollback segment(s) are active.  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: 5.6.32 started; log sequence number 0  
2020-08-18 12:21:09 16513 \[Note\] InnoDB: !!! innodb\_force\_recovery is set to 6 !!!  
2020-08-18 12:21:09 16513 \[Note\] Recovering after a crash using mysql-bin  
2020-08-18 12:21:11 16513 \[ERROR\] Error in Log\_event::read\_log\_event(): 'Event crc check failed! Most likely there is event corruption.', data\_len: 8207, event\_type: 30  
2020-08-18 12:21:11 16513 \[Note\] Starting crash recovery...  
2020-08-18 12:21:11 16513 \[Note\] Crash recovery finished.  
2020-08-18 12:21:11 16513 \[Note\] Crashed binlog file ./mysql-bin.002433 size is 289306093, but recovered up to 288218335. Binlog trimmed to 288218335 bytes.  
2020-08-18 12:21:51 16513 \[Note\] Server hostname (bind-address): '\*'; port: 3306  
2020-08-18 12:21:51 16513 \[Note\] IPv6 is available.  
2020-08-18 12:21:51 16513 \[Note\]   - '::' resolves to '::';  
2020-08-18 12:21:51 16513 \[Note\] Server socket created on IP: '::'.  
2020-08-18 12:21:51 16513 \[ERROR\] InnoDB: Failed to find tablespace for table '"mysql"."slave\_master\_info"' in the cache. Attempting to load the tablespace with space id 4.  
2020-08-18 12:21:51 16513 \[Warning\] InnoDB: Allocated tablespace 4, old maximum was 0  
2020-08-18 12:21:51 16513 \[ERROR\] InnoDB: Failed to find tablespace for table '"mysql"."slave\_worker\_info"' in the cache. Attempting to load the tablespace with space id 5.  
2020-08-18 12:21:51 16513 \[ERROR\] InnoDB: Failed to find tablespace for table '"mysql"."slave\_relay\_log\_info"' in the cache. Attempting to load the tablespace with space id 3.  
2020-08-18 12:21:51 16513 \[Note\] Event Scheduler: Loaded 0 events  
2020-08-18 12:21:51 16513 \[Note\] /usr/sbin/mysqld: ready for connections.  
Version: '5.6.32-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

虽然使用 innodb\_force\_recovery 参数强制恢复启动 mysql， 但启动日志中还是几个ERROR错误日志：  
\[ERROR\] Error in Log\_event::read\_log\_event(): 'Event crc check failed! Most likely there is event corruption.', data\_len: 8207, event\_type: 30

\[ERROR\] InnoDB: Failed to find tablespace for table '"mysql"."slave\_master\_info"' in the cache.   
\[ERROR\] InnoDB: Failed to find tablespace for table '"mysql"."slave\_worker\_info"' in the cache.  
\[ERROR\] InnoDB: Failed to find tablespace for table '"mysql"."slave\_relay\_log\_info"' in the cache.

进入MySQL Shell界面查看基本信息

\# mysql -uroot -p123456

mysql> show slave status;  
Empty set (0.00 sec)

mysql>   
mysql> show binary logs;  
\+------------------+-----------+  
| Log\_name         | File\_size |  
\+------------------+-----------+  
| mysql-bin.001412 | 524295715 |  
| mysql-bin.001413 | 524347356 |  
| mysql-bin.001414 | 524339596 |  
| mysql-bin.001415 | 524291826 |  
......  
......  
| mysql-bin.002431 | 524322299 |  
| mysql-bin.002432 | 524302936 |  
| mysql-bin.002433 | 288218335 |  
| mysql-bin.002434 |       120 |  
\+------------------+-----------+  
1023 rows in set (0.01 sec)

查看表空间

mysql> SELECT CONCAT(ROUND(SUM(index\_length)/(1024\*1024), 6), ' MB') AS 'Total Index Size'  FROM information\_schema.TABLES WHERE table\_schema LIKE 'mysql';  
\+------------------+  
| Total Index Size |  
\+------------------+  
| 0.103516 MB      |  
\+------------------+  
1 row in set (0.01 sec)

查看 mysql db 中的所有表的信息

mysql> SELECT CONCAT(table\_schema,'.',table\_name) AS 'Table Name',  table\_rows AS 'Number of Rows',  CONCAT(ROUND(data\_length/(1024\*1024\*1024),6),' G') AS 'Data Size',  CONCAT(ROUND(index\_length/(1024\*1024\*1024),6),' G') AS 'Index Size' ,  CONCAT(ROUND((data\_length+index\_length)/(1024\*1024\*1024),6),' G') AS'Total' FROM information\_schema.TABLES  WHERE table\_schema LIKE 'mysql';  
\+---------------------------------+----------------+------------+------------+------------+  
| Table Name                      | Number of Rows | Data Size  | Index Size | Total      |  
\+---------------------------------+----------------+------------+------------+------------+  
| mysql.columns\_priv              |              0 | 0.000000 G | 0.000004 G | 0.000004 G |  
| mysql.db                        |              2 | 0.000001 G | 0.000005 G | 0.000006 G |  
| mysql.event                     |              0 | 0.000000 G | 0.000002 G | 0.000002 G |  
| mysql.func                      |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.general\_log               |              2 | 0.000000 G | 0.000000 G | 0.000000 G |  
| mysql.help\_category             |             40 | 0.000001 G | 0.000003 G | 0.000004 G |  
| mysql.help\_keyword              |            611 | 0.000112 G | 0.000020 G | 0.000132 G |  
| mysql.help\_relation             |           1218 | 0.000010 G | 0.000020 G | 0.000030 G |  
| mysql.help\_topic                |            583 | 0.000528 G | 0.000021 G | 0.000549 G |  
| mysql.innodb\_index\_stats        |              0 | 0.000015 G | 0.000000 G | 0.000015 G |  
| mysql.innodb\_table\_stats        |              0 | 0.000015 G | 0.000000 G | 0.000015 G |  
| mysql.ndb\_binlog\_index          |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.plugin                    |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.proc                      |              0 | 0.000000 G | 0.000004 G | 0.000004 G |  
| mysql.procs\_priv                |              0 | 0.000000 G | 0.000004 G | 0.000004 G |  
| mysql.proxies\_priv              |              2 | 0.000001 G | 0.000005 G | 0.000006 G |  
| mysql.servers                   |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.slave\_master\_info         |              0 | 0.000015 G | 0.000000 G | 0.000015 G |  
| mysql.slave\_relay\_log\_info      |              0 | 0.000015 G | 0.000000 G | 0.000015 G |  
| mysql.slave\_worker\_info         |              0 | 0.000015 G | 0.000000 G | 0.000015 G |  
| mysql.slow\_log                  |              2 | 0.000000 G | 0.000000 G | 0.000000 G |  
| mysql.tables\_priv               |              0 | 0.000000 G | 0.000004 G | 0.000004 G |  
| mysql.time\_zone                 |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.time\_zone\_leap\_second     |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.time\_zone\_name            |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.time\_zone\_transition      |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.time\_zone\_transition\_type |              0 | 0.000000 G | 0.000001 G | 0.000001 G |  
| mysql.user                      |             10 | 0.000001 G | 0.000002 G | 0.000003 G |  
\+---------------------------------+----------------+------------+------------+------------+  
28 rows in set (0.01 sec)

mysql启动时会进行 crc32 checksum 校验。

关于ibdata1文件  
mysql大多数磁盘空间被InnoDB的共享表空间 ibdata1 使用。如果 innodb\_file\_per\_table = ON，则表被存储在他们自己的表空间里（也就是每个表的数据单独保存，文件名为：表名.ibd，性能更高），但是共享表空间仍然在存储其它的 InnoDB 内部数据：  
数据字典（InnoDB 表的元数据）  
变更缓冲区  
双写缓冲区  
UNDO日志  
其中的一些在 Percona 服务器上可以被配置来避免增长过大的。例如你可以通过 innodb\_ibuf\_max\_size 设置最大变更缓冲区，或设置 innodb\_doublewrite\_file 来将双写缓冲区存储到一个分离的文件。  
MySQL 5.6也支持创建外部的UNDO表空间，存储到自己的文件中，而不存储到ibdata1文件中。

先尝试通过 innochecksum 来修复  
\[root@localhost mysql\]\# innochecksum -v /var/lib/mysql/ibdata1   
InnoDB offline file checksum utility.

Variables (--variable-name=value)  
and boolean options \{FALSE|TRUE\}  Value (after reading options)  
\--------------------------------- ----------------------------------------  
verbose                           TRUE  
debug                             FALSE  
count                             FALSE  
start-page                        0  
end-page                          0  
page                              0  
file /var/lib/mysql/ibdata1 = 817889280 bytes (49920 pages)...  
InnoChecksum; checking pages in range 0 to 49919  
Fail; page 65 invalid (fails innodb and crc32 checksum)  
\[root@localhost mysql\]\#

修复时报错。

再尝试使用 mysqlcheck 修复：  
\# mysqlcheck -uroot -p123456 --repair -A   
修复报错：  
The storage engine for the table doesn't support repair  
InnoDB支持外键参照完整性，具备故障恢复能力，所以不需要修复。

重启 mysql 检查日志发现 Event crc check failed! 的错误消失了，只剩下 Failed to find tablespace for table 的错误。

由于我配置 innodb\_file\_per\_table = ON ，所以再单独对报错的几张表进行修复：

innochecksum -v /var/lib/mysql/mysql/slave\_master\_info.ibd  
innochecksum -v /var/lib/mysql/mysql/slave\_worker\_info.ibd  
innochecksum -v /var/lib/mysql/mysql/slave\_relay\_log\_info.ibd

执行结果如下：  
\[root@localhost mysql\]\# innochecksum -v /var/lib/mysql/mysql/slave\_worker\_info.ibd  
InnoDB offline file checksum utility.

Variables (--variable-name=value)  
and boolean options \{FALSE|TRUE\}  Value (after reading options)  
\--------------------------------- ----------------------------------------  
verbose                           TRUE  
debug                             FALSE  
count                             FALSE  
start-page                        0  
end-page                          0  
page                              0  
file /var/lib/mysql/mysql/slave\_worker\_info.ibd = 98304 bytes (6 pages)...  
InnoChecksum; checking pages in range 0 to 5  
\[root@localhost mysql\]\#   
\[root@localhost mysql\]\# innochecksum -v /var/lib/mysql/mysql/slave\_relay\_log\_info.ibd   
InnoDB offline file checksum utility.

Variables (--variable-name=value)  
and boolean options \{FALSE|TRUE\}  Value (after reading options)  
\--------------------------------- ----------------------------------------  
verbose                           TRUE  
debug                             FALSE  
count                             FALSE  
start-page                        0  
end-page                          0  
page                              0  
file /var/lib/mysql/mysql/slave\_relay\_log\_info.ibd = 98304 bytes (6 pages)...  
InnoChecksum; checking pages in range 0 to 5  
\[root@localhost mysql\]\#

再重启mysql，查看日志，又出现了一些其他的类似的 Failed to find tablespace for table 的错误，逐个修复处理  
反复尝试多次修复重启，有些错误能修复不再报错，但下面三个错误再次出现，而且后面始终无法解决  
innochecksum -v /var/lib/mysql/mysql/slave\_master\_info.ibd  
innochecksum -v /var/lib/mysql/mysql/slave\_worker\_info.ibd  
innochecksum -v /var/lib/mysql/mysql/slave\_relay\_log\_info.ibd

从这信息来看，始终还是没能恢复：  
2020-08-18 15:48:41 26490 \[Note\] InnoDB: Starting crash recovery.  
2020-08-18 15:48:41 26490 \[Note\] InnoDB: Reading tablespace information from the .ibd files...  
2020-08-18 15:48:41 26490 \[Note\] InnoDB: Restoring possible half-written data pages  
2020-08-18 15:48:41 26490 \[Note\] InnoDB: from the doublewrite buffer...  
InnoDB: Warning: database page corruption or a failed  
InnoDB: file read of space 545489 page 3706.  
InnoDB: Trying to recover it from the doublewrite buffer.

mysql -uroot -p123456  
use mysql;  
optimize table slave\_master\_info;  
optimize table slave\_worker\_info;  
optimize table slave\_relay\_log\_info;  
exit

service mysql restart

查看日志仍然报同样的错误

尝试重置binlog功能试试看：  
mysql -uroot -p123456  
mysql> reset master;  
Query OK, 0 rows affected (12.30 sec)

前面的方法都搞不定

最后尝试删除ib\_logfile0、ib\_logfile1、ibdata1

step1) 修改配置  
vim /etc/my.cnf  
\[mysqld\]  
innodb\_force\_recovery = 6

step2) mysqldump导出数据  
mkdir /opt/bak\_mysqldump  
mysqldump -uroot -p123456 -A > /opt/bak\_mysqldump/all\_database\_bak.sql  //备份全部数据库的数据和结构，恢复时执行： mysqldump -uroot -p123456 -A < /opt/bak\_mysqldump/all\_database\_bak.sql

step3) 删除ib\_logfile0、ib\_logfile1、ibdata1  
先备份一下原来的文件，然后再删除

step4) 修改my.cnf配置  
vim /etc/my.cnf  
\[mysqld\]  
innodb\_force\_recovery = 0

step5) 重启数据库  
service mysql restart

step6) 恢复数据  
mysqldump -uroot -p123456 -A < /opt/bak\_mysqldump/all\_database\_bak.sql

由于我 step2) mysqldump导出数据时出错，没法把所有库进行导出，所以我最终最接删除了所有/var/lib/mysql/下的ib\_logfile0、ib\_logfile1、ibdata1以及目录下的数据库目录（系统库除外：information\_schema，mysql，performance\_schema）  
所以也没有执行step6。数据最终没有备份和恢复。

删除后重启MySQL服务，启动OK。后面所有库表重新创建。