《Kubernetes 構建 MySQL MGR 集群實戰教程》

#### 一、前言 ?

MySQL Group Replication (MGR) 是 MySQL 官方提供的高可用集群方案，基于 Paxos 協議實現多節點數據強一致性。本教程將指導如何在 Kubernetes 上部署 MySQL MGR 集群，適用于生產級高可用場景。

---

#### 二、環境準備 ?

1. **Kubernetes 集群** ?

? ?- 版本 ≥ 1.24，支持 StatefulSet 和 PersistentVolume。 ?
- 推薦工具：Minikube（測試）或 kubeadm 部署的生產集群。 ?

2. **存儲類（StorageClass）** ?

? ?- 確保動態存儲供應已配置（如 `standard` 或 `rook-cephfs`）。 ?

3. **MySQL 鏡像** ?

? ?- 使用官方鏡像或定制鏡像（需包含 MGR 插件）： ?

? ? ?```dockerfile
FROM mysql:8.0.28
RUN echo "plugin-load-add=group_replication.so" >> /etc/mysql/my.cnf
```

---

#### 三、部署 MySQL MGR 集群 ?

##### 1. 創建 nfs網絡存儲配置

```yaml
# 在 czkmaster1 上搭建 NFS 服務器（選擇存儲空間足夠的節點）
sudo yum install -y nfs-utils
sudo mkdir -p /nfs/mysql/mysql-data-mysql-{0,1,2}
sudo chown -R 999:999 /nfs/mysql ?# MySQL 容器用戶ID
sudo chmod -R 755 /nfs/mysql

# 配置 NFS 共享
echo "/nfs/mysql *(rw,sync,no_root_squash,no_subtree_check)" | sudo tee -a /etc/exports

# 啟動 NFS 服務
sudo systemctl enable --now nfs-server rpcbind
sudo exportfs -a

# 在其他節點安裝 NFS 客戶端
sudo yum install -y nfs-utils
```

##### 2. 創建 ConfigMap 配置 ?

```yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql-config
namespace: mysql
data:
my.cnf: |
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

# Group Replication配置
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="mysql-0.mysql:33061"
loose-group_replication_group_seeds="mysql-0.mysql:33061,mysql-1.mysql:33061,mysql-2.mysql:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off

max_connections=1000
innodb_buffer_pool_size=1G

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
```

##### 3. 創建 nfs-storageclass 配置 ?

```yaml
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
name: nfs-mysql
provisioner: k8s-sigs.io/nfs-subdir-external-provisioner
parameters:
archiveOnDelete: "false"
pathPattern: "${.PVC.namespace}/${.PVC.name}"
```

##### 4. 創建 secret 配置 ?

```yaml
apiVersion: v1
kind: Secret
metadata:
name: mysql-secret
namespace: mysql
type: Opaque
data:
root-password: bXlzcWwtcm9vdC0xMjM= ?# mysql-root-123
replication-password: cmVwbGljYXRpb24tMTIz ?# replication-123
```

##### 5. 創建 Headless Service 與四層代理

```yaml
apiVersion: v1
kind: Service
metadata:
name: mysql
namespace: mysql
spec:
ports:
- name: mysql
port: 3306
targetPort: 3306
clusterIP: None
selector:
app: mysql
---
apiVersion: v1
kind: Service
metadata:
name: mysql-read
namespace: mysql
spec:
ports:
- name: mysql
port: 3306
targetPort: 3306
selector:
app: mysql
```

##### 6. 創建 pv-final.yaml

```yaml
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-pv-0
spec:
capacity:
storage: 10Gi
volumeMode: Filesystem
accessModes:
- ReadWriteOnce
persistentVolumeReclaimPolicy: Retain
storageClassName: nfs-mysql
nfs:
path: /nfs/mysql/mysql-data-mysql-0
server: 192.168.179.170
claimRef:
name: mysql-data-mysql-0
namespace: mysql
---
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-pv-1
spec:
capacity:
storage: 10Gi
volumeMode: Filesystem
accessModes:
- ReadWriteOnce
persistentVolumeReclaimPolicy: Retain
storageClassName: nfs-mysql
nfs:
path: /nfs/mysql/mysql-data-mysql-1
server: 192.168.179.170
claimRef:
name: mysql-data-mysql-1
namespace: mysql
---
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-pv-2
spec:
capacity:
storage: 10Gi
volumeMode: Filesystem
accessModes:
- ReadWriteOnce
persistentVolumeReclaimPolicy: Retain
storageClassName: nfs-mysql
nfs:
path: /nfs/mysql/mysql-data-mysql-2
server: 192.168.179.170
claimRef:
name: mysql-data-mysql-2
namespace: mysql
```

##### 7. 部署 StatefulSet ?

