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

最新推荐文章于 2024-05-16 00:50:09 发布

sunny05296

最新推荐文章于 2024-05-16 00:50:09 发布

阅读量6.9k

点赞数 1

分类专栏：数据库常见问题

本文链接：https://blog.csdn.net/sunny05296/article/details/108087931

版权

常见问题同时被 2 个专栏收录

149 篇文章 4 订阅

订阅专栏

数据库

136 篇文章 5 订阅

订阅专栏

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。后面所有库表重新创建。

sunny05296

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
MySQL断电重启后导致数据库无法启动

MySQL断电重启后导致数据库无法启动报错信息如下：InnoDB: Submit a detailed bug report to http://bugs.mysql.com.InnoDB: If you get repeated assertion failures or crashes, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablesp
复制链接

扫一扫