namespace
mysql-ns.yaml
apiVersion: v1
kind: Namespace
metadata:
labels:
kubernetes.io/metadata.name: wgs-mysql
name: wgs-mysql
创建ns
# kubectl apply -f mysql-ns.yaml
namespace/wgs-mysql created
查看ns
# kubectl get ns wgs-mysql -o yaml
apiVersion: v1
kind: Namespace
metadata:
annotations:
kubectl.kubernetes.io/last-applied-configuration: |
{"apiVersion":"v1","kind":"Namespace","metadata":{"annotations":{},"name":"wgs-mysql"}}
creationTimestamp: "2023-01-05T08:53:13Z"
labels:
kubernetes.io/metadata.name: wgs-mysql
name: wgs-mysql
resourceVersion: "1170334"
uid: cacc6993-b891-4a50-8491-21944d1b4383
spec:
finalizers:
- kubernetes
status:
phase: Active
secret
mysql-secret.yaml
apiVersion: v1
kind: Secret
metadata:
name: mysql-auth
namespace: wgs-mysql
type: Opaque
data:
username: cm9vdA== # echo -n 'root' | base64
password: MTIzNDU2 # echo -n '123456' | base64
创建secret
# kubectl apply -f mysql-secret.yaml
secret/mysql-auth created
查看secret
# kubectl get secret -n wgs-mysql
NAME TYPE DATA AGE
mysql-auth Opaque 2 24s
pv
mysql-pv.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-datadir-1
namespace: wgs-mysql
spec:
capacity:
storage: 5Gi
accessModes:
- ReadWriteOnce
storageClassName: mysql
nfs:
path: /data/k8s-data/mysql/mysql-datadir-1
server: 192.168.174.102
---
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-datadir-2
namespace: wgs-mysql
spec:
capacity:
storage: 5Gi
accessModes:
- ReadWriteOnce
storageClassName: mysql
nfs:
path: /data/k8s-data/mysql/mysql-datadir-2
server: 192.168.174.102
---
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-datadir-3
namespace: wgs-mysql
spec:
capacity:
storage: 5Gi
accessModes:
- ReadWriteOnce
storageClassName: mysql
nfs:
path: /data/k8s-data/mysql/mysql-datadir-3
server: 192.168.174.102
创建pv
# kubectl apply -f mysql-pv.yaml
persistentvolume/mysql-datadir-1 created
persistentvolume/mysql-datadir-2 created
persistentvolume/mysql-datadir-3 created
查看pv
# kubectl get pv -n wgs-mysql
NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE
mysql-datadir-1 5Gi RWO Retain Available 33s
mysql-datadir-2 5Gi RWO Retain Available 33s
mysql-datadir-3 5Gi RWO Retain Available 33s
configMap
mysql-configmap.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql
namespace: wgs-mysql
labels:
app: mysql
data:
master.cnf: |
# Apply this config only on the master.
[mysqld]
log-bin
log_bin_trust_function_creators=1
lower_case_table_names=1
slave.cnf: |
# Apply this config only on slaves.
[mysqld]
super-read-only
log_bin_trust_function_creators=1
创建configmap资源
# kubectl apply -f mysql-configmap.yaml
configmap/mysql created
查看configmap资源
# kubectl describe configmap mysql -n wgs-mysql
Name: mysql
Namespace: wgs-mysql
Labels: app=mysql
Annotations: <none>
Data
====
master.cnf:
----
# Apply this config only on the master.
[mysqld]
log-bin
log_bin_trust_function_creators=1
lower_case_table_names=1
slave.cnf:
----
# Apply this config only on slaves.
[mysqld]
super-read-only
log_bin_trust_function_creators=1
BinaryData
====
Events: <none>
service
mysql-svc.yaml
# Headless service for stable DNS entries of StatefulSet members.
apiVersion: v1
kind: Service
metadata:
namespace: wgs-mysql
name: mysql
labels:
app: mysql
spec:
ports:
- name: mysql
port: 3306
clusterIP: None
selector:
app: mysql
---
# Client service for connecting to any MySQL instance for reads.
# For writes, you must instead connect to the master: mysql-0.mysql.
apiVersion: v1
kind: Service
metadata:
name: mysql-read
namespace: wgs-mysql
labels:
app: mysql
spec:
ports:
- name: mysql
port: 3306
selector:
app: mysql
创建service资源
# kubectl apply -f mysql-svc.yaml
service/mysql created
service/mysql-read created
查看svc
# kubectl get svc -n wgs-mysql
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
mysql ClusterIP None <none> 3306/TCP 45s
mysql-read ClusterIP 10.100.176.252 <none> 3306/TCP 45s
StatefulSet
mysql-statefulset.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql
namespace: wgs-mysql
spec:
selector:
matchLabels:
app: mysql
app.kubernetes.io/name: mysql
serviceName: mysql
replicas: 3
template:
metadata:
labels:
app: mysql
app.kubernetes.io/name: mysql
spec:
initContainers:
- name: init-mysql
image: mysql:5.7
command:
- bash
- "-c"
- |
set -ex
# 基于 Pod 序号生成 MySQL 服务器的 ID。
[[ $HOSTNAME =~ -([0-9]+)$ ]] || exit 1
ordinal=${BASH_REMATCH[1]}
echo [mysqld] > /mnt/conf.d/server-id.cnf
# 添加偏移量以避免使用 server-id=0 这一保留值。
echo server-id=$((100 + $ordinal)) >> /mnt/conf.d/server-id.cnf
# 将合适的 conf.d 文件从 config-map 复制到 emptyDir。
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
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-pass
key: password
volumeMounts:
- name: conf
mountPath: /mnt/conf.d
- name: config-map
mountPath: /mnt/config-map
- name: clone-mysql
image: uhub.service.ucloud.cn/xk-base/xtrabackup:1.0
command:
- bash
- "-c"
- |
set -ex
# 如果已有数据，则跳过克隆。
[[ -d /var/lib/mysql/mysql ]] && exit 0
# 跳过主实例（序号索引 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
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-pass
key: password
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
containers:
- name: mysql
image: mysql:5.7
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-pass
key: password
#env:
#- name: MYSQL_ALLOW_EMPTY_PASSWORD
# value: "1"
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","-p123456"]
initialDelaySeconds: 30
periodSeconds: 10
timeoutSeconds: 5
readinessProbe:
exec:
# 检查我们是否可以通过 TCP 执行查询（skip-networking 是关闭的）。
command: ["mysql", "-h", "127.0.0.1", "-uroot", "-p123456", "-e", "SELECT 1"]
#command: ["mysql", "-h", "127.0.0.1", "-e", "SELECT 1"]
initialDelaySeconds: 5
periodSeconds: 2
timeoutSeconds: 1
- name: xtrabackup
image: uhub.service.ucloud.cn/xk-base/xtrabackup:1.0
ports:
- name: xtrabackup
containerPort: 3307
command:
- bash
- "-c"
- |
set -ex
cd /var/lib/mysql
# 确定克隆数据的 binlog 位置（如果有的话）。
if [[ -f xtrabackup_slave_info && "x$(<xtrabackup_slave_info)" != "x" ]]; then
# XtraBackup 已经生成了部分的 “CHANGE MASTER TO” 查询
# 因为我们从一个现有副本进行克隆。(需要删除末尾的分号!)
cat xtrabackup_slave_info | sed -E 's/;$//g' > change_master_to.sql.in
# 在这里要忽略 xtrabackup_binlog_info （它是没用的）。
rm -f xtrabackup_slave_info xtrabackup_binlog_info
elif [[ -f xtrabackup_binlog_info ]]; then
# 我们直接从主实例进行克隆。解析 binlog 位置。
[[ `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
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"
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-pass
key: password
volumeMounts:
- name: data
mountPath: /var/lib/mysql
subPath: mysql
- name: conf
mountPath: /etc/mysql/conf.d
resources:
requests:
cpu: 100m
memory: 100Mi
volumes:
- name: conf
emptyDir: {}
- name: config-map
configMap:
name: mysql
volumeClaimTemplates:
- metadata:
name: data
namespace: wgs-mysql
spec:
accessModes: ["ReadWriteOnce"]
storageClassName: mysql
resources:
requests:
storage: 5Gi
创建mysql资源
# kubectl apply -f mysql-statefulset.yaml
statefulset.apps/mysql created
查看pod
# kubectl get pod -n wgs-mysql
NAME READY STATUS RESTARTS AGE
mysql-0 2/2 Running 0 2m43s
mysql-1 2/2 Running 0 2m24s
mysql-2 2/2 Running 0 2m6s
查看container 日志
init-mysql
~# kubectl logs mysql-0 init-mysql -n wgs-mysql
+ [[ mysql-0 =~ -([0-9]+)$ ]]
+ ordinal=0
+ echo '[mysqld]'
+ echo server-id=100
+ [[ 0 -eq 0 ]]
+ cp /mnt/config-map/master.cnf /mnt/conf.d/
clone-mysql
# kubectl logs mysql-0 clone-mysql -n wgs-mysql
+ [[ -d /var/lib/mysql/mysql ]]
+ exit 0
mysql
# kubectl logs mysql-0 mysql -n wgs-mysql
2023-01-09 02:26:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.40-1.el7 started.
2023-01-09 02:26:40+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-01-09 02:26:40+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.40-1.el7 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2023-01-09T02:26:41.597858Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-01-09T02:26:41.615131Z 0 [Note] mysqld (mysqld 5.7.40-log) starting as process 1 ...
2023-01-09T02:26:41.623624Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=mysql-0-bin' to avoid this problem.
2023-01-09T02:26:41.636487Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-01-09T02:26:41.636615Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-01-09T02:26:41.636643Z 0 [Note] InnoDB: Uses event mutexes
2023-01-09T02:26:41.636661Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
...
2023-01-09T02:26:42.634308Z 0 [Note] Failed to start slave threads for channel ''
2023-01-09T02:26:42.935982Z 0 [Note] Event Scheduler: Loaded 0 events
2023-01-09T02:26:42.936353Z 0 [Note] mysqld: ready for connections.
Version: '5.7.40-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
xtrabackup
# kubectl logs mysql-0 xtrabackup -n wgs-mysql
+ cd /var/lib/mysql
+ [[ -f xtrabackup_slave_info ]]
+ [[ -f xtrabackup_binlog_info ]]
+ [[ -f change_master_to.sql.in ]]
+ 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=123456'
查看主从状态
mysql-0
# kubectl run mysql-client --image=mysql:5.7 -i --rm --restart=Never -n wgs-mysql -- mysql -hmysql-0.mysql -uroot -p123456 -e 'show master status\G'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
File: mysql-0-bin.000003
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
pod "mysql-client" deleted
mysql-1
# kubectl run mysql-client --image=mysql:5.7 -i --rm --restart=Never -n wgs-mysql -- mysql -hmysql-1.mysql -uroot -p123456 -e 'show slave status\G'
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-0-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-1-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-0-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: 531
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: f2783afc-8dac-11ed-8f09-dabada1a97cf
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:
pod "mysql-client" deleted
mysql-2
# kubectl run mysql-client --image=mysql:5.7 -i --rm --restart=Never -n wgs-mysql -- mysql -hmysql-2.mysql -uroot -p123456 -e 'show slave status\G'
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-0-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-2-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-0-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: 531
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: f2783afc-8dac-11ed-8f09-dabada1a97cf
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:
pod "mysql-client" deleted
验证主从同步
创建测试数据
# kubectl run mysql-client --image=mysql:5.7 -i --rm --restart=Never -n wgs-mysql --\
> mysql -h mysql-0.mysql -p123456 <<EOF
> CREATE DATABASE test;
> CREATE TABLE test.messages (message VARCHAR(250));
> INSERT INTO test.messages VALUES ('hello');
> EOF
If you don't see a command prompt, try pressing enter.
pod "mysql-client" deleted
查询数据
# kubectl run mysql-client --image=mysql:5.7 -i -t --rm --restart=Never -n wgs-mysql --\
> mysql -h mysql-read -p123456 -e "SELECT * FROM test.messages"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------+
| message |
+---------+
| hello |
+---------+
pod "mysql-client" deleted
查看read请求负载效果
# kubectl run mysql-client-loop --image=mysql:5.7 -i -t --rm --restart=Never -n wgs-mysql --\
> bash -ic "while sleep 1; do mysql -h mysql-read -p123456 -e 'SELECT @@server_id,NOW()'; done"
If you don't see a command prompt, try pressing enter.
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+---------------------+
| @@server_id | NOW() |
+-------------+---------------------+
| 102 | 2023-01-09 03:33:10 |
+-------------+---------------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+---------------------+
| @@server_id | NOW() |
+-------------+---------------------+
| 101 | 2023-01-09 03:33:11 |
+-------------+---------------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+---------------------+
| @@server_id | NOW() |
+-------------+---------------------+
| 100 | 2023-01-09 03:33:12 |
+-------------+---------------------+
查看pv状态
pv
# kubectl get pv -n wgs-mysql
NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE
mysql-datadir-1 5Gi RWO Retain Bound wgs-mysql/data-mysql-0 mysql 2d17h
mysql-datadir-2 5Gi RWO Retain Bound wgs-mysql/data-mysql-1 mysql 2d17h
mysql-datadir-3 5Gi RWO Retain Bound wgs-mysql/data-mysql-2 mysql 2d17h
pvc
# kubectl get pvc -n wgs-mysql
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
data-mysql-0 Bound mysql-datadir-1 5Gi RWO mysql 2d17h
data-mysql-1 Bound mysql-datadir-2 5Gi RWO mysql 2d17h
data-mysql-2 Bound mysql-datadir-3 5Gi RWO mysql 2d17h
清理mysql资源
删除mysql-statefulset
# kubectl delete -f mysql-statefulset.yaml
statefulset.apps "mysql" deleted
删除pvc
kubectl delete pvc -l app=mysql -n wgs-mysql
# kubectl delete pvc data-mysql-2 -n wgs-mysql
persistentvolumeclaim "data-mysql-2" deleted
# kubectl delete pvc data-mysql-1 -n wgs-mysql
persistentvolumeclaim "data-mysql-1" deleted
# kubectl delete pvc data-mysql-0 -n wgs-mysql
persistentvolumeclaim "data-mysql-0" deleted
删除pv
# kubectl delete -f mysql-pv.yaml
persistentvolume "mysql-datadir-1" deleted
persistentvolume "mysql-datadir-2" deleted
persistentvolume "mysql-datadir-3" deleted
删除其它资源
# kubectl delete -f mysql-secret.yaml -f mysql-configmap.yaml -f mysql-secret.yaml