一、K8s 部署 MySQL
安装规划
| 组件 | replicas | 类型 |
|---|---|---|
| mysql-master | 1 | StatefulSet |
| mysql-slave | 1 | StatefulSet |
使用 k8s 版本为：v1.18.0 。
本次使用 OpenEBS 来作为存储引擎，OpenEBS 是一个开源的、可扩展的存储平台，它提供了一种简单的方式来创建和管理持久化存储卷。它支持各种存储后端，包括但不限于 ZFS、Btrfs、XFS 等。同时，OpenEBS 具有高度的可扩展性和可配置性，可以满足不同的存储需求。
安装 OpenEBS ：
kubectl apply -f https://openebs.github.io/charts/openebs-operator.yaml
验证OpenEBS是否正确安装：
kubectl get pods -n openebs
所有的 OpenEBS pods 都处于Running状态表示正常。
1. 创建命名空间
vi mysql-ns.yml
apiVersion: v1
kind: Namespace
metadata:
name: mysql
labels:
name: mysql
kubectl apply -f mysql-ns.yml
2. 创建一个默认的存储卷：
vi mysql-local-storage.yml
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
name: mysql-local-storage
annotations:
openebs.io/cas-type: local
cas.openebs.io/config: |
- name: StorageType
value: hostpath
- name: BasePath
value: /data/openebs/mysql
provisioner: openebs.io/local
reclaimPolicy: Delete
volumeBindingMode: WaitForFirstConsumer
kubectl apply -f mysql-local-storage.yml
3. 部署 MySQL 主节点：
vi mysql-master.yml
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
volumeClaimTemplates:
- metadata:
name: mysql-master-data
spec:
accessModes: ["ReadWriteOnce"]
storageClassName: mysql-local-storage
resources:
requests:
storage: 5Gi
kubectl apply -f mysql-master.yml
查看 pod ：
kubectl get pods -n mysql
4. 部署 MySQL 从节点：
vi mysql-slave.yml
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
volumeClaimTemplates:
- metadata:
name: mysql-slave-data
spec:
accessModes: ["ReadWriteOnce"]
storageClassName: mysql-local-storage
resources:
requests:
storage: 5Gi
kubectl apply -f mysql-slave.yml
查看 pod ：
kubectl get pods -n mysql
二、主从配置
主节点配置
可以在外面使用 MySQL 客户端连接 mysql-master ,注意换成你的 k8s nodel ip :
mysql -h 192.168.40.12 -P 31306 -u root -proot
查看当前 master 状态：
show master status;
注意这里查询出来的 File 和 Position 下面主从复制时会用到。
为从节点创建一个同步用户：
CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replica123'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%'; FLUSH PRIVILEGES;
从节点配置
在外面使用 MySQL 客户端连接 mysql-master ,注意换成你的 k8s nodel ip :
mysql -h 192.168.40.12 -P 31307 -u root -proot
同步指向主节点：
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;
注意这里的 MASTER_HOST 指向的是主节点的 Service ：
启动同步进程：
start slave;
查看同步状态：
show slave status\G
看到 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 则表示启动成功。
三、主从测试
首先在主节点创建数据库 testdb:
create database testdb;
然后在从节点查看数据库：
show databases;
可以正常查到主节点创建的数据库。
然后在主节点创建测试表：
use testdb; create table `test` ( `id` int not null auto_increment, `name` varchar(255) default null, primary key (`id`) ) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;
接着在从节点查看表：
use testdb; show tables;
可以正常看到创建的表。
然后在主节点写入一条测试数据：
insert into test(name) values('小明');
然后到从节点查看表数据：
select * from test;
可以正常查到写入的数据。
