树莓派MySQL崩溃触发Signal 11无法启动，求数据恢复方案

问题背景 #

树莓派上运行3年的应用突然故障，根源是MySQL无法启动。已尝试设置innodb_force_recovery=6仍无效，现在只想导出数据后在新实例恢复，无法使用mysqldump，求可行的恢复建议。

相关配置与操作日志 #

1. MySQL配置文件my.cnf #

ubuntu@ubuntu:/etc/mysql$ cat my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#


[mysqld]
innodb_force_recovery=6
innodb_purge_threads=0

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

2. 启动MySQL服务失败日志 #

ubuntu@ubuntu:/etc/mysql$ /etc/init.d/mysql start
Starting mysql (via systemctl): mysql.service==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to start 'mysql.service'.
Authenticating as: Ubuntu (ubuntu)
Password: 
==== AUTHENTICATION COMPLETE ===
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
 failed!

3. MySQL服务状态日志 #

ubuntu@ubuntu:/etc/mysql$ systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Tue 2023-05-09 21:58:52 UTC; 18s ago
    Process: 4125 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)

May 09 21:58:51 ubuntu systemd[1]: mysql.service: Control process exited, code=exited, status=1/FAILURE
May 09 21:58:51 ubuntu systemd[1]: mysql.service: Failed with result 'exit-code'.
May 09 21:58:51 ubuntu systemd[1]: Failed to start MySQL Community Server.
May 09 21:58:52 ubuntu systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
May 09 21:58:52 ubuntu systemd[1]: Stopped MySQL Community Server.
May 09 21:58:52 ubuntu systemd[1]: mysql.service: Start request repeated too quickly.
May 09 21:58:52 ubuntu systemd[1]: mysql.service: Failed with result 'exit-code'.
May 09 21:58:52 ubuntu systemd[1]: Failed to start MySQL Community Server.

4. mysqld启动崩溃日志 #

ubuntu@ubuntu:/etc/mysql$ mysqld start
ubuntu@ubuntu:/etc/mysql$ cat /var/log/mysql/error.log

2023-05-09T22:00:07.501574Z 0 [Warning] [MY-011037] [Server] The CYCLE timer is not available. WAIT events in the performance_schema will not be timed.
2023-05-09T22:00:08.440437Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.20.04.1) starting as process 4322
2023-05-09T22:00:08.466499Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2023-05-09T22:00:08Z UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=f16c68f8f63a58e9eabfa0c5f251618a2ffed724
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 0x100000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2d) [0x1bf2ad2]
/usr/sbin/mysqld(print_fatal_signal(int)+0x253) [0x1099b58]
/usr/sbin/mysqld(handle_fatal_signal+0x71) [0x1099c72]
/lib/arm-linux-gnueabihf/libc.so.6(+0x27590) [0xb6605590]
/usr/sbin/mysqld(memory::Aligned_atomic<long>::Aligned_atomic()+0x4d) [0x19747e6]
/usr/sbin/mysqld(Delegate::Delegate(unsigned int)+0x39) [0x1974a3e]
/usr/sbin/mysqld(delegates_init()+0x37) [0x1974b80]
/usr/sbin/mysqld(+0x9fb69c) [0xef269c]
/usr/sbin/mysqld(mysqld_main(int, char**)+0x1f73) [0xef8d08]
/lib/arm-linux-gnueabihf/libc.so.6(__libc_start_main+0x97) [0xb65f55a4]
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.

5. 连接失败日志 #

ubuntu@ubuntu:/etc/mysql$ mysql -u root -p 
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

离线数据恢复方案 #

1. 物理文件迁移法（针对InnoDB） #

• 先停掉所有MySQL相关进程：sudo systemctl stop mysql
• 找到MySQL数据目录（通常是/var/lib/mysql），完整复制到安全路径：sudo cp -r /var/lib/mysql /tmp/mysql-backup
• 在新环境（或同一设备重装）安装完全相同版本的MySQL（本次为8.0.33-0ubuntu0.20.04.1）
• 停止新安装的MySQL服务：sudo systemctl stop mysql
• 清空新数据目录：sudo rm -rf /var/lib/mysql/*
• 将备份文件复制到新数据目录：sudo cp -r /tmp/mysql-backup/* /var/lib/mysql/
• 修复文件权限：sudo chown -R mysql:mysql /var/lib/mysql
• 给新实例的my.cnf添加innodb_force_recovery=6，尝试启动MySQL；启动成功后立即用mysqldump导出所有数据：mysqldump -u root -p --all-databases > all-dbs.sql
• 导出完成后，去掉innodb_force_recovery配置，重新初始化MySQL，再导入备份的SQL文件。

2. 第三方工具直接读取InnoDB文件 #

• 用ibdata2sql或兼容的MySQL工具，直接读取ibdata1和各数据库目录下的*.ibd文件，提取数据生成可导入的SQL脚本
• 操作步骤：
  1. 安装与原MySQL版本兼容的工具
  2. 备份ibdata1和所有*.ibd文件
  3. 运行工具扫描文件，生成SQL脚本
  4. 在新MySQL实例中执行脚本恢复数据

3. 硬件排查（优先操作） #

报错中的signal 11（段错误）大概率和树莓派的SD卡损坏有关，先检查SD卡健康状态：sudo fsck /dev/mmcblk0p2，若检测到坏块，先更换SD卡再进行数据恢复。

内容的提问来源于stack exchange，提问作者Mark