Location>code7788 >text

Read/Write Separation of MySQL in Kubernetes

Popularity：70 ℃/2024-11-08 11:37:50

Read/Write Separation of MySQL in Kubernetes

The separation of reads and writes for MySQL in Kubernetes can be achieved through a master-slave replication architecture. In this architecture, the MySQL Master handles all write operations and the MySQL Slave handles all read operations. Here is a detailed step-by-step guide:

Step 1: Create a Kubernetes Cluster

Make sure you have a well-running Kubernetes cluster, 3+ nodes are recommended for better resource allocation and high availability.

Step 2: Create a MySQL Master-Slave Replication Docker Image

  1. First, you need to build a MySQL mirror that supports master-slave replication, or just use an existing MySQL mirror that supports master-slave replication.

  2. If you want to configure it yourself, you can start with the official MySQL image and support master-slave replication through the settings file.

  3. The main configurations are as follows:

    • Master node configuration (Master): set server-id and enable binary logging (log-bin).

    • Slave Configuration (Slave): set a different server-id and configure it as a slave node.

Step 3: Create a Kubernetes Secret to Store MySQL Passwords

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: <base64-encoded root password>
  mysql-replication-user:: <base64-encoded replication username>.
  mysql-replication-password:: <base64-encoded replication password>.

Step 4: Deploying the MySQL Master Node

  1. Create a 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

  1. 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 MySQL Slave Nodes

  1. Creating configuration files for slave nodes：

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

  1. Create a Service for a MySQL slave node:

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

Step 6: Set Up Master-Slave Replication

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

  1. Log in to the master node and create the user to be used for replication:

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

  2. Get the master node status:

    SHOW MASTER STATUS;

  3. Log in to the slave node and configure it as a slave to the master node:

    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=<Position obtained in the previous step.>;
    START SLAVE;

  4. Check the slave node status to verify that the synchronization was successful:

    SHOW SLAVE STATUS\G

Step 7: Configure Read/Write Segregation

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

  1. Creating a MySQL Read/Write Separated 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

  2. Read-write separation is achieved by the application layer (e.g., application code) choosing to access different services:

    • Write operations: by means of themysql-read-write Service connection.

    • Read operation: bymysql-read-only Service connection.

Step 8: Test Read/Write Separation

  1. Send the write request to themysql-read-write service to verify that the data was written correctly.

  2. Send the read operation request to themysql-read-only service to ensure that data written by the master node can be read on the slave node.

Step 9: Monitoring and Maintenance

You can monitor your MySQL cluster with Prometheus and Grafana to keep an eye on the latency of the master-slave replication and the health of the nodes so that you can handle failures in a timely manner.

summarize

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

<Previous Next>