k8s 部署 mysql 故障恢复记录

k8s 集群 用 helm 部署 mariadb （https://charts.bitnami.com/bitnami）

https://github.com/bitnami/charts/tree/main/bitnami/mariadb

采用了主从部署

default   mariadb-primary-0    1/1  Running   0   10m   10.224.166.150   node1
default   mariadb-secondary-0  1/1  Running   1   13d   10.224.104.26    node2

由于一次机房停电，导致磁盘文件异常。primary 启动不了

mariadb 06:32:07.66 Welcome to the Bitnami mariadb container
mariadb 06:32:07.67 Subscribe to project updates by watching https://github.com/bitnami/containers
mariadb 06:32:07.68 Submit issues and feature requests at https://github.com/bitnami/containers/issues
mariadb 06:32:07.68
mariadb 06:32:07.69 INFO  ==> ** Starting MariaDB setup **
mariadb 06:32:07.73 INFO  ==> Validating settings in MYSQL_*/MARIADB_* env vars
mariadb 06:32:07.74 INFO  ==> Initializing mariadb database
mariadb 06:32:07.80 WARN  ==> The mariadb configuration file '/opt/bitnami/mariadb/conf/my.cnf' is not writable. Configurations based on environment variables will not be applied for this file.
mariadb 06:32:07.81 INFO  ==> Using persisted data
mariadb 06:32:07.89 INFO  ==> Running mysql_upgrade
mariadb 06:32:07.92 INFO  ==> Starting mariadb in background
2024-10-22  6:32:07 0 [Note] Starting MariaDB 10.6.12-MariaDB-log source revision 4c79e15cc3716f69c044d4287ad2160da8101cdc as process 52
2024-10-22  6:32:07 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-10-22  6:32:07 0 [Note] InnoDB: Using transactional memory
2024-10-22  6:32:07 0 [Note] InnoDB: Number of pools: 1
2024-10-22  6:32:07 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-10-22  6:32:07 0 [Note] mysqld: O_TMPFILE is not supported on /opt/bitnami/mariadb/tmp (disabling future attempts)
2024-10-22  6:32:07 0 [Note] InnoDB: Using Linux native AIO
2024-10-22  6:32:07 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2024-10-22  6:32:08 0 [Note] InnoDB: Completed initialization of buffer pool
2024-10-22  6:32:08 0 [Note] InnoDB: 128 rollback segments are active.
2024-10-22  6:32:08 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-10-22  6:32:08 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-10-22  6:32:08 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2024-10-22  6:32:08 0 [Note] InnoDB: 10.6.12 started; log sequence number 16872896567; transaction id 46736708
2024-10-22  6:32:08 0 [Note] InnoDB: Loading buffer pool(s) from /bitnami/mariadb/data/ib_buffer_pool
2024-10-22  6:32:08 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-10-22  6:32:08 0 [Note] Server socket created on IP: '127.0.0.1'.
2024-10-22  6:32:08 0 [Note] Zerofilling moved table:  './mysql/db'
2024-10-22  6:32:08 0 [ERROR] mysql.db: Page      8192:  Got error: 175 when reading datafile
2024-10-22  6:32:08 0 [Warning] Checking table:   './mysql/db'
2024-10-22  6:32:08 0 [ERROR] mysql.db: Table is probably from another system and must be zerofilled or repaired ('REPAIR TABLE table_name') to be usable on this system
2024-10-22  6:32:08 0 [Warning] Recovering table: './mysql/db'
2024-10-22  6:32:08 0 [Note] mysql.db: Running zerofill on moved table
2024-10-22  6:32:08 0 [ERROR] mysql.db: Page      8192:  Got error: 175 when reading datafile
2024-10-22  6:32:08 0 [ERROR] Couldn't repair table: mysql.db
2024-10-22  6:32:08 0 [ERROR] Fatal error: Can't open and lock privilege tables: Index for table 'db' is corrupt; try to repair it
2024-10-22  6:32:08 0 [ERROR] Aborting
Warning: Memory not freed: 280

解决过程

尝试 innodb_force_recovery 方案，无效。

修改 mysql.cnf ，添加配置，启动失败
kubectl edit cm -n ds-system aicore-mariadb-primary
> innodb_force_recovery=6
> innodb_purge_thread=1

异常文件恢复方案

由于从节点的实例正常，检查两边文件

[root@node1 mysql]# ll /data/k8s/mysql-master/data/mysql/db*
-rw-rw---- 1 elastic elastic  3446 May 11 09:20 db.frm
-rw-rw---- 1 elastic elastic  8192 Oct  9 09:19 db.MAD
-rw-rw---- 1 elastic elastic  8192 Oct  9 09:09 db.MAD-241009011242.BAK
-rw-rw---- 1 elastic elastic 16384 Oct 22 15:42 db.MAI
-rw-rw---- 1 elastic elastic    67 May 11 09:20 db.opt

[root@node2 ~]# ls /data/k8s/mysql-secondary/data/mysql -l
-rw-rw---- 1 elastic elastic     3446 May 11 09:20 db.frm
-rw-rw---- 1 elastic elastic    16384 Oct  1 10:40 db.MAD
-rw-rw---- 1 elastic elastic    24576 Oct  1 10:40 db.MAI
-rw-rw---- 1 elastic elastic       67 May 11 09:20 db.opt

mariadb-primary 的 db.MAD 比 aicore-mariadb-secondary 的文件大小差异很大，把从节点的db表文件全部复制过来

 scp node2:/data/k8s/mysql-secondary/data/mysql/db* /data/k8s/mysql-master/data/mysql/

重启 mariadb-primary 后成功