k8s部署mysql双主双从

这样配置的集群即使pod重启ip变化也不会断开主从配置。server_id = 1#另一台写2。B bin-log日志开始同步的位置。B bin-log日志开始同步的位置。生产环境中密码采用高级别的密码，将。#启动完成后，进入双pod。是刚创建用于同步的用户名，配置主从同步信息（podB。是刚创建用于同步的用户名，配置主从同步信息（pod。容器启动时赋予的端口，是执行同步的用户密码，容器启动时赋予

lw1148961676

888人浏览 · 2024-07-08 16:48:48

lw1148961676 · 2024-07-08 16:48:48 发布

# 编写master所需yaml文件

apiVersion: apps/v1
kind: StatefulSet
metadata:
namespace: '{{ .Release.Namespace }}'
name: 'mysql-master'
labels:
app: mysql-master
appCluster: mysql-master-cluster
spec:
serviceName: "yozo-db-master"
replicas: 1
selector:
matchLabels:
app: mysql-master
appCluster: mysql-master-cluster
template:
metadata:
labels:
app: mysql-master
appCluster: mysql-master-cluster
spec:
initContainers:
- name: initdb
image: {{ .Values.global.images.registry }}/{{ .Values.busybox.registry.image }}:{{ .Values.busybox.registry.tag }}
imagePullPolicy: {{ .Values.global.images.pullPolicy }}
{{- if .Values.busybox.resources }}
resources: {{- toYaml .Values.busybox.resources | nindent 12 }}
{{- end }}
securityContext:
runAsUser: 0
command:
- sh
- -c
- |
cp -r /data/mysql/initdb/* /tmp/mysql/docker-entrypoint-initdb.d/
chown -R 1099.1099 /tmp/mysql/docker-entrypoint-initdb.d/
volumeMounts:
- mountPath: /tmp/mysql/docker-entrypoint-initdb.d
name: mysql-initdb
securityContext:
runAsUser: 1099
runAsGroup: 1099
fsGroup: 1099
containers:
- name: mysql
image: {{ .Values.global.images.registry }}/{{ .Values.mysql.registry.image }}:{{ .Values.mysql.registry.tag }}
imagePullPolicy: {{ .Values.global.images.pullPolicy }}
livenessProbe:
failureThreshold: 6
initialDelaySeconds: 60
periodSeconds: 30
successThreshold: 1
tcpSocket:
port: 3306
timeoutSeconds: 1
readinessProbe:
tcpSocket:
port: 3306
failureThreshold: 8
initialDelaySeconds: 60
periodSeconds: 30
successThreshold: 1
{{- if .Values.mysql.resources }}
resources: {{- toYaml .Values.mysql.resources | nindent 12 }}
{{- end }}
env:
- name: LANG
value: "C.UTF-8"
- name: MYSQL_ROOT_PASSWORD
value: "{{ .Values.mysql.access.passwd }}"
- name: MYSQL_ROOT_USER
value: "{{ .Values.mysql.access.user }}"
- name: MYSQL_REPLICATION_USER
value: "repl"
- name: MYSQL_REPLICATION_PASSWORD
value: "Yozosoft2023"
- name: TZ
value: "Asia/Shanghai"
ports:
- name: mysql
protocol: "TCP"
containerPort: 3306
volumeMounts:
- mountPath: /docker-entrypoint-initdb.d
name: mysql-initdb
- mountPath: /bitnami/mysql/data
name: mysql-data
subPath: common/mysql-master/data
- mountPath: /opt/bitnami/mysql/logs
name: mysql-logs
subPath: common/mysql-master/logs
- mountPath: /etc/localtime
name: localtime
subPath: Shanghai
- name: mysql-master-conf
mountPath: /opt/bitnami/mysql/conf/my_custom.cnf
subPath: my_custom.cnf
volumes:
- name: mysql-initdb
emptyDir: {}
- name: localtime
configMap:
name: localtime-config
- name: mysql-data
persistentVolumeClaim:
claimName: yozo-data-pvc
- name: mysql-logs
persistentVolumeClaim:
claimName: yozo-log-pvc
- name: mysql-master-conf
configMap:
name: mysql-master-config
restartPolicy: Always

---
apiVersion: v1
kind: Service
metadata:
namespace: '{{ .Release.Namespace }}'
name: 'yozo-db-master'
labels:
app: mysql-master
appCluster: mysql-master-cluster
spec:
selector:
app: mysql-master
appCluster: mysql-master-cluster
ports:
- protocol: TCP
port: 3306
targetPort: 3306
{{ if .Values.loadbalancer.enabled }}
type: LoadBalancer
{{- else }}
type: ClusterIP
{{ end }}

---
apiVersion: v1
kind: ConfigMap
metadata:
namespace: '{{ .Release.Namespace }}'
name: mysql-master-config
data:
my_custom.cnf: |
[mysqld]
lower_case_table_names = 1
max_connections = 4000
log_bin = /opt/bitnami/mysql/logs/mysql-bin
server_id = 1 #另一台写2

connect_timeout = 60
max_prepared_stmt_count = 65535
key_buffer_size = 512M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 32
interactive_timeout = 3600
wait_timeout = 1800
table_open_cache = 2048
open_files_limit = 65535
back_log = 600
max_connect_errors = 6000
max_allowed_packet = 128M
tmp_table_size = 256M
max_heap_table_size = 512M
bulk_insert_buffer_size = 64M
external-locking = FALSE
query_cache_size = 512M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
read_buffer_size = 8M
read_rnd_buffer_size = 8M
net_buffer_length = 256k
default-storage-engine = InnoDB
innodb_buffer_pool_size = 4G
innodb_buffer_pool_chunk_size = 256M
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
thread_stack = 256K
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
slow_query_log = 1
long_query_time = 8.0
slow_query_log_file = /opt/bitnami/mysql/logs/slow-query.log
binlog_format = row
expire_logs_days = 30
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
log_bin_trust_function_creators = 1

optimizer_switch = "index_merge=off"
optimizer_switch = "index_merge_sort_union=off"

# 编写slave所需yaml文件

apiVersion: apps/v1
kind: StatefulSet
metadata:
namespace: '{{ .Release.Namespace }}'
name: 'mysql-slave'
labels:
app: mysql-slave
appCluster: mysql-slave-cluster
spec:
serviceName: "yozo-db-slave"
replicas: 1
selector:
matchLabels:
app: mysql-slave
appCluster: mysql-slave-cluster
template:
metadata:
labels:
app: mysql-slave
appCluster: mysql-slave-cluster
spec:
initContainers:
- name: initdb
image: {{ .Values.global.images.registry }}/{{ .Values.busybox.registry.image }}:{{ .Values.busybox.registry.tag }}
imagePullPolicy: {{ .Values.global.images.pullPolicy }}
{{- if .Values.busybox.resources }}
resources: {{- toYaml .Values.busybox.resources | nindent 12 }}
{{- end }}
securityContext:
runAsUser: 0
command:
- sh
- -c
- |
cp -r /data/mysql/initdb/* /tmp/mysql/docker-entrypoint-initdb.d/
chown -R 1099.1099 /tmp/mysql/docker-entrypoint-initdb.d/
volumeMounts:
- mountPath: /tmp/mysql/docker-entrypoint-initdb.d
name: mysql-initdb
securityContext:
runAsUser: 1099
runAsGroup: 1099
fsGroup: 1099
containers:
- name: mysql
image: {{ .Values.global.images.registry }}/{{ .Values.mysql.registry.image }}:{{ .Values.mysql.registry.tag }}
imagePullPolicy: {{ .Values.global.images.pullPolicy }}
livenessProbe:
failureThreshold: 6
initialDelaySeconds: 60
periodSeconds: 30
successThreshold: 1
tcpSocket:
port: 3306
timeoutSeconds: 1
readinessProbe:
tcpSocket:
port: 3306
failureThreshold: 8
initialDelaySeconds: 60
periodSeconds: 30
successThreshold: 1
{{- if .Values.mysql.resources }}
resources: {{- toYaml .Values.mysql.resources | nindent 12 }}
{{- end }}
env:
- name: LANG
value: "C.UTF-8"
- name: MYSQL_ROOT_PASSWORD
value: "{{ .Values.mysql.access.passwd }}"
- name: MYSQL_ROOT_USER
value: "{{ .Values.mysql.access.user }}"
- name: MYSQL_REPLICATION_USER
value: "repl"
- name: MYSQL_REPLICATION_PASSWORD
value: "Yozosoft2023"
- name: TZ
value: "Asia/Shanghai"
ports:
- name: mysql
protocol: "TCP"
containerPort: 3306
volumeMounts:
- mountPath: /docker-entrypoint-initdb.d
name: mysql-initdb
- mountPath: /bitnami/mysql/data
name: mysql-data
subPath: common/mysql-slave/data
- mountPath: /opt/bitnami/mysql/logs
name: mysql-logs
subPath: common/mysql-slave/logs
- mountPath: /etc/localtime
name: localtime
subPath: Shanghai
- name: mysql-slave-conf
mountPath: /opt/bitnami/mysql/conf/my_custom.cnf
subPath: my_custom.cnf
volumes:
- name: mysql-initdb
emptyDir: {}
- name: localtime
configMap:
name: localtime-config
- name: mysql-data
persistentVolumeClaim:
claimName: yozo-data-pvc
- name: mysql-logs
persistentVolumeClaim:
claimName: yozo-log-pvc
- name: mysql-slave-conf
configMap:
name: mysql-slave-config
restartPolicy: Always

---
apiVersion: v1
kind: Service
metadata:
namespace: '{{ .Release.Namespace }}'
name: 'yozo-db-slave'
labels:
app: mysql-slave
appCluster: mysql-slave-cluster
spec:
selector:
app: mysql-slave
appCluster: mysql-slave-cluster
ports:
- protocol: TCP
port: 3306
targetPort: 3306
{{ if .Values.loadbalancer.enabled }}
type: LoadBalancer
{{- else }}
type: ClusterIP
{{ end }}

---
apiVersion: v1
kind: ConfigMap
metadata:
namespace: '{{ .Release.Namespace }}'
name: mysql-slave-config
data:
my_custom.cnf: |
[mysqld]
lower_case_table_names = 1
max_connections = 4000
log_bin = /opt/bitnami/mysql/logs/mysql-bin
server_id = 2

connect_timeout = 60
max_prepared_stmt_count = 65535
key_buffer_size = 512M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 32
interactive_timeout = 3600
wait_timeout = 1800
table_open_cache = 2048
open_files_limit = 65535
back_log = 600
max_connect_errors = 6000
max_allowed_packet = 128M
tmp_table_size = 256M
max_heap_table_size = 512M
bulk_insert_buffer_size = 64M
external-locking = FALSE
query_cache_size = 512M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
read_buffer_size = 8M
read_rnd_buffer_size = 8M
net_buffer_length = 256k
default-storage-engine = InnoDB
innodb_buffer_pool_size = 4G
innodb_buffer_pool_chunk_size = 256M
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
thread_stack = 256K
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
slow_query_log = 1
long_query_time = 8.0
slow_query_log_file = /opt/bitnami/mysql/logs/slow-query.log
binlog_format = row
expire_logs_days = 30
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
log_bin_trust_function_creators = 1

optimizer_switch = "index_merge=off"
optimizer_switch = "index_merge_sort_union=off"

#启动完成后，进入双pod

注:生产环境中密码采用高级别的密码，将'%'换成另一节点的ip
grant replication  slave,reload,super  on *.*  to 'repl'@'%' identified by 'Yozosoft2023';

flush privileges;

#初始化bin-log日志 （双节点）

reset master;

show master status;

#配置主从同步信息（podA）

change master to master_host='mysql-slave-0.yozo-db-slave.yozo.svc.cluster.local',master_port=3306,master_user='repl',master_password='Yozosoft2023',master_log_file='mysql-bin.000001',master_log_pos=154;

这里的master_host是指podB的dns地址，master_port是B上的mysql容器启动时赋予的端口，master_uesr是刚创建用于同步的用户名，master_password是执行同步的用户密码，master_log_file是主机B的bin-log日志，master_log_pos是从主机B bin-log日志开始同步的位置。

#配置主从同步信息（podB）

change master to master_host='mysql-master-0.yozo-db-master.yozo.svc.cluster.local',master_port=3306,master_user='repl',master_password='Yozosoft2023',master_log_file='mysql-bin.000001',master_log_pos=154;

这里的master_host是指podA的dns地址，master_port是A上的mysql容器启动时赋予的端口，master_uesr是刚创建用于同步的用户名，master_password是执行同步的用户密码，master_log_file是主机B的bin-log日志，master_log_pos是从主机B bin-log日志开始同步的位置。

flush privileges;

#开启主从同步（双节点）

start slave;

查看是否成功：

show slave status\G

# 这样配置的集群即使pod重启ip变化也不会断开主从配置

K8S/Kubernetes

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

更多推荐

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

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

K8S/Kubernetes

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

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