《Kubernetes 构建 MySQL MGR 集群实战教程》

原创已于 2025-09-06 10:19:27 修改 · 834 阅读

20 ·

CC 4.0 BY-SA版权

文章标签：

#kubernetes #mysql #容器

于 2025-09-06 10:16:46 首次发布

#### 一、前言

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）。

---

#### 八、总结