Implementing mysql master and backup in k8s

Article directory

• 1. Implementation of mysql master and backup in k8s
• • 1.1 Environmental information
  • 1.2 Deploy nfs-provisioner
  • • 1.2.1 Install nfs
    • 1.2.2 Deploy nfs-provisioner
  • 1.3 Install mysql
  • 1.4 Check whether synchronization occurs on the standby database

1. Implementation of mysql master and backup in k8s

1.1 Environmental information

th>

Machine	Operating system	ip	mysql version	k8s version	storageClass
master1	CentOS7.8	192.168.0.20	mysql5.7.42	1.27.1	nfs
node1	CentOS7. 8	192.168.0.21	mysql5.7.42	1.27.1	nfs

1.2 Deploy nfs-provisioner

Description:
Use statefulSet to deploy dual-machine MySQL, so storageClass needs to be provided, and nfs-provisioner is used here.

1.2.1 Install nfs

Here nfs is installed on node1 node
mkdir /mnt/nfs & amp; & amp; sh nfs_install.sh /mnt/nfs 192.168.0.0/24

nfs_install.sh

#!/bin/bash

### How to install it? ###
### Installing nfs-server requires two parameters: 1. Mount point 2. Network segment that allows access to nfs-server ###

### How to use it? ###
### Client node `yum -y install nfs-utils rpcbind`, and then mount the nfs-server directory to the local ###
### For example: echo "192.168.0.20:/mnt/data01 /mnt/data01 nfs defaults 0 0" >> /etc/fstab & amp; & amp; mount -a ###

mount_point=$1
subnet=$2

function nfs_server() {<!-- -->
  systemctl stop firewalld
  systemctl disable firewalld
  setenforce 0
  sed -i 's/^SELINUX.*/SELINUX\=disabled/' /etc/selinux/config
  yum -y install nfs-utils rpcbind
  mkdir -p $mount_point
  echo "$mount_point ${subnet}(rw,sync,no_root_squash)" >> /etc/exports
  systemctl start rpcbind & amp; & amp; systemctl enable rpcbind
  systemctl restart nfs-server & amp; & amp; systemctl enable nfs-server
  chown -R nfsnobody:nfsnobody $mount_point
}

function usage() {<!-- -->
echo "Require 2 argument: [mount_point] [subnet]
eg: sh $0 /mnt/data01 192.168.10.0/24"
}

declare -i arg_nums
arg_nums=$#
if [ $arg_nums -eq 2 ];then
  nfs_server
else
  usage
  exit 1
fi

1.2.2 Deploy nfs-provisioner

Execute kubectl create namespace devops & amp; & amp; kubectl apply -f nfs-provisioner.yaml on the master1 node

nfs-provisioner.yaml

apiVersion: v1
kind: ServiceAccount
metadata:
  name: nfs-provisioner
  namespace: devops
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
  name: nfs-provisioner-runner
rules:
- apiGroups: [""]
  resources: ["persistentvolumes"]
  verbs: ["get", "list", "watch", "create", "delete"]
- apiGroups: [""]
  resources: ["persistentvolumeclaims"]
  verbs: ["get", "list", "watch", "update"]
- apiGroups: ["storage.k8s.io"]
  resources: ["storageclasses"]
  verbs: ["get", "list", "watch"]
- apiGroups: [""]
  resources: ["events"]
  verbs: ["create", "update", "patch"]
- apiGroups: [""]
  resources: ["services", "endpoints"]
  verbs: ["get"]
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata:
  name: run-nfs-provisioner
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: ClusterRole
  name: nfs-provisioner-runner
subjects:
- kind: ServiceAccount
  name: nfs-provisioner
  namespace: devops
---
apiVersion: rbac.authorization.k8s.io/v1
kind: Role
metadata:
  name: leader-locking-nfs-provisioner
  namespace: devops
rules:
- apiGroups: [""]
  resources: ["endpoints"]
  verbs: ["get", "list", "watch", "create", "update", "patch"]
