使用环境变量执行MySQL init命令以创建数据库

Question

我正在尝试创建一个数据库，如果数据库不存在，并设置privledges，但是我很难让它工作，我一直在获取You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' at line 1

我已经包括了执行命令的各种方法

Yaml：

  initContainers:
  - name: database-setup
    image: mysql:5.7
    imagePullPolicy: IfNotPresent
    # command:  ["rm", "-fr", "/var/lib/mysql/lost+found"]
    # SQL Syntax error:
    command: [sh,-c,mysql -h mysql-service -p$MYSQL_ROOT_PASSWORD -e "CREATE DATABASE IF NOT EXISTS `$WORDPRESS_DB_NAME` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; GRANT ALL PRIVILEGES ON $WORDPRESS_DB_NAME.* TO '$WORDPRESS_DB_USER'@'%' IDENTIFIED BY '$WORDPRESS_DB_PASSWORD'; FLUSH PRIVILEGES;"]

      # Enviroment Variables arn't passed into mysql shell:
      # - sh
      # - -c
      # - mysql -h mysql-service -p$MYSQL_ROT_PASSWORD
      # - CREATE DATABASE IF NOT EXISTS $WORDPRESS_DB_NAME DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
      # - GRANT ALL PRIVILEGES ON $WORDPRESS_DB_NAME.* TO '$WORDPRESS_DB_USER'@'%' IDENTIFIED BY '$WORDPRESS_DB_PASSWORD';
      # - FLUSH PRIVILEGES;
      # - EXIT;

      # SQL Syntax error:
      # - sh
      # - -c
      # - mysql -h mysql-service -p$MYSQL_ROOT_PASSWORD -e "CREATE DATABASE IF NOT EXISTS `$WORDPRESS_DB_NAME` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; GRANT ALL PRIVILEGES ON $WORDPRESS_DB_NAME.* TO '$WORDPRESS_DB_USER'@'%' IDENTIFIED BY '$WORDPRESS_DB_PASSWORD'; FLUSH PRIVILEGES;

    env:
    - name: WORDPRESS_DB_NAME
      value: wordpress-green
    - name: WORDPRESS_DB_USER
      value: wordpress-green
    - name: WORDPRESS_DB_PASSWORD
      valueFrom:
        secretKeyRef:
          name: wordpress-mysql-pass
          key: wordpress-mysql-pass
    - name: MYSQL_ROOT_PASSWORD
      valueFrom:
        secretKeyRef:
          name: mysql-root-pass
          key: mysql-root-pass

Answer 1

尝试为echo行执行一个MySQL来调试返回的变量：

mysql -h mysql-service -p\\REDACTED\\ -e CREATE DATABASE IF NOT EXISTS DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; GRANT ALL PRIVILEGES ON wordpress-green.* TO 'wordpress-green'@'%' IDENTIFIED BY '\\REDACTED\\'; FLUSH PRIVILEGES;

我通过删除$WORDPRESS_DB_NAME修复了未传递给create的 -这再次导致语法错误，因为它不喜欢数据库名中的破折号。相反，我只是使用camelCase作为数据库名称

谢谢你的帮助提姆·比格莱森

Answer 2

我遇到了同样的问题，但我希望在数据库名称中使用连字符(因为这是Kubernetes资源命名的有效语法)。这就是对我起作用的东西

        command:
        - /bin/sh
        - -c
        - mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USER --password=$MYSQL_PWD
          --execute="CREATE DATABASE IF NOT EXISTS \"$WORDPRESS_DB_NAME\";"