docker、k8s部署 mysql group replication 和 ProxySQL 读写分离

MySQL Group Replication（简称MGR）是MySQL官方推出的一个高可用与高扩展的解决方案。MySQL组复制它提供了高可用、高扩展、高可靠的MySQL集群服务，这里部署的 mysql 版本 5.7.33，架构是一读一写。特别要注意一个关键点: 必须保证各个mysql节点的主机名不一致，并且能通过主机名找到各成员！即把组内成员的主机名和 ip 写到 hosts 里，这个坑卡了我很久。

docker 部署 mysql

docker 版本 24.0.7，可根据自己的需求修改容器名和容器主机名以及挂载路径

10.1.11.81 master

10.1.11.80 slave

mster 节点部署 mysql

docker run -d --name=mysql_alphanew_master --hostname=alphanew_master --add-host=alphanew_master:10.1.11.81 --add-host=alphanew_slave:10.1.11.80  --network=host -e MYSQL_ROOT_PASSWORD=xxxxxx   -v /share/home/mysql/alpha_new2/master/conf/mysql.conf.d:/etc/mysql/mysql.conf.d -v /share/home/mysql/alpha_new2/master/data/:/var/lib/mysql -v /share/home/mysql/alpha_new2/master/logs:/etc/mysql/logs mysql:5.7

slave 节点部署 mysql

docker run -d --name=mysql_alphanew_slave --hostname=alphanew_slave --add-host=alphanew_master:10.1.11.81 --add-host=alphanew_slave:10.1.11.80  --network=host -e MYSQL_ROOT_PASSWORD=xxxxxx -v /share/home/mysql/alpha_new2/slave/conf/mysql.conf.d:/etc/mysql/mysql.conf.d -v /share/home/mysql/alpha_new2/slave/data/:/var/lib/mysql -v /share/home/mysql/alpha_new2/slave/logs:/etc/mysql/logs harbor.wenbo/ops/mysql:5.7

配置文件是挂载上去的，日志在挂载的 data 目录下“mysql_err.log”。配置中需要修改的地方注释会指出

