Kubernetes StatefulSet中MySQL环境变量未生效问题求助

问题描述 #

在Kubernetes的StatefulSet中配置了MYSQL_ROOT_PASSWORD、MYSQL_USER、MYSQL_PASSWORD环境变量并关联对应Secret，但认为这些环境变量未在Pod中生效。

已完成的排查操作 #

• 通过exec -it进入Pod执行echo $MYSQL_ROOT_PASSWORD，能输出正确密码；
• Secret的base64编码使用echo -n 'password' | base64生成，确认编码无误；
• 已尝试添加MYSQL_ROOT_HOST变量并设置值为'%'；

配置文件与Pod信息 #

StatefulSet配置 #

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  namespace: mysql
spec:
  serviceName: mysql
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: mysql:8.2.0
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: root-password
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: username
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        - name: MYSQL_ROOT_HOST
          value: '%'
        ports:
        - containerPort: 3306
          name: redis
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes: [ "ReadWriteOnce" ]
      storageClassName: mysql-local
      resources:
        requests:
          storage: 1Gi

Secret配置 #

apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
  namespace: mysql
type: Opaque
data:
  root-password: YXNkZjE1MTU=
  password: YXNkZjE1MTU=
stringData:
  username: sueshin

Pod描述信息 #

[root@master mysql]# k describe -n mysql pod mysql-0
Name:             mysql-0
Namespace:        mysql
Priority:         0
Service Account:  default
Node:             worker0/192.168.57.102
Start Time:       Fri, 08 Dec 2023 11:22:59 +0900
Labels:           app=mysql
                  apps.kubernetes.io/pod-index=0
                  controller-revision-hash=mysql-6cb4685fdc
                  statefulset.kubernetes.io/pod-name=mysql-0
Annotations:      cni.projectcalico.org/containerID: 3c86c6e7ef25296947f07f1864279d64a737ad879835c699c0b1e2e309054274
                  cni.projectcalico.org/podIP: 192.168.204.97/32
                  cni.projectcalico.org/podIPs: 192.168.204.97/32
Status:           Running
IP:               192.168.204.97
IPs:
  IP:           192.168.204.97
Controlled By:  StatefulSet/mysql
Containers:
  mysql:
    Container ID:   containerd://54fc5d6d397bea60d502a75262ee9fbbadc26e9d9842544de819b2e1ef46d607
    Image:          mysql:8.2.0
    Image ID:       docker.io/library/mysql@sha256:1773f3c7aa9522f0014d0ad2bbdaf597ea3b1643c64c8ccc2123c64afd8b82b1
    Port:           3306/TCP
    Host Port:      0/TCP
    State:          Running
      Started:      Fri, 08 Dec 2023 11:22:59 +0900
    Ready:          True
    Restart Count:  0
    Environment:
      MYSQL_ROOT_PASSWORD:  <set to the key 'root-password' in secret 'mysql-secret'>  Optional: false
      MYSQL_USER:           <set to the key 'username' in secret 'mysql-secret'>       Optional: false
      MYSQL_PASSWORD:       <set to the key 'password' in secret 'mysql-secret'>       Optional: false
      MYSQL_ROOT_HOST:      %
    Mounts:
      /var/lib/mysql from data (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-qs5qh (ro)
Conditions:
  Type              Status
  Initialized       True
  Ready             True
  ContainersReady   True
  PodScheduled      True
Volumes:
  data:
    Type:       PersistentVolumeClaim (a reference to a PersistentVolumeClaim in the same namespace)
    ClaimName:  data-mysql-0
    ReadOnly:   false
  kube-api-access-qs5qh:
    Type:                    Projected (a volume that contains injected data from multiple sources)
    TokenExpirationSeconds:  3607
    ConfigMapName:           kube-root-ca.crt
    ConfigMapOptional:       <nil>
    DownwardAPI:             true
QoS Class:                   BestEffort
Node-Selectors:              <none>
Tolerations:                 node.kubernetes.io/not-ready:NoExecute op=Exists for 300s
                             node.kubernetes.io/unreachable:NoExecute op=Exists for 300s
Events:
  Type    Reason     Age    From               Message
  ----    ------     ----   ----               -------
  Normal  Scheduled  7m15s  default-scheduler  Successfully assigned mysql/mysql-0 to worker0
  Normal  Pulled     7m15s  kubelet            Container image "mysql:8.2.0" already present on machine
  Normal  Created    7m15s  kubelet            Created container mysql
  Normal  Started    7m15s  kubelet            Started container mysql

问题分析与解决 #

核心结论 #

环境变量已经在Pod中正常生效：从Pod描述的Environment部分可以看到变量已正确关联Secret，且exec命令能输出正确值也验证了这一点。你觉得未生效的原因是：MySQL官方镜像仅在首次启动（数据目录为空）时才会使用这些环境变量初始化数据库，后续启动会直接加载已有数据，忽略初始化类环境变量。

验证方法 #

进入Pod查看数据目录内容，确认是否已有初始化数据：

kubectl exec -it -n mysql mysql-0 -- ls -la /var/lib/mysql

如果能看到ibdata1、mysql、sys等目录/文件，说明数据库已经初始化过，此时环境变量不会再被用来修改密码或创建用户。

解决步骤 #

方案1：重新初始化数据库（适合无数据需要保留的场景）

1. 删除StatefulSet：

   kubectl delete statefulset -n mysql mysql
   

2. 删除对应的PersistentVolumeClaim：

   kubectl delete pvc -n mysql data-mysql-0
   

3. 重新部署StatefulSet：

   kubectl apply -f your-statefulset-file.yaml
   

   此时新创建的PVC是空的，MySQL启动时会读取环境变量完成初始化。

方案2：手动修改MySQL用户（适合需要保留现有数据的场景）

1. 进入Pod连接MySQL：

   kubectl exec -it -n mysql mysql-0 -- mysql -uroot -p
   

   输入旧的root密码登录（如果是首次部署但之前有初始化，密码是之前设置的）。
2. 修改root密码：

   ALTER USER 'root'@'%' IDENTIFIED BY 'your-new-root-password';
   

3. 创建指定用户或修改用户密码：

   CREATE USER 'sueshin'@'%' IDENTIFIED BY 'your-user-password';
   GRANT ALL PRIVILEGES ON *.* TO 'sueshin'@'%';
   FLUSH PRIVILEGES;
   

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