mysql在k8s环境里安装及搭建主从架构

master_host: 这个参数是master的地址，kubernetes提供的解析规则是 pod名称.service名称.命名空间.svc.cluster.local ，所以我们master的mysql地址是 deploy-mysql-master-0.deploy-mysql-master-svc.deploy-test.svc.cluster.local。`user_id` BIGINT

幻灭431

1360人浏览 · 2024-08-21 13:52:09

幻灭431 · 2024-08-21 13:52:09 发布

1、环境准备

k8s集群，版本1.27.0

2、搭建nfs服务器

本次用的k8smaster节点作为nfs服务器，因为需要在两个工作节点上连接nfs，所以工作节点上也要安装nfs

yum install -y nfs-utils

我们直接在nfs服务器(k8s-master)当中创建这三个目录并写入 /etc/exports 文件夹中（创建的目录可以修改）

mkdir -p /data/nfs/{mysql-master,mysql-slaver-01,mysql-slaver-02}

cat >> /etc/exports << EOF

/data/nfs/mysql-master *(rw,sync,no_root_squash)

/data/nfs/mysql-slaver-01 *(rw,sync,no_root_squash)

/data/nfs/mysql-slaver-02 *(rw,sync,no_root_squash)

EOF

执行命令启动并验证目录是否暴露成功

systemctl enable --now nfs-server

showmount -e 192.168.15.21

3、安装mysql集群

(1) 创建命名空间

kubectl create namespace mysql

或

apiVersion: v1

kind: Namespace

metadata:

name: mysql

spec: {}

status: {}

(2) 创建MySQL密码的Secret

创建一个存储了MySQL密码的Secret，我们可以直接使用这行命令生成这个Secret的资源清单文件：

# 注意修改root的密码和命名空间，我的root密码设置为的是qwerty,生成后存入yaml文件执行

kubectl create secret generic hamysql-password --namespace=mysql --from-literal=mysql_root_password=qwerty --dry-run=client -o=yaml

(3) 安装MySQL主节点

创建pv和pvc

apiVersion: v1

kind: PersistentVolume

metadata:

name: deploy-mysql-master-nfs-pv

namespace: mysql

spec:

capacity:

storage: 1Gi

accessModes:

- ReadWriteMany

nfs:

# 注意修改IP地址和暴露的目录（如果不一样）

server: 192.168.1.160

path: /data/nfs/mysql-master

storageClassName: "nfs"

---

apiVersion: v1

kind: PersistentVolumeClaim

metadata:

name: deploy-mysql-master-nfs-pvc

namespace: mysql

spec:

accessModes:

- ReadWriteMany

storageClassName: "nfs"

resources:

requests:

storage: 1Gi

volumeName: deploy-mysql-master-nfs-pv

创建pv和pvc kubectl apply -f xxx

kubectl get pv,pvc -n mysql

写一个my.cnf文件

[mysqld]

skip-host-cache

skip-name-resolve

datadir = /var/lib/mysql

socket = /var/run/mysqld/mysqld.sock

secure-file-priv = /var/lib/mysql-files

pid-file = /var/run/mysqld/mysqld.pid

user = mysql

secure-file-priv = NULL

server-id = 1

log-bin = master-bin

log_bin_index = master-bin.index

binlog_do_db =wvg_user

binlog_ignore_db = information_schema

binlog_ignore_db = mysql

binlog_ignore_db = performance_schema

binlog_ignore_db = sys

binlog-format = ROW

[client]

socket = /var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

注意几个配置

# server id，要注意多个mysql节点唯一

server-id = 1

# 生成的logbin的文件名

log-bin = master-bin

log_bin_index = master-bin.index

# 同步哪个数据库，这里我们为了测试之同步wvg_user这个数据库,写*/不写就是所有都同步

binlog_do_db = wvg_user

# 排除哪个数据库，可以写多行，排除的数据库不会被主从同步，这里写上mysql自带的几个数据库

binlog_ignore_db = information_schema

