k8s部署mysql主从（GTID异步模式）
1、基础环境
（1）系统版本
[root@master231]:~/dxl/lb-wp-mysql# cat /etc/os-release
PRETTY_NAME="Ubuntu 22.04.3 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.3 LTS (Jammy Jellyfish)"
VERSION_CODENAME=jammy
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=jammy
（2）k8s版本
[root@master231]:~/dxl/lb-wp-mysql# kubeadm version
kubeadm version: &version.Info{Major:"1", Minor:"23", GitVersion:"v1.23.17", GitCommit:"953be8927218ec8067e1af2641e540238ffd7576", GitTreeState:"clean", BuildDate:"2023-02-22T13:33:14Z", GoVersion:"go1.19.6", Compiler:"gc", Platform:"linux/amd64"}
[root@master231]:~/dxl/lb-wp-mysql# kubelet --version
Kubernetes v1.23.17
[root@master231]:~/dxl/lb-wp-mysql# kubectl get nodes
NAME STATUS ROLES AGE VERSION
master231 Ready control-plane,master 4d21h v1.23.17
worker232 Ready <none> 4d21h v1.23.17
worker233 Ready <none> 4d21h v1.23.17
2、目录结构
[root@master231]:~/mysql# ll -A
total 20
-rw-r--r-- 1 root root 680 Mar 14 15:25 cm-init-mysql.yaml
-rw-r--r-- 1 root root 1341 Mar 14 14:01 cm-mycnf-mysql.yaml
-rw-r--r-- 1 root root 1172 Mar 14 15:03 deploy-master-mysql.yaml
-rw-r--r-- 1 root root 1189 Mar 14 15:12 deploy-slave-mysql.yaml
-rw-r--r-- 1 root root 152 Mar 14 08:12 mysql-svc.yaml
3、cm-init-mysql.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: initsh-cm
data:
init-master-gtid-sh: |
#!/bin/bash
sleep 10
mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO '${MYSQL_USER}'@'%';"
init-slave-gtid-semisync-sh: |
#!/bin/bash
sleep 10
mysql -u root -e "CHANGE MASTER TO
MASTER_HOST='mysql-svc',
MASTER_USER='${MY_MYSQL_USER}',
MASTER_PASSWORD='${MY_MYSQL_PASSWORD}',
MASTER_AUTO_POSITION = 1;
START SLAVE;"
<1>主库给同步专用用户授权读取主库的二进制日志，从库设置主库信息用于同步
<2>从库不用官方变量，避免自动创建用户，用户应来自主库的同步
4、cm-mycnf-mysql.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: mycnf-cm
data:
master-gtid: |
[mysqld]
user=mysql
port=3306
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
skip-name-resolve
server_id=100
log-bin=mysql-bin
binlog_format=ROW
binlog_expire_logs_seconds=1209600
sync_binlog=1
innodb_log_file_size=1G
innodb_flush_method=O_DIRECT
max_connections=2000
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-authentication-plugin=mysql_native_password
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
plugin-load-add=semisync_master.so
[client]
socket=/var/run/mysqld/mysqld.sock
slave-gtid-semisync: |
[mysqld]
user=mysql
port=3306
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
skip-name-resolve
server_id=200
log-bin=mysql-bin
binlog_format=ROW
binlog_expire_logs_seconds=604800
relay-log=mysql-relay-bin
log-slave-updates=ON
read_only=ON
relay_log_info_repository=TABLE
relay_log_recovery=ON
innodb_buffer_pool_size=4G
character-set-server=utf8mb4
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
plugin-load-add=semisync_slave.so
[client]
socket=/var/run/mysqld/mysqld.sock
5、deploy-master-mysql.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
name: dpy-mysql-master
labels:
app: mysql
role: master
spec:
replicas: 1
selector:
matchLabels:
app: mysql
role: master
template:
metadata:
labels:
app: mysql
role: master
spec:
volumes:
- name: mycnf
configMap:
name: mycnf-cm
items:
- key: master-gtid
path: my.cnf
- name: initsh
configMap:
name: initsh-cm
items:
- key: init-master-gtid-sh
path: init-gtid.sh
containers:
- name: mysql-master
image: 10.0.0.250/wp_mysql/mysql:v1
volumeMounts:
- name: mycnf
mountPath: /etc/my.cnf
subPath: my.cnf
- name: initsh
mountPath: /docker-entrypoint-initdb.d/init-gtid.sh
subPath: init-gtid.sh
env:
- name: MYSQL_USER
value: "dxl"
- name: MYSQL_PASSWORD
value: "dxl123!@#"
- name: MYSQL_ALLOW_EMPTY_PASSWORD
value: "yes"
args:
- --default-authentication-plugin=mysql_native_password
6、deploy-slave-mysql.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
name: dpy-mysql-slave
labels:
app: mysql
role: slave
spec:
replicas: 1
selector:
matchLabels:
app: mysql
role: slave
template:
metadata:
labels:
app: mysql
role: slave
spec:
volumes:
- name: mycnf
configMap:
name: mycnf-cm
items:
- key: slave-gtid-semisync
path: my.cnf
- name: initsh
configMap:
name: initsh-cm
items:
- key: init-slave-gtid-semisync-sh
path: init-gtid.sh
containers:
- name: mysql-slave
image: 10.0.0.250/wp_mysql/mysql:v1
volumeMounts:
- name: mycnf
mountPath: /etc/my.cnf
subPath: my.cnf
- name: initsh
mountPath: /docker-entrypoint-initdb.d/init-gtid.sh
subPath: init-gtid.sh
env:
- name: MY_MYSQL_USER
value: "dxl"
- name: MY_MYSQL_PASSWORD
value: "dxl123!@#"
- name: MYSQL_ALLOW_EMPTY_PASSWORD
value: "yes"
args:
- --default-authentication-plugin=mysql_native_password
7、mysql-svc.yaml
apiVersion: v1
kind: Service
metadata:
name: mysql-svc
spec:
type: ClusterIP
ports:
- port: 3306
selector:
app: mysql
role: master
8、部署
[root@master231]:~/mysql# kubectl delete -f .
configmap "initsh-cm" deleted
configmap "mycnf-cm" deleted
deployment.apps "dpy-mysql-master" deleted
deployment.apps "dpy-mysql-slave" deleted
service "mysql-svc" deleted
[root@master231]:~/mysql# kubectl get po
NAME READY STATUS RESTARTS AGE
dpy-mysql-master-79cf5f9446-xrzhw 1/1 Running 0 54s
dpy-mysql-slave-8494d9dffd-ccwhw 1/1 Running 0 54s
9、测试
（1）主库创建数据库test01
[root@master231]:~/mysql# kubectl exec -it pod/dpy-mysql-master-79cf5f9446-xrzhw -- mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.08 sec)
mysql> create database test01;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test01 |
+--------------------+
5 rows in set (0.00 sec)
mysql>
（2）从库查看
[root@master231]:~/mysql# kubectl exec -it dpy-mysql-slave-8494d9dffd-ccwhw -- mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test01 |
+--------------------+
5 rows in set (0.01 sec)
mysql>
10、开启半同步
将下列操作固化到代码中时未成功，故手动操作
上述操作为异步模式，该部分内容为开启半同步模式，实现性能和数据安全的平衡
（1）从库
1）原状态
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
2）从库开启半同步
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.01 sec)
3）再次检查
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
（2）主库
1）原状态
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
+-----------------------------+-------+
1 row in set (0.00 sec)
2）主库开启半同步
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
3）再次检查
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.02 sec)
11、root用户加密码
当在配置清单中设置密码时导致容器启动失败，故设为空并在数据库中手动操作
（1）查看
mysql> select user,host,authentication_string from mysql.user where user='root';
+------+-----------+-----------------------+
| user | host | authentication_string |
+------+-----------+-----------------------+
| root | % | |
| root | localhost | |
+------+-----------+-----------------------+
2 rows in set (0.08 sec)
（2）设置密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'dxl456$%^';
Query OK, 0 rows affected (0.04 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'dxl456$%^';
Query OK, 0 rows affected (0.03 sec)
（3）再次检查
mysql> select user,host,authentication_string from mysql.user where user='root';
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | % | *E33AB5FBA819403A3CC2F036379883844209FB4B |
| root | localhost | *E33AB5FBA819403A3CC2F036379883844209FB4B |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
12、查看状态
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: mysql-svc
Master_User: dxl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 388
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 460
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 388
Relay_Log_Space: 1117
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 4ae06a9d-1fbf-11f1-a5ae-5e5c6d67c15a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 4ae06a9d-1fbf-11f1-a5ae-5e5c6d67c15a:1-8
Executed_Gtid_Set: 4ae06a9d-1fbf-11f1-a5ae-5e5c6d67c15a:1-8,
4af4f039-1fbf-11f1-bc80-c2624419c803:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
GTID：
Retrieved_Gtid_Set: 4ae06a9d-1fbf-11f1-a5ae-5e5c6d67c15a:1-8
Executed_Gtid_Set: 4ae06a9d-1fbf-11f1-a5ae-5e5c6d67c15a:1-8,4af4f039-1fbf-11f1-bc80-c2624419c803:1-5
Auto_Position: 1
同步状态：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
13、缺点
1、root密码和半同步的设置未能实现代码化，试了半天未能成功
2、还未进行/var/lib/mysql/和/var/log/mysql的持久化（主从双节点持久化）
浙公网安备 33010602011771号