[root@c01n02 ~]# cat /share/home/mysql/alpha_new2/master/conf/mysql.conf.d/mysqld.cnf 
[mysqld]
lower_case_table_names=0
character-set-client-handshake = FALSE
character-set-server = utf8mb4
default-time_zone='+8:00'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONpid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
datadir        = /var/lib/mysql
port = 6106  #按需修改 mysql 端口
server_id=1  #每台节点 id 要不同，slave 改为 2 以此类推
log-bin=mysql-bin
enforce-gtid-consistency = 1
gtid_mode = ON
binlog_format=ROW
expire_logs_days = 14#group replication configlog-slave-updates = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-checksum = NONE
# prevent use of non-transactional storage engines
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE"
# InnoDB gap locks are problematic for multi-primary conflict detection; none are used with READ-COMMITTED
# So if you don't rely on REPEATABLE-READ semantics and/or wish to use multi-primary mode then this
# isolation level is recommended 
transaction-isolation = 'READ-COMMITTED'# group replication specific options
plugin-load = group_replication.so
loose_group_replication = FORCE_PLUS_PERMANENT
transaction-write-set-extraction = XXHASH64
loose_group_replication_start_on_boot = OFF  #当组复制搭完，改成 ON 服务挂了重启可以自动加入组
loose_group_replication_bootstrap_group = OFF
loose_group_replication_group_name = "5964d6f0-4410-11ef-a1ee-00001029fe81"
loose_group_replication_local_address = '10.1.11.81:24905'  #改为本机节点的 ip，即 slave 改为 10.1.11.80:24905
loose_group_replication_group_seeds = '10.1.11.81:24905,10.1.11.80:24905'  #改为自己组内节点的 ip
loose_group_replication_ip_whitelist = "10.96.0.0/16,172.16.0.0/16,10.1.11.0/24,10.4.0.0/20"  #组复制内相互访问的白名单，按需修改log-error=mysql_err.loginnodb_buffer_pool_size = 3G
#skip-grant-tables=1init_connect='SET NAMES utf8mb4'
## Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0max_allowed_packet = 1G
innodb_lock_wait_timeout = 300
wait_timeout = 86400
interactive_timeout = 86400
net_read_timeout = 180
net_write_timeout = 180
innodb_log_file_size = 1024M
thread_stack = 512K
max_connections = 1000slow_query_log=ON
log_output=FILE
long_query_time=10
log_slow_admin_statements=ON
log_slow_slave_statements=ON[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

master 加入组复制

进入容器进入数据库

docker exec -it mysql_alphanew_master bash

mysql -p1qaz2wsx

授权组复制用户

set sql_log_bin=0;
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication';
flush privileges;
set sql_log_bin=1;
change master to master_user='replication',master_password='replication' for channel 'group_replication_recovery';

加入组并查看组内节点状态

set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;
SELECT * FROM performance_schema.replication_group_members;

正常来说只能看到一个节点，我这里主从已经加入好了

slave 加入组复制

进入容器进入数据库

docker exec -it mysql_alphanew_slave bash

mysql -p1qaz2wsx

授权组复制用户

set sql_log_bin=0;
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication';
flush privileges;
set sql_log_bin=1;
change master to master_user='replication',master_password='replication' for channel 'group_replication_recovery';

加入组并查看组内节点状态

start group_replication;
SELECT * FROM performance_schema.replication_group_members;

k8s 部署 mysql

k8s 版本 1.28.2，这里用 statufelset 部署 mysql，configmap 添加配置文件，pv持久化数据

configmap 配置文件，无需修改

[root@mgt01 mgr-sts]# cat mysql-configmap.yaml 
apiVersion: v1
kind: ConfigMap
metadata:name: mysql-confignamespace: alphanewlabels:app: mysql
data:mysqld.cnf: |[mysqld]lower_case_table_names=1character-set-client-handshake = FALSEcharacter-set-server = utf8mb4default-time_zone='+8:00'sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONpid-file    = /var/run/mysqld/mysqld.pidsocket        = /var/run/mysqld/mysqld.sockdatadir        = /var/lib/mysqlport = 3306server_id=1log-bin=mysql-binenforce-gtid-consistency = 1gtid_mode = ONbinlog_format=ROWexpire_logs_days = 14#group replication configlog-slave-updates = ONmaster-info-repository = TABLErelay-log-info-repository = TABLEbinlog-checksum = NONE# prevent use of non-transactional storage enginesdisabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE"# InnoDB gap locks are problematic for multi-primary conflict detection; none are used with READ-COMMITTED# So if you don't rely on REPEATABLE-READ semantics and/or wish to use multi-primary mode then this# isolation level is recommended transaction-isolation = 'READ-COMMITTED'# group replication specific optionsplugin-load = group_replication.soloose_group_replication = FORCE_PLUS_PERMANENTtransaction-write-set-extraction = XXHASH64loose_group_replication_start_on_boot = OFFloose_group_replication_bootstrap_group = OFFloose_group_replication_group_name = "5964d6f0-4410-11ef-a1ee-00001029fe81"loose_group_replication_local_address = 'mysql-0:24904'loose_group_replication_group_seeds = 'mysql-0:24904,mysql-1:24904'loose_group_replication_ip_whitelist = "0.0.0.0/0"  log-error=mysql_err.loginnodb_buffer_pool_size = 3G#skip-grant-tables=1init_connect='SET NAMES utf8mb4'## Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0max_allowed_packet = 1Ginnodb_lock_wait_timeout = 300wait_timeout = 86400interactive_timeout = 86400net_read_timeout = 180net_write_timeout = 180innodb_log_file_size = 1024Mthread_stack = 512Kmax_connections = 1000slow_query_log=ONlog_output=FILElong_query_time=10log_slow_admin_statements=ONlog_slow_slave_statements=ON[client]default-character-set = utf8mb4[mysql]default-character-set = utf8mb4mysqldcnf.sh: |#!/bin/bash# 复制 mysqld.cnf 到 /etc/mysql/mysql.conf.d/cp /data/mysqld.cnf /etc/mysql/mysql.conf.d/# 获取主机名的最后一位数字last_digit=$(hostname | rev | cut -c 1 | rev)# 修改 server_id 和 loose_group_replication_local_addresssed -i "s/server_id=1/server_id=$((last_digit + 1))/g" /etc/mysql/mysql.conf.d/mysqld.cnfsed -i "s/loose_group_replication_local_address = 'mysql-0:24904'/loose_group_replication_local_address = '$(hostname):24904'/g" /etc/mysql/mysql.conf.d/mysqld.cnf#修改host对应的ipsed -i "/$(hostname)/s/^[0-9.]\+ /$(hostname -I | awk '{print $1}') /" /data/hosts/hosts

无头服务 service配置，无需修改

[root@mgt01 mgr-sts]# cat svc.yaml 
apiVersion: v1
kind: Service
metadata:name: mysqlnamespace: alphanewlabels:app: mysqlapp.kubernetes.io/name: mysql
spec:ports:- name: mysqlport: 3306- name: internalport: 24904clusterIP: Noneselector:app: mysql
---
apiVersion: v1
kind: Service
metadata:name: mysql-readnamespace: alphanewlabels:app: mysql
spec:type: NodePortports:- name: mysqlport: 3306selector:app: mysql

statefelset 配置，副本数量、root密码、存储大小按需修改，这里挂载了一个hostPath hosts 目录按需修改路径

[root@mgt01 mgr-sts]# cat sts.yaml 
apiVersion: apps/v1
kind: StatefulSet
metadata:labels:app: mysqlname: mysqlnamespace: alphanew
spec:replicas: 2  #副本数量serviceName: mysqlselector:matchLabels:app: mysqltemplate:metadata:labels:app: mysqlspec:affinity:podAntiAffinity:requiredDuringSchedulingIgnoredDuringExecution:- labelSelector:matchExpressions:- key: appoperator: Invalues:- mysqltopologyKey: "kubernetes.io/hostname"containers:- name: mysqlimage: harbor.wenbo/ops/mysql:5.7lifecycle:postStart:exec:command: ["/bin/bash", "/data/mysqldcnf.sh"]readinessProbe:exec:command:- sh- -c- |if [ /data/hosts/hosts -nt /etc/hosts ]; thengrep -v 'mysql.alphanew.svc.cluster.local' /etc/hosts > /tmp/hostscat /data/hosts/hosts >> /tmp/hostscp /tmp/hosts /etc/hostsfiinitialDelaySeconds: 10periodSeconds: 15              resources:requests:cpu: "1"memory: "1024Mi"volumeMounts:- name: mysql-datamountPath: /var/lib/mysql/- name: mysql-configmountPath: /data- mountPath: /data/hostsname: hosts-volume            ports:- containerPort: 3306name: client- containerPort: 24904env:- name: TZvalue: "Asia/Shanghai"- name: MYSQL_ROOT_PASSWORDvalue: "xxxxxx"  #root用户密码- name: MYSQL_ROOT_HOSTvalue: "%"               - name: MYSQL_INITDB_SKIP_TZINFOvalue: "1"volumes:- name: mysql-configconfigMap:name: mysql-config- name: hosts-volumehostPath:path: /share/k8s-storage/db/alphanew/hosts    #hosts 目录路径volumeClaimTemplates:- metadata:name: mysql-dataspec:accessModes:- ReadWriteOnceresources:requests:storage: 100Gi   #存储大小

hosts 目录，hostpath 挂载需要每个节点都有这个路径文件，nfs可以实现，也可以用pv来实现，statefulset 几个副本这个hosts文件就有几行，mysql-n 记得修改以此类推，ip不改无所谓。

挂载这个文件的目的在于让组复制里的 pod 主机名和对应的 ip 写入 /etc/hosts 文件里（configmap里的脚本和就绪性探针来实现），不然加入复制组有问题。这里通过挂载共享目录来实现，若是有更好的方案可以留言。

[root@mgt01 mgr-sts]# ls /share/k8s-storage/db/alphanew/hosts
hosts
[root@mgt01 mgr-sts]# cat /share/k8s-storage/db/alphanew/hosts/hosts 
172.16.225.80  mysql-0.mysql.alphanew.svc.cluster.local    mysql-0.mysql    mysql-0
172.16.79.31  mysql-1.mysql.alphanew.svc.cluster.local    mysql-1.mysql    mysql-1

加入复制组和 docker 部署的是一样的，进入 pod 执行就行。第一次加入组后记得修改配置文件‘loose_group_replication_start_on_boot = ON’，这样服务重启才会自动加入组复制。

部署 ProxySQL

ProxySQL是一个高性能的开源数据库代理，专门用于在数据库系统和客户端之间进行流量路由和负载均衡。它能够管理和优化数据库连接，提高应用程序的性能和可用性。这里是在 k8s 上面部署的 proxysql 版本 2.6.3。

配置文件 configmap，namespace 按需修改，其他无需修改。

[root@mgt01 proxysql]# cat cm.yaml 
apiVersion: v1
data:proxysql.cnf: |datadir="/var/lib/proxysql"admin_variables={admin_credentials="admin:admin;radmin:radmin"mysql_ifaces="0.0.0.0:6032"}mysql_variables={threads=4max_connections=2048default_query_delay=0default_query_timeout=36000000have_compress=truepoll_timeout=2000interfaces="0.0.0.0:6033"default_schema="information_schema"stacksize=1048576server_version="5.5.30"connect_timeout_server=3000monitor_username="monitor"monitor_password="monitor"monitor_history=600000monitor_connect_interval=60000monitor_ping_interval=10000monitor_read_only_interval=1500monitor_read_only_timeout=500ping_interval_server_msec=120000ping_timeout_server=500commands_stats=truesessions_sort=trueconnect_retries_on_failure=10}
kind: ConfigMap
metadata:name: proxysqlcmnamespace: db

deployment 和 service yaml 文件，namespace 和 service 暴露方式以及 image 镜像地址按需修改。集群内所有节点都挂载了 gpfs /share 目录，即 proxysql 持久化用的 hostpath 可以按场景修改。

[root@mgt01 proxysql]# cat deploy.yaml 
apiVersion: apps/v1
kind: Deployment
metadata:name: proxysql-deploymentnamespace: db
spec:replicas: 1  selector:matchLabels:app: proxysqltemplate:metadata:labels:app: proxysqlspec:containers:- name: proxysql-containerimage: harbor.wenbo/dockerhub/proxysql/proxysql:2.6.3ports:- containerPort: 6033- containerPort: 6032- containerPort: 6070volumeMounts:- name: proxysql-datamountPath: /var/lib/proxysql- name: proxysql-configmountPath: /etc/proxysql.cnfsubPath: proxysql.cnfvolumes:- name: proxysql-configconfigMap:name: proxysqlcm- name: proxysql-datahostPath:path: /share/k8s-storage/proxysql---
apiVersion: v1
kind: Service
metadata:name: proxysql-servicenamespace: db
spec:type: NodePortselector:app: proxysqlports:- port: 6033targetPort: 6033nodePort: 16033name: external- port: 6032targetPort: 6032nodePort: 16032name: internal- port: 6070targetPort: 6070nodePort: 16070name: spare

执行 yaml 文件部署完成，进入到 pod 里

kubectl exec -it -n ops proxysql-deployment-656cbd849c-qthft bash

mysql -h127.0.0.1 -P6032 -uradmin -pradmin --prompt "ProxySQL RAdmin>"

添加后端数据库

插入后端数据库，这里用 docker 部署的 mysql，在 mysql_servers 表中配置。和加载到 runtime 实际生效的配置，必须从数据库加载后才能生效。以及持久化 disk 存储的配置，用于 ProxySQL 启动时加载，确保配置不会因重启或故障而丢失。

注意修改mysql ip 和端口。

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES(5,'10.1.11.81',6106,1),(6,'10.1.11.81',6106,100),(6,'10.1.11.80',6106,1000);
load mysql servers to runtime;
save mysql servers to disk;

查看实时生效的后端数据库。这里主库可读，主从可读权重对比 1:10，避免从库挂了数据库不可读。

select * from runtime_mysql_servers;

配置读写id

写组 hostgroup_ip 为 5，读组 hostgroup_ip 为 6。应用到实时配置并保存到磁盘。

insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,check_type) values(5,6,'read_only');
load mysql servers to runtime;
save mysql servers to disk;