... # 还有几行省略

# binlog的格式

binlog-format = ROW

接下来将创建一个ConfigMap来存储这个配置文件。可以使用以下配置生成yaml资源清单文件内容：

kubectl create configmap mysql-master-cm -n mysql --from-file=my.cnf --dry-run=client -o yaml

部署mysql主节点

apiVersion: v1

data:

my.cnf: |

[mysqld]

skip-host-cache

skip-name-resolve

datadir = /var/lib/mysql

socket = /var/run/mysqld/mysqld.sock

secure-file-priv = /var/lib/mysql-files

pid-file = /var/run/mysqld/mysqld.pid

user = mysql

secure-file-priv = NULL

server-id = 1

log-bin = master-bin

log_bin_index = master-bin.index

binlog_do_db = wvg_user

binlog_ignore_db = information_schema

binlog_ignore_db = mysql

binlog_ignore_db = performance_schema

binlog_ignore_db = sys

binlog-format = ROW

[client]

socket = /var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

kind: ConfigMap

metadata:

name: mysql-master-cm

namespace: mysql

---

apiVersion: v1

kind: Service

metadata:

name: deploy-mysql-master-svc

namespace: mysql

labels:

app: mysql-master

spec:

ports:

- port: 3306

name: mysql

targetPort: 3306

nodePort: 30306

selector:

app: mysql-master

type: NodePort

sessionAffinity: ClientIP

---

apiVersion: apps/v1

kind: StatefulSet

metadata:

name: deploy-mysql-master

namespace: mysql

spec:

selector:

matchLabels:

app: mysql-master

serviceName: "deploy-mysql-master-svc"

replicas: 1

template:

metadata:

labels:

app: mysql-master

spec:

terminationGracePeriodSeconds: 10

containers:

- args:

- --character-set-server=utf8mb4

- --collation-server=utf8mb4_unicode_ci

- --lower_case_table_names=1

- --default-time_zone=+8:00

name: mysql

image: docker.io/library/mysql:8.0.34

ports:

- containerPort: 3306

name: mysql

volumeMounts:

- name: mysql-data

mountPath: /var/lib/mysql

- name: mysql-conf

mountPath: /etc/my.cnf

readOnly: true

subPath: my.cnf

env:

- name: MYSQL_ROOT_PASSWORD

valueFrom:

secretKeyRef:

key: mysql_root_password

name: mysql-password

volumes:

- name: mysql-data

persistentVolumeClaim:

claimName: deploy-mysql-master-nfs-pvc

- name: mysql-conf

configMap:

name: mysql-master-cm

items:

- key: my.cnf

mode: 0644

path: my.cnf

部署这个文件 kubectl apply -f mysql-master.yaml

(4) 安装mysql两个从节点

创建pv和pvc

apiVersion: v1

kind: PersistentVolume

metadata:

name: deploy-mysql-slave-01-nfs-pv

namespace: mysql

spec:

capacity:

storage: 1Gi

accessModes:

- ReadWriteMany

nfs:

server: 192.168.15.21

path: /data/nfs/mysql-slaver-01

storageClassName: "nfs"

---

apiVersion: v1

kind: PersistentVolumeClaim

metadata:

name: deploy-mysql-slave-01-nfs-pvc

namespace: mysql

spec:

accessModes:

- ReadWriteMany

storageClassName: "nfs"

resources:

requests:

storage: 1Gi

volumeName: deploy-mysql-slave-01-nfs-pv

第一个从节点的配置文件

[mysqld]

skip-host-cache

skip-name-resolve

datadir = /var/lib/mysql

socket = /var/run/mysqld/mysqld.sock

secure-file-priv = /var/lib/mysql-files

pid-file = /var/run/mysqld/mysqld.pid

user = mysql

secure-file-priv = NULL

server-id = 2

log-bin = slave-bin

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

[client]

socket = /var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

创建configmap

kubectl create configmap mysql-slave-01-cm -n mysql --from-file=my.cnf --dry-run=client -o yaml

