Kubernetes mysql statefulset with root password

If you created a secret like this (just as example):

kubectl create secret generic mysql-pass --from-literal=password=YOUR_PASSWORD

then use it as environment variable:

 spec:      containers:      - image: mysql:5.6        name: mysql        env:        - name: MYSQL_ROOT_PASSWORD          valueFrom:            secretKeyRef:              name: mysql-pass              key: password

or mount it as volume:

spec:  containers:  - name: mycontainer    image: mysql    volumeMounts:    - name: foo      mountPath: /etc/foo      readOnly: true  volumes:  - name: foo    secret:      secretName: mysql-pass      items:      - key: password        path: my-secret/my-password

password secret is stored under /etc/foo/my-secret/my-password file instead of /etc/foo/password.

Reference: https://kubernetes.io/docs/tutorials/stateful-application/mysql-wordpress-persistent-volume/#create-a-secret-for-mysql-password

EDIT:

I made a try with the file you linked in your answer and I've seen that passwords missed in a couple of points.

Here the file I succesfully used:

apiVersion: apps/v1beta1kind: StatefulSetmetadata:  name: mysqlspec:  selector:    matchLabels:      app: mysql  serviceName: mysql  replicas: 1  template:    metadata:      labels:        app: mysql    spec:      initContainers:      - name: init-mysql        image: mysql:5.7        command:        - bash        - "-c"        - |          set -ex          # Generate mysql server-id from pod ordinal index.          [[ `hostname` =~ -([0-9]+)$ ]] || exit 1          ordinal=${BASH_REMATCH[1]}          echo [mysqld] > /mnt/conf.d/server-id.cnf          # Add an offset to avoid reserved server-id=0 value.          echo server-id=$((100 + $ordinal)) >> /mnt/conf.d/server-id.cnf          # Copy appropriate conf.d files from config-map to emptyDir.          if [[ $ordinal -eq 0 ]]; then            cp /mnt/config-map/master.cnf /mnt/conf.d/          else            cp /mnt/config-map/slave.cnf /mnt/conf.d/          fi        volumeMounts:        - name: conf          mountPath: /mnt/conf.d        - name: config-map          mountPath: /mnt/config-map      - name: clone-mysql        image: gcr.io/google-samples/xtrabackup:1.0        command:        - bash        - "-c"        - |          set -ex          # Skip the clone if data already exists.          [[ -d /var/lib/mysql/mysql ]] && exit 0          # Skip the clone on master (ordinal index 0).          [[ `hostname` =~ -([0-9]+)$ ]] || exit 1          ordinal=${BASH_REMATCH[1]}          [[ $ordinal -eq 0 ]] && exit 0          # Clone data from previous peer.          ncat --recv-only mysql-$(($ordinal-1)).mysql 3307 | xbstream -x -C /var/lib/mysql          # Prepare the backup.          xtrabackup --prepare --target-dir=/var/lib/mysql        volumeMounts:        - name: data          mountPath: /var/lib/mysql          subPath: mysql        - name: conf          mountPath: /etc/mysql/conf.d      containers:      - name: mysql        image: mysql:5.7        env:        - name: MYSQL_ROOT_PASSWORD          valueFrom:            secretKeyRef:              name: mysql-pass              key: MYSQL_ROOT_PASSWORD        ports:        - name: mysql          containerPort: 3306        volumeMounts:        - name: data          mountPath: /var/lib/mysql          subPath: mysql        - name: conf          mountPath: /etc/mysql/conf.d        resources:          requests:            cpu: 500m            memory: 1Gi        livenessProbe:          exec:            command:            - bash            - "-c"            - |              set -ex              mysqladmin -uroot -p$MYSQL_ROOT_PASSWORD ping &> /dev/null          initialDelaySeconds: 30          periodSeconds: 10          timeoutSeconds: 5        readinessProbe:          exec:            # Check we can execute queries over TCP (skip-networking is off).            command:            - bash            - "-c"            - |              set -ex              mysql -h 127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD -e "SELECT 1" &> /dev/null          initialDelaySeconds: 5          periodSeconds: 2          timeoutSeconds: 1      - name: xtrabackup        image: gcr.io/google-samples/xtrabackup:1.0        ports:        - name: xtrabackup          containerPort: 3307        command:        - bash        - "-c"        - |          set -ex          cd /var/lib/mysql          # Determine binlog position of cloned data, if any.          if [[ -f xtrabackup_slave_info ]]; then            # XtraBackup already generated a partial "CHANGE MASTER TO" query            # because we're cloning from an existing slave.            mv xtrabackup_slave_info change_master_to.sql.in            # Ignore xtrabackup_binlog_info in this case (it's useless).            rm -f xtrabackup_binlog_info          elif [[ -f xtrabackup_binlog_info ]]; then            # We're cloning directly from master. Parse binlog position.            [[ `cat xtrabackup_binlog_info` =~ ^(.*?)[[:space:]]+(.*?)$ ]] || exit 1            rm xtrabackup_binlog_info            echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}',\                  MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in          fi          # Check if we need to complete a clone by starting replication.          if [[ -f change_master_to.sql.in ]]; then            echo "Waiting for mysqld to be ready (accepting connections)"            until mysql -h 127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD -e "SELECT 1"; do sleep 1; done            echo "Initializing replication from clone position"            # In case of container restart, attempt this at-most-once.            mv change_master_to.sql.in change_master_to.sql.orig            mysql -h 127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD <<EOF          $(<change_master_to.sql.orig),            MASTER_HOST='mysql-0.mysql',            MASTER_USER='root',            MASTER_PASSWORD=$MYSQL_ROOT_PASSWORD,            MASTER_CONNECT_RETRY=10;          START SLAVE;          EOF          fi          # Start a server to send backups when requested by peers.          exec ncat --listen --keep-open --send-only --max-conns=1 3307 -c \            "xtrabackup --backup --slave-info --stream=xbstream --host=127.0.0.1 --user=root"        volumeMounts:        - name: data          mountPath: /var/lib/mysql          subPath: mysql        - name: conf          mountPath: /etc/mysql/conf.d        resources:          requests:            cpu: 100m            memory: 100Mi      volumes:      - name: conf        emptyDir: {}      - name: config-map        configMap:          name: mysql  volumeClaimTemplates:    - metadata:        name: data      spec:        accessModes: ["ReadWriteOnce"]        resources:          requests:            storage: 10Gi

Remember that you need a config-map:

apiVersion: v1kind: ConfigMapmetadata:  name: mysql  labels:    app: mysqldata:  master.cnf: |    # Apply this config only on the master.    [mysqld]    log-bin  slave.cnf: |    # Apply this config only on slaves.    [mysqld]    super-read-only

the services (used inside the scripts:

# Headless service for stable DNS entries of StatefulSet members.apiVersion: v1kind: Servicemetadata:  name: mysql  labels:    app: mysqlspec:  ports:  - name: mysql    port: 3306  clusterIP: None  selector:    app: mysql---# Client service for connecting to any MySQL instance for reads.# For writes, you must instead connect to the master: mysql-0.mysql.apiVersion: v1kind: Servicemetadata:  name: mysql-read  labels:    app: mysqlspec:  ports:  - name: mysql    port: 3306  selector:    app: mysql

and a persistence volume to be used by statefulSet.

The answer provided by Nicola Ben is close, but there were a few mistakes in the StatefulSet declaration.

The main problem is that the environment variable is declared in the mysql image but not in the xtrabackup where it is also referenced. Secondly the START SLAVE and xtrabackup commands should take the user and password as well.

Here is a working version of the StatefulSet yaml declaration:

apiVersion: apps/v1kind: StatefulSetmetadata:  name: mysqlspec:  selector:    matchLabels:      app: mysql  serviceName: mysql  replicas: 2  template:    metadata:      labels:        app: mysql    spec:      initContainers:      - name: init-mysql        image: mysql:5.7        command:        - bash        - "-c"        - |          set -ex          # Generate mysql server-id from pod ordinal index.          [[ `hostname` =~ -([0-9]+)$ ]] || exit 1          ordinal=${BASH_REMATCH[1]}          echo [mysqld] > /mnt/conf.d/server-id.cnf          # Add an offset to avoid reserved server-id=0 value.          echo server-id=$((100 + $ordinal)) >> /mnt/conf.d/server-id.cnf          # Copy appropriate conf.d files from config-map to emptyDir.          if [[ $ordinal -eq 0 ]]; then            cp /mnt/config-map/master.cnf /mnt/conf.d/          else            cp /mnt/config-map/slave.cnf /mnt/conf.d/          fi        volumeMounts:        - name: conf          mountPath: /mnt/conf.d        - name: config-map          mountPath: /mnt/config-map      - name: clone-mysql        image: gcr.io/google-samples/xtrabackup:1.0        command:        - bash        - "-c"        - |          set -ex          # Skip the clone if data already exists.          [[ -d /var/lib/mysql/mysql ]] && exit 0          # Skip the clone on master (ordinal index 0).          [[ `hostname` =~ -([0-9]+)$ ]] || exit 1          ordinal=${BASH_REMATCH[1]}          [[ $ordinal -eq 0 ]] && exit 0          # Clone data from previous peer.          ncat --recv-only mysql-$(($ordinal-1)).mysql 3307 | xbstream -x -C /var/lib/mysql          # Prepare the backup.          xtrabackup --prepare --target-dir=/var/lib/mysql        volumeMounts:        - name: data          mountPath: /var/lib/mysql          subPath: mysql        - name: conf          mountPath: /etc/mysql/conf.d      containers:      - name: mysql        image: mysql:5.7        env:        - name: MYSQL_ROOT_PASSWORD          valueFrom:            secretKeyRef:              name: mysql-pass-root              key: password        - name: MYSQL_USER          value: server        - name: MYSQL_PASSWORD          valueFrom:            secretKeyRef:              name: mysql-pass-user              key: password        - name: MYSQL_DATABASE          value: medlor        ports:        - name: mysql          containerPort: 3306        volumeMounts:        - name: data          mountPath: /var/lib/mysql          subPath: mysql        - name: conf          mountPath: /etc/mysql/conf.d        resources:          requests:            cpu: 500m            memory: 1Gi        livenessProbe:          exec:            command: ["mysqladmin", "-uroot", "-p$MYSQL_ROOT_PASSWORD", "ping"]          initialDelaySeconds: 30          periodSeconds: 10          timeoutSeconds: 5        readinessProbe:          exec:            # Check we can execute queries over TCP (skip-networking is off).            command:            - /bin/sh            - -ec            - >-              mysql -h127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD -e'SELECT 1'          initialDelaySeconds: 5          periodSeconds: 2          timeoutSeconds: 1      - name: xtrabackup        image: gcr.io/google-samples/xtrabackup:1.0        env:        - name: MYSQL_ROOT_PASSWORD          valueFrom:            secretKeyRef:              name: mysql-pass-root              key: password        ports:        - name: xtrabackup          containerPort: 3307        command:        - bash        - "-c"        - |          set -ex          cd /var/lib/mysql          # Determine binlog position of cloned data, if any.          if [[ -f xtrabackup_slave_info ]]; then            # XtraBackup already generated a partial "CHANGE MASTER TO" query            # because we're cloning from an existing slave.            mv xtrabackup_slave_info change_master_to.sql.in            # Ignore xtrabackup_binlog_info in this case (it's useless).            rm -f xtrabackup_binlog_info          elif [[ -f xtrabackup_binlog_info ]]; then            # We're cloning directly from master. Parse binlog position.            [[ `cat xtrabackup_binlog_info` =~ ^(.*?)[[:space:]]+(.*?)$ ]] || exit 1            rm xtrabackup_binlog_info            echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}',\                  MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in          fi          # Check if we need to complete a clone by starting replication.          if [[ -f change_master_to.sql.in ]]; then            echo "Waiting for mysqld to be ready (accepting connections)"            until mysql -h 127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD -e "SELECT 1"; do sleep 1; done            echo "Initializing replication from clone position"            # In case of container restart, attempt this at-most-once.            mv change_master_to.sql.in change_master_to.sql.orig            mysql -h 127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD <<EOF          $(<change_master_to.sql.orig),            MASTER_HOST='mysql-0.mysql',            MASTER_USER='root',            MASTER_PASSWORD='$MYSQL_ROOT_PASSWORD',            MASTER_CONNECT_RETRY=10;          START SLAVE USER='root' PASSWORD='$MYSQL_ROOT_PASSWORD';          EOF          fi          # Start a server to send backups when requested by peers.          exec ncat --listen --keep-open --send-only --max-conns=1 3307 -c \            "xtrabackup --backup --slave-info --stream=xbstream --host=127.0.0.1 --user=root --password=$MYSQL_ROOT_PASSWORD"        volumeMounts:        - name: data          mountPath: /var/lib/mysql          subPath: mysql        - name: conf          mountPath: /etc/mysql/conf.d        resources:          requests:            cpu: 100m            memory: 100Mi      volumes:      - name: conf        emptyDir: {}      - name: config-map        configMap:          name: mysql  volumeClaimTemplates:  - metadata:      name: data    spec:      accessModes: ["ReadWriteOnce"]      resources:        requests:          storage: 10Gi

I also noticed that in his file he has the replicas set to 1 which would only start the master and no slaves, so that might explain why some were getting startup failure on the slaves.

There are much more to do to make a production version of mysql cluster by referencing the Kubernetes statefulSet mysql example. Besides that example require write-read split.

I eventually choose a Helm Chart of Percona xtradb cluster. Each node in the cluster is writable and readable.

Link here: https://github.com/helm/charts/tree/master/stable/percona-xtradb-cluster