查看实时读写 id，这里判断后端数据库为仅读还是读写，通过后端数据库全局变量 ’read_only‘ 来实现。

select * from runtime_mysql_replication_hostgroups;

配置后端数据库用户

后端数据库和 proxysql 添加的用户名和密码的是一样，在 mysql_users 表中配置。

mysql master 后端添加用户。

CREATE USER 'lay'@'%' IDENTIFIED BY 'xxxxxx';
GRANT ALL PRIVILEGES ON *.* TO 'lay'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

proxysql 添加用户，需要指定一个默认的主机组，当客户端连接没指定后端数据的时候默认会连到这个主机组，-D 指定数据库。并应用到实时配置并保存到磁盘。

insert into mysql_users(username,password,default_hostgroup) values('lay','xxxxxx',5);
load mysql users to runtime;
save mysql users to disk;

查看 proxysql 实时用户。

select * from runtime_mysql_users;

设置 Proxysql 监控用户

PrxySQL将使用这些凭据建立与 MySQL 服务器的监控连接，并定期获取性能指标、查询统计和连接状态等信息。这些收集到的数据可以用于性能监控、故障排除和优化分析等用途。且当一个复制组中写组宕机，会自动调整实时配置把读组 id 改为写组 id。用当前的配置举例说：当后端 81 宕机，会把后端 80 hostgroup_id 改为 5。

