Deploying MySQL master-slave in k8s

1、 Deployment instructions

You can use kubesphere to quickly build MySQL environment.

• Stateful service extraction is configured as configmap
• Stateful services must use PVC to persist data
• The stable domain name provided by DNS is used for access within the service cluster

When k8s deploys stateful services, refer to the above figure.
Deployment details:

• Stateful service extraction is configured as configmap
  We used it beforeDocker deploying MySQLIn k8s, we will also mount the data such as conf, logs and data to the host computer. In k8s, there is a special space for managing configuration files, that is, configmap mentioned above. We can extract some commonly used configurations to make configmap configuration. Later, no matter whether MySQL is restarted or a new MySQL is created, the configuration in the same configmap can be used The first point is to extract the configuration of the stateful service into configmap, so that you only need to change the configmap to modify the configuration later
• Stateful services must use PVC to persist data
  Each MySQL uses its own data storage, so there is a special data storage space in k8s, which is the PVC mentioned above, Each MySQL will allocate a PVC data storage space or share a PVC space. That is, like the data directory mounted by docker, the data in MySQL will still exist after the MySQL container in docker is restarted. Then, after MySQL in k8s is hung and restarted, persistent data will be found in PVC. In this way, there will be no data loss caused by pulling up MySQL on other nodes The problem of loss
• DNS is used to provide stable domain name for service cluster access
  In the above figure, there is a master node MySQL and two slave nodes mysql. In this MySQL Cluster, nodes need to communicate with each other. To achieve communication and access between nodes, headless is required Service service, the service is mutual access between clusters. In k8s, the smallest deployment unit is pod. For example, mysql0 is a pod. Then we package this pod as a service, and let k8s generate a domain name for the service. DNS provides a stable domain name for this service. As shown in the figure, mysql-0.test domain name is provided for mysql0 service, and other pod pairs are provided After the exposed service, the corresponding domain name will also be provided, so that the communication of each node can use the domain name access. The advantage of providing the domain name here is to prevent the exposed service from suddenly hanging up and pulling up on other machines, then the IP will change. Then all applications in the cluster, including the service itself, can use the domain name to access

2、 Creating a master-slave stateful service

1. Creating MySQL master stateful service

Operation steps:
1) Basic information:

2) , container image:
Set the MySQL container image, MySQL:5.7 Version, the memory is set to 2G, and the environment variable uses the MySQL key set before.

3) , Mount storage:

So, you can open a separate key window to mount the fileConfiguration center > configuration, create configuration file:

① Create configuration basic information

② , setting MySQL configuration file
my.cnf

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
#master-slaver repication
server_id=1
log-bin=mysql-bin
read-only=0
binlog-do-db=gulimall_ums
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_oms
binlog-do-db=gulimall_sms
binlog-do-db=gulimall_wms
binlog-do-db=gulimall_admin
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

my.cnf The configuration content can be directly copied and pasted into the following text box. Kubesphere provides a visual interface and yaml editing mode. We can click Edit mode to see how yaml files are written.

mysql-master- cnf.yaml Editing mode:

apiVersion: v1
kind: ConfigMap
metadata:
  namespace: gulimall
  labels:
    app: mysql-master-cnf
  name: mysql-master-cnf
  annotations:
    kubesphere.io/alias -Name: master configuration
spec:
  template:
    metadata:
      labels:
        app: mysql-master-cnf
data:
  my.cnf: |-
    [client]
    default-character-set=utf8
    [mysql]
    default-character-set=utf8
    [mysqld]
    init_connect='SET collation_connection = utf8_unicode_ci'
    init_connect='SET NAMES utf8'
    character-set-server=utf8
    collation-server=utf8_unicode_ci
    skip-character-set-client-handshake
    skip-name-resolve
    #master-slaver repication
    server_id=1
    log-bin=mysql-bin
    read-only=0
    binlog-do-db=gulimall_ums
    binlog-do-db=gulimall_pms
    binlog-do-db=gulimall_oms
    binlog-do-db=gulimall_sms
    binlog-do-db=gulimall_wms
    binlog-do-db=gulimall_admin
    replicate-ignore-db=mysql
    replicate-ignore-db=sys
    replicate-ignore-db=information_schema
    replicate-ignore-db=performance_schema

After setting the configuration, click create to create it

4) Create storage PVC
Storage volume > create

① Basic information settings

② , storage volume settings

Since the storage volume of openebs was not created when kubesphere was installed, you can only select local. You should create openebs.

After the graphical interface is created, we can see the yaml of editing mode:

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  namespace: gulimall
  name: gulimall-mysql-master-pvc
  labels:
    app: gulimall-mysql-master-pvc
  annotations:
    kubesphere.io/alias -Name: main node PVC
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi
  template:
    metadata:
      labels:
        app: gulimall-mysql-master-pvc
  storageClassName: local

Then click create to create a good PVC:

5) , stateful service mount storage
After creating the configuration and PVC, we will continue to create the mount storage step of the stateful service, and then proceed to the following steps:
Configuration file mount:

Storage volume mount:

Then click Add to set the page:

Then click next,advanced settingKeep the default and click create. In addition, we can also look at the yaml file of edit mode:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: gulimall
  labels:
    app: gulimall-mysql-master
  name: gulimall-mysql-master-qpr2er
  annotations:
    kubesphere.io/alias -Name: MySQL master node
spec:
  replicas: 1
  selector:
    matchLabels:
      app: gulimall-mysql-master
  template:
    metadata:
      labels:
        app: gulimall-mysql-master
      annotations:
        kubesphere.io/containerSecrets: null
        logging.kubesphere.io/logsidecar-config: '{}'
    spec:
      containers:
        - name: container-jbrfx9
          type: worker
          imagePullPolicy: IfNotPresent
          resources:
            requests:
              cpu: '0.01'
              memory: 10Mi
            limits:
              cpu: '0.98'
              memory: 1700Mi
          image: 'mysql:5.7'
          ports:
            - name: tcp-3306
              protocol: TCP
              containerPort: 3306
              servicePort: 3306
            - name: tcp-33060
              protocol: TCP
              containerPort: 33060
              servicePort: 33060
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secret
                  key: MYSQL_ROOT_PASSWORD
          volumeMounts:
            - name: volume-vvijno
              readOnly: false
              mountPath: /etc/mysql
            - name: volume-dddvwk
              readOnly: false
              mountPath: /var/lib/mysql
      serviceAccount: default
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 100
              podAffinityTerm:
                labelSelector:
                  matchLabels:
                    app: gulimall-mysql-master
                topologyKey: kubernetes.io/hostname
      initContainers: []
      imagePullSecrets: null
      volumes:
        - name: volume-vvijno
          configMap:
            name: mysql-master-cnf
            items:
              - key: my.cnf
                path: my.cnf
        - name: volume-dddvwk
          persistentVolumeClaim:
            claimName: gulimall-mysql-master-pvc
  updateStrategy:
    type: RollingUpdate
    rollingUpdate:
      partition: 0
  serviceName: gulimall-mysql-master
---
apiVersion: v1
kind: Service
metadata:
  namespace: gulimall
  labels:
    app: gulimall-mysql-master
  annotations:
    kubesphere.io/serviceType: statefulservice
    kubesphere.io/alias -Name: MySQL master node
  name: gulimall-mysql-master
spec:
  sessionAffinity: ClientIP
  selector:
    app: gulimall-mysql-master
  ports:
    - name: tcp-3306
      protocol: TCP
      port: 3306
      targetPort: 3306
    - name: tcp-33060
      protocol: TCP
      port: 33060
      targetPort: 33060
  clusterIP: None
  sessionAffinityConfig:
    clientIP:
      timeoutSeconds: 10800

Create a good page:

2. Creating MySQL slave stateful service

You can refer to the service creation steps on the master.
① Create a slave configuration mount file
mysql-slaver-cnf
my.cnf

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
#master-slaver repication
server_id=2
log-bin=mysql-bin
read-only=1
binlog-do-db=gulimall_ums
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_oms
binlog-do-db=gulimall_wms
binlog-do-db=gulimall_admin

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

② Create storage volume PVC
③ Create a slave stateful service

Created from node:

3、 Configure master slave

On the top, we have created master-slave services through k8s, while on the bottom, we synchronize master-slave services through settings.

1. Main database settings

Enter the master container terminal:

#1. Enter mysql
>  mysql -uroot -p123456

#2. Authorize root to access remotely (master-slave is irrelevant, in order to facilitate us to connect to MySQL remotely)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#3. Add users for synchronization
mysql> GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

#4. View the master status
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 889
     Binlog_Do_DB: gulimall_ums,gulimall_pms,gulimall_oms,gulimall_sms,gulimall_wms,gulimall_admin
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

After setting, record the file filemysql-bin.000003From the library synchronization needs to be used, and then exit exit.

2. Set from library

Enter the slave container terminal:

#Enter MySQL
mysql -uroot -p123456

#Setting the connection between the main database and the main database DNS: guilimall MySQL- master.gulimall
change master to master_host='gulimall-mysql-master.gulimall',master_user='backup',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=0,master_port=3306;

#Start synchronization from library
start slave;

#View slave status
show slave status\G;

An error was reported from the Library:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: gulimall-mysql-master.gulimall
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 4
               Relay_Log_File: gulimall-mysql-slaver-5mehjw-0-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema
  Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:

resolvent:

stop slave;
reset slave;
start slave;

Then print the slave synchronization status:

You can see that it has been set successfully.

3. Test whether the master-slave is synchronized

The master database creates a database:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> CREATE DATABASE `gulimall_oms` DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gulimall_oms       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Enter slave Library:

You can see that the slave database has also synchronized thegulimall_omsSo far, our master-slave service on k8s has been created.

---

Related articles:
Digtime.cn -Deploy-92-k8s deploy MySQL master slave
K8s deployed stateful service
Kubernetes (k8s) stateful cluster service deployment and management