Deploying MySQL master-slave in a kubernetes cluster

This article describes the deployment of MySQL master-slave cluster in a kubernetes environment, with data persistence using NFS.

First. Introduction to the Environment
MySQL Version: 5.7

Mysql Master node:
Host Name: VM1
IP Address: 192.168.115.5/24

Mysql slave node:
Host Name: VM2
IP Address: 192.168.115.6/24

NFS Node:
Host Name: VM2
IP Address: 192.168.115.6/24
Shared Directories:/home/mysql_master,/home/mysql_slave

Second, prepare MySQL master-slave mirroring environment
Dockerfile, docker-entrypoint.sh files are as follows
https://github.com/docker-library/mysql/tree/master/5.7
Since we are going to configure MySQL master and slave, we need to make a little change to the Dockerfile, docker-entrypoint.sh files, mainly in the MySQL master-slave configuration section.

Prepare the mirror for master
Copy Dockerfile, docker-entrypoint.sh for the build master image file.
Add the following to the dockerfile to set MySQL master's Server-id to 1

RUN sed -i ‘/\[mysqld\]/a server-id=1\nlog-bin‘ /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following in docker-entrypoint.sh, create a copy user and assign permissions, refresh the system permissions table

echo "CREATE USER ‘$MYSQL_REPLICATION_USER‘@‘%‘ IDENTIFIED BY ‘$MYSQL_REPLICATION_PASSWORD‘ ;" | "${mysql[@]}" 
echo "GRANT REPLICATION SLAVE ON *.* TO ‘$MYSQL_REPLICATION_USER‘@‘%‘ IDENTIFIED BY ‘$MYSQL_REPLICATION_PASSWORD‘ ;" | "${mysql[@]}" 
echo ‘FLUSH PRIVILEGES ;‘ | "${mysql[@]}"

Prepare the slave image
Copy Dockerfile, docker-entrypoint.sh for the build slave image file.
Add the following in Dockerfile to set the MySQL slave server-id to a random number

RUN RAND="$(date +%s | rev | cut -c 1-2)$(echo ${RANDOM})" && sed -i ‘/\[mysqld\]/a server-id=‘$RAND‘\nlog-bin‘ /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following in docker-entrypoint.sh, configure the host, user, password, and other parameters that connect to the master host, and start the replication process.

echo "STOP SLAVE;" | "${mysql[@]}" echo "CHANGE MASTER TO master_host=‘$MYSQL_MASTER_SERVICE_HOST‘, master_user=‘$MYSQL_REPLICATION_USER‘, master_password=‘$MYSQL_REPLICATION_PASSWORD‘ ;" | "${mysql[@]}" echo "START SLAVE;" | "${mysql[@]}"

Third, start using modified dockerfile to create MySQL master and slave images.

# cd /root/kubernetes/lnmp/mysql/Dockerfiles/Master
# docker build -t registry.fjhb.cn/mysql-master:0.1 .
# cd /root/kubernetes/lnmp/mysql/Dockerfiles/Slave
# docker build -t registry.fjhb.cn/mysql-slave:0.1 .
# docker push registry.fjhb.cn/mysql-master:0.1
# docker push registry.fjhb.cn/mysql-slave:0.1

Fourth. creating PV and PVC for MySQL master-slave storage persistence data
Create the directory on the NFS server

# cd /home/
# mkdir mysql_master mysql_slave

Two sets of PV and PVC are created from the Yaml file

# cd /root/kubernetes/lnmp/mysql/Storage
# cat nfs-pv-master.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
  name: pv-nfs-mysql-master
spec:
  capacity:
    storage: 5Gi 
  accessModes:
  - ReadWriteOnce
  nfs: 
    path: /home/mysql_master
    server: 192.168.115.6
  persistentVolumeReclaimPolicy: Recycle 

# cat nfs-pvc-master.yaml
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: pv-nfs-mysql-master
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 5Gi
# cat nfs-pv-slave.yaml 
apiVersion: v1
kind: PersistentVolume
metadata:
  name: pv-nfs-mysql-slave
spec:
  capacity:
    storage: 6Gi 
  accessModes:
  - ReadWriteOnce
  nfs: 
    path: /home/mysql_slave
    server: 192.168.115.6
  persistentVolumeReclaimPolicy: Recycle 