---
apiVersion: rbac.authorization.k8s.io/v1
kind: RoleBinding
metadata:
  name: leader-locking-nfs-provisioner
  namespace: devops
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: Role
  name: leader-locking-nfs-provisioner
subjects:
- kind: ServiceAccount
  name: nfs-provisioner
  namespace: devops
---
apiVersion: apps/v1
Kind: Deployment
metadata:
  name: nfs-provisioner
  namespace: devops
spec:
  selector:
    matchLabels:
       app: nfs-provisioner
  replicas: 1
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: nfs-provisioner
    spec:
      serviceAccountName: nfs-provisioner
      containers:
        - name: nfs-provisioner
          image: docker.io/gmoney23/nfs-client-provisioner:latest
          volumeMounts:
            - name: nfs-client-root
              mountPath: /persistentvolumes
          env:
            - name: PROVISIONER_NAME
              value: example.com/nfs
            - name: NFS_SERVER
              value: 192.168.0.21
            - name: NFS_PATH
              value: /mnt/nfs
      volumes:
        - name: nfs-client-root
          nfs:
            server: 192.168.0.21
            path: /mnt/nfs
---
kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: nfs
provisioner: example.com/nfs
#reclaimPolicy: Retain

1.3 Install mysql

kubectl apply -f deploy.yaml

deploy.yaml

apiVersion: v1
kind: Namespace
metadata:
  name: mysql
  labels:
    app: mysql

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql
  namespace: mysql
  labels:
    app: mysql
data:
  master.cnf: |
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    max_connections=2000
    default-time_zone=' + 8:00'
    character-set-server=utf8mb4
    collation-server=utf8mb4_unicode_ci
    innodb_buffer_pool_size=536870912
    datadir=/var/lib/mysql
    pid-file=/var/run/mysqld/mysqld.pid
    log-error=/var/lib/mysql/error.log
    log-bin=mysqllog
    skip-name-resolve
    lower-case-table-names=1
    log_bin_trust_function_creators=1
  slave.cnf: |
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    max_connections=2000
    default-time_zone=' + 8:00'
    character-set-server=utf8mb4
    collation-server=utf8mb4_unicode_ci
    innodb_buffer_pool_size=536870912
    datadir=/var/lib/mysql
    pid-file=/var/run/mysqld/mysqld.pid
    log-error=/var/lib/mysql/error.log
    super-read-only
    skip-name-resolve
    log-bin=mysql-bin
    lower-case-table-names=1
    log_bin_trust_function_creators=1

---
apiVersion: v1
Kind: Secret
metadata:
  name: mysql-secret
  namespace: mysql
  labels:
    app: mysql
type: Opaque
data:
  password: TnNiZzExMTEqQCE= # Nsbg1111*@!
  replicationUser: Y29weQ== #copy
  replicationPassword: TnNiZzExMTEqQCE= #Nsbg1111*@!

---
apiVersion: v1
Kind: Service
metadata:
  name: mysql
  namespace: mysql
  labels:
    app: mysql
spec:
  selector:
    app: mysql
  clusterIP: None
  ports:
  - name: mysql
    port: 3306

