mysql 一主二从集群的搭建

一 环境准备

192.169.31.132	node01	mysql主节点
192.169.31.133	node02	mysql从节点
192.169.31.134	node03	mysql从节点

二 搭建nfs服务器

1.安装NFS: 在三个节点都需要安装，（master节点当做nfs服务器，其他两个节点连接到nfs服务器）

yum install -y nfs-utils

2.我们直接在nfs服务器(k8s-master)当中创建这三个目录并写入 /etc/exports 文件夹中（创建的目录可以修改）：

mkdir -p /data/nfs/{mysql-master,mysql-slaver-01,mysql-slaver-02}
cat >> /etc/exports << EOF
/data/nfs/mysql-master *(rw,sync,no_root_squash)
/data/nfs/mysql-slaver-01 *(rw,sync,no_root_squash)
/data/nfs/mysql-slaver-02 *(rw,sync,no_root_squash)
EOF

3.启动nfs服务器

systemctl enable --now nfs-server

4. 已经暴露的目录

showmount -e 192.168.31.132

三 安装mysql集群的yaml配置文件

# PV配置
apiVersion: v1
kind: PersistentVolume
metadata:
  name: deploy-mysql-master-nfs-pv
  namespace: deploy-test
spec:
  capacity:
    storage: 2Gi
  accessModes:
    - ReadWriteMany
  nfs:
    # 注意修改IP地址和暴露的目录（如果不一样）
    server: 192.168.31.132
    path: /data/nfs/mysql-master
  storageClassName: "nfs"

---
# PVC配置
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: deploy-mysql-master-nfs-pvc
  namespace: deploy-test
spec:
  accessModes:
    - ReadWriteMany
  storageClassName: "nfs"
  resources:
    requests:
      storage: 2Gi
  volumeName: deploy-mysql-master-nfs-pv


---
#Secret配置
apiVersion: v1
data:
  mysql_root_password: MjAyNXBhc3M=
kind: Secret
metadata:
  creationTimestamp: null
  name: mysql-password
  namespace: deploy-test

---
# ConfigMap配置(分为主节点配置和从节点配置)
apiVersion: v1
data:
  my.cnf: |
    [mysqld]
    skip-name-resolve 
    datadir = /var/lib/mysql
    socket = /var/run/mysqld/mysqld.sock
    pid-file  = /var/run/mysqld/mysqld.pid
    user = mysql
   #不同实例不同id值(2,3..)
    server-id = 1 
    log-bin = mysql-bin
    log_bin_index  = mysql-bin.index
    relay-log  = relay-log-bin
    relay-log-index = relay-log-bin.index
    binlog_expire_logs_seconds = 604800
    binlog_ignore_db = information_schema
    binlog_ignore_db = mysql
    binlog_ignore_db = performance_schema
    binlog_ignore_db = sys
    # mysql从节点如果需要配置proxysql需要开启read_only
    # read_only=1
    gtid_mode = ON   
    enforce_gtid_consistency = ON    
    log_replica_updates = ON         
    [client]
    socket = /var/run/mysqld/mysqld.sock
    !includedir /etc/mysql/conf.d/
kind: ConfigMap
metadata:
  creationTimestamp: null
  name: mysql-master-cm
  namespace: deploy-test

---
#Service配置(分为主节点配置和从节点配置，从节点配置30308端口，两个从节点配置一个Service即可)
apiVersion: v1
kind: Service
metadata:
  name: deploy-mysql-master-svc
  namespace: deploy-test
  labels:
    app: mysql-master 
    # app: mysql-slave
spec:
  ports:
  - port: 3306
    name: mysql
    targetPort: 3306 
    nodePort: 30306
    # nodePort: 30308 
  selector:
    app: mysql-master 
    # app: mysql-slave
  type: NodePort
  sessionAffinity: ClientIP

---
#StatefulSet配置(分为主节点配置和从节点配置)
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: deploy-mysql-master
  # name: deploy-mysql-slave-01
  # name: deploy-mysql-slave-02
  namespace: deploy-test
spec:
  selector:
    matchLabels:
      app: mysql-master
      # app: mysql-slave
  serviceName: "deploy-mysql-master-svc"
  replicas: 1
  template:
    metadata:
      labels:
        app: mysql-master
        # app: mysql-slave
    spec:
      nodeSelector:
        kubernetes.io/hostname: node01
        # kubernetes.io/hostname: node02
        # kubernetes.io/hostname: node03
      terminationGracePeriodSeconds: 10
      containers:
      - args:
        - --character-set-server=utf8mb4
        - --collation-server=utf8mb4_unicode_ci
        - --lower_case_table_names=1
        - --default-time_zone=+8:00
        name: mysql
        image: mysql:8.0.36
        ports:
        - containerPort: 3306
          name: mysql
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
        - name: mysql-conf
          mountPath: /etc/my.cnf
          readOnly: true
          subPath: my.cnf
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              key: mysql_root_password
              name: mysql-password
      volumes:
      - name: mysql-data
        persistentVolumeClaim:
          claimName: deploy-mysql-master-nfs-pvc
      - name: mysql-conf
        configMap:
          name: mysql-master-cm
          items:
          - key: my.cnf
            mode: 0644
            path: my.cnf

部署实例如图

四 配置主从同步

mysql主节点操作

进入主节点容器

kubectl exec -itn deploy-test pod/deploy-mysql-master-0 -- mysql -uroot -p

创建复制专用用户

CREATE USER 'repl'@'*' IDENTIFIED BY '2025pass' WITH 'mysql_native_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'*';
FLUSH PRIVILEGES;

查看 GTID 状态

SHOW MASTER STATUS\G

-- 输出示例：
-- File: mysql-bin.000001
-- Position: 154
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

mysql从节点操作，两个从节点一样的操作

进入从节点容器

kubectl exec -itn deploy-test pod/deploy-mysql-slave-01-0 -- mysql -uroot -p

配置同步主库信息

CHANGE MASTER TO
MASTER_HOST = '192.168.31.132',
MASTER_USER = 'repl',
MASTER_PASSWORD = '2025pass',
‌MASTER_PORT = 30306,
MASTER_AUTO_POSITION = 1;

启动复制

START SLAVE;

检查复制状态

SHOW SLAVE STATUS\G

关键指标

参考[XiaoHH Supermede 的《Kubernetes(k8s)上搭建一主两从的mysql8集群》] ，(https://blog.csdn.net/m0_51510236/article/details/133145221)