k8s部署mysql主从集群
1.mysql主从集群介绍
一主两从架构以低成本实现高性能读写分离和高可用性，是中型系统平衡资源与可靠性的理想选择，架构容易理解和部署。
2.部署环境
| IP | 节点 | 操作系统 | k8s版本 |
mysql版本 |
docker版本 |
| 172.16.4.85 | master1 | centos7.8 | 1.23.17 | 20.10.9 | |
| 172.16.4.86 | node1 | centos7.8 | 1.23.17 | 20.10.9 | |
| 172.16.4.87 | node2 | centos7.8 | 1.23.17 | 5.7.44 | 20.10.9 |
| 172.16.4.89 | node3 | centos7.8 | 1.23.17 | 5.7.44 | 20.10.9 |
| 172.16.4.90 | node4 | centos7.8 | 1.23.17 | 5.7.44 | 20.10.9 |
3.mysql主从集群部署
3.1 nfs csi部署
https://www.cnblogs.com/Leonardo-li/p/188131403.2 创建namespace
kubectl create namespace mysql3.3 创建mysql configmap
# ConfigMap
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql
namespace: mysql
labels:
app: mysql
data:
master.cnf: |
# 主节点配置
[mysqld]
log-bin = mysql-bin
binlog-format = ROW
expire-logs-days = 7
max-binlog-size = 500M
slave.cnf: |
# 从节点配置
[mysqld]
#super-read-only = ON
relay-log = relay-bin
log-slave-updates = ON
read-only = ON3.4 创建mysql secret
# Secret
apiVersion: v1
kind: Secret
metadata:
name: mysql-secret
namespace: mysql
labels:
app: mysql
type: Opaque
data:
password: eXR4QDEyMzQ= # 密码为 123456（建议生产环境使用更复杂密码）
#创建密码 echo -n "MyS3cr3tP@ssw0rd!" | base643.5 创建mysql service
# Headless Service
apiVersion: v1
kind: Service
metadata:
name: mysql
namespace: mysql
labels:
app: mysql
spec:
ports:
- name: mysql
port: 23306
targetPort: 3306
clusterIP: None
selector:
app: mysql
---
# Read Service (LoadBalancer)
apiVersion: v1
kind: Service
metadata:
name: mysql-read
namespace: mysql
labels:
app: mysql
spec:
ports:
- name: mysql
port: 23306
targetPort: 3306
selector:
app: mysql3.6 创建mysql statefulset
- 172.16.4.177:8090/public/mysql:5.7.44 #是在hub.docker.com下载的镜像推到了私有harbor仓库，
- 172.16.4.177:8090/ltzx/ist0ne/xtrabackup:latest #下载镜像推到私有harbor仓库，如果有朋友想要的话，可以私我。
# StatefulSet
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
namespace: mysql
spec:
serviceName: mysql
replicas: 3
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
initContainers:
- name: init-mysql
image: 172.16.4.177:8090/public/mysql:5.7.44
securityContext:
runAsUser: 0 # 以 root 用户运行避免权限问题
command:
- "sh"
- "-c"
- |
set -ex
# 使用 Kubernetes 注入的 HOSTNAME 环境变量
pod_name="$HOSTNAME"
ordinal=$(echo "$pod_name" | awk -F- '{print $NF}')
[ -z "$ordinal" ] && exit 1 # 序号为空则退出
# 生成 server-id 配置
echo "[mysqld]" > /mnt/conf.d/server-id.cnf
echo "server-id=$((100 + ordinal))" >> /mnt/conf.d/server-id.cnf
# 复制主从配置
if [ "$ordinal" -eq 0 ]; then
cp /mnt/config-map/master.cnf /mnt/conf.d/
else
cp /mnt/config-map/slave.cnf /mnt/conf.d/
fi
volumeMounts:
- name: conf
mountPath: /mnt/conf.d
- name: config-map
mountPath: /mnt/config-map
- name: clone-mysql
image: 172.16.4.177:8090/ltzx/ist0ne/xtrabackup:latest
command:
- "bash"
- "-c"
- |
set -ex
[[ -d /var/lib/mysql/mysql ]] && exit 0
[[ "$HOSTNAME" =~ -([0-9]+)$ ]] || exit 1
ordinal=${BASH_REMATCH[1]}
[[ $ordinal -eq 0 ]] && exit 0
ncat --recv-only mysql-$(($ordinal-1)).mysql 3307 | xbstream -x -C /var/lib/mysql
xtrabackup --prepare --target-dir=/var/lib/mysql
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
containers:
- name: mysql
image: 172.16.4.177:8090/public/mysql:5.7.44
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: password
ports:
- name: mysql
containerPort: 3306
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
resources:
requests:
cpu: 500m
memory: 1Gi
livenessProbe:
exec:
command:
- "sh"
- "-c"
- "mysqladmin ping -uroot -p${MYSQL_ROOT_PASSWORD}"
initialDelaySeconds: 30
periodSeconds: 10
timeoutSeconds: 5
readinessProbe:
exec:
command:
- "sh"
- "-c"
- "mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e 'SELECT 1'"
initialDelaySeconds: 5
periodSeconds: 2
timeoutSeconds: 1
- name: xtrabackup
image: 172.16.4.177:8090/ltzx/ist0ne/xtrabackup:latest
ports:
- name: xtrabackup
containerPort: 3307
command:
- "bash"
- "-c"
- |
set -ex
cd /var/lib/mysql
if [[ -f xtrabackup_slave_info && "x$(<xtrabackup_slave_info)" != "x" ]]; then
cat xtrabackup_slave_info | sed -E 's/;$//g' > change_master_to.sql.in
rm -f xtrabackup_slave_info xtrabackup_binlog_info
elif [[ -f xtrabackup_binlog_info ]]; then
[[ $(cat xtrabackup_binlog_info) =~ ^(.*?)[[:space:]]+(.*?)$ ]] || exit 1
rm -f xtrabackup_binlog_info xtrabackup_slave_info
echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}',\
MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in
fi
if [[ -f change_master_to.sql.in ]]; then
until mysql -h 127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD -e "SELECT 1"; do sleep 1; done
mysql -h 127.0.0.1 -uroot -p$MYSQL_ROOT_PASSWORD \
-e "$(<change_master_to.sql.in), \
MASTER_HOST='mysql-0.mysql', \
MASTER_USER='root', \
MASTER_PASSWORD='$MYSQL_ROOT_PASSWORD', \
MASTER_CONNECT_RETRY=10; \
START SLAVE;" || exit 1
mv change_master_to.sql.in change_master_to.sql.orig
fi
exec ncat --listen --keep-open --send-only --max-conns=1 3307 -c \
"xtrabackup --backup --slave-info --stream=xbstream --host=127.0.0.1 --user=root --password=$MYSQL_ROOT_PASSWORD"
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: password
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
volumes:
- name: conf
emptyDir: {}
- name: config-map
configMap:
name: mysql
volumeClaimTemplates:
- metadata:
name: data
spec:
accessModes: ["ReadWriteOnce"]
storageClassName: nfs-csi
resources:
requests:
storage: 10Gi3.7 mysql集群状态查看
[root@master1 mysql-new]# kubectl get pv | grep data-mysql
pvc-36697d9f-7079-467e-8f96-f13c190cc5e5 10Gi RWO Retain Bound mysql/data-mysql-1 nfs-csi 23m
pvc-cb0b2873-720b-493b-9f2b-851e093aec49 10Gi RWO Retain Bound mysql/data-mysql-2 nfs-csi 22m
pvc-d4e2dd10-2540-4e37-9929-34eeaa18a7b7 10Gi RWO Retain Bound mysql/data-mysql-0 nfs-csi 31m
[root@master1 mysql-new]# kubectl get pvc -n mysql
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
data-mysql-0 Bound pvc-d4e2dd10-2540-4e37-9929-34eeaa18a7b7 10Gi RWO nfs-csi 31m
data-mysql-1 Bound pvc-36697d9f-7079-467e-8f96-f13c190cc5e5 10Gi RWO nfs-csi 23m
data-mysql-2 Bound pvc-cb0b2873-720b-493b-9f2b-851e093aec49 10Gi RWO nfs-csi 22m
[root@master1 mysql-new]# kubectl get cm -n mysql
NAME DATA AGE
kube-root-ca.crt 1 80d
mysql 2 23m
[root@master1 mysql-new]# kubectl get secret -n mysql
NAME TYPE DATA AGE
default-token-lgh7p kubernetes.io/service-account-token 3 80d
mysql-secret Opaque 1 24m
[root@master1 mysql-new]# kubectl get svc -n mysql
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
mysql ClusterIP None <none> 23306/TCP 24m
mysql-read ClusterIP 10.106.82.62 <none> 23306/TCP 24m
[root@master1 mysql-new]# kubectl get sts -n mysql
NAME READY AGE
mysql 3/3 24m
[root@master1 mysql-new]# kubectl get pods -n mysql
NAME READY STATUS RESTARTS AGE
mysql-0 2/2 Running 0 24m
mysql-1 2/2 Running 1 (23m ago) 24m
mysql-2 2/2 Running 1 (23m ago) 23m3.8 查看mysql主从复制状态
# mysql 主
[root@master1 mysql-new]# kubectl exec -n mysql mysql-0 -- mysql -uroot -pytx@1234 -e "SHOW MASTER STATUS\G"
Defaulted container "mysql" out of: mysql, xtrabackup, init-mysql (init), clone-mysql (init)
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
# mysql 从1
[root@master1 mysql-new]# kubectl exec -n mysql mysql-1 -- mysql -uroot -pytx@1234 -e "SHOW SLAVE STATUS\G"
Defaulted container "mysql" out of: mysql, xtrabackup, init-mysql (init), clone-mysql (init)
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-0.mysql
Master_User: root
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
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: 154
Relay_Log_Space: 521
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: 100
Master_UUID: b25c6bb0-139b-11f0-91cd-9280d3f7704a
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:
# mysql 从2
[root@master1 mysql-new]# kubectl exec -n mysql mysql-2 -- mysql -uroot -pytx@1234 -e "SHOW SLAVE STATUS\G"
Defaulted container "mysql" out of: mysql, xtrabackup, init-mysql (init), clone-mysql (init)
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-0.mysql
Master_User: root
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
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: 154
Relay_Log_Space: 521
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: 100
Master_UUID: b25c6bb0-139b-11f0-91cd-9280d3f7704a
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: 3.9 验证mysql主从同步
- 进入mysql-0主数据库创建库表以及测试数据
kubectl exec -it mysql-0 -n mysql -- /bin/bash# 创建数据库test123
mysql> create database test123;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+------------------------+
| Database |
+------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test123 |
| xtrabackup_backupfiles |
+------------------------+
6 rows in set (0.00 sec)
# 在库test123里创建表users
mysql> use test123;
Database changed
mysql> CREATE TABLE users (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(255) NOT NULL
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+-------------------+
| Tables_in_test123 |
+-------------------+
| users |
+-------------------+
1 row in set (0.00 sec)
mysql> desc users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
#在users表中插入数据，id是自增的，所以只需要插入name字段就行
mysql> INSERT INTO users (name) VALUES
-> ('Alice'), ('Bob'), ('Charlie'), ('David'), ('Eva'),
-> ('Frank'), ('Grace'), ('Henry'), ('Ivy'), ('Jack');
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO users (name) VALUES^C
mysql> select * from users;
+----+---------+
| id | name |
+----+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
| 5 | Eva |
| 6 | Frank |
| 7 | Grace |
| 8 | Henry |
| 9 | Ivy |
| 10 | Jack |
+----+---------+
10 rows in set (0.00 sec)
mysql> INSERT INTO users (name) VALUES
-> ('Name1'), ('Name2'), ('Name3'), ('Name4'), ('Name5'),
-> ('Name6'), ('Name7'), ('Name8'), ('Name9'), ('Name10');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from users;
+----+---------+
| id | name |
+----+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
| 5 | Eva |
| 6 | Frank |
| 7 | Grace |
| 8 | Henry |
| 9 | Ivy |
| 10 | Jack |
| 11 | Name1 |
| 12 | Name2 |
| 13 | Name3 |
| 14 | Name4 |
| 15 | Name5 |
| 16 | Name6 |
| 17 | Name7 |
| 18 | Name8 |
| 19 | Name9 |
| 20 | Name10 |
+----+---------+
20 rows in set (0.00 sec)- 在mysql-1从库查询数据是否同步
[root@master1 ~]# kubectl exec -it mysql-1 -n mysql -- mysql -uroot -pytx@1234 -e "select * from test123.users;"
Defaulted container "mysql" out of: mysql, xtrabackup, init-mysql (init), clone-mysql (init)
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
| 5 | Eva |
| 6 | Frank |
| 7 | Grace |
| 8 | Henry |
| 9 | Ivy |
| 10 | Jack |
| 11 | Name1 |
| 12 | Name2 |
| 13 | Name3 |
| 14 | Name4 |
| 15 | Name5 |
| 16 | Name6 |
| 17 | Name7 |
| 18 | Name8 |
| 19 | Name9 |
| 20 | Name10 |
+----+---------+- 在mysql-2从库查询数据是否同步
[root@master1 ~]# kubectl exec -it mysql-2 -n mysql -- mysql -uroot -pytx@1234 -e "select * from test123.users;"
Defaulted container "mysql" out of: mysql, xtrabackup, init-mysql (init), clone-mysql (init)
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
| 5 | Eva |
| 6 | Frank |
| 7 | Grace |
| 8 | Henry |
| 9 | Ivy |
| 10 | Jack |
| 11 | Name1 |
| 12 | Name2 |
| 13 | Name3 |
| 14 | Name4 |
| 15 | Name5 |
| 16 | Name6 |
| 17 | Name7 |
| 18 | Name8 |
| 19 | Name9 |
| 20 | Name10 |
+----+---------+- 可以看到刚刚创建的库表以及测试数据都已经同步过来了
4.部署到指定node节点
- 如果想把mysql节点部署到指定的node上，可以在statefulset.yaml中添加如下配置，需要注意节点名称需与实际的节点名称一致，我的是node1、node2这种的
# StatefulSet
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
namespace: mysql
spec:
serviceName: mysql
replicas: 3
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
# 添加节点调度策略
affinity:
nodeAffinity:
requiredDuringSchedulingIgnoredDuringExecution:
nodeSelectorTerms:
- matchExpressions:
- key: kubernetes.io/hostname
operator: In
values:
- node2 # 节点名称需与集群中的实际名称一致
- node3
- node4
# 以下是原有配置
initContainers:
- name: init-mysql
# ... 其他容器配置保持不变 ...5.业务连接mysql地址
5.1 连接地址
- 主节点（写操作）
地址：mysql-0.mysql.mysql.svc.cluster.local:3306
适用场景：所有写入操作（如 INSERT/UPDATE/DELETE）和事务性操作。
说明：
mysql-0 是主节点 Pod 名称，通过 Headless Service 直接访问。
仅允许通过此地址写入，避免从节点写入导致数据不一致。
- 从节点（读操作）
地址：mysql-read.mysql.svc.cluster.local:23306
适用场景：所有读操作（如 SELECT），负载均衡到两个从节点。
说明：
mysql-read 是 LoadBalancer Service，自动分发读请求到 mysql-1 和 mysql-2。
支持水平扩展读性能，适合高并发查询。
- 单个从节点直连（调试或特殊场景）
地址示例：mysql-1.mysql.mysql.svc.cluster.local:3306
适用场景：
测试特定从节点状态（如 SHOW SLAVE STATUS）。
直接分析某个从节点的数据快照
5.2 java连接示例
spring:
datasource:
# 主库（写操作）
write:
url: jdbc:mysql://mysql-0.mysql.mysql.svc.cluster.local:3306/app_db?useSSL=false&characterEncoding=utf8
username: root
password: ytx@1234
driver-class-name: com.mysql.cj.jdbc.Driver
hikari:
connection-timeout: 3000
maximum-pool-size: 5 # 写连接池大小
# 从库（读操作）
read:
url: jdbc:mysql://mysql-read.mysql.svc.cluster.local:23306/app_db?useSSL=false&characterEncoding=utf8
username: root
password: ytx@1234
driver-class-name: com.mysql.cj.jdbc.Driver
hikari:
connection-timeout: 3000
maximum-pool-size: 10 # 读连接池大小（可更大）6.从库扩展
- 如果有更多的读操作，且有较多的node节点，可以继续扩展副本，将副本3修改为副本4或者更多
# 将 replicas 从 3 调整为 4（新增一个从节点）
kubectl patch statefulset mysql -n mysql --type='json' -p='[{"op": "replace", "path": "/spec/replicas", "value":4}]'
# 或直接编辑 YAML：
kubectl edit statefulset mysql -n mysql # 修改 replicas: 47.参考文档
- 参考1
https://www.modb.pro/db/159844- 参考2是官方文档，但用来克隆的镜像 gcr.io/google-samples/xtrabackup:1.0 在docker 19.03 之前的版本才能下载，之后的版本已经不支持这种格式镜像了，所以我也就选择了【参考1】中博主使用的镜像
https://kubernetes.io/zh-cn/docs/tasks/run-application/run-replicated-stateful-application/
至此k8s部署mysql主从集群就完成了！！！
浙公网安备 33010602011771号