一文搞定！在 Kubernetes 集群上部署主从复制 MySQL

kind: ConfigMap
apiVersion: v1
metadata:
  name: mysql-source-cnf
data:
  mysqld.cnf: |-
    [mysqld]
    # performance settings
    lock_wait_timeout = 3600
    open_files_limit = 65535
    back_log = 1024
    max_connections = 2048
    max_connect_errors = 1000000
    table_open_cache = 1024
    table_definition_cache = 1024
    thread_stack = 512K
    sort_buffer_size = 4M
    join_buffer_size = 4M
    read_buffer_size = 8M
    read_rnd_buffer_size = 4M
    bulk_insert_buffer_size = 64M
    thread_cache_size = 768
    interactive_timeout = 600
    wait_timeout = 600
    tmp_table_size = 32M
    max_heap_table_size = 32M
    innodb_open_files = 1024

    # TLS 配置
    tls_version = TLSv1.2

    # Replication settings
    server_id = 1
    log_bin = mysql-bin
    log_bin_index = mysql-bin.index
    binlog_format = row

kind: ConfigMap
apiVersion: v1
metadata:
  name: mysql-replica-cnf
  namespace: default
  annotations:
    kubesphere.io/creator: admin
data:
  mysqld.cnf: |-
    [mysqld]
    # performance settings
    lock_wait_timeout = 3600
    open_files_limit = 65535
    back_log = 1024
    max_connections = 2048
    max_connect_errors = 1000000
    table_open_cache = 1024
    table_definition_cache = 1024
    thread_stack = 512K
    sort_buffer_size = 4M
    join_buffer_size = 4M
    read_buffer_size = 8M
    read_rnd_buffer_size = 4M
    bulk_insert_buffer_size = 64M
    thread_cache_size = 768
    interactive_timeout = 600
    wait_timeout = 600
    tmp_table_size = 32M
    max_heap_table_size = 32M
    innodb_open_files = 1024

    # TLS 配置
    tls_version = TLSv1.2

    # Replication settings
    server_id = 2
    log_bin = mysql-bin
    log_bin_index = mysql-bin.index
    binlog_format = row
    relay-log = mysql-relay-bin
    relay-log-index = mysql-relay-bin.index
    skip_slave_start = 1
    log_slave_updates = 1
    read_only = 1

$ kubectl apply -f mysql-source-cnf.yaml
$ kubectl apply -f mysql-replica-cnf.yaml

kind: Secret
apiVersion: v1
metadata:
  name: mysql-secret
data:
  MYSQL_ROOT_PASSWORD: T3BzWGxhYjIwMjQh
type: Opaque

$ kubectl apply -f mysql-secret.yaml

kind: Service
apiVersion: v1
metadata:
  name: mysql-source-headless
  labels:
    app: mysql-source
spec:
  ports:
    - name: tcp-3306
      protocol: TCP
      port: 3306
      targetPort: 3306
  selector:
    app: mysql-source
  clusterIP: None
  type: ClusterIP

$ kubectl apply -f mysql-source-svc.yaml

kind: Service
apiVersion: v1
metadata:
  name: mysql-replica-headless
  labels:
    app: mysql-replica
spec:
  ports:
    - name: tcp-3306
      protocol: TCP
      port: 3306
      targetPort: 3306
  selector:
    app: mysql-replica
  clusterIP: None
  type: ClusterIP

$ kubectl apply -f mysql-replica-svc.yaml

apiVersion: v1
kind: Service
metadata:
  name: mysql-source-external
spec:
  type: NodePort
  selector:
    app: mysql-source
  ports:
  - port: 3306
    targetPort: 3306
    nodePort: 31306

$ kubectl apply -f mysql-external-svc.yaml

kind: StatefulSet
apiVersion: apps/v1
metadata:
  name: mysql-source
  labels:
    app: mysql-source
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql-source
  template:
    metadata:
      labels:
        app: mysql-source
    spec:
      volumes:
        - name: host-time
          hostPath:
            path: /etc/localtime
            type: ''
        - name: config
          configMap:
            name: mysql-source-cnf
            defaultMode: 420
      containers:
        - name: mysql-source
          image: mysql:5.7.44
          ports:
            - containerPort: 3306
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secret
                  key: MYSQL_ROOT_PASSWORD
          resources:
            limits:
              cpu: '2'
              memory: 4Gi
            requests:
              cpu: 100m
              memory: 100Mi
          volumeMounts:
            - name: host-time
              mountPath: /etc/localtime
            - name: data
              mountPath: /var/lib/mysql
            - name: config
              readOnly: true
              mountPath: /etc/mysql/conf.d/
          imagePullPolicy: IfNotPresent
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        accessModes: ["ReadWriteOnce"]
        resources:
          requests:
            storage: 10Gi
        storageClassName: nfs-sc
        volumeMode: Filesystem
  serviceName: mysql-source-headless