---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  namespace: mysql
  labels:
    app: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  serviceName: mysql
  replicas: 2
  template:
    metadata:
      labels:
        app: mysql
    spec:
      initContainers:
      - name: init-mysql
        image: docker.io/library/mysql:5.7.42
        command:
        - bash
        - "-c"
        - |
          set-ex
          #Get the serial number from the hostname of the pod through regular expressions. If it is not intercepted, exit the program.
          ordinal=`cat /etc/hostname | awk -F"-" '{print $2}'` || exit 1
          #Input the serverId into the corresponding configuration file. The path can be arbitrary (just match it later), but the file name cannot be changed.
          echo [mysqld] > /etc/mysql/conf.d/server-id.cnf
          # Since server-id cannot be 0, add 100 to the ID to avoid it.
          server_id=$((100 + $ordinal))
          echo "server-id=$server_id" >> /etc/mysql/conf.d/server-id.cnf
          if [[ ${ordinal} -eq 0 ]]; then
            # If the serial number of the Pod is 0, it means it is the Master node. Copy the Master configuration file from the ConfigMap to the /mnt/conf.d directory.
            cp /mnt/config-map/master.cnf /etc/mysql/conf.d
          else
            # Otherwise, copy the Slave configuration file in ConfigMap
            cp /mnt/config-map/slave.cnf /etc/mysql/conf.d
          fi
          echo "ending..."
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        - name: MYSQL_REPLICATION_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: replicationUser
        - name: MYSQL_REPLICATION_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: replicationPassword
        volumeMounts:
        - name: conf
          mountPath: /etc/mysql/conf.d
        - name: config-map
          mountPath: /mnt/config-map
      containers:
      - name: mysql
        image: docker.io/library/mysql:5.7.42
        life cycle:
         postStart:
          exec:
            command:
            - bash
            - "-c"
            - |
              set-ex
              cd /var/lib/mysql
              #Check whether there is a file named mysqlInitOk, an identification file produced by ourselves to prevent repeated initialization of the cluster
              if [ ! -f mysqlInitOk ]; then
                echo "Waiting for mysqld to be ready (accepting connections)"
                #Execute a mysql command to check whether mysql has been initialized. If not, execute it repeatedly until it can run.
                  #until mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "use mysql;SELECT 1;"; do sleep 1; done
                  sleep 5s
                  echo "Initialize ready"
                  #Determine whether it is master or slave
                  pod_seq=`cat /etc/hostname | awk -F"-" '{print $2}'`
                  if [ $pod_seq -eq 0 ];then
                    #Create master-slave account
                  mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "create user '${MYSQL_REPLICATION_USER}'@'%' identified by '${MYSQL_REPLICATION_PASSWORD}';"
                  #Setting permissions
                  mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "grant replication slave on *.* to '${MYSQL_REPLICATION_USER}'@'%' with grant option;"
                  #Refresh configuration
                  mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "flush privileges;"
                  #Initialize master
                  mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "reset master;"
                else
                  #Set the master for slave connection
                  #mysql-0.mysql.mysql origin {pod-name}.{service-name}.{namespace}
                  mysql -e \
                  "change master to master_host='mysql-0.mysql.mysql',master_port=3306, \
                  master_user='${MYSQL_REPLICATION_USER}',master_password='${MYSQL_REPLICATION_PASSWORD}', \
                  master_log_file='mysqllog.000001',master_log_pos=154;"
                  #resetslave
                  mysql -e "reset slave;"
                  #Start synchronization
                  mysql -e "start slave;"
                  #Change to read-only mode
                  mysql -e "set global read_only=1;"
                fi
                #Create an identification file after running to prevent repeated initialization of the cluster
                touch mysqlInitOk
              fi
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        - name: MYSQL_REPLICATION_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: replicationUser
        - name: MYSQL_REPLICATION_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: replicationPassword
        ports:
        - name: mysql
          containerPort: 3306
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
        - name: run-mysql
          mountPath: /var/run/mysql
        resources:
          requests:
            cpu: 500m
            memory: 2Gi
        #Set survival probe
        livenessProbe:
          exec:
            command: ["mysqladmin", "ping", "-uroot", "-p${MYSQL_ROOT_PASSWORD}"]
          initialDelaySeconds: 30
          periodSeconds: 10
          timeoutSeconds: 5
        #Set readiness probe
        readinessProbe:
          exec:
            command: ["mysqladmin", "ping", "-uroot", "-p${MYSQL_ROOT_PASSWORD}"]
          initialDelaySeconds: 5
          periodSeconds: 10
          timeoutSeconds: 1
      volumes:
      - name: config-map
        configMap:
          name: mysql
  volumeClaimTemplates:
  - metadata:
      name:data
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: nfs
      resources:
        requests:
          Storage: 5Gi
  - metadata:
      name: conf
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: nfs
      resources:
        requests:
          Storage: 100Mi
  - metadata:
      name: run-mysql
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: nfs
      resources:
        requests:
          Storage: 100Mi

1.4 Check whether the standby database is synchronized