Detailed steps to implement MySQL read and write separation in Kubernetes

Implementing MySQL read and write separation in Kubernetes

Implementing MySQL read and write separation in Kubernetes can be achieved through the master-slave replication architecture. In this architecture, the MySQL master node (Master) handles all write operations, while the MySQL slave node (Slave) handles all read operations. Here is a detailed step guide:

Step 1: Create a Kubernetes cluster

Make sure you have a well-run Kubernetes cluster, and it is recommended to have more than 3 nodes to better allocate resources and achieve high availability.

Step 2: Create a MySQL master-slave copy Docker image

First, you need to build a MySQL image that supports master-slave replication, or directly use an existing MySQL image that supports master-slave replication.
If you want to configure it yourself, you can start with the MySQL official image and support master-slave replication by setting files.
The main configurations are as follows:
- Master node configuration (Master): Set server-id and enable binary log (log-bin).
- Slave configuration (Slave): Set different server-ids and configure them as slaves.

Step 3: Create a Kubernetes Secret Storage MySQL Password

For security, we can use Kubernetes Secret to store MySQL passwords.

apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
type: Opaque
data:
  mysql-root-password: &lt;base64Encodedrootpassword&gt;
  mysql-replication-user: &lt;base64Encodedreplicationusername&gt;
  mysql-replication-password: &lt;base64Encodedreplicationpassword&gt;

Step 4: Deploy the MySQL master node

Create the configuration file for the master node：

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-master
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
      role: master
  template:
    metadata:
      labels:
        app: mysql
        role: master
    spec:
      containers:
      - name: mysql
        image: mysql:5.7
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: mysql-root-password
        - name: MYSQL_REPLICATION_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: mysql-replication-user
        - name: MYSQL_REPLICATION_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: mysql-replication-password
        ports:
        - containerPort: 3306
        volumeMounts:
        - name: mysql-persistent-storage
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-persistent-storage
        persistentVolumeClaim:
          claimName: mysql-pv-claim

Create a Service for the MySQL master node:

apiVersion: v1
kind: Service
metadata:
  name: mysql-master
spec:
  ports:
  - port: 3306
    targetPort: 3306
  selector:
    app: mysql
    role: master

Step 5: Deploy the MySQL slave

Create a configuration file for a slave node：

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-slave
spec:
  replicas: 2
  selector:
    matchLabels:
      app: mysql
      role: slave
  template:
    metadata:
      labels:
        app: mysql
        role: slave
    spec:
      containers:
      - name: mysql
        image: mysql:5.7
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: mysql-root-password
        - name: MYSQL_REPLICATION_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: mysql-replication-user
        - name: MYSQL_REPLICATION_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: mysql-replication-password
        - name: MYSQL_MASTER_HOST
          value: "mysql-master"
        ports:
        - containerPort: 3306
        volumeMounts:
        - name: mysql-persistent-storage
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-persistent-storage
        persistentVolumeClaim:
          claimName: mysql-pv-claim

Create a MySQL slave service:

apiVersion: v1
kind: Service
metadata:
  name: mysql-slave
spec:
  ports:
  - port: 3306
    targetPort: 3306
  selector:
    app: mysql
    role: slave

Step 6: Set Master-slave Copy

After the slave node is started, execute the following command to configure master-slave replication:

CREATE USER 'replication'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

Get the master node status:

SHOW MASTER STATUS;

CHANGE MASTER TO
    MASTER_HOST='mysql-master',
    MASTER_USER='replication',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='<File obtained in the previous step>',
    MASTER_LOG_POS=&lt;Obtained in the previous step Position&gt;;
START SLAVE;

Check the slave node status to confirm that the synchronization is successful:

SHOW SLAVE STATUS\G

Step 7: Configure read and write separation

In Kubernetes, you can use a custom Service to achieve read and write separation:

Create a MySQL read-write separation service:

apiVersion: v1
kind: Service
metadata:
  name: mysql-read-write
spec:
  ports:
  - port: 3306
    targetPort: 3306
  selector:
    app: mysql
    role: master
---
apiVersion: v1
kind: Service
metadata:
  name: mysql-read-only
spec:
  ports:
  - port: 3306
    targetPort: 3306
  selector:
    app: mysql
    role: slave

Read and write separation through the application layer (such as application code) selection to access different services:
- Write operation: Bymysql-read-writeService connection.
- Read operation: Bymysql-read-onlyService connection.

Step 8: Test Read and Write Separation

Send a write operation request tomysql-read-writeService, verify that the data is written correctly.
Send read operation request tomysql-read-onlyService to ensure that the data written by the master node can be read from the slave node.

Step 9: Monitoring and Maintenance

The MySQL cluster can be monitored through Prometheus and Grafana, paying attention to the delay of master-slave replication and the health status of nodes in order to handle failures in a timely manner.

Summarize

The master node is responsible for handling write operations, and the slave node is responsible for handling read operations. Applications can connect to different services according to their needs to achieve efficient database read and write separation.

This is the end of this article about implementing MySQL reading and writing separation in Kubernetes. For more related content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!