mysql master 后端添加用户。

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE,REPLICATION CLIENT ON *.* TO 'monitor'@'%';

peoxysql 配置监控用户，并查看实时配置

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql variables to runtime;
save mysql variables to disk;
SELECT * FROM runtime_global_variables WHERE variable_name IN ('mysql-monitor_username', 'mysql-monitor_password');

配置读写分离规则

查询规则决定了如何处理不同类型的査询语句。通过在 mysql_query_rules 表中定义查询规则，可以实现灵活的查询路由和行为控制。

插入查询规则，proxysql 若是代理多个复制组需要填写 “schemaname” 字段，也就是后端数据库的名字。这里的读语句会代理到 hostgroup_id:6，其他语句会代理到 hostgroup:5，也就是实现读写分离。

INSERT INTO mysql_query_rules (active,schemaname, match_pattern, destination_hostgroup, apply)
VALUES (1,"AlphaKR", '^SELECT.*|SHOW.*', 6, 1), (1,"AlphaKR", '.*', 5, 1); 
load mysql query rules to runtime;
save mysql query rules to disk;
select * from runtime_mysql_query_rules\G;
select rule_id,active,schemaname,match_pattern, destination_hostgroup, apply from runtime_mysql_query_rules;

查看实时查询规则

查看读写走向

找一台有 mysql 客户端的机器连接到 proxysql，执行一些读写操作

mysql -ulay -p -h10.1.11.236 -P26033

查看 proxysql 代理读写走向，可以看到查表走的 hostgroup_id 读组 6，创建库和删除库走的 hostgrop_id 写组 5。读写分离成功。

SELECT hostgroup hg,schemaname,client_address,username,digest_text FROM stats_mysql_query_digest limit 10;

查看后端数据库状态

正常状态为 ‘ONLINE’，其他状态有 ‘OFFLINE_SOFT’ 软离线状态，这种状态下,现有的连接仍然可以使用该主机,但是新的连接将不会被路由到该主机。‘OFFLINE_HARD’ 硬离线状态，所有现有连接和新连接都不会被路由到该主机。

select * from runtime_mysql_servers;

多个复制组

上面可以看到，proxysql 代理了两个数据库的，插入的用户 'lay' 指定了 default_hostgroup 为 5，所以一直操作的是 AlphaKR 数据库，可以用参数 -D 指定数据库来实现操作不同后端数据库。

可以看到同一个 ip、端口、用户名、密码可以连接到不同的后端数据库，这是读写分离外的另一个好处。

有问题可以在评论区留言。