method to create database and users automatically

[Coolfeather2]

solution to updating StatefulSet objects in Kubernetes is currently a manual process, meaning we have to execute MySQL commands in the mariadb pod to add new databases and users.

    initContainers:
      - name: database-setup
        image: mysql:5.7
        imagePullPolicy: IfNotPresent
        # command:  ["rm", "-fr", "/var/lib/mysql/lost+found"]
        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;"]
          # - 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;
        env:
        - name: WORDPRESS_DB_NAME
          value: wp-wd
        - name: WORDPRESS_DB_USER
          value: wp-wd
        - name: WORDPRESS_DB_PASSWORD
          valueFrom:
            secretKeyRef:
              name: wp-wd-pass
              key: password
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-root-pass
              key: mysql-root-pass

[chepurko]

Awesome, do you have this running in your own cluster? Tested? Would love to update the YAML to this.

[Coolfeather2]

Yep, using it in mine

[chepurko]

Sorry to come back so late... can you post the entire YAML? Which one is this in? wp-Deployment.yaml? I'm unfamiliar with the InitContainers directive.

[Coolfeather2]

I have modified my setup so I will put together the yaml using wp-wd-Deployment.yaml:

apiVersion: apps/v1
kind: Deployment
metadata:
  creationTimestamp: null
  labels:
    app: wp-wd
    tier: frontend
    track: stable
  name: wp-wd
  namespace: wp-wd
spec:
  replicas: 1
  selector:
    matchLabels:
      app: wp-wd
      name: wp-nginx
      tier: frontend
      track: stable
  strategy:
    rollingUpdate:
      maxSurge: 0
      maxUnavailable: 1
    type: RollingUpdate
  template:
    metadata:
      creationTimestamp: null
      labels:
        app: wp-wd
        name: wp-nginx
        tier: frontend
        track: stable
    spec:
      initContainers:
      - name: database-setup
        image: mysql:5.7
        imagePullPolicy: IfNotPresent
        # command:  ["rm", "-fr", "/var/lib/mysql/lost+found"]
        command: [sh,-c,mysql -h mariadb.core.svc.cluster.local -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;"]
          # - 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;
        env:
        - name: WORDPRESS_DB_NAME
          value: dbWPWD
        - name: WORDPRESS_DB_USER
          value: wp-wd
        - name: WORDPRESS_DB_PASSWORD
          valueFrom:
            secretKeyRef:
              key: mariadb-pass-wp-wd.txt
              name: mariadb-pass-wp-wd
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              key: mariadb-pass-root.txt
              name: mariadb-pass-root
      containers:
      - env:
        - name: WORDPRESS_DB_HOST
          value: mariadb.core.svc.cluster.local
        - name: WORDPRESS_DB_NAME
          value: dbWPWD
        - name: WORDPRESS_DB_USER
          value: wp-wd
        - name: WORDPRESS_DB_PASSWORD
          valueFrom:
            secretKeyRef:
              key: mariadb-pass-wp-wd.txt
              name: mariadb-pass-wp-wd
        image: daxio/wordpress-redis:v1.6-fpm
        imagePullPolicy: IfNotPresent
        name: wp
        resources: {}
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
        volumeMounts:
        - mountPath: /var/www/html
          name: wp-persistent-storage
        - mountPath: /usr/local/etc/php/conf.d
          name: php
        - mountPath: /var/run/nginx-cache
          name: cache
      - image: daxio/nginx-naxsi:v1.1.3
        imagePullPolicy: IfNotPresent
        name: wp-nginx
        ports:
        - containerPort: 80
          name: http
          protocol: TCP
        resources: {}
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
        volumeMounts:
        - mountPath: /var/www/html
          name: wp-persistent-storage
        - mountPath: /etc/nginx
          name: nginx
        - mountPath: /etc/nginx-conf/conf.d
          name: nginx-conf-d
        - mountPath: /etc/nginx-conf/global
          name: nginx-global
        - mountPath: /etc/nginx-conf/html
          name: nginx-html
        - mountPath: /var/run/nginx-cache
          name: cache
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      terminationGracePeriodSeconds: 30
      volumes:
      - name: wp-persistent-storage
        persistentVolumeClaim:
          claimName: wp-wd-pv-claim
      - configMap:
          defaultMode: 420
          name: php
        name: php
      - configMap:
          defaultMode: 420
          name: nginx
        name: nginx
      - configMap:
          defaultMode: 420
          name: nginx-conf-d
        name: nginx-conf-d
      - configMap:
          defaultMode: 420
          name: nginx-global
        name: nginx-global
      - configMap:
          defaultMode: 420
          name: nginx-html
        name: nginx-html
      - emptyDir:
          medium: Memory
        name: cache
status: {}

Hopefully I got all that right

[Coolfeather2]

I'm not sure if you can swap out

        command: [sh,-c,mysql -h mariadb.core.svc.cluster.local -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;"]

to have a WORDPRESS_DB_HOST environment variable instead of mariadb.core.svc.cluster.local