K8s高效部署MySQL主从集群指南

最新推荐文章于 2025-11-11 11:25:41 发布

原创最新推荐文章于 2025-11-11 11:25:41 发布 · 1.1k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#kubernetes #mysql #adb

k8s v1.28 部署mysql 一主一从

参考网址：

参考文档:基础思路都是这个文档里面的，笔者根据自己的情况进行了修改

1. 环境准备

k8s服务器

ip	角色	系统	主机名	cpu	mem
192.168.133.133	master	centos8	master	2	8
192.168.133.131	node1	centos8	k8s-node1	2	8
192.168.133.128	node1	centos8	k8s-node2	2	8

nfs服务器

ip	角色	系统	主机名	cpu	mem	用途
192.168.133.133	master	centos8	master	2	8	k8s主节点兼 nfs服务器
192.168.133.131	node1	centos8	k8s-node1	2	8	工作节点
192.168.133.128	node1	centos8	k8s-node2	2	8	工作节点

2. 安装nfs 服务（具体看笔者的nfs安装文档）

3. 部署方案

3.1. 部署架构图

请添加图片描述

3.2. 部署方案架构图

数据库配置文件my.conf抽取为ConfigMap
数据库初始化密码抽取为Secret
- MySQL 部署初始化时可以提供 root 用户的密码，本方案使用 Secret，且主从节点使用相同的配置。
使用StatefulSet创建MySQL服务
- 部署mysql不使用Deployment,使用StatefulSet
使用PVC持久化数据
- 使用 StatefulSet 的 volumeClaimTemplates 功能动态创建 PVC 存储卷声明。
集群使用DNS提供稳定的域名
- 主从节点分别创建 Headless Service 服务，主从同步时使用 Headless Service 服务的 DNS 域名。
应用访问设置
- k8s 集群内的应用，访问 MySQL 主节点的 Headless Service 对应的 DNS 域名，集群外的应用通过 mysql-source-external 服务的 NodePort 端口访问 MySQL 主节点。

4. 部署mysql

4.1. pv卷配置

rm -rf mysql-pv.yaml
cat > mysql-pv.yaml<<EOF
 
# ========== PV基础定义 ==========
apiVersion: v1
kind: PersistentVolume
metadata:
# PV资源名称，集群内唯一标识
  name: mysql-nfs-pv
# ========== 核心规格配置 ==========
spec:
  # 存储类名称，需与StorageClass的metadata.name匹配
  storageClassName: mysql-nfs-storage
  
  # 存储容量配置（注意单位需使用Mi/Gi等标准格式）
  capacity:
  # 实际容量需小于NFS服务器可用空间
    storage: 500M
  # 访问模式：支持多节点同时读写
  accessModes:
  # 适用于NFS/Ceph等共享存储
    - ReadWriteMany
  # ========== NFS专属配置 ==========
  nfs:
  # NFS服务器IP地址
    server: 192.168.133.133
    path: "/data/nfs/mysql"
EOF
kubectl apply -f mysql-pv.yaml

4.1.1 补充SSL(自签名证书测试环境)

mysql8.0 链接默认使用ssl/tls的方式
生成证书

# 创建临时目录
mkdir ssl-certs && cd ssl-certs

# 生成 CA 私钥和证书
openssl genrsa -out ca-key.pem 2048
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem \
  -subj "/CN=MySQL Cluster CA"

# 生成服务器证书（主库）
openssl req -newkey rsa:2048 -nodes -days 3650 -keyout server-key.pem -out server-req.pem \
  -subj "/CN=mysql-source-headless.mysql-cluster.svc.cluster.local"
openssl x509 -req -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -in server-req.pem -out server-cert.pem

# 生成客户端证书（从库）
openssl req -newkey rsa:2048 -nodes -days 3650 -keyout client-key.pem -out client-req.pem \
  -subj "/CN=mysql-replica"
openssl x509 -req -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 02 -in client-req.pem -out client-cert.pem

# 验证证书
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

4.2. ConfigMap配置

主节点配置

rm -rf mysql-source-cnf.yaml
cat > mysql-source-cnf.yaml<<EOF
kind: ConfigMap
apiVersion: v1
metadata:
  name: mysql-source-cnf
  namespace: mysql-cluster
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
    # SSL 配置
    ssl-ca = /etc/mysql/ssl/ca.pem
    ssl-cert = /etc/mysql/ssl/server-cert.pem
    ssl-key = /etc/mysql/ssl/server-key.pem
    # 强制复制用户使用 SSL
    require_secure_transport = ON
