Kubernetes-mysql-localvolume集群部署
背景
目前在高可用分布式集群的架构下要求mysql数据库做读写分离集群部署，来减轻数据库压力，本章节将介绍基于kubernetes，localvolume做mysql数据本地存储方式进行集群部署官方建议利用nfs做mysql数据的动态存储
一、环境准备，安装kubernetes集群
当前节点master、slave1、slave2分别为kubernetes主节点和两个从节点
kubernetes安装不在此赘述，请参考参考资料进行安装
参考资料
1-Kubernetes基于Centos7构建基础环境（一）
2-Kubernetes基于Centos7构建基础环境（二）
3-Kubernetes基于Centos7构建基础环境（三）
4-Kubernetes-基于Centos7安装面板及监控（四）xincan.gitee.io/posts/2aa23…)
| 集群名称 | 集群域名 | 说明 |
|---|---|---|
| base | base.xincan.cn | 部署harbor、nfs等服务 |
| master | master.xincan.cn | kubernetes主节点，做污点容忍，排除业务资源，nfs客户端等 |
| slave1 | slave1.xincan.cn | kubernetes从节点，nfs客户端等 |
| slave2 | slave2.xincan.cn | kubernetes从节点，nfs客户端等 |
二、总体流程：
- 在kubernetes主节点上某个目录下创建mysql-localvolume文件夹，也可以自定义，主要保证内部kubernetes资源定义文件一样即可；
- 进入此文件夹之后，复制一下文档中列出的kubernetes资源并保存到此目录下，所有资源如下列表;
[root@master mysql-localVolume]# ll
mysql-localVolume
├── 1-mysql-namespace.yaml
├── 2-mysql-secret.yaml
├── 3-mysql-storageclass.yaml
├── 4-mysql-master-pv.yaml
├── 5-mysql-slave-pv.yaml
├── 6-mysql-configmap.yaml
├── 7-mysql-service.yaml
└── 8-mysql-statefulset.yaml
- 分别在kubernetes集群子节点上，找到两个从节点，分别在两个子节点上的根目录下创建/data/文件，用于保存mysql集群数据，目前我的是在slave1.xincan.cn、slave2.xincan.cn的根目录下分别创建的/data/文件夹；
- 资源创建；
- 效果展示；
- 测试主从同步，外部链接；
三、资源创建
- 创建mysql的命名空间Namespace，名称为：mysql
- 将所有的资源挂载到此命名空间下；
[root@master mysql-localVolume]# vim 1-mysql-namespace.yaml
apiVersion: v1
kind: Namespace
metadata:
name: mysql
labels:
app: mysql
-
创建密钥存储安全配置Secret，名称为：mysql-secret
-
通过echo对数据库密码123456进行
base64加密，默认用户是root，后续用到； -
echo -n "123456" | base64
-
[root@master mysql-localVolume]# vim 2-mysql-secret.yaml
apiVersion: v1
kind: Secret
metadata:
name: mysql-secret
namespace: mysql
labels:
app: mysql
type: Opaque
data:
password: MTIzNDU2
- 创建存储挂载资源StorageClass，名称为：mysql-local-storage
[root@master mysql-localVolume]# vim 3-mysql-storageclass.yaml
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
name: mysql-local-storage
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer
-
创建mysql主节点PersistentVolume，名称为：mysql-master-pv
-
slave1.xincan.cn：此节点是kubernetes集群中的一个子节点，作为mysql的master节点的存储节点；
-
spec.local.path: /data/：mysql启动后会将数据存储到slave1.xincan.cn节点的/data/mysql下；
-
[root@master mysql-localVolume]# vim 4-mysql-master-pv.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-master-pv
spec:
capacity:
storage: 5Gi
volumeMode: Filesystem
accessModes:
- ReadWriteOnce
persistentVolumeReclaimPolicy: Delete
storageClassName: mysql-local-storage
local:
path: /data/
nodeAffinity:
required:
nodeSelectorTerms:
- matchExpressions:
- key: kubernetes.io/hostname
operator: In
values:
- slave1.xincan.cn
- 创建mysql从节点PersistentVolume，名称为：mysql-slave-pv
- slave2.xincan.cn：此节点是kubernetes集群中的一个子节点，作为mysql的slave节点的存储节点；
- spec.local.path: /data/：mysql启动后会将数据存储到slave2.xincan.cn节点的/data/mysql下；
[root@master mysql-localVolume]# vim 4-mysql-slave-pv.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-slave-pv
spec:
capacity:
storage: 5Gi
volumeMode: Filesystem
accessModes:
- ReadWriteOnce
persistentVolumeReclaimPolicy: Delete
storageClassName: mysql-local-storage
local:
path: /data/
nodeAffinity:
required:
nodeSelectorTerms:
- matchExpressions:
- key: kubernetes.io/hostname
operator: In
values:
- slave2.xincan.cn
- 创建mysql的资源配置ConfigMap，名称为：mysql
[root@master mysql-localVolume]# vim 6-mysql-configmap.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql
namespace: mysql
labels:
app: mysql
data:
master.cnf: |
# Mmaster配置
[mysqld]
log-bin=mysqllog
skip-name-resolve
#设置时区和字符集
default-time-zone='+8:00'
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
slave.cnf: |
# Slave配置
[mysqld]
super-read-only
skip-name-resolve
log-bin=mysql-bin
replicate-ignore-db=mysql
#设置时区和字符集
default-time-zone='+8:00'
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
-
创建mysql服务对外暴露访问资源Service，名称为：mysql-master，mysql-read
-
mysql-master：设置mysql主库对外提供服务名称，用于数据库的增删改；
-
mysql-read：设置msyql从库对外提供服务名称，用于数据的读取数据；
-
有情提醒：kubernetes选择器匹配时，需要加上指定的mysql主库，如：statefulset.kubernetes.io/pod-name: mysql-0
命令如下：
-
[root@master mysql-multiple]# kubectl -n mysql get pods --show-labels | grep mysql-0 | awk '{print $6}' | awk -F, '{print $3}'
statefulset.kubernetes.io/pod-name=mysql-0
[root@master mysql-multiple]#
[root@master mysql-localVolume]# vim 7-mysql-service.yaml
apiVersion: v1
kind: Service
metadata:
name: mysql-master
namespace: mysql
labels:
app: mysql
spec:
ports:
- name: mysql
protocol: TCP
port: 3306
targetPort: 3306
nodePort: 32060
type: NodePort
selector:
statefulset.kubernetes.io/pod-name: mysql-0
app: mysql
---
apiVersion: v1
kind: Service
metadata:
name: mysql-read
namespace: mysql
labels:
app: mysql
spec:
ports:
- name: mysql
protocol: TCP
port: 3306
targetPort: 3306
nodePort: 32070
type: NodePort
selector:
app: mysql
- 创建mysql无头服务StatefulSet，名称为：根据metadata.name+“-”+实例序号，序号从0开始,如我的最后结果是：mysql-0，mysql-1
- 通过
xtrabackup做数据库处理、如：增量备份、权量备份等等，在此不多讲 - replicas: 2 最终会生成2分mysql实例
- 通过
[root@master mysql-localVolume]# vim 8-mysql-statefulset.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
namespace: mysql
labels:
app: mysql
spec:
selector:
matchLabels:
app: mysql
serviceName: mysql-master
replicas: 2
template:
metadata:
labels:
app: mysql
spec:
initContainers:
- name: init-mysql
image: base.xincan.cn/library/mysql:v5.7.28
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: password
optional: true
command:
- bash
- "-c"
- |
set -ex
# 从 Pod 的序号，生成 server-id
[[ $(hostname) =~ -([0-9]+)$ ]] || exit 1
ordinal=${BASH_REMATCH[1]}
echo [mysqld] > /mnt/conf.d/server-id.cnf
# 由于 server-id 不能为 0，因此给 ID 加 100 来避开它
echo server-id=$((100 + $ordinal)) >> /mnt/conf.d/server-id.cnf
# 如果 Pod 的序号为 0，说明它是 Master 节点，从 ConfigMap 里把 Master 的配置文件拷贝到 /mnt/conf.d 目录下
# 否则，拷贝 ConfigMap 里的 Slave 的配置文件
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: base.xincan.cn/library/xtrabackup:v1.0
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: password
optional: true
command:
- bash
- "-c"
- |
set -ex
# 拷贝操作只需要在第一次启动时进行，所以数据已经存在则跳过
[[ -d /var/lib/mysql/mysql ]] && exit 0
# Master 节点（序号为 0）不需要这个操作
[[ $(hostname) =~ -([0-9]+)$ ]] || exit 1
ordinal=${BASH_REMATCH[1]}
[[ $ordinal == 0 ]] && exit 0
# 使用 ncat 指令，远程地从前一个节点拷贝数据到本地
ncat --recv-only mysql-$(($ordinal-1)).mysql-master 3307 | xbstream -x -C /var/lib/mysql
# 执行 --prepare，这样拷贝来的数据就可以用作恢复了
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: base.xincan.cn/library/mysql:v5.7.28
env:
# - name: MYSQL_ALLOW_EMPTY_PASSWORD
# value: "1"
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: password
optional: true
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: ["mysqladmin", "ping", "-uroot", "-p${MYSQL_ROOT_PASSWORD}"]
initialDelaySeconds: 30
periodSeconds: 10
timeoutSeconds: 5
readinessProbe:
exec:
command: ["mysqladmin", "ping", "-uroot", "-p${MYSQL_ROOT_PASSWORD}"]
initialDelaySeconds: 5
periodSeconds: 2
timeoutSeconds: 1
- name: xtrabackup
image: base.xincan.cn/library/xtrabackup:v1.0
ports:
- name: xtrabackup
containerPort: 3307
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: password
optional: true
command:
- bash
- "-c"
- |
set -ex
cd /var/lib/mysql
# 从备份信息文件里读取 MASTER_LOG_FILE 和 MASTER_LOG_POS 这 2 个字段的值，用来拼装集群初始化 SQL
if [[ -f xtrabackup_slave_info ]]; then
# 如果 xtrabackup_slave_info 文件存在，说明这个备份数据来自于另一个 Slave 节点
# 这种情况下，XtraBackup 工具在备份的时候，就已经在这个文件里自动生成了 "CHANGE MASTER TO" SQL 语句
# 所以，只需要把这个文件重命名为 change_master_to.sql.in，后面直接使用即可
mv xtrabackup_slave_info change_master_to.sql.in
# 所以，也就用不着 xtrabackup_binlog_info 了
rm -f xtrabackup_binlog_info
elif [[ -f xtrabackup_binlog_info ]]; then
# 如果只是存在 xtrabackup_binlog_info 文件，说明备份来自于 Master 节点，就需要解析这个备份信息文件，读取所需的两个字段的值
[[ $(cat xtrabackup_binlog_info) =~ ^(.*?)[[:space:]]+(.*?)$ ]] || exit 1
rm xtrabackup_binlog_info
# 把两个字段的值拼装成 SQL，写入 change_master_to.sql.in 文件
echo "CHANGE MASTER TO MASTER_LOG_FILE='${BASH_REMATCH[1]}',\
MASTER_LOG_POS=${BASH_REMATCH[2]}" > change_master_to.sql.in
fi
# 如果存在 change_master_to.sql.in，就意味着需要做集群初始化工作
if [[ -f change_master_to.sql.in ]]; then
# 但一定要先等 MySQL 容器启动之后才能进行下一步连接 MySQL 的操作
echo "Waiting for mysqld to be ready（accepting connections）"
until mysql -h 127.0.0.1 -uroot -p${MYSQL_ROOT_PASSWORD} -e "SELECT 1"; do sleep 1; done
echo "Initializing replication from clone position"
# 将文件 change_master_to.sql.in 改个名字
# 防止这个 Container 重启的时候，因为又找到了 change_master_to.sql.in，从而重复执行一遍初始化流程
mv change_master_to.sql.in change_master_to.sql.orig
# 使用 change_master_to.sql.orig 的内容，也就是前面拼装的 SQL，组成一个完整的初始化和启动 Slave 的 SQL 语句
mysql -h 127.0.0.1 -uroot -p${MYSQL_ROOT_PASSWORD} << EOF
$(< change_master_to.sql.orig),
MASTER_HOST='mysql-0.mysql-master',
MASTER_USER='root',
MASTER_PASSWORD='${MYSQL_ROOT_PASSWORD}',
MASTER_CONNECT_RETRY=10;
START SLAVE;
EOF
fi
# 使用 ncat 监听 3307 端口。
# 它的作用是，在收到传输请求的时候，直接执行 xtrabackup --backup 命令，备份 MySQL 的数据并发送给请求者
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}"
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: mysql-local-storage
resources:
requests:
storage: 2Gi
- 创建资源
- 进入mysql-single执行命令：kubectl apply -f ../mysql-localVolume/
- 监听Pod创建命令：kubectl -n mysql get pod -w -o wide
- 撤销进程命令：control+z
- 删除所有资源命令：kubectl delete -f ../mysql-localVolume/
[root@master mysql-localVolume]# kubectl apply -f ../mysql-localVolume/
namespace/mysql created
secret/mysql-secret created
storageclass.storage.k8s.io/mysql-local-storage created
persistentvolume/mysql-master-pv created
persistentvolume/mysql-slave-pv created
configmap/mysql created
service/mysql-master created
service/mysql-read created
statefulset.apps/mysql created
[root@master mysql-localVolume]#
[root@master mysql-localVolume]# kubectl -n mysql get pod -w -o wide
NAME READY STATUS RESTARTS AGE IP NODE NOMINATED NODE READINESS GATES
mysql-0 2/2 Running 0 17s 192.168.226.34 slave1.xincan.cn <none> <none>
mysql-1 2/2 Running 0 7s 192.168.34.66 slave2.xincan.cn <none> <none>
[root@master mysql-localVolume]# kubectl delete -f ../mysql-localVolume/
namespace "mysql" deleted
secret "mysql-secret" deleted
storageclass.storage.k8s.io "mysql-local-storage" deleted
persistentvolume "mysql-master-pv" deleted
persistentvolume "mysql-slave-pv" deleted
configmap "mysql" deleted
service "mysql-master" deleted
service "mysql-read" deleted
statefulset.apps "mysql" deleted
[root@master mysql-localVolume]#
四、效果展示
- 通过
kubectl -n mysql get secret,sc,pv,pvc，查询mysql命名空间下对应的secret加密资源，pv（persistentvolume）数据挂载资源，pvc资源 - 通过
kubectl -n mysql get all，查询mysql命名空间下对应的pod资源，service对外服务暴露资源 - 验证slave1.xincan.cn /data/下mysql存储文件，验证slave2.xincan.cn /data/下mysql存储文件
[root@master mysql-localVolume]# kubectl -n mysql get secret,pv,pvc
NAME TYPE DATA AGE
secret/mysql-secret Opaque 1 77s
NAME PROVISIONER RECLAIMPOLICY VOLUMEBINDINGMODE ALLOWVOLUMEEXPANSION AGE
storageclass.storage.k8s.io/mysql-local-storage kubernetes.io/no-provisioner Delete WaitForFirstConsumer false 77s
NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE
persistentvolume/mysql-master-pv 5Gi RWO Delete Bound mysql/data-mysql-0 mysql-local-storage 77s
persistentvolume/mysql-slave-pv 5Gi RWO Delete Bound mysql/data-mysql-1 mysql-local-storage 77s
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
persistentvolumeclaim/data-mysql-0 Bound mysql-master-pv 5Gi RWO mysql-local-storage 77s
persistentvolumeclaim/data-mysql-1 Bound mysql-slave-pv 5Gi RWO mysql-local-storage 67s
[root@master mysql-localVolume]# kubectl -n mysql get all
NAME READY STATUS RESTARTS AGE
pod/mysql-0 2/2 Running 0 92s
pod/mysql-1 2/2 Running 0 82s
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
service/mysql-master NodePort 10.1.118.85 <none> 3306:32060/TCP 92s
service/mysql-read NodePort 10.1.78.105 <none> 3306:32070/TCP 92s
NAME READY AGE
statefulset.apps/mysql 2/2 92s
[root@master mysql-localVolume]# kubectl -n mysql get pod -o wide
NAME READY STATUS RESTARTS AGE IP NODE NOMINATED NODE READINESS
mysql-0 2/2 Running 0 100s 192.168.226.60 slave1.xincan.cn <none> <none>
mysql-1 2/2 Running 0 90s 192.168.34.66 slave2.xincan.cn <none> <none>
[root@master mysql-localVolume]#
# slave1.xincan.cn 节点下数据存储情况
[root@slave1 data]# ls
mysql
[root@slave1 data]# cd mysql/
[root@slave1 mysql]# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysqllog.000002 mysqllog.000005 performance_schema server-cert.pem xtrabackup_backupfiles
ca-key.pem client-key.pem ib_logfile0 mysql mysqllog.000003 mysqllog.000006 private_key.pem server-key.pem
ca.pem ib_buffer_pool ib_logfile1 mysqllog.000001 mysqllog.000004 mysqllog.index public_key.pem sys
[root@slave1 mysql]#
# slave2.xincan.cn 节点下数据存储情况
[root@slave2 data]# ls
mysql
[root@slave2 data]# cd mysql/
[root@slave2 mysql]# ls
auto.cnf client-cert.pem ib_logfile1 mysql-1-relay-bin.000011 mysql-bin.000004 relay-log.info xtrabackup_binlog_pos_innodb
backup-my.cnf client-key.pem ibtmp1 mysql-1-relay-bin.index mysql-bin.index server-cert.pem xtrabackup_checkpoints
ca-key.pem ib_buffer_pool master.info mysql-bin.000001 performance_schema server-key.pem xtrabackup_info
ca.pem ibdata1 mysql mysql-bin.000002 private_key.pem sys xtrabackup_logfile
change_master_to.sql.orig ib_logfile0 mysql-1-relay-bin.000010 mysql-bin.000003 public_key.pem xtrabackup_backupfiles
[root@slave2 mysql]#
五、测试主从同步，外部navicat连接
- 命令方式测试主从同步：
- 进入主库：mysql-1这个pod，此pod为主节点，所以具有写的权限，在此执行
create database hatech，提示创建成功，并进行查询验证操作。
[root@master mysql-localVolume]# kubectl -n mysql exec -it mysql-0 -- mysql -uroot -p123456
Defaulting container name to mysql.
Use 'kubectl describe pod/mysql-0 -n mysql' to see all of the containers in this pod.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 513
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+------------------------+
| Database |
+------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xtrabackup_backupfiles |
+------------------------+
5 rows in set (0.00 sec)
mysql> create database hatech;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+------------------------+
| Database |
+------------------------+
| information_schema |
| hatech |
| mysql |
| performance_schema |
| sys |
| xtrabackup_backupfiles |
+------------------------+
6 rows in set (0.00 sec)
mysql>
- 进入从库mysql-1这个pod，通过
show databases;进行验证，可以看到数据库列表中已经出现hatech这个数据库，接着删除此库，发现删除不了,提示--super-read-only，至此从库只读模式验证完毕。
[root@master mysql-localVolume]# kubectl -n mysql exec -it mysql-1 -- mysql -uroot -p123456
Defaulting container name to mysql.
Use 'kubectl describe pod/mysql-1 -n mysql' to see all of the containers in this pod.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 850
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+------------------------+
| Database |
+------------------------+
| information_schema |
| hatech |
| mysql |
| performance_schema |
| sys |
| xtrabackup_backupfiles |
+------------------------+
6 rows in set (0.00 sec)
mysql> drop database hatech;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>
- Navicat连接测试
-
查询kuernetes集群下
service中，mysql涉及到的对外服务，可以看出有两个NodePort对外暴露的服务，友情提示：msql-read，此服务读取时包括所有的主库和从库，应为kubernetes中service本身具备服务注册功能
[root@master mysql-localVolume]# kubectl -n mysql get svc
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
mysql-master NodePort 10.1.118.85 <none> 3306:32060/TCP 48m
mysql-read NodePort 10.1.78.105 <none> 3306:32070/TCP 48m
[root@master mysql-localVolume]#
- Navicat体现
- 创建32060主库端口数据库连接成功
- 创建32070主库端口数据库连接成功
- 通过界面化操作，在主节点上创建hatech数据库后，刷新从库，则同步完成
- 通过界面化操作，在从节点上删除hatech数据库后，提示删除失败，从库提供只读功能，至此验证完毕
六：结束语
生产环境官方强烈建议利用nfs做mysql的动态存储，后续会有介绍
