MariaDB数据库文件损坏的问题

Question

当我的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停靠容器错误日志文件：

[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数据库进行了完整的物理备份，并且已经将其还原到本地/dev/test环境中。

当我在localhost (xampp)上启动MariaDB时，问题仍然存在(就像在我的码头容器上一样)。

以下是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

我在网站上的数据库文件b：

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

Answer 1

看起来MDEV 28870在10.7.5里修好了。

物理备份可以保留某些形式的损坏。因此，如果您的备份正在产生这种情况，那么：

• 使用--innodb_force_recovery=6作为容器
• 对容器中的数据库进行逻辑转储。
• 还原到10.7.5+容器中。