EOF
kubectl apply -f mysql-source-cnf.yaml

从节点ConfigMap配置

rm -rf mysql-replica-cnf.yaml
cat > mysql-replica-cnf.yaml<<EOF
kind: ConfigMap
apiVersion: v1
metadata:
  name: mysql-replica-cnf
  namespace: mysql-cluster
  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
    # SSL 配置
    ssl-ca = /etc/mysql/ssl/ca.pem
    ssl-cert = /etc/mysql/ssl/server-cert.pem
    ssl-key = /etc/mysql/ssl/server-key.pem
EOF
kubectl apply -f mysql-replica-cnf.yaml

4.3. 创建Secret

我们创建一个 Secret 用来存储 MySQL root 用户的密码，主从使用相同的 Secret 配置。

运行 echo -n "mysqlAdmin" | base64 -w0 命令生成 base64 编码的密码。

echo -n "mysqlAdmin" | base64 -w0
# 返回:bXlzcWxBZG1pbg==

rm -rf mysql-secret.yaml
cat >mysql-secret.yaml<<EOF
kind: Secret
apiVersion: v1
metadata:
  name: mysql-secret
  namespace: mysql-cluster
data:
  MYSQL_ROOT_PASSWORD: bXlzcWxBZG1pbg==
type: Opaque
EOF
kubectl apply -f mysql-secret.yaml

# 创建证书
kubectl create secret generic mysql-ssl-certs \
  -n mysql-cluster \
  --from-file=/root/mysql/ssl-certs/ca.pem \
  --from-file=/root/mysql/ssl-certs/server-cert.pem \
  --from-file=/root/mysql/ssl-certs/server-key.pem \
  --from-file=/root/mysql/ssl-certs/client-cert.pem \
  --from-file=/root/mysql/ssl-certs/client-key.pem

4.4. 创建Service

主节点headless服务

rm -rf mysql-source-svc.yaml
cat > mysql-source-svc.yaml<<EOF
kind: Service
apiVersion: v1
metadata:
  name: mysql-source-headless
  namespace: mysql-cluster
  labels:
    app: mysql-source
spec:
  ports:
    - name: mysql-port
      protocol: TCP
      port: 3306
      targetPort: 3306
  selector:
    app: mysql-source
    appCluster: mysql-cluster
  clusterIP: None
EOF
kubectl apply -f mysql-source-svc.yaml

从节点headless服务

rm -rf mysql-replica-svc.yaml
cat > mysql-replica-svc.yaml<<EOF
kind: Service
apiVersion: v1
metadata:
  name: mysql-replica-headless
  namespace: mysql-cluster
  labels:
    app: mysql-replica
spec:
  ports:
    - name: mysql-port
      protocol: TCP
      port: 3306
      targetPort: 3306
  selector:
    app: mysql-replica
  clusterIP: None
EOF
kubectl apply -f mysql-replica-svc.yaml

4.5. 外部访问服务NodePort

使用NodePort 方式发布 k8s 集群上的 MySQL 服务给外部应用访问，指定的端口为 30036。

rm -rf mysql-nodeport-svc.yaml
cat > mysql-nodeport-svc.yaml<<EOF
apiVersion: v1
kind: Service
metadata:
  name: mysql-source-external
  namespace: mysql-cluster
spec:
  type: NodePort
  selector:
    app: mysql-source
  ports:
  - port: 3306
    targetPort: 3306
    # port 可选 30000-32767
    nodePort: 30036
EOF
kubectl apply -f mysql-nodeport-svc.yaml

4.6. 创建有状态服务 StatefulSet

为什么不选择无状态服务(Deployment)呢?

稳定的网络身份：StatefulSet 为每个 Pod 分配了一个持久且唯一的网络标识符，这对于 MySQL 这类需要固定主机名或网络地址以维持主从复制关系的数据库服务至关重要。
持久化存储：StatefulSet 易于与持久化存储卷结合使用，确保数据库数据的持久保存，即便是在 Pod 重启或重新调度后。
适合有状态应用：StatefulSet 是为有状态应用设计的，如数据库和消息队列，它提供了必要的支持来维护这些应用的状态。

主节点StatefulSet

rm -rf mysql-source-statefulset-svc.yaml
cat > mysql-source-statefulset-svc.yaml<<EOF

# StatefulSet API版本及类型声明
kind: StatefulSet
apiVersion: apps/v1

