MariaDB 數據庫文件損壞的問題

當我的 Docker 容器上的 MariaDB 數據庫不斷自行重啟時，我發現問題出在我的數據庫中（我認為它已損壞）。

root@website:~# docker-compose ps
     Name                     Command                 State                    Ports
----------------------------------------------------------------------------------------------------
root_mariadb_1      docker-entrypoint.sh mariadbd    Restarting
root_phpmyadmin_1   /docker-entrypoint.sh apac ...   Up           0.0.0.0:9500->80/tcp,:::9500->80/t
                                                                 cp
root_redis_1        /opt/bitnami/scripts/redis ...   Restarting

MariaDB docker 容器錯誤日誌文件：

[0m 2022-07-27 23:22:00+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.7.3+maria~focal started.
[0m 2022-07-27 23:22:00+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
[0m 2022-07-27 23:22:00+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.7.3+maria~focal started.
[0m 2022-07-27 23:22:00+00:00 [Note] [Entrypoint]: MariaDB upgrade not required
[0m 2022-07-27 23:22:00 0 [Note] mariadbd (server 10.7.3-MariaDB-1:10.7.3+maria~focal) starting as process 1 ...
[0m 2022-07-27 23:22:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
[0m 2022-07-27 23:22:00 0 [Note] InnoDB: Number of transaction pools: 1
[0m 2022-07-27 23:22:00 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
[0m 2022-07-27 23:22:00 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
[0m 2022-07-27 23:22:00 0 [Note] InnoDB: Using Linux native AIO
[0m 2022-07-27 23:22:00 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
[0m 2022-07-27 23:22:00 0 [Note] InnoDB: Completed initialization of buffer pool
[0m 2022-07-27 23:22:00 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=16587504,22296402
[0m 2022-07-27 23:22:00 0 [ERROR] InnoDB: Missing FILE_CREATE, FILE_DELETE or FILE_MODIFY before FILE_CHECKPOINT for tablespace 60
[0m 2022-07-27 23:22:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
[0m 2022-07-27 23:22:00 0 [Note] InnoDB: Starting shutdown...
[0m 2022-07-27 23:22:01 0 [ERROR] Plugin 'InnoDB' init function returned error.
[0m 2022-07-27 23:22:01 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[0m 2022-07-27 23:22:01 0 [Note] Plugin 'FEEDBACK' is disabled.
[0m 2022-07-27 23:22:01 0 [ERROR] Unknown/unsupported storage engine: InnoDB
[0m 2022-07-27 23:22:01 0 [ERROR] Aborting

這是 docker-compose.yml 文件：

version: '3.4'
services:
 mariadb:
   image: mariadb
   ports:
     - 3306:3306
   environment:
     MYSQL_ROOT_PASSWORD: "pass"
   restart: always
   volumes:
   - '/data/mysql:/var/lib/mysql'

我已經對我的 MariaDB 數據庫進行了完整的物理備份，並且我已經將它恢復到了我的本地/開發/測試環境。

當我在 localhost (xampp) 上啟動 MariaDB 時，問題仍然存在（就像在我的 docker 容器上一樣）。

這是來自 localhost/xampp 的 MariaDB 錯誤日誌：

2022-07-29 11:04:20 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-07-29 11:04:20 0 [Note] InnoDB: Number of transaction pools: 1
2022-07-29 11:04:20 0 [Note] InnoDB: Using generic crc32 instructions
2022-07-29 11:04:20 0 [Note] InnoDB: Initializing buffer pool, total size = 16777216, chunk size = 16777216
2022-07-29 11:04:20 0 [Note] InnoDB: Completed initialization of buffer pool
2022-07-29 11:04:20 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=16587504,22296402
2022-07-29 11:04:20 0 [ERROR] InnoDB: Missing FILE_CREATE, FILE_DELETE or FILE_MODIFY before FILE_CHECKPOINT for tablespace 60
2022-07-29 11:04:20 0 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
2022-07-29 11:04:20 0 [Note] InnoDB: Starting shutdown...
2022-07-29 11:04:21 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-07-29 11:04:21 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-07-29 11:04:21 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-07-29 11:04:21 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-07-29 11:04:21 0 [ERROR] Aborting

MariaDB 上的文件和文件夾：

root@website:/data/mysql# ls -l
total 206316
-rw-rw---- 1 systemd-coredump systemd-coredump  18268160 Jul 29 05:29 aria_log.00000001
-rw-rw---- 1 systemd-coredump systemd-coredump        52 Jul 29 05:29 aria_log_control
-rw-rw---- 1 systemd-coredump systemd-coredump     16384 Jul 25 16:27 ddl_recovery-backup.log
-rw-rw---- 1 systemd-coredump systemd-coredump       872 Apr 10 01:34 ib_buffer_pool
-rw-rw---- 1 systemd-coredump systemd-coredump  79691776 Jul 25 16:27 ibdata1
-rw-rw---- 1 systemd-coredump systemd-coredump 100663296 Jul 25 16:27 ib_logfile0
-rw-rw---- 1 systemd-coredump systemd-coredump  12582912 Jul 25 16:27 ibtmp1
-rw-rw---- 1 systemd-coredump systemd-coredump         0 Apr 10 01:27 multi-master.info
drwx------ 2 systemd-coredump systemd-coredump      4096 Apr 10 01:27 mysql
-rw-r--r-- 1 systemd-coredump systemd-coredump        14 Apr 10 01:27 mysql_upgrade_info
drwx------ 2 systemd-coredump systemd-coredump      4096 May 28 05:44 websitedb
drwx------ 2 systemd-coredump systemd-coredump      4096 Apr 10 01:27 performance_schema
drwx------ 2 systemd-coredump systemd-coredump     12288 Apr 10 01:27 sys

我在 websitedb 上的數據庫文件：

root@website:/data/mysql/websitedb# ls -l
total 13404
-rw-rw---- 1 systemd-coredump systemd-coredump     2094 Apr 10 02:00 slide.frm
-rw-rw---- 1 systemd-coredump systemd-coredump    98304 Jul 25 16:27 slide.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump       67 Apr 10 01:58 db.opt
-rw-rw---- 1 systemd-coredump systemd-coredump     4630 Apr 10 02:00 role.frm
-rw-rw---- 1 systemd-coredump systemd-coredump   114688 Jul 25 16:27 role.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump     7505 Apr 10 02:00 setting.frm
-rw-rw---- 1 systemd-coredump systemd-coredump    98304 Jul 25 16:27 setting.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump    10307 Apr 10 02:00 user.frm
-rw-rw---- 1 systemd-coredump systemd-coredump   212992 Jul 25 16:27 user.ibd

看起來MDEV-28870 已在 10.7.5 中修復。

物理備份可以保留某些形式的損壞。因此，如果您的備份正在生成這個，那麼：

• 用作--innodb_force_recovery=6容器
• 對容器中的數據庫進行邏輯轉儲
• 恢復到 10.7.5+ 容器中。

引用自：https://dba.stackexchange.com/questions/315007