```yaml
[root@czkmaster1 mysql]# cat mysql-simple-statefulset.yaml?
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
namespace: mysql
spec:
serviceName: mysql ?# 核心：與 Headless Service 匹配，確保 DNS 生成
replicas: 3
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
containers:
- name: mysql
image: docker.io/library/mysql:8.0.28
ports:
- containerPort: 3306 ?# MySQL 業務端口
- containerPort: 33061 ?# 【補充】MGR 集群通信端口（必需）
env:
- name: MYSQL_ROOT_PASSWORD
value: "mysql-root-123"
- name: MYSQL_DATABASE
value: "appdb"
- name: MYSQL_ROOT_PASSWORD
value: "mysql-root-123"
- name: MYSQL_REPLICATION_PASSWORD ?# 添加復制密碼
value: "replication-123"
- name: MYSQL_DATABASE
value: "appdb"
volumeMounts:
- name: mysql-data
mountPath: /var/lib/mysql ?# 數據持久化
- name: mysql-config
mountPath: /etc/mysql/conf.d/ ?# 配置文件掛載（MGR 參數通過這里注入）
resources:
requests:
memory: "1Gi"
cpu: "500m"
limits:
memory: "2Gi"
cpu: "1000m"
# 【補充】就緒探針：確保 MySQL 服務正常后才加入集群
readinessProbe:
exec:
command: ["mysqladmin", "ping", "-uroot", "-p$(MYSQL_ROOT_PASSWORD)"]
initialDelaySeconds: 30 ?# 啟動后延遲30秒檢查（避免服務未就緒誤判）
periodSeconds: 10 ?# 每10秒檢查一次
timeoutSeconds: 5 ?# 超時時間5秒
volumes:
- name: mysql-config
configMap:
name: mysql-config ?# 需確保該 ConfigMap 已創建
volumeClaimTemplates:
- metadata:
name: mysql-data
spec:
accessModes: [ "ReadWriteOnce" ]
storageClassName: "nfs-mysql" ?# 你的 NFS 存儲類，確保可用
resources:
requests:
storage: 10Gi

```

#### 四、初始化 MGR 集群 ?

1. **檢查pod狀態** ?

? ?```
kubectl get pods -n mysql -o wide
```

2. **測試MYSQL鏈接** ?

? ?```
kubectl exec -it mysql-0 -n mysql -- mysql -uroot -pmysql-root123 -e "SHOW DATABASES;"
```

3. **檢查MYSQL版本和插件支持** ?

? ?```
kubectl exec -it mysql-0 -n mysql -- mysql -uroot -pmysql-root-123 -e "SELECT VERSION(); SHOW PLUGINS;"
```

4. **在每個節點上安裝Group Replication插件** ?

? ?```
for i in 0 1 2; do
kubectl exec -it mysql-$i -n mysql -- mysql -uroot -pmysql-root-123 -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
done
```

5. **驗證插件安裝**

? ?```
kubectl exec -it mysql-0 -n mysql -- mysql -uroot -pmysql-root-123 -e "SHOW PLUGINS LIKE '%group%';"
```

6. **批量執行從節點加入命令**

? ?```
for i in 1 2; do
kubectl exec -i mysql-$i -n mysql -- mysql -uroot -pmysql-root-123 -e "
# 配置從節點參數
SET GLOBAL group_replication_local_address=CONCAT('mysql-', $i, '.mysql:33061');
SET GLOBAL group_replication_group_seeds='mysql-0.mysql:33061,mysql-1.mysql:33061,mysql-2.mysql:33061';
SET GLOBAL group_replication_ip_allowlist='%';

# 配置復制用戶并啟動組復制
CHANGE MASTER TO?
MASTER_USER='root',?
MASTER_PASSWORD='mysql-root-123'?
FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
"
done
```

7. **最終驗證整個集群狀態**

? ?```
kubectl exec -i mysql-0 -n mysql -- mysql -uroot -pmysql-root-123 -e "
SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE?
FROM performance_schema.replication_group_members;
"
```

8. **成功標志（所有節點 ONLINE）**

---

#### 五、高可用測試 ?

1. **故障轉移** ?
- 手動刪除 `mysql-0` Pod，觀察其他節點是否自動選舉新 Primary。 ?
2. **數據一致性** ?
- 在 Primary 節點插入數據，檢查 Secondary 節點是否同步。 ?

---

#### 六、監控與維護 ?

1. **Prometheus 監控** ?
- 配置 MySQL Exporter 采集 MGR 指標。 ?
2. **備份策略** ?
- 使用 `mysqldump` 或 Percona XtraBackup 定期備份。 ?

---

#### 七、常見問題 ?

1. **節點無法加入集群** ?
- 檢查防火墻是否放行 3306 端口。 ?
- 驗證 `group_replication_group_seeds` 配置。 ?
2. **腦裂問題** ?
- 確保奇數節點數（如 3 或 5）。 ?

---

#### 八、總結 ?