# 元数据定义
metadata:
  # StatefulSet名称
  name: mysql-source
  # 所属命名空间
  namespace: mysql-cluster
  labels:
    # 用于服务发现的标签
    app: mysql-source

# 规格定义
spec:
  # Headless Service名称（用于DNS解析）
  serviceName: mysql-source-headless
  # 副本数（单节点部署）
  replicas: 1
  selector:
    matchLabels:
      # 必须与template.metadata.labels匹配
      app: mysql-source
      # 集群标识标签
      appCluster: mysql-cluster
  # Pod模板
  template:
    metadata:
      labels:
        # Pod标签（需与selector匹配）
        app: mysql-source
        appCluster: mysql-cluster
    spec:
      # 优雅终止等待时间
      terminationGracePeriodSeconds: 20
      # 反亲和性配置（避免Pod部署到同一节点）
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 100
            podAffinityTerm:
              labelSelector:
                matchExpressions:
                - key: app
                  operator: In
                  values: ["mysql"]
              topologyKey: kubernetes.io/hostname
      # 卷定义
      volumes:
        # 宿主机时间卷（保持容器时间同步）
        - name: host-time
          hostPath:
            # 宿主机时间文件路径
            path: /etc/localtime
            type: ''
        # MySQL配置卷
        - name: config
          configMap:
            # 关联的ConfigMap名称
            name: mysql-source-cnf
            # 文件权限（0644）
            defaultMode: 420
        # 证书卷
        - name: ssl-certs
          secret:
            secretName: mysql-ssl-certs
      # 容器定义
      containers:
        # 容器名称
        - name: mysql-source
          # MySQL 8.0官方镜像
          image: mysql:8.0
          ports:
            # MySQL默认端口
            - containerPort: 3306
          env:
            # root密码（从Secret获取）
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  # Secret名称
                  name: mysql-secret
                  key: MYSQL_ROOT_PASSWORD
            # 自动获取Pod IP（用于主从配置）
            - name: POD_IP
              valueFrom:
                fieldRef:
                  fieldPath: status.podIP
            - name: MYSQL_SSL_CERT
              # 容器内挂载的文件
              value: /etc/mysql/ssl/server-cert.pem
            - name: MYSQL_SSL_KEY
              value: /etc/mysql/ssl/server-key.pem
            - name: MYSQL_SSL_CA
              value: /etc/mysql/ssl/ca.pem
          # 资源限制
          resources:
            limits:
              cpu: "2"
              memory: 4Gi
            requests:
              cpu: 100m
              memory: 100Mi
          # 卷挂载
          volumeMounts:
            # 同步宿主机时间
            - name: host-time
              mountPath: /etc/localtime
            # MySQL数据目录
            - name: mysql-data
              mountPath: /var/lib/mysql
            # 配置文件目录
            - name: config
              readOnly: true
              mountPath: /etc/mysql/conf.d/
            - name: ssl-certs
              readOnly: true
              # 容器内路径
              mountPath: /etc/mysql/ssl
          # 镜像拉取策略
          imagePullPolicy: IfNotPresent

  # 持久化卷声明模板
  volumeClaimTemplates:
  - metadata:
      name: mysql-data
      namespace: mysql-cluster
    spec:
      # 访问模式（单节点读写）
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          # 存储空间申请大小
          storage: 1Gi
      # 存储类名称（需预先创建）
      storageClassName: mysql-nfs-storage
      # 卷模式（文件系统）
      volumeMode: Filesystem
EOF
kubectl apply -f mysql-source-statefulset-svc.yaml

从节点StatefulSet

rm -rf mysql-replica-statefulset-svc.yaml
cat > mysql-replica-statefulset-svc.yaml<<EOF
kind: StatefulSet
apiVersion: apps/v1
metadata:
  name: mysql-replica
  namespace: mysql-cluster
  labels:
    app: mysql-replica
