Kubernetes中SQL初始化文件无法创建MySQL表问题排查

问题描述 #

在Kubernetes部署MySQL服务，目标通过ConfigMap中的SQL初始化脚本创建main数据库及configs表。部署完成后，main数据库成功创建，但使用PyMySQL向main.configs插入数据时触发1146错误（表不存在），手动通过PyMySQL创建该表可正常执行，推测初始化脚本未成功创建表。

相关配置代码 #

Kubernetes资源配置 #

apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
type: Opaque
stringData:
  mysql-root-password: abcd1234567
  mysql-user: batman
  mysql-database: main
  mysql-password: abcd1234567
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-initdb-config
data:  # store initial SQL commands
  init.sql: |
    CREATE USER 'batman'@'%' IDENTIFIED BY 'abcd1234567';
    GRANT ALL PRIVILEGES ON *.* TO 'batman'@'%';
    FLUSH PRIVILEGES;
    
    CREATE DATABASE IF NOT EXISTS main;
    USE main;
    CREATE TABLE configs(
        task_id VARCHAR(124) NOT NULL,
        PRIMARY KEY (task_id)
    ) ENGINE=InnoDB;
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 100Mi
---
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  ports:
  - port: 3306
  selector:
    app: mysql
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - image: mysql:5.6
        name: mysql
        env:
          - name: MYSQL_ROOT_PASSWORD
            valueFrom:
              secretKeyRef:
                name: mysql-secrets
                key: mysql-root-password
          - name: MYSQL_USER
            valueFrom:
              secretKeyRef:
                name: mysql-secrets
                key: mysql-user
          - name: MYSQL_DATABASE
            valueFrom:
              secretKeyRef:
                name: mysql-secrets
                key: mysql-database
          - name: MYSQL_PASSWORD
            valueFrom:
              secretKeyRef:
                name: mysql-secrets
                key: mysql-password
        ports:
          - containerPort: 3306
            name: mysql

        volumeMounts:
          - name: mysql-persistent-storage
            mountPath: /var/lib/mysql
            subPath: "mysql"
          - name: mysql-initdb
            mountPath: /docker-entrypoint-initdb.d  # used to configure our database

      volumes:
        - name: mysql-persistent-storage
          persistentVolumeClaim:
            claimName: mysql-pv-claim
        - name: mysql-initdb
          configMap:
            name: mysql-initdb-config

PyMySQL代码 #

import pymysql

host = 'localhost'
port = 8080  # The port you forwarded with kubectl
user = 'batman'
password = 'abcd1234567'
database = 'main'

# Establish a connection
connection = pymysql.connect(
    host=host,
    port=port,
    user=user,
    password=password,
    database=database
)
cursor = connection.cursor()

query = """
        INSERT INTO
          configs (task_id)
        VALUES
          ('{}')
        """.format(*['id001'])
cursor.execute(query)
connection.commit()

问题原因分析 #

1. Secret名称引用错误：Deployment中所有secretKeyRef的name字段写为mysql-secrets，但实际Secret的名称是mysql-secret，导致容器无法正确加载环境变量，可能干扰初始化脚本的执行上下文。
2. 初始化脚本上下文依赖问题：脚本中依赖USE main;切换数据库，在部分场景下（如脚本执行权限、执行顺序），上下文切换可能未生效，导致表未创建在目标数据库中。
3. PersistentVolume数据残留：若之前部署过MySQL且未清理PV数据，MySQL镜像仅会在首次启动（/var/lib/mysql目录为空）时执行初始化脚本，后续启动不会重新执行，导致表未创建。

解决方案 #

1. 修复Secret名称引用错误 #

修改Deployment中所有secretKeyRef的name为mysql-secret，与实际Secret名称一致：

env:
  - name: MYSQL_ROOT_PASSWORD
    valueFrom:
      secretKeyRef:
        name: mysql-secret  # 修正为正确的Secret名称
        key: mysql-root-password
  - name: MYSQL_USER
    valueFrom:
      secretKeyRef:
        name: mysql-secret  # 修正为正确的Secret名称
        key: mysql-user
  - name: MYSQL_DATABASE
    valueFrom:
      secretKeyRef:
        name: mysql-secret  # 修正为正确的Secret名称
        key: mysql-database
  - name: MYSQL_PASSWORD
    valueFrom:
      secretKeyRef:
        name: mysql-secret  # 修正为正确的Secret名称
        key: mysql-password

2. 优化初始化脚本，明确指定数据库 #

修改ConfigMap中的SQL脚本，直接在CREATE TABLE语句中指定数据库，避免依赖USE命令的上下文切换：

CREATE USER 'batman'@'%' IDENTIFIED BY 'abcd1234567';
GRANT ALL PRIVILEGES ON *.* TO 'batman'@'%';
FLUSH PRIVILEGES;

CREATE DATABASE IF NOT EXISTS main;
-- 直接指定数据库名称创建表
CREATE TABLE main.configs(
    task_id VARCHAR(124) NOT NULL,
    PRIMARY KEY (task_id)
) ENGINE=InnoDB;

3. 清理PersistentVolume数据（若有残留） #

如果PV中已有旧数据库数据，需清理以触发初始化脚本重新执行：

• 删除PVC和PV：

  kubectl delete pvc mysql-pv-claim
  # 若PV为静态创建，需手动删除PV：kubectl delete pv <pv-name>
  

• 重新应用Kubernetes配置：

  kubectl apply -f your-config-file.yaml
  

验证步骤 #

1. 重新部署MySQL服务后，通过端口转发连接Pod：

   kubectl port-forward pod/<mysql-pod-name> 8080:3306
   

2. 使用PyMySQL执行插入代码，确认无1146错误，数据成功插入。
3. 或直接登录MySQL验证表存在：

   kubectl exec -it <mysql-pod-name> -- mysql -u batman -pabcd1234567 main
   mysql> SHOW TABLES;
   

   应看到configs表。

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