$ kubectl apply -f mysql-source-sts.yaml

kind: StatefulSet
apiVersion: apps/v1
metadata:
  name: mysql-replica
  labels:
    app: mysql-replica
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql-replica
  template:
    metadata:
      labels:
        app: mysql-replica
    spec:
      volumes:
        - name: host-time
          hostPath:
            path: /etc/localtime
            type: ''
        - name: config
          configMap:
            name: mysql-replica-cnf
            defaultMode: 420
      containers:
        - name: mysql-replica
          image: mysql:5.7.44
          ports:
            - containerPort: 3306
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secret
                  key: MYSQL_ROOT_PASSWORD
          resources:
            limits:
              cpu: '2'
              memory: 4Gi
            requests:
              cpu: 100m
              memory: 100Mi
          volumeMounts:
            - name: host-time
              mountPath: /etc/localtime
            - name: data
              mountPath: /var/lib/mysql
            - name: config
              readOnly: true
              mountPath: /etc/mysql/conf.d/
          imagePullPolicy: IfNotPresent
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        accessModes: ["ReadWriteOnce"]
        resources:
          requests:
            storage: 10Gi
        storageClassName: nfs-sc
        volumeMode: Filesystem
  serviceName: mysql-replica-headless

$ kubectl apply -f mysql-replica-sts.yaml

$ kubectl get pods -o wide
NAME              READY   STATUS    RESTARTS   AGE   IP              NODE           NOMINATED NODE   READINESS GATES
mysql-replica-0   1/1     Running   0          8s    10.233.94.20    ksp-worker-1   <none>           <none>
mysql-source-0    1/1     Running   0          12s   10.233.96.173   ksp-worker-3   <none>           <none>

$ kubectl exec -it mysql-source-0 -- mysql -u root -p -e "show variables like '%max_conn%';"
Enter password:
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_connect_errors | 1000000 |
| max_connections    | 2048    |
+--------------------+---------+

$ kubectl exec -it mysql-source-0 -- mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

-- 创建用户并设置密码
mysql> CREATE USER 'repuser'@'%' IDENTIFIED BY 'ChangeMe';
Query OK, 0 rows affected (0.01 sec)

-- 赋予权限
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repuser'@'%';
Query OK, 0 rows affected (0.01 sec)

-- 刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      773 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

$ kubectl exec -it mysql-replica-0 -- mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> change master to master_host='mysql-source-headless.default.svc.cluster.local',master_port=3306,master_user='repuser',master_password='ChangeMe',master_log_file='mysql_bin.000003',master_log_pos=773;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

# 查看从从库状态
mysql> show slave status\G;

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-source-headless.default.svc.cluster.local
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 773
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 986
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes    # 重点注意
            Slave_SQL_Running: Yes    # 重点注意
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 773
              Relay_Log_Space: 2949023
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 95818d7b-7baf-11ef-819e-1af693721830
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates    # 重点注意
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql> create database opsxlab;
Query OK, 1 row affected (0.01 sec)

# 切换数据库
mysql> use opsxlab;
Database changed

# 创建表
mysql> create table member(name varchar(10), phone varchar(11));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into member(name, phone) values ("opsxlab", "18888888888");
Query OK, 1 row affected (0.02 sec)

# 查看表
mysql> show tables;
+-------------------+
| Tables_in_opsxlab |
+-------------------+
| member            |
+-------------------+
1 row in set (0.00 sec)

# 查看表内数据
mysql> select * from member;
+---------+-------------+
| name    | phone       |
+---------+-------------+
| opsxlab | 18888888888 |
+---------+-------------+
1 row in set (0.00 sec)

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

# 切换数据库
mysql> use opsxlab;
Database changed

# 查看表
mysql> show tables;
+-------------------+
| Tables_in_opsxlab |
+-------------------+
| member            |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from member;
+---------+-------------+
| name    | phone       |
+---------+-------------+
| opsxlab | 18888888888 |
+---------+-------------+
1 row in set (0.00 sec)