创建从节点yaml文件

apiVersion: v1

data:

my.cnf: |

[mysqld]

skip-host-cache

skip-name-resolve

datadir = /var/lib/mysql

socket = /var/run/mysqld/mysqld.sock

secure-file-priv = /var/lib/mysql-files

pid-file = /var/run/mysqld/mysqld.pid

user = mysql

secure-file-priv = NULL

server-id = 2

log-bin = slave-bin

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

[client]

socket = /var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

kind: ConfigMap

metadata:

name: mysql-slave-01-cm

namespace: mysql

---

apiVersion: v1

kind: Service

metadata:

name: deploy-mysql-slave-svc

namespace: mysql

labels:

app: mysql-slave

spec:

ports:

- port: 3306

name: mysql

targetPort: 3306

nodePort: 30308

selector:

app: mysql-slave

type: NodePort

sessionAffinity: ClientIP

---

apiVersion: apps/v1

kind: StatefulSet

metadata:

name: deploy-mysql-slave-01

namespace: mysql

spec:

selector:

matchLabels:

app: mysql-slave

serviceName: "deploy-mysql-slave-svc"

replicas: 1

template:

metadata:

labels:

app: mysql-slave

spec:

terminationGracePeriodSeconds: 10

containers:

- args:

- --character-set-server=utf8mb4

- --collation-server=utf8mb4_unicode_ci

- --lower_case_table_names=1

- --default-time_zone=+8:00

name: mysql

image: docker.io/library/mysql:8.0.34

ports:

- containerPort: 3306

name: mysql

volumeMounts:

- name: mysql-data

mountPath: /var/lib/mysql

- name: mysql-conf

mountPath: /etc/my.cnf

readOnly: true

subPath: my.cnf

env:

- name: MYSQL_ROOT_PASSWORD

valueFrom:

secretKeyRef:

key: mysql_root_password

name: mysql-password

volumes:

- name: mysql-data

persistentVolumeClaim:

claimName: deploy-mysql-slave-01-nfs-pvc

- name: mysql-conf

configMap:

name: mysql-slave-01-cm

items:

- key: my.cnf

mode: 0644

path: my.cnf

第二个pv和pvc

apiVersion: v1

kind: PersistentVolume

metadata:

name: deploy-mysql-slave-02-nfs-pv

namespace: mysql

spec:

capacity:

storage: 1Gi

accessModes:

- ReadWriteMany

nfs:

server: 192.168.15.21

path: /data/nfs/mysql-slaver-02

storageClassName: "nfs"

---

apiVersion: v1

kind: PersistentVolumeClaim

metadata:

name: deploy-mysql-slave-02-nfs-pvc

namespace: mysql

spec:

accessModes:

- ReadWriteMany

storageClassName: "nfs"

resources:

requests:

storage: 1Gi

volumeName: deploy-mysql-slave-02-nfs-pv

配置文件

[mysqld]

skip-host-cache

skip-name-resolve

datadir = /var/lib/mysql

socket = /var/run/mysqld/mysqld.sock

secure-file-priv = /var/lib/mysql-files

pid-file = /var/run/mysqld/mysqld.pid

user = mysql

secure-file-priv = NULL

server-id = 3

log-bin = slave-bin

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

[client]

socket = /var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

configmap

kubectl create configmap mysql-slave-02-cm -n deploy-test --from-file=my.cnf --dry-run=client -o yaml

安装mysql2

apiVersion: v1

data:

my.cnf: |

[mysqld]

skip-host-cache

skip-name-resolve

datadir = /var/lib/mysql

socket = /var/run/mysqld/mysqld.sock

secure-file-priv = /var/lib/mysql-files

pid-file = /var/run/mysqld/mysqld.pid

user = mysql

secure-file-priv = NULL

server-id = 3

log-bin = slave-bin

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

[client]

socket = /var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

kind: ConfigMap

metadata:

name: mysql-slave-02-cm

namespace: mysql

---

apiVersion: apps/v1