spec:
  serviceName: mysql-replica-headless
  replicas: 1
  selector:
    matchLabels:
      app: mysql-replica
      appCluster: mysql-cluster
  template:
    metadata:
      labels:
        app: mysql-replica
        appCluster: mysql-cluster
    spec:
      volumes:
        - name: host-time
          hostPath:
            path: /etc/localtime
            type: ""
        - name: config
          configMap:
            name: mysql-replica-cnf
            defaultMode: 420
        - name: ssl-certs
          secret:
            secretName: mysql-ssl-certs
      containers:
        - name: mysql-replica
          image: mysql:8.0
          ports:
            - containerPort: 3306
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secret
                  key: MYSQL_ROOT_PASSWORD
            - name: MYSQL_SSL_CERT
              # 容器内挂载的文件
              value: /etc/mysql/ssl/server-cert.pem
            - name: MYSQL_SSL_KEY
              value: /etc/mysql/ssl/server-key.pem
            - name: MYSQL_SSL_CA
              value: /etc/mysql/ssl/ca.pem
          resources:
            limits:
              cpu: "2"
              memory: 4Gi
            requests:
              cpu: 100m
              memory: 100Mi
          volumeMounts:
            - name: host-time
              mountPath: /etc/localtime
            - name: mysql-data
              mountPath: /var/lib/mysql
            - name: config
              readOnly: true
              mountPath: /etc/mysql/conf.d/
            - name: ssl-certs
              readOnly: true
              # 容器内路径
              mountPath: /etc/mysql/ssl
          imagePullPolicy: IfNotPresent
  volumeClaimTemplates:
    - metadata:
        name: mysql-data
        namespace: mysql-cluster
      spec:
        accessModes: ["ReadWriteOnce"]
        resources:
          requests:
            storage: 1Gi
        storageClassName: mysql-nfs-storage
        volumeMode: Filesystem
EOF
kubectl apply -f mysql-replica-statefulset-svc.yaml

4.7. 验证Mysql pod 状态

kubectl get pods -o wide -n mysql-cluster

验证自定义配置是否生效

kubectl exec -it mysql-source-0 -n mysql-cluster -- mysql -u root -p -e "show variables like '%max_conn%';"
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| max_connect_errors     | 1000000 |
| max_connections        | 2048    |
| mysqlx_max_connections | 100     |
+------------------------+---------+

5. 配置主从同步

5.1. 主节点配置

进入主节点

kubectl exec -it mysql-source-0 -n mysql-cluster -- mysql -u root -p

创建主从同步用户

-- 创建用户并设置密码
CREATE USER 'repuser'@'%' IDENTIFIED BY 'mysqlAdmin';
-- 赋予权限
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repuser'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

repuser: 主从复制用户，实际使用中请替换

ChangeMe: 主从复制用户的密码，实际使用中请替换

查看主节点状态

show master status;
-- 注意 : 记录 File 列的值 mysql-bin.000003 和 Position  列的 773，从库同步时会用到。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      884 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.2. 从节点配置

进入从节点

kubectl exec -it mysql-replica-0 -n mysql-cluster -- mysql -u root -p

配置主从同步

change master to master_host='mysql-source-headless.mysql-cluster.svc.cluster.local',master_port=3306,master_user='repuser',master_password='mysqlAdmin',master_log_file='mysql-bin.000012',master_log_pos=157;

说明：

master_host: 同步主节点的 DNS，使用 mysql-source-headless.default.svc.cluster.local
mysql-source-headless:Headless Service 名称（对应 StatefulSet 的主节点服务）
mysql-cluster:命名空间
svc.cluster.local:k8s 默认的DNS后缀

master_port: 主节点服务端口
master_user: 主节点创建的同步用户的名称
master_password: 主节点创建的同步用户的密码
master_log_file: 主库 Show Master 状态时， File 字段的值
master_log_pos: 主库 Show Master 状态时， Position 字段的值

配置复制使用SSL

CHANGE MASTER TO
  MASTER_SSL = 1,
  MASTER_SSL_CA = '/etc/mysql/ssl/ca.pem',
  MASTER_SSL_CERT = '/etc/mysql/ssl/client-cert.pem',
  MASTER_SSL_KEY = '/etc/mysql/ssl/client-key.pem',
  MASTER_SSL_VERIFY_SERVER_CERT = 1;

启动主从同步

start slave;

查看主从同步状态

show slave status \G;

-- 成功内容
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: mysql-source-headless.default.svc.cluster.local
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000006
          Read_Master_Log_Pos: 884
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql_bin.000006
             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: 884
              Relay_Log_Space: 157
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2005
                Last_IO_Error: Error connecting to source 'repuser@mysql-source-headless.default.svc.cluster.local:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Unknown MySQL server host 'mysql-source-headless.default.svc.cluster.local' (-2)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 250625 17:37:20
     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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

        重点查看这两个是否YES
        Slave_IO_Running: Yes #重点
        Slave_SQL_Running: Yes #重点
        **Last_IO_Errno、Last_IO_Error**：错误信息
        **Last_SQL_Errno、Last_SQL_Error**：错误信息

