mysql故障mysqld got signal 6，由于异常断电或者系统异常重启时MySQL没有正常退出导致MySQL无法启动

环境说明

ubuntu 22.10 系统下 mysql 8.0.31

mysql 故障报错日志

查看日志

journalctl -xeu mysql.service
1

日志输出

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details.
root@iot:/var/lib# journalctl -xeu mysql.service
░░ Automatic restarting of the unit mysql.service has been scheduled, as the result for
░░ the configured Restart= setting for the unit.
Nov 30 03:05:33 iot systemd[1]: Stopped MySQL Community Server.
░░ Subject: A stop job for unit mysql.service has finished
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░ 
░░ A stop job for unit mysql.service has finished.
░░ 
░░ The job identifier is 2313 and the job result is done.
Nov 30 03:05:33 iot systemd[1]: Starting MySQL Community Server...
░░ Subject: A start job for unit mysql.service has begun execution
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░ 
░░ A start job for unit mysql.service has begun execution.
░░ 
░░ The job identifier is 2313.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

/var/log/mysql/error.log 日志

2022-11-30T03:21:48.101578Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-0ubuntu2) starting as process 33469
2022-11-30T03:21:48.106653Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-30T03:21:48.318340Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-11-30T03:21:48.371646Z 1 [Warning] [MY-011018] [InnoDB] Skip updating information_schema metadata in InnoDB read-only mode.
2022-11-30T03:21:48.371681Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode.
2022-11-30T03:21:48.374776Z 2 [Warning] [MY-011018] [Server] Skip updating information_schema metadata in InnoDB read-only mode.
2022-11-30T03:21:48.375353Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2022-11-30T03:21:48.375372Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2022-11-30T03:21:48.375552Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2022-11-30T03:21:48.381685Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2022-11-30T03:21:48.395808Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-11-30T03:21:48.395829Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-11-30T03:21:48.410646Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-11-30T03:21:48.410724Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31-0ubuntu2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2022-11-30T03:23:06.407052Z 9 [ERROR] [MY-013183] [InnoDB] Assertion failure: btr0pcur.cc:335:page_is_comp(next_page) == page_is_comp(page) thread 140444293867200
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/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2022-11-30T03:23:06Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=42d545154c26941ea0e6813d3e9adc0f8c30ed3c
Thread pointer: 0x7fbb70011410
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 = 7fbbbc3f1d00 thread_stack 0x100000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x561593045c31]
/usr/sbin/mysqld(print_fatal_signal(int)+0x2b7) [0x5615926c01f7]
/usr/sbin/mysqld(my_server_abort()+0x6d) [0x5615926c038d]
/usr/sbin/mysqld(my_abort()+0xe) [0x56159303ba7e]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x16f) [0x56159320cf4f]
/usr/sbin/mysqld(btr_pcur_t::move_to_next_page(mtr_t*)+0x1c8) [0x561593240ef8]
/usr/sbin/mysqld(+0x160947e) [0x5615930e847e]
/usr/sbin/mysqld(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long)+0xaf9) [0x5615931c8e39]
/usr/sbin/mysqld(ha_innobase::general_fetch(unsigned char*, unsigned int, unsigned int)+0xc9) [0x56159308faa9]
/usr/sbin/mysqld(handler::ha_rnd_next(unsigned char*)+0x66) [0x5615927a21f6]
/usr/sbin/mysqld(TableScanIterator::Read()+0x79) [0x5615928dfc69]
/usr/sbin/mysqld(Query_expression::ExecuteIteratorQuery(THD*)+0x328) [0x5615926486c8]
/usr/sbin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0xbf) [0x5615925d1b5f]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x1c0) [0x5615925d0ff0]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x911) [0x561592594e11]
/usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x432) [0x561592598602]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1a53) [0x56159259a6e3]
/usr/sbin/mysqld(do_command(THD*)+0x1d4) [0x56159259b184]
/usr/sbin/mysqld(+0xbd947f) [0x5615926b847f]
/usr/sbin/mysqld(+0x192d719) [0x56159340c719]
/lib/x86_64-linux-gnu/libc.so.6(+0x90402) [0x7fbbd5c90402]
/lib/x86_64-linux-gnu/libc.so.6(+0x11f590) [0x7fbbd5d1f590]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fbb70d80340): is an invalid pointer
Connection ID (thread ID): 9
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
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

msyql故障恢复

故障恢复启动msyql

使用恢复模式重启mysql
在/etc/mysql/mysql.conf.d/mysqld.cnf 文件[mysqld]下增加

innodb_force_recovery = 1
1

innodb_force_recovery: 说明请看 https://www.cnblogs.com/gaogao67/p/10558531.html

然后启动mysql命令

systemctl start mysql
1

查看是否启动成功，如果不报错那么启动成功。
如果报错那么继续修改配置。
我这边是修改到 3 时启动成功

innodb_force_recovery = 3
1

备份数据库

方式一 备份全部库

mysqldump \
> --lock-tables=0 \
> --all-databases > all-2022-11-30.sql
1
2
3

报错

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `d1_alert_record` at row: 55489
1

那么跳过这张表

mysqldump \
> --lock-tables=0 \
> --all-databases \
> --ignore-table=paas.d1_alert_record > all-2022-11-30.sql
1
2
3
4

方式二 指定数据库备份

mysqldump \
> --lock-tables=0 \
> --databases paas > paas-2022-11-30.sql
1
2
3

报错

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `d1_alert_record` at row: 55489
1

那么跳过这张表

mysqldump \
> --lock-tables=0 \
> --databases paas \
> --ignore-table=paas.d1_alert_record > paas-2022-11-30.sql
1
2
3
4

如果还有报错，继续增加跳过。
最后备份保存下来是 坏了 3张表

服务器上数据库重建恢复

因为坏了3张表，所以数据库上面的文件直接删除不要了。让msyql 重新建立新的库文件
把/etc/mysql/mysql.conf.d/mysqld.cnf 文件[mysqld]下 innodb_force_recovery 的配置项注释掉

# 停止msyql
systemctl stop mysql
# 备份原始文件
tar -zcvPf mysql.tgz /var/lib/mysql
# 删除 数据库文件
rm -rf /var/lib/mysql/*
1
2
3
4
5
6

初始化mysql数据库

mysqld --initialize --console
1

如果没有输出密码
那么到 /var/log/mysql/error.log 中 A temporary password is generated for root@localhost: 这样的字符，后面的就是密码

安装 mysql数据库

mysqld install
1

启动 msyql

systemctl start mysql
1

进入mysql 命令行

mysql -uroot -p
1

这个时候，因为是初始化的新数据库，那么要修改root的默认密码，在msyql 命令行执行如下，修改你想要的密码

alter user 'root'@'localhost' identified by '123456';
1

如果你想创建用户请看
https://blog.csdn.net/fenglailea/article/details/123741354

数据库恢复

因为我这边是指定数据备份，那么要先建立数据库

CREATE DATABASE IF NOT EXISTS paas \
CHARACTER SET utf8mb4 \
COLLATE utf8mb4_general_ci ;
1
2
3

导入数据库

source /root/paas-2022-11-30.sql
1

到此恢复完成

参考
https://zhuanlan.zhihu.com/p/115502394
https://www.cnblogs.com/gaogao67/p/10558531.html