kind: StatefulSet

metadata:

name: deploy-mysql-slave-02

namespace: mysql

spec:

selector:

matchLabels:

app: mysql-slave-02

serviceName: "deploy-mysql-slave-svc"

replicas: 1

template:

metadata:

labels:

app: mysql-slave-02

spec:

terminationGracePeriodSeconds: 10

containers:

- args:

- --character-set-server=utf8mb4

- --collation-server=utf8mb4_unicode_ci

- --lower_case_table_names=1

- --default-time_zone=+8:00

name: mysql

image: docker.io/library/mysql:8.0.34

ports:

- containerPort: 3306

name: mysql

volumeMounts:

- name: mysql-data

mountPath: /var/lib/mysql

- name: mysql-conf

mountPath: /etc/my.cnf

readOnly: true

subPath: my.cnf

env:

- name: MYSQL_ROOT_PASSWORD

valueFrom:

secretKeyRef:

key: mysql_root_password

name: mysql-password

volumes:

- name: mysql-data

persistentVolumeClaim:

claimName: deploy-mysql-slave-02-nfs-pvc

- name: mysql-conf

configMap:

name: mysql-slave-02-cm

items:

- key: my.cnf

mode: 0644

path: my.cnf

4、搭建主从集群并验证

kubectl exec -itn mysql pod/deploy-mysql-master-0 -- mysql -uroot -p qwerty

show master status;

来到子节点执行以下命令

change master to master_host='deploy-mysql-master-0.deploy-mysql-master-svc.deploy-test.svc.cluster.local', master_port=3306, master_user='root', master_password='root', master_log_file='master-bin.000003', master_log_pos=157, master_connect_retry=30, get_master_public_key=1;

master_port: 主节点的mysql端口，我们没改默认是3306

master_user: 登录到主节点的mysql用户

master_password: 登录到主节点要用到的密码

master_log_file: 我们之前查看mysql主节点状态时候的 File 字段

master_log_pos: 我们之前查看mysql主节点状态时候的 Position 字段

master_connect_retry: 主节点重连时间

get_master_public_key: 连接主mysql的公钥获取方式

然后执行

start slave;

show slave status\G 查看状态

测试主从集群

CREATE DATABASE `wvg_user`;

USE `wvg_user`;

CREATE TABLE `user` (

`user_id` BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户id',

`username` VARCHAR(50) NOT NULL COMMENT '用户名',

`age` TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',

`gender` TINYINT UNSIGNED DEFAULT 2 COMMENT '性别；0=男,1=女,2=未知'

) COMMENT '用户表';

INSERT INTO `user` (`username`, `age`, `gender`) VALUES ('wvg', '23', '0');

K8S/Kubernetes

K8S/Kubernetes社区为您提供最前沿的新闻资讯和知识内容

更多推荐

【深度】阿里巴巴万级规模 K8s 集群全局高可用体系之美

作者 | 韩堂、柘远、沉醉来源 | 阿里巴巴云原生公众号前言台湾作家林清玄在接受记者采访的时候，如此评价自己 30 多年写作生涯：“第一个十年我才华横溢，‘贼光闪现’，令周边黯然失色；第二个十年，我终于‘宝光现形’，不再去抢风头，反而与身边的美丽相得益彰；进入第三个十年，繁华落尽见真醇，我进入了‘醇光初现’的阶段，真正体味到了境界之美”。长夜有穷，真水无香。领略过了 K8s“身在江

K8S/Kubernetes

如何基于 K8s 构建下一代 DevOps 平台？

作者 | 孙健波（天元）导读：当前云原生 DevOps 体系现状如何？面临哪些挑战？如何通过 OAM 解决云原生 DevOps 场景下的诸多问题？云原生开发应用模型 OAM(Open Application Model) 社区核心成员孙健波将为大家一一解答，并分享如何基于 OAM 和 Kubernetes 打造无限能力的下一代 DevOps 平台。什么是 DevOps？为什么基于 Kub