5.3. 验证主从同步

确认同步状态，在主节点新建数据库及表并添加数据，然后在从节点查询以验证主从同步是否正常。

主库检查复制链接

SELECT * FROM performance_schema.threads 
WHERE PROCESSLIST_COMMAND = 'Binlog Dump';

mysql> SELECT * FROM performance_schema.threads 
    -> WHERE PROCESSLIST_COMMAND = 'Binlog Dump';
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-----------------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+------------------+-------------------+-----------------------+--------------+------------------+------------------+
| THREAD_ID | NAME                      | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE                                               | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP | EXECUTION_ENGINE | CONTROLLED_MEMORY | MAX_CONTROLLED_MEMORY | TOTAL_MEMORY | MAX_TOTAL_MEMORY | TELEMETRY_ACTIVE |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-----------------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+------------------+-------------------+-----------------------+--------------+------------------+------------------+
|        49 | thread/sql/one_connection | FOREGROUND |             12 | repuser          | 10.0.169.163     | NULL           | Binlog Dump         |              404 | Source has sent all binlog to replica; waiting for more updates | NULL             |             NULL | NULL | YES          | YES     | SSL/TLS         |          132 | USR_default    | PRIMARY          |             18480 |                 30816 |       136816 |           167160 | NO               |
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-----------------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+------------------+-------------------+-----------------------+--------------+------------------+------------------+
1 row in set (0.04 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)

7.2 从库查看数据

查看数据库

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)

从结果中可以看到从库也已经同步了刚在主库创建的opsxlab库、member 表及相应的数据。

至此，我们已经完成了在 KubeSphere 管理的 Kubernetes 集群上手动部署 MySQL 主从同步的全过程。

问题

无法解析问题

Error connecting to source 'repuser@mysql-source-headless.mysql-cluster.svc.cluster.local:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Unknown MySQL server host 'mysql-source-headless.mysql-cluster.svc.cluster.local' (-2)

解释
- 从库无法解析 mysql-source-headless.mysql-cluster.svc.cluster.local 这个主机名。
- 这通常是因为 DNS 或本地 hosts 文件未正确配置，导致从库无法找到主库的 IP 地址。
可能原因
1. Kubernetes 集群内部 DNS 服务异常。
2. 主库的 Service (mysql-source-headless) 未正常运行。
3. 从库所在的 Pod 网络配置有问题（如 CoreDNS 未生效）。

解决方案

检查主库服务是否可用:在从库所在的 Pod 中执行以下命令，测试主库是否可访问

# 尝试解析主库主机名
nslookup mysql-source-headless.mysql-cluster.svc.cluster.local

# 测试网络连通性（如果解析成功）
ping mysql-source-headless.mysql-cluster.svc.cluster.local
telnet mysql-source-headless.mysql-cluster.svc.cluster.local 3306

‌检查 Kubernetes Service 状态

确保主库的 Headless Service 已正确创建并指向主库 Pod：

kubectl get svc -n mysql-cluster | grep mysql-source-headless
kubectl describe svc mysql-source-headless -n mysql-cluster

检查主库pod状态

确认主库 Pod 正在运行且监听 3306 端口：

kubectl get pods -n mysql-cluster | grep mysql-source
kubectl logs mysql-source-xxxx -n mysql-cluster  # 查看主库日志

验证修复

STOP SLAVE;
START SLAVE;
SHOW SLAVE STATUS \G;  -- 检查 Slave_IO_Running 是否变为 Yes

认证插件问题

mysql8.0 默认是通过**caching_sha2_password**认证的需要SSL/TLS的链接方式,

报错:

# 复制通道未启用加密
Error connecting to source 'repuser@mysql-source-headless.mysql-cluster.svc.cluster.local:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection

解决方案:

启用SSL/TLS链接(推荐)
修改从库的复制配置,强制使用SSL:具体步骤已经嵌入到上面的部署步骤中

STOP SLAVE;
CHANGE MASTER TO 
  MASTER_SSL=1,
  MASTER_SSL_CA='/path/to/ca.pem',      -- CA 证书路径
  MASTER_SSL_CERT='/path/to/client-cert.pem',
  MASTER_SSL_KEY='/path/to/client-key.pem',
  MASTER_SSL_VERIFY_SERVER_CERT=1;      -- 可选：验证服务器证书
START SLAVE;