# cat nfs-pvc-slave.yaml 
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: pv-nfs-mysql-slave
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 6Gi

# kubectl create -f nfs-pv-master.yaml
# kubectl create -f nfs-pvc-master.yaml
# kubectl create -f nfs-pv-slave.yaml 
# kubectl create -f nfs-pvc-slave.yaml

Fifth. Create a MySQL master Replicationcontroller and services

based on a yaml file

# cat mysql-master-rc.yaml 
apiVersion: v1
kind: ReplicationController
metadata:
 name: mysql-master
 labels:
  name: mysql-master
spec:
  replicas: 1
  selector:
   name: mysql-master
  template:
   metadata:
    labels:
     name: mysql-master
   spec:
    containers:
    - name: mysql-master
      image: registry.fjhb.cn/mysql-master:0.1
      volumeMounts:
        - mountPath: /var/lib/mysql
          name: mysql-master-data
      ports:
      - containerPort: 3306
      env:
      - name: MYSQL_ROOT_PASSWORD
        value: "12345678"
      - name: MYSQL_REPLICATION_USER
        value: "repl"
      - name: MYSQL_REPLICAITON_PASSWORD
        value: "12345678"
    volumes:
     - name: mysql-master-data
       persistentVolumeClaim:
        claimName: pv-nfs-mysql-master

# cat mysql-master-svc.yaml 
apiVersion: v1
kind: Service
metadata:
  name: mysql-master
  labels:
   name: mysql-master
spec:
  type: NodePort
  ports:
  - port: 3306
    targetPort: 3306
    name: http
    nodePort: 30066
  selector:
    name: mysql-master

# kubectl create -f mysql-master-rc.yaml 
# kubectl create -f mysql-master-svc.yaml

Connect to master test using MySQL client

Sixth. create MySQL slave replicationcontroller and services

from YAML files

# cat mysql-slave-rc.yaml 
apiVersion: v1
kind: ReplicationController
metadata:
 name: mysql-slave
 labels:
  name: mysql-slave
spec:
  replicas: 1
  selector:
   name: mysql-slave
  template:
   metadata:
    labels:
     name: mysql-slave
   spec:
    containers:
    - name: mysql-slave
      image: registry.fjhb.cn/mysql-slave:0.1
      volumeMounts:
        - mountPath: /var/lib/mysql
          name: mysql-slave-data
      ports:
      - containerPort: 3306
      env:
      - name: MYSQL_ROOT_PASSWORD
        value: "12345678"
      - name: MYSQL_REPLICATION_USER
        value: "repl"
      - name: MYSQL_REPLICAITON_PASSWORD
        value: "12345678"
    volumes:
     - name: mysql-slave-data
       persistentVolumeClaim:
        claimName: pv-nfs-mysql-slave

# cat mysql-slave-svc.yaml 
apiVersion: v1
kind: Service
metadata:
  name: mysql-slave
  labels:
   name: mysql-slave
spec:
  type: NodePort
  ports:
  - port: 3306
    targetPort: 3306
    name: http
    nodePort: 30067
  selector:
    name: mysql-slave

# kubectl create -f mysql-slave-rc.yaml 
# kubectl create -f mysql-slave-svc.yaml

Seventh. Testing and troubleshooting
View the status of the replication through the MySQL command line connection slave, and discover that the status is connecting

Using the slave container to connect to master, found that the root account can be connected properly, and the use of REPL account is not properly connected, prompting access deny

Modify the REPL user password on master

mysql> alter user [email protected]‘%‘ identified by "12345678";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

After completing the above modifications on the slave to verify that the REPL account can be connected properly

The change master operation is performed on the slave because the Gtid parameter is not configured when the image is created, so the master_auto_position=1 parameter cannot be used here, and if it is to be turned on, it needs to modify dockerfile and regenerate the image.

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
CHANGE MASTER TO MASTER_HOST=‘mysql-master‘, MASTER_USER=‘repl‘, MASTER_PASSWORD=‘12345678‘;
mysql> start slave;

Reference Documentation:
Https://www.jianshu.com/p/509b65e9a4f5

Deploying MySQL master-slave in a kubernetes cluster