数据库断电之后无法启动：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
2022-12-26 11:18:12 0x7f8f39ffb700 InnoDB: Assertion failure in thread 140253130110720 in file fut0lst.ic line 93
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
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.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
03:18:12 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
|
MySQL 有个一个特性：Forcing InnoDB Recovery，启用这个特性需要设置 innodb_force_recovery 大于 0。
innodb_force_recovery 可以设置为 1-6，大的值包含前面所有小于它的值的影响。
1
2
3
4
5
6
7
8
9
10
11
|
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): 不执行事务回滚操作。
4 (SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。不要进行统计操作。该值可能永久损坏数据文件。若使用了该值，则将来要删除和重建辅助索引。
5 (SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志，InnoDB 存储引擎会将未提交的事务视为已提交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。
6 (SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。使数据库页处于废止状态，继而可能引起 B 树或者其他数据库结构更多的损坏。
|
注意：
1
2
3
4
5
6
7
8
9
|
为了安全，当设置参数值大于 0 后，可以对表进行 select, create, drop 操作,但 insert, update 或者 delete 这类操作是不允许的。MySQL 5.6.15 以后，当 innodb_force_recovery 的值大于等于 4 的时候，InnoDB 表处于只读模式。
在值小于等于 3 时可以通过 select 来 dump 表，可以 drop 或者 create 表。MySQL 5.6.27 后大于 3 的值也支持 DROP TABLE；
如果事先知道哪个表导致了崩溃则可 drop 掉这个表。如果碰到了由失败的大规模导入或大量 ALTER TABLE 操作引起的 runaway rollback，则可 kill 掉 mysqld 线程然后设置 innodb_force_recovery = 3 使数据库重启后不进行 rollback。然后删除导致 runaway rollback 的表；
如果表内的数据损坏导致不能 dump 整个表内容。那么附带 order by primary_key desc 从句的查询或许能够 dump 出损坏部分之后的部分数据；
若使用更高的 innodb_force_recovery 值，那么一些损坏的数据结构可能引起复杂的查询无法运行。此时可能只能运行最基本的 select * from table 语句。
|
我们可以从1-6开始尝试直到数据库可以启动为止
1
|
innodb_force_recovery = 2
|
重启mysql：
1
|
$ systemctl restart mysqld
|
备份数据库：
1
2
|
$ mysqldump -uroot -p --all-databases --default-character-set=utf8 > all.sql
$ tar zcvf mysql_bak.tgz /opt/mysql
|
注释innodb_force_recovery，并停止数据库：
1
|
# innodb_force_recovery=2
|
1
|
$ systemctl stop mysqld
|
删除原数据库：
1
|
$ rm -fr /opt/mysql/data/*
|
重新初始化数据库：
1
|
$ ./mysqld --initialize-insecure --user=mysql --initialize --lower-case-table-names=1 --basedir=/opt/mysql --datadir=/opt/mysql/data
|
修改数据库密码允许远程登录：
1
2
|
$ GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
$ FLUSH PRIVILEGES;
|
登录的时候后台提示如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
2022-12-26T03:54:36.731795Z 0 [Warning] Failed to open optimizer cost constant tables
2022-12-26T03:54:36.732199Z 0 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
2022-12-26T03:54:36.733663Z 0 [Note] Failed to start slave threads for channel ''
2022-12-26T03:54:36.733710Z 0 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
2022-12-26T03:54:36.733724Z 0 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
2022-12-26T03:54:36.733735Z 0 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
2022-12-26T03:54:36.733747Z 0 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
2022-12-26T03:54:36.733760Z 0 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure
2022-12-26T03:54:36.733771Z 0 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
2022-12-26T03:54:36.733783Z 0 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
2022-12-26T03:54:36.733793Z 0 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure
2022-12-26T03:54:36.733805Z 0 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure
2022-12-26T03:54:36.733816Z 0 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
2022-12-26T03:54:36.733826Z 0 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
2022-12-26T03:54:36.733839Z 0 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
2022-12-26T03:54:36.733849Z 0 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
2022-12-26T03:54:36.733860Z 0 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
2022-12-26T03:54:36.733870Z 0 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
2022-12-26T03:54:36.733882Z 0 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_type' has the wrong structure
2022-12-26T03:54:36.733892Z 0 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
2022-12-26T03:54:36.733902Z 0 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
2022-12-26T03:54:36.733912Z 0 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure
2022-12-26T03:54:36.733922Z 0 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
2022-12-26T03:54:36.733932Z 0 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
2022-12-26T03:54:36.733941Z 0 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure
2022-12-26T03:54:36.733950Z 0 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
2022-12-26T03:54:36.733961Z 0 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_index_usage' has the wrong structure
2022-12-26T03:54:36.733972Z 0 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_table' has the wrong structure
2022-12-26T03:54:36.733982Z 0 [ERROR] Native table 'performance_schema'.'table_lock_waits_summary_by_table' has the wrong structure
2022-12-26T03:54:36.733992Z 0 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
2022-12-26T03:54:36.734004Z 0 [ERROR] Native table 'performance_schema'.'events_stages_current' has the wrong structure
2022-12-26T03:54:36.734021Z 0 [ERROR] Native table 'performance_schema'.'events_stages_history' has the wrong structure
2022-12-26T03:54:36.734032Z 0 [ERROR] Native table 'performance_schema'.'events_stages_history_long' has the wrong structure
2022-12-26T03:54:36.734043Z 0 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_thread_by_event_name' has the wrong structure
2022-12-26T03:54:36.734055Z 0 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_account_by_event_name' has the wrong structure
2022-12-26T03:54:36.734066Z 0 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_user_by_event_name' has the wrong structure
2022-12-26T03:54:36.734076Z 0 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_host_by_event_name' has the wrong structure
2022-12-26T03:54:36.734088Z 0 [ERROR] Native table 'performance_schema'.'events_stages_summary_global_by_event_name' has the wrong structure
2022-12-26T03:54:36.734100Z 0 [ERROR] Native table 'performance_schema'.'events_statements_current' has the wrong structure
2022-12-26T03:54:36.734111Z 0 [ERROR] Native table 'performance_schema'.'events_statements_history' has the wrong structure
2022-12-26T03:54:36.734121Z 0 [ERROR] Native table 'performance_schema'.'events_statements_history_long' has the wrong structure
2022-12-26T03:54:36.734132Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_thread_by_event_name' has the wrong structure
2022-12-26T03:54:36.734143Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_account_by_event_name' has the wrong structure
2022-12-26T03:54:36.734154Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure
2022-12-26T03:54:36.734165Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
2022-12-26T03:54:36.734176Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
2022-12-26T03:54:36.734186Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
2022-12-26T03:54:36.734197Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_program' has the wrong structure
2022-12-26T03:54:36.734207Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_current' has the wrong structure
2022-12-26T03:54:36.734217Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history' has the wrong structure
2022-12-26T03:54:36.734229Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history_long' has the wrong structure
2022-12-26T03:54:36.734244Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_thread_by_event_name' has the wrong structure
2022-12-26T03:54:36.734260Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_account_by_event_name' has the wrong structure
2022-12-26T03:54:36.734276Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_user_by_event_name' has the wrong structure
2022-12-26T03:54:36.734291Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_host_by_event_name' has the wrong structure
2022-12-26T03:54:36.734307Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_global_by_event_name' has the wrong structure
2022-12-26T03:54:36.734318Z 0 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
2022-12-26T03:54:36.734330Z 0 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
2022-12-26T03:54:36.734339Z 0 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
2022-12-26T03:54:36.734349Z 0 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
2022-12-26T03:54:36.734359Z 0 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
2022-12-26T03:54:36.734370Z 0 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
2022-12-26T03:54:36.734383Z 0 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
2022-12-26T03:54:36.734394Z 0 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
2022-12-26T03:54:36.734404Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_global_by_event_name' has the wrong structure
2022-12-26T03:54:36.734416Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_account_by_event_name' has the wrong structure
2022-12-26T03:54:36.734427Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_host_by_event_name' has the wrong structure
2022-12-26T03:54:36.734439Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_thread_by_event_name' has the wrong structure
2022-12-26T03:54:36.734450Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_user_by_event_name' has the wrong structure
2022-12-26T03:54:36.734460Z 0 [ERROR] Native table 'performance_schema'.'table_handles' has the wrong structure
2022-12-26T03:54:36.734472Z 0 [ERROR] Native table 'performance_schema'.'metadata_locks' has the wrong structure
2022-12-26T03:54:36.734483Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_configuration' has the wrong structure
2022-12-26T03:54:36.734494Z 0 [ERROR] Native table 'performance_schema'.'replication_group_members' has the wrong structure
2022-12-26T03:54:36.734504Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure
2022-12-26T03:54:36.734515Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_configuration' has the wrong structure
2022-12-26T03:54:36.734525Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status' has the wrong structure
2022-12-26T03:54:36.734536Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_coordinator' has the wrong structure
2022-12-26T03:54:36.734547Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_worker' has the wrong structure
2022-12-26T03:54:36.734558Z 0 [ERROR] Native table 'performance_schema'.'replication_group_member_stats' has the wrong structure
2022-12-26T03:54:36.734569Z 0 [ERROR] Native table 'performance_schema'.'prepared_statements_instances' has the wrong structure
2022-12-26T03:54:36.734579Z 0 [ERROR] Native table 'performance_schema'.'user_variables_by_thread' has the wrong structure
2022-12-26T03:54:36.734622Z 0 [ERROR] Native table 'performance_schema'.'status_by_account' has the wrong structure
2022-12-26T03:54:36.734644Z 0 [ERROR] Native table 'performance_schema'.'status_by_host' has the wrong structure
2022-12-26T03:54:36.734654Z 0 [ERROR] Native table 'performance_schema'.'status_by_thread' has the wrong structure
2022-12-26T03:54:36.734665Z 0 [ERROR] Native table 'performance_schema'.'status_by_user' has the wrong structure
2022-12-26T03:54:36.734675Z 0 [ERROR] Native table 'performance_schema'.'global_status' has the wrong structure
2022-12-26T03:54:36.734684Z 0 [ERROR] Native table 'performance_schema'.'session_status' has the wrong structure
2022-12-26T03:54:36.734695Z 0 [ERROR] Native table 'performance_schema'.'variables_by_thread' has the wrong structure
2022-12-26T03:54:36.734705Z 0 [ERROR] Native table 'performance_schema'.'global_variables' has the wrong structure
2022-12-26T03:54:36.734714Z 0 [ERROR] Native table 'performance_schema'.'session_variables' has the wrong structure
|
1
|
./mysql_upgrade -uroot -p123456
|
重启数据库：
1
|
$ systemctl restart mysqld
|
把数据库导入回去：
1
|
$ ./mysql -uroot -p123456 < all.sql
|
