Simple Mysql 8 replication on k8s

💡 NOTE: Interested in mysql-operator instead? See this blogpost.

Official kubernetes docs have a page about running replicated statefulset using mysql, unfortunately:

example uses mysql 5.7
it’s quite hard to grasp - it uses some custom percona-based image and assumes that you might want to scale statefulset into infinity
as both images are dated it just won’t work on ARM machines

I think in small to medium organisations/teams/projects what you usually want it some finite amount of replicas - maybe one that you could use for some read-only operations and for doing backups from. So let’s try to do that.

Let’s create a two separate statefulsets under same namespace - one will be leader and another one follower - we will implemented GTID-based replication.

First we’re gonna need to prepare xtrabackup docker image:

FROM bitnami/percona-xtrabackup:8.2.0-1

USER root

RUN apt update && \
    apt install -y ncat

The extra bit is ncat which is technically a network cat command. Build it and push it into the wild so your k8s cluster can pull it from somewhere.

docker build . -t my-image/percona-xtrabackup:8.2.0-1

Then let’s prepare a leader statefulset.

Note: I’m going to simplify the yamls below a little bit and omit irrelevant bits

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: mysql
  selector:
    matchLabels:
      app: mysql
  replicas: 1
  updateStrategy:
    type: RollingUpdate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
        - name: mysql
          image: mysql:8.2
          args:
            # leader options
            - "--server-id=1"
            - "--gtid-mode=ON"
          env:
            - name: MYSQL_USER
              valueFrom:
                secretKeyRef:
                  name: mysql
                  key: MYSQL_USER
            - name: MYSQL_DATABASE
              valueFrom:
                secretKeyRef:
                  name: mysql
                  key: MYSQL_DATABASE
            - name: MYSQL_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql
                  key: MYSQL_PASSWORD
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql
                  key: MYSQL_ROOT_PASSWORD
          ports:
            - containerPort: 3306
              name: mysql
          volumeMounts:
            - mountPath: /var/lib/mysql
              name: mysql
        - name: xtrabackup
          image: my-image/percona-xtrabackup:8.2.0-1
          ports:
            - name: xtrabackup
              containerPort: 3307
          command:
            - bash
            - "-c"
            - |
              set -ex
              cd /var/lib/mysql

              # 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"              
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql
                  key: MYSQL_ROOT_PASSWORD
          volumeMounts:
            - name: mysql-cloud
              mountPath: /var/lib/mysql
      volumes:
        - name: mysql
          persistentVolumeClaim:
            claimName: mysql

We’re gonna explicitly set the server_id and enable GTID based logging in our mysql container.
We’re gonna start xtrabackup so follower can request fresh copy of the database

That alone should work - you should have a pod with two containers running - mysql and xtrabackup waiting for the requests.

Now let’s create a follower mysql user using mysql command line:

CREATE USER 'follower'@'%' IDENTIFIED BY '<your password>';
GRANT REPLICATION SLAVE ON *.* TO 'follower'@'%';
FLUSH PRIVILEGES;

and store that REPLICATION_USER and REPLICATION_PASSWORD in mysql-follower secret - we’re going to need that in the follower.

