k8s-mysql主从部署
一.环境信息
mysql版本 :8.0
k8s 版本1.22
使用nfs作为共享存储
二.配置mysql主节点yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql-master-config
namespace: mysql
labels:
app: mysql-master-config
data:
my.cnf: |+
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 主从同步
server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
slave-skip-errors = all
event_scheduler = 1
max_allowed_packet = 64M
# Custom config should go here
!includedir /etc/mysql/conf.d/
---
# headless service
apiVersion: v1
kind: Service
metadata:
name: mysql-master-svc
namespace: mysql
labels:
app: mysql-master-svc
spec:
clusterIP: None
ports:
- name: master-port
port: 3306
selector:
app: mysql-master
---
# NodePort service
apiVersion: v1
kind: Service
metadata:
name: mysql-master-nodeport
namespace: mysql
labels:
app: mysql-master-nodeport
spec:
clusterIP:
ports:
- name: master-port
port: 3306
nodePort: 31306
targetPort: 3306
selector:
app: mysql-master
type: NodePort
target-port:
externalTrafficPolicy: Cluster
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql-master
namespace: mysql
spec:
serviceName: "mysql-master-svc"
replicas: 1
selector:
matchLabels:
app: mysql-master
template:
metadata:
labels:
app: mysql-master
spec:
containers:
- name: mysql-master
image: mysql:8.0.20
ports:
- containerPort: 3306
name: master-port
env:
- name: MYSQL_ROOT_PASSWORD
value: "root"
- name: TZ
value: "Asia/Shanghai"
volumeMounts:
- name: mycnf
mountPath: /etc/mysql/my.cnf
subPath: my.cnf
- name: mysql-master-data
mountPath: /var/lib/mysql
volumes:
- name: mycnf
configMap:
name: mysql-master-config
- name: mysql-master-data
nfs:
server: 192.168.110.190
path: /data/data/mysql-master
三.配置mysql从节点yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mysql-slave-config
namespace: mysql
labels:
app: mysql-slave-config
data:
my.cnf: |+
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 主从同步
server-id = 2
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
slave-skip-errors = all
event_scheduler = 1
max_allowed_packet = 64M
read-only=1
# Custom config should go here
!includedir /etc/mysql/conf.d/
---
# headless service
apiVersion: v1
kind: Service
metadata:
name: mysql-slave-svc
namespace: mysql
labels:
app: mysql-slave-svc
spec:
clusterIP: None
ports:
- name: slave-port
port: 3306
selector:
app: mysql-slave
---
# NodePort service
apiVersion: v1
kind: Service
metadata:
name: mysql-slave-nodeport
namespace: mysql
labels:
app: mysql-slave-nodeport
spec:
clusterIP:
ports:
- name: slave-port
port: 3306
nodePort: 31307
targetPort: 3306
selector:
app: mysql-slave
type: NodePort
target-port:
externalTrafficPolicy: Cluster
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: mysql-slave
namespace: mysql
spec:
serviceName: "mysql-slave-svc"
replicas: 1
selector:
matchLabels:
app: mysql-slave
template:
metadata:
labels:
app: mysql-slave
spec:
containers:
- name: mysql-slave
image: mysql:8.0.20
ports:
- containerPort: 3306
name: slave-port
env:
- name: MYSQL_ROOT_PASSWORD
value: "root"
- name: TZ
value: "Asia/Shanghai"
volumeMounts:
- name: mycnf
mountPath: /etc/mysql/my.cnf
subPath: my.cnf
- name: mysql-slave-data
mountPath: /var/lib/mysql
volumes:
- name: mycnf
configMap:
name: mysql-slave-config
- name: mysql-slave-data
nfs:
server: 192.168.110.190
path: /data/data/mysql-slave
三、主从配置
主节点操作
获取mysql的内部ip
kubectl get svc -n mysql
1.登录主节点
MySQL mysql -h 192.168.40.12 -P 31306 -u root -proot
show master status;
mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000004 | 152 | | | |
+——————+———-+————–+——————+——————-+
2.创建从节点数据同步用户
CREATE USER ‘replica’@’%’ IDENTIFIED WITH mysql_native_password BY ‘replica123’;
GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’%’;
FLUSH PRIVILEGES;
从节点操作
1.登录从节点
mysql -h 192.168.110.190 -P 31307 -u root -proot
2.同步指向主节点
CHANGE MASTER TO MASTER_HOST=’mysql-master-svc’,MASTER_USER=’replica’,MASTER_PASSWORD=’replica123′,MASTER_LOG_FILE=’mysql-bin.000004′,MASTER_LOG_POS=152,MASTER_PORT=3306;
3.开启同步
start slave;