<p>Kubernetes documentation says that for mysql pods we need to use Stateful sets in order to avoid "split brain" situations when one pod dies, in other words, to declare one "master" node to which data will be written to, and if that pod dies, elect new master, that's why i want this deployment and service to transfer to stateful set:</p>

<pre><code>  ---

    apiVersion: apps/v1
    kind: Deployment
    metadata:
      name: mysql-container
    spec:
      replicas: 3
      selector:
        matchLabels:
          app: mysql-container
      template:
        metadata:
          labels:
            app: mysql-container
        spec:
          containers:
          - name: mysql-container
            image: mysql:dev
            imagePullPolicy: "IfNotPresent"
            envFrom:
              - secretRef:
                 name: prod-secrets
            ports:
            - containerPort: 3306
             # container (pod) path
            volumeMounts:
              - name: mysql-persistent-storage
                mountPath: /data/db

          # minikube path
          volumes:
            - name: mysql-persistent-storage
              persistentVolumeClaim:
               claimName: mysql-pvc
            #resources:
            #  requests:
            #    memory: 300Mi
            #    cpu: 400m
            #  limits:
            #    memory: 400Mi
            #    cpu: 500m  
          restartPolicy: Always

---

apiVersion: v1
kind: Service
metadata:
  name: mysql

spec:
  # Open port 3306 only to pods in cluster
  selector:
    app: mysql-container

  ports:
    - name: mysql
      port: 3306
      protocol: TCP
      targetPort: 3306
  type: ClusterIP
</code></pre>

<p>i created stateful set following: <a href="https://kubernetes.io/docs/tasks/run-application/run-replicated-stateful-application/" rel="nofollow noreferrer">this guide</a></p>

<p>Under containers section i specified environment variables from file, ie. removed</p>

<pre><code> env:
        - name: MYSQL_ALLOW_EMPTY_PASSWORD
          value: "1"
</code></pre>

<p>Statefulset:</p>

<pre><code>apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  serviceName: mysql
  replicas: 3
  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] &gt; /mnt/conf.d/server-id.cnf
          # Add an offset to avoid reserved server-id=0 value.
          echo server-id=$((100 + $ordinal)) &gt;&gt; /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 ]] &amp;&amp; exit 0
          # Skip the clone on master (ordinal index 0).
          [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          [[ $ordinal -eq 0 ]] &amp;&amp; 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:dev
        imagePullPolicy: "IfNotPresent"
        envFrom:
          - secretRef:
             name: prod-secrets
        ports:
        - name: mysql
          containerPort: 3306
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
        resources:
          #requests:
           # cpu: 300m
           # memory: 1Gi
        livenessProbe:
          exec:
            command: ["mysqladmin", "ping"]
          initialDelaySeconds: 30
          periodSeconds: 10
          timeoutSeconds: 5
        readinessProbe:
          exec:
            # Check we can execute queries over TCP (skip-networking is off).
            command: ["mysql", "-h", "127.0.0.1", "-e", "SELECT 1"]
          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 &amp;&amp; "x$(&lt;xtrabackup_slave_info)" != "x" ]]; then
            # XtraBackup already generated a partial "CHANGE MASTER TO" query
            # because we're cloning from an existing slave. (Need to remove the tailing semicolon!)
            cat xtrabackup_slave_info | sed -E 's/;$//g' &gt; change_master_to.sql.in
            # Ignore xtrabackup_binlog_info in this case (it's useless).
            rm -f xtrabackup_slave_info 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 -f xtrabackup_binlog_info xtrabackup_slave_info
            echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}',\
                  MASTER_LOG_POS=${BASH_REMATCH[2]}" &gt; 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 -e "SELECT 1"; do sleep 1; done

            echo "Initializing replication from clone position"
            mysql -h 127.0.0.1 \
                  -e "$(&lt;change_master_to.sql.in), \
                          MASTER_HOST='mysql-0.mysql', \
                          MASTER_USER='root', \
                          MASTER_PASSWORD='', \
                          MASTER_CONNECT_RETRY=10; \
                        START SLAVE;" || exit 1
            # In case of container restart, attempt this at-most-once.
            mv change_master_to.sql.in change_master_to.sql.orig
          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
      - name: data
        persistentVolumeClaim:
            claimName: mysql-pvc
</code></pre>

<p>Services:</p>

<pre><code># Headless service for stable DNS entries of StatefulSet members.
apiVersion: v1
kind: Service
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  ports:
  - name: mysql
    port: 3306
    protocol: TCP
    targetPort: 3306
  type: ClusterIP
  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: v1
kind: Service
metadata:
  name: mysql-read
  labels:
    app: mysql
spec:
  ports:
  - name: mysql
    port: 3306
    protocol: TCP
    targetPort: 3306
  type: ClusterIP  
  selector:
    app: mysql
</code></pre>

<p>I have env file from which i created secret:</p>

<pre><code>kubectl create secret prod-secrets \
       --from-env-file=env.example 
</code></pre>

<p>Problem is that i can't access mysql (Access denied), pods using credentials specified in secret, without Stateful set, all works fine. All pods are running, no errors in logs</p>

<p>How to specify values in secrets into Statefulset ?</p>

<p>I presume that i need somehow to pass those secrets to command section but have no idea how, example from Kuberenets page assumes credentials are not used</p>

<p>If there is less complicated way to use stateful set for mysql,please let me know, thanks.</p>

overflowed
<p>At the end i managed to escape above complications by creating volume templates, created PV for each pod, both volumes are synchronized, no duplicate entries in database, and if one node fails,data are preserved</p>

<p>storage.yaml:</p>

<pre><code>apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:

  name: localstorage

provisioner: kubernetes.io/no-provisioner
volumeBindingMode: Immediate
reclaimPolicy: Delete
allowVolumeExpansion: True

---

kind: PersistentVolume
apiVersion: v1
metadata:
  name: mysql-01
  labels:
    type: local
spec:
  storageClassName: localstorage
  capacity:
    storage: 5Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/mnt/mysql01"

---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: mysql-02
  labels:
    type: local
spec:
  storageClassName: localstorage
  capacity:
    storage: 5Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/mnt/mysql02"
</code></pre>

<p>Statefulset:</p>

<pre><code>apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-container
spec:
  serviceName: mysql
  replicas: 2
  selector:
    matchLabels:
      app: mysql-container
  template:
    metadata:
      labels:
        app: mysql-container
    spec:
      containers:
      - name: mysql-container
        image: mysql:dev
        imagePullPolicy: "IfNotPresent"
        envFrom:
          - secretRef:
             name: prod-secrets
        ports:
        - containerPort: 3306
        # container (pod) path
        volumeMounts:
          - name: mysql-persistent-storage
            mountPath: /var/lib/mysql

        resources:
          requests:
            memory: 300Mi
            cpu: 400m
          limits:
            memory: 400Mi
            cpu: 500m
      restartPolicy: Always

  volumeClaimTemplates:
    - metadata:
        name: mysql-persistent-storage
      spec:
        storageClassName: localstorage
        accessModes: ["ReadWriteOnce"]
        resources:
         requests:
          storage: 5Gi
        selector:
         matchLabels:
          type: local
</code></pre>
