k8s中部署mysql主从
环境:
OS:Centos 7
k8s:1.28
1.创建nfs目录
mkdir -p /k8s/mysql/master
mkdir -p /k8s/mysql/slave
vi /etc/exports
/k8s/ *(insecure,rw,sync,no_root_squash)
systemctl restart nfs
[root@master k8s]# more /etc/exports
/k8s/ *(rw,sync,no_root_squash)
[root@master k8s]# exportfs -v
/k8s <world>(sync,wdelay,hide,no_subtree_check,sec=sys,rw,secure,no_root_squash,no_all_squash)
其他节点执行:
[root@node1 ~]# showmount -e 192.168.1.102
Export list for 192.168.1.102:
/k8s *
[root@node2 etcd]# showmount -e 192.168.1.102
Export list for 192.168.1.102:
/k8s *
2.创建命名空间
kubectl create namespace ns-mysql
3.创建 Secret
配置root的mysql密码123
[root@master mysql]# echo -n 'mysql' | base64
bXlzcWw=
vi 1-secret.yaml
apiVersion: v1
data:
MYSQL_ROOT_PASSWORD: bXlzcWw= #密码mysql 采用base64编码后结果
kind: Secret
metadata:
name: mysql-secret
namespace: ns-mysql
type: Opaque
kubectl apply -f 1-secret.yaml
4.编写my.cnf配置文件(configmap)
master my.cnf 配置放置在 configmap
vi 2-configmap-master.yaml
[root@master mysql]# more 2-configmap-master.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql-master-cnf
namespace: ns-mysql
data:
my.cnf: |-
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
skip-name-resolve
#master-slaver repication
server_id=1
log-bin=mysql-bin
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
gtid_mode=ON
enforce_gtid_consistency = ON
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
'
kubectl apply -f 2-configmap-master.yaml
slave my.cnf配置
vi 2-configmap-slave.yaml
[root@master mysql]# more 2-configmap-slave.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql-slave-cnf
namespace: ns-mysql
data:
my.cnf: |-
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
skip-name-resolve
#master-slaver repication
server_id=2
log-bin=mysql-bin
read-only=1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
gtid_mode=ON
enforce_gtid_consistency = ON
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
'
kubectl apply -f 2-configmap-slave.yaml
5.配置pv
master挂载卷配置
vi 3-master-pv.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-pv-master
namespace: ns-mysql
spec:
accessModes:
- ReadWriteOnce
capacity:
storage: 5Gi
nfs:
path: /k8s/mysql/master
readOnly: false
server: 192.168.1.102
kubectl apply -f 3-master-pv.yaml
vi 3-slave-pv.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
name: mysql-pv-slave
namespace: ns-mysql
spec:
accessModes:
- ReadWriteOnce
capacity:
storage: 5Gi
nfs:
path: /k8s/mysql/slave
readOnly: false
server: 192.168.1.102
kubectl apply -f 3-slave-pv.yaml
6.配置pvc
master pvc:
vi 4-master-pvc.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: mysql-pvc-master
namespace: ns-mysql
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 5Gi
volumeName: mysql-pv-master
kubectl apply -f 4-master-pvc.yaml
slave pvc:
vi 4-slave-pvc.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: mysql-pvc-slave
namespace: ns-mysql
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 5Gi
volumeName: mysql-pv-slave
kubectl apply -f 4-slave-pvc.yaml
7.创建有状态副本集StatefulSet
master有状态副本集
vi 5-master-sts.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
namespace: ns-mysql
labels:
app: mysql-master
name: mysql-master
annotations:
kubesphere.io/alias-name: mysql主节点
spec:
replicas: 1
selector:
matchLabels:
app: mysql-master
template:
metadata:
labels:
app: mysql-master
annotations:
kubesphere.io/containerSecrets: null
logging.kubesphere.io/logsidecar-config: '{}'
spec:
containers:
- name: master-container
##type: worker 去掉
imagePullPolicy: IfNotPresent
resources:
requests:
cpu: '0.01'
memory: 10Mi
limits:
cpu: '0.98'
memory: 1700Mi
image: registry.cn-shenzhen.aliyuncs.com/hxlk8s/mysql:5.7.30
ports:
- name: tcp-3306
protocol: TCP
containerPort: 3306
##servicePort: 3306 去掉
- name: tcp-33060
protocol: TCP
containerPort: 33060
##servicePort: 33060 去掉
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: MYSQL_ROOT_PASSWORD
volumeMounts:
- name: master-cnf-volume
readOnly: false
mountPath: /etc/mysql
- name: master-data-volume
readOnly: false
mountPath: /var/lib/mysql
serviceAccount: default
affinity:
podAntiAffinity:
preferredDuringSchedulingIgnoredDuringExecution:
- weight: 100
podAffinityTerm:
labelSelector:
matchLabels:
app: mysql-master
topologyKey: kubernetes.io/hostname
initContainers: []
imagePullSecrets: null
volumes:
- name: master-cnf-volume #映射configMap信息
configMap:
name: mysql-master-cnf
items:
- key: my.cnf
path: my.cnf
- name: master-data-volume #映射pvc信息
persistentVolumeClaim:
claimName: mysql-pvc-master
updateStrategy:
type: RollingUpdate
rollingUpdate:
partition: 0
serviceName: mysql-master
kubectl apply -f 5-master-sts.yaml
slave有状态副本集
vi 5-slave-sts.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
namespace: ns-mysql
labels:
app: mysql-slave
name: mysql-slave
annotations:
kubesphere.io/alias-name: mysql主节点
spec:
replicas: 1
selector:
matchLabels:
app: mysql-slave
template:
metadata:
labels:
app: mysql-slave
annotations:
kubesphere.io/containerSecrets: null
logging.kubesphere.io/logsidecar-config: '{}'
spec:
containers:
- name: slave-container
##type: worker
imagePullPolicy: IfNotPresent
resources:
requests:
cpu: '0.01'
memory: 10Mi
limits:
cpu: '0.98'
memory: 1700Mi
image: registry.cn-shenzhen.aliyuncs.com/hxlk8s/mysql:5.7.30
ports:
- name: tcp-3306
protocol: TCP
containerPort: 3306
##servicePort: 3306
- name: tcp-33060
protocol: TCP
containerPort: 33060
##servicePort: 33060
env:
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
name: mysql-secret
key: MYSQL_ROOT_PASSWORD
volumeMounts:
- name: slave-cnf-volume
readOnly: false
mountPath: /etc/mysql
- name: slave-data-volume
readOnly: false
mountPath: /var/lib/mysql
serviceAccount: default
affinity:
podAntiAffinity:
preferredDuringSchedulingIgnoredDuringExecution:
- weight: 100
podAffinityTerm:
labelSelector:
matchLabels:
app: mysql-slave
topologyKey: kubernetes.io/hostname
initContainers: []
imagePullSecrets: null
volumes:
- name: slave-cnf-volume #映射configMap信息
configMap:
name: mysql-slave-cnf
items:
- key: my.cnf
path: my.cnf
- name: slave-data-volume #映射pvc信息
persistentVolumeClaim:
claimName: mysql-pvc-slave
updateStrategy:
type: RollingUpdate
rollingUpdate:
partition: 0
serviceName: mysql-slave
kubectl apply -f 5-slave-sts.yaml
8.创建无头服务HeadlessService
master无头服务
vi 6-master-headless-svc.yaml
apiVersion: v1
kind: Service
metadata:
namespace: ns-mysql
labels:
app: mysql-master
annotations:
kubesphere.io/serviceType: statefulservice
kubesphere.io/alias-name: mysql主节点
name: mysql-master
spec:
sessionAffinity: ClientIP
selector:
app: mysql-master
ports:
- name: tcp-3306
protocol: TCP
port: 3306
targetPort: 3306
- name: tcp-33060
protocol: TCP
port: 33060
targetPort: 33060
clusterIP: None
sessionAffinityConfig:
clientIP:
timeoutSeconds: 10800
kubectl apply -f 6-master-headless-svc.yaml
slave无头服务
vi 6-slave-headless-svc.yaml
apiVersion: v1
kind: Service
metadata:
namespace: ns-mysql
labels:
app: mysql-slave
annotations:
kubesphere.io/serviceType: statefulservice
kubesphere.io/alias-name: mysql主节点
name: mysql-slave
spec:
sessionAffinity: ClientIP
selector:
app: mysql-slave
ports:
- name: tcp-3306
protocol: TCP
port: 3306
targetPort: 3306
- name: tcp-33060
protocol: TCP
port: 33060
targetPort: 33060
clusterIP: None
sessionAffinityConfig:
clientIP:
timeoutSeconds: 10800
kubectl apply -f 6-slave-headless-svc.yaml
9.创建外部访问的NodePort Service
master NodePort Service
vi 7-master-nodeport-svc.yaml
apiVersion: v1
kind: Service
metadata:
name: mysql-master-front
labels:
app: mysql-master
namespace: ns-mysql
spec:
selector:
app: mysql-master
type: NodePort
ports:
- name: ''
port: 3306
protocol: TCP
targetPort: 3306
nodePort: 30001
sessionAffinity: None
kubectl apply -f 7-master-nodeport-svc.yaml
slave NodePort Service
vi 7-slave-nodeport-svc.yaml
apiVersion: v1
kind: Service
metadata:
name: mysql-slave-front
labels:
app: mysql-slave
namespace: ns-mysql
spec:
selector:
app: mysql-slave
type: NodePort
ports:
- name: ''
port: 3306
protocol: TCP
targetPort: 3306
nodePort: 30002
sessionAffinity: None
kubectl apply -f 7-slave-nodeport-svc.yaml
此时,mysql主机的部署已经完成,可以使用外部客户端访问了.
10.主从同步-创建同步账号
[root@master mysql]# kubectl get pod -n ns-mysql
NAME READY STATUS RESTARTS AGE
mysql-master-0 1/1 Running 0 28m
mysql-slave-0 1/1 Running 0 26m
进入到主库
kubectl exec -it mysql-master-0 -n ns-mysql -- /bin/bash
# 1.进入mysql内部
> mysql -uroot -pmysql
#切换到 mysql DB
mysql> USE mysql;
# 查看root用户是否具备远程访问权限
mysql> select Host,User,authentication_string,password_expired,password_last_changed from user;
mysql> show grants for 'root'@'%';
+-------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
# 2.添加用来同步的用户
mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by 'mysql';
Query OK, 0 rows affected, 1 warning (0.01 sec)
# 4.查看master状态
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 444
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
11.创建一个 busybox,执行以下命令查看主库dns
vi 8-busybox.yaml
apiVersion: v1
kind: Pod
metadata:
name: busybox
namespace: ns-mysql
spec:
containers:
- name: busybox
image: registry.cn-shenzhen.aliyuncs.com/hxlk8s/busybox:1.28
command:
- sleep
- "3600"
imagePullPolicy: IfNotPresent
restartPolicy: Always
busybox这里使用1.28版本的,之前使用1.32版本,报如下错误.
[root@master mysql]# kubectl exec -it busybox -n ns-mysql -- /bin/sh
/ # nslookup mysql-master
Server: 10.96.0.10
Address: 10.96.0.10:53
** server can't find mysql-master.ns-mysql.svc.cluster.local: NXDOMAIN
*** Can't find mysql-master.svc.cluster.local: No answer
*** Can't find mysql-master.cluster.local: No answer
*** Can't find mysql-master.ns-mysql.svc.cluster.local: No answer
*** Can't find mysql-master.svc.cluster.local: No answer
*** Can't find mysql-master.cluster.local: No answer
kubectl apply -f 8-busybox.yaml
进入busybox容器内，执行以下命令
[root@master mysql]# kubectl exec -it busybox -n ns-mysql -- /bin/sh
/ # nslookup mysql-master
Server: 10.96.0.10
Address: 10.96.0.10:53
11.配置主从同步
然后进入到mysql-slave内部
[root@master mysql]# kubectl get pods -n ns-mysql
NAME READY STATUS RESTARTS AGE
busybox 1/1 Running 0 5m52s
mysql-master-0 1/1 Running 0 39m
mysql-slave-0 1/1 Running 0 37m
kubectl exec -it mysql-slave-0 -n ns-mysql -- /bin/bash
# 进入mysql内部
mysql -uroot -pmysql
change master to master_host='mysql-master.ns-mysql.svc.cluster.local',master_user='repl',master_password='mysql',master_port=3306,master_auto_position=1;
# 启动从库同步
start slave;
# 查看从从库状态
show slave status\G;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master.ns-mysql.svc.cluster.local
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 444
Relay_Log_File: mysql-slave-0-relay-bin.000002
Relay_Log_Pos: 657
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
12.主从验证
登录主库:
我们这里使用mysql客户端通过30001端口进行登录
mysql -h 192.168.1.102 -uroot -pmysql -P30001
mysql -h 192.168.1.105 -uroot -pmysql -P30001
mysql -h 192.168.1.106 -uroot -pmysql -P30001
每个nodeport都可以登录
mysql -h 192.168.1.102 -uroot -pmysql -P30001
create database db_test;
use db_test;
create table tb_test(
id int NOT NULL AUTO_INCREMENT COMMENT '主键',
name varchar(32),
PRIMARY KEY (`id`)
);
insert into tb_test(name) values('name1');
insert into tb_test(name) values('name2');
insert into tb_test(name) values('name3');
insert into tb_test(name) values('name4');
insert into tb_test(name) values('name5');
从库查看
[root@node2 ~]# mysql -h 192.168.1.102 -uroot -pmysql -P30002
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.30-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use db_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [db_test]> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tb_test |
+-------------------+
1 row in set (0.01 sec)
MySQL [db_test]> select * from tb_test;
+----+-------+
| id | name |
+----+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
| 5 | name5 |
+----+-------+
5 rows in set (0.00 sec)
13.通过客户端mysqldump 导出k8s mysql中的数据
/opt/mysql57/bin/mysqldump -h 192.168.1.102 -uroot -P30001 -p db_test tb_test --default-character-set=utf8 --skip-disable-keys --lock-tables=false --hex-blob --skip-add-drop-table>/tmp/tb_test.sql
疑问点:xtrabackup如何在k8s中使用
分类:
docker&k8s
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
2019-03-04 oracle传输表空间相关