Speaking of - the follower bit:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-follower
spec:
  serviceName: mysql-follower
  selector:
    matchLabels:
      app: mysql-follower
  replicas: 1
  updateStrategy:
    type: RollingUpdate
  template:
    metadata:
      labels:
        app: mysql-follower
    spec:
      initContainers:
        - name: clone-mysql
          image: my-image/percona-xtrabackup:8.2.0-1
          command:
            - bash
            - "-c"
            - |
              set -ex

              # Skip the clone if data already exists.
              [[ -d /var/lib/mysql/mysql ]] && exit 0

              # Clone data from leader
              ncat --recv-only mysql 3307 | xbstream -x -C /var/lib/mysql
              # Prepare the backup.
              xtrabackup --prepare --target-dir=/var/lib/mysql              
          volumeMounts:
            - name: mysql-follower
              mountPath: /var/lib/mysql
      containers:
        - name: mysql
          image: mysql:8.2
          args:
            # follower options
            - "--server-id=2"
            - "--gtid-mode=ON"
            - "--log-bin=OFF"
            - "--enforce-gtid-consistency=ON"
            - "--super-read-only"
            - "--skip-replica-start=ON" # IMPORTANT: You will want to switch it to OFF after you set everything up
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql
                  key: MYSQL_ROOT_PASSWORD
          ports:
            - containerPort: 3306
              name: mysql
          volumeMounts:
            - mountPath: /var/lib/mysql
              name: mysql-follower
        - name: replication
          image: mysql:8.2
          command:
            - bash
            - "-c"
            - |
              set -e
              cd /var/lib/mysql

              # This file should be created by init container:
              # https://docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/working_with_binary_logs.html
              if [[ -f xtrabackup_binlog_info ]]; then
                echo "xtrabackup_binlog_info exists"
                cat xtrabackup_binlog_info

                [[ $(cat xtrabackup_binlog_info) =~ ^([^.]*.[0-9]+)[[:space:]]+([0-9]+)[[:space:]]+([0-9a-zA-Z:-]+) ]] || exit 1
                rm -f xtrabackup_binlog_info xtrabackup_slave_info

                GTID_MATCHED=${BASH_REMATCH[3]}

                echo "Found GTID match: ${GTID_MATCHED}"
                echo "SET GLOBAL gtid_purged='${GTID_MATCHED}';" > gtid_purged.sql.in
              fi

              # Check if we need to complete a clone by starting replication.
              if [[ -f gtid_purged.sql.in ]]; then
                echo "Waiting for mysqld to be ready (accepting connections)"
                until mysql -p"$MYSQL_ROOT_PASSWORD" -h 127.0.0.1 -e "SELECT 1"; do sleep 2; done

                echo "Initializing replication"

                mysql -h 127.0.0.1 \
                      -p"$MYSQL_ROOT_PASSWORD" \
                      -e "RESET MASTER; \
                      $(<gtid_purged.sql.in) \
                      CHANGE REPLICATION SOURCE TO \
                      SOURCE_HOST='mysql', \
                      SOURCE_USER='$REPLICATION_USER', \
                      SOURCE_PASSWORD='$REPLICATION_PASSWORD', \
                      SOURCE_AUTO_POSITION = 1, \
                      SOURCE_CONNECT_RETRY = 30; \
                      START REPLICA;" || exit 1
                # In case of container restart, attempt this at-most-once.
                mv gtid_purged.sql.in gtid_purged.sql.orig
              fi

              echo "All done"
              trap : TERM INT; sleep infinity & wait              
          env:
            - name: REPLICATION_USER
              valueFrom:
                secretKeyRef:
                  name: mysql-follower
                  key: REPLICATION_USER
            - name: REPLICATION_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-follower
                  key: REPLICATION_PASSWORD
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql
                  key: MYSQL_ROOT_PASSWORD
          volumeMounts:
            - name: mysql-follower
              mountPath: /var/lib/mysql
      volumes:
        - name: mysql-follower
          persistentVolumeClaim:
            claimName: mysql-follower

This can be hard to grasp at first glance, what is happening here is:

in initContainers we’re going to try to clone the mysql using percona xtrabackup from the leader (thus the container with the ncat in our leader statefulset)
xtrabackup will create a file called xtrabackup_binlog_info and based on that file we will find transaction id so replica starts replicating from the correct point in the transaction history and then we will start replication. Thus the skip-replica-start definition - honestly speaking I’m not sure atm if you can get away with setting this to ON straight away as it’s been a while since I set it up - but give it a try and let me know!

If everything went right you should see in follower logs something like:

Replica receiver thread for channel ‘’: connected to source ‘follower@mysql:3306’ with server_uuid=7ee6b0b5-3370-11ee-bd46-be │ │ 0882b78b76, server_id=1. Starting GTID-based replication.