docker、k8s部署 mysql group replication 和 ProxySQL 读写分离
MySQL Group Replication（简称MGR）是MySQL官方推出的一个高可用与高扩展的解决方案。MySQL组复制它提供了高可用、高扩展、高可靠的MySQL集群服务，这里部署的 mysql 版本 5.7.33，架构是一读一写。特别要注意一个关键点: 必须保证各个mysql节点的主机名不一致，并且能通过主机名找到各成员！，这个坑卡了我很久。
·
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.7slave 节点部署 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_SUBSTITUTION
pid-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 config
log-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.log
innodb_buffer_pool_size = 3G
#skip-grant-tables=1
init_connect='SET NAMES utf8mb4'
## Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_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 = 1000
slow_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;
修改配置文件‘loose_group_replication_start_on_boot = ON’，这样服务重启才会自动加入组复制。
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-config
namespace: alphanew
labels:
app: mysql
data:
mysqld.cnf: |
[mysqld]
lower_case_table_names=1
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_SUBSTITUTION
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
port = 3306
server_id=1
log-bin=mysql-bin
enforce-gtid-consistency = 1
gtid_mode = ON
binlog_format=ROW
expire_logs_days = 14
#group replication config
log-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
loose_group_replication_bootstrap_group = OFF
loose_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.log
innodb_buffer_pool_size = 3G
#skip-grant-tables=1
init_connect='SET NAMES utf8mb4'
## Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_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 = 1000
slow_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
mysqldcnf.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_address
sed -i "s/server_id=1/server_id=$((last_digit + 1))/g" /etc/mysql/mysql.conf.d/mysqld.cnf
sed -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对应的ip
sed -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: mysql
namespace: alphanew
labels:
app: mysql
app.kubernetes.io/name: mysql
spec:
ports:
- name: mysql
port: 3306
- name: internal
port: 24904
clusterIP: None
selector:
app: mysql
---
apiVersion: v1
kind: Service
metadata:
name: mysql-read
namespace: alphanew
labels:
app: mysql
spec:
type: NodePort
ports:
- name: mysql
port: 3306
selector:
app: mysql
statefelset 配置，副本数量、root密码、存储大小按需修改，这里挂载了一个hostPath hosts 目录按需修改路径
[root@mgt01 mgr-sts]# cat sts.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
labels:
app: mysql
name: mysql
namespace: alphanew
spec:
replicas: 2 #副本数量
serviceName: mysql
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
affinity:
podAntiAffinity:
requiredDuringSchedulingIgnoredDuringExecution:
- labelSelector:
matchExpressions:
- key: app
operator: In
values:
- mysql
topologyKey: "kubernetes.io/hostname"
containers:
- name: mysql
image: harbor.wenbo/ops/mysql:5.7
lifecycle:
postStart:
exec:
command: ["/bin/bash", "/data/mysqldcnf.sh"]
readinessProbe:
exec:
command:
- sh
- -c
- |
if [ /data/hosts/hosts -nt /etc/hosts ]; then
grep -v 'mysql.alphanew.svc.cluster.local' /etc/hosts > /tmp/hosts
cat /data/hosts/hosts >> /tmp/hosts
cp /tmp/hosts /etc/hosts
fi
initialDelaySeconds: 10
periodSeconds: 15
resources:
requests:
cpu: "1"
memory: "1024Mi"
volumeMounts:
- name: mysql-data
mountPath: /var/lib/mysql/
- name: mysql-config
mountPath: /data
- mountPath: /data/hosts
name: hosts-volume
ports:
- containerPort: 3306
name: client
- containerPort: 24904
env:
- name: TZ
value: "Asia/Shanghai"
- name: MYSQL_ROOT_PASSWORD
value: "xxxxxx" #root用户密码
- name: MYSQL_ROOT_HOST
value: "%"
- name: MYSQL_INITDB_SKIP_TZINFO
value: "1"
volumes:
- name: mysql-config
configMap:
name: mysql-config
- name: hosts-volume
hostPath:
path: /share/k8s-storage/db/alphanew/hosts #hosts 目录路径
volumeClaimTemplates:
- metadata:
name: mysql-data
spec:
accessModes:
- ReadWriteOnce
resources:
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"
restapi_enabled=true
restapi_port=6070
prometheus_memory_metrics_interval=30
}
mysql_variables=
{
threads=4
max_connections=5000
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
kind: ConfigMap
metadata:
name: proxysqlcm
namespace: 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-deployment
namespace: db
spec:
replicas: 1
selector:
matchLabels:
app: proxysql
template:
metadata:
labels:
app: proxysql
spec:
containers:
- name: proxysql-container
image: harbor.wenbo/dockerhub/proxysql/proxysql:2.6.3
ports:
- containerPort: 6033
- containerPort: 6032
- containerPort: 6070
volumeMounts:
- name: proxysql-data
mountPath: /var/lib/proxysql
- name: proxysql-config
mountPath: /etc/proxysql.cnf
subPath: proxysql.cnf
volumes:
- name: proxysql-config
configMap:
name: proxysqlcm
- name: proxysql-data
hostPath:
path: /share/k8s-storage/proxysql
---
apiVersion: v1
kind: Service
metadata:
name: proxysql-service
namespace: db
spec:
type: NodePort
selector:
app: proxysql
ports:
- port: 6033
targetPort: 6033
nodePort: 16033
name: external
- port: 6032
targetPort: 6032
nodePort: 16032
name: internal
- port: 6070
targetPort: 6070
nodePort: 16070
name: spare执行 yaml 文件部署完成，进入到 pod 里
kubectl exec -it -n ops proxysql-deployment-656cbd849c-qthft bashmysql -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、端口、用户名、密码指定数据库可以连接到不同的后端数据库，亲测 pymysql也能实现，这是读写分离外的另一个好处。
当 proxysql v2.0.6以上 配置了读写分离，在设置环境变量的时候，会绑定到你的默认组读就会报错如下：
9006 ProxySQL Error: connection is locked to hostgroup XX but trying to reach hostgroup YY
解决办法，可以关闭绑定：
set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;有问题可以在评论区留言。
参考文章：
MySQL 5.7 基于组复制(MySQL Group Replication) - 运维小结 - 散尽浮华 - 博客园 (cnblogs.com)
K8S/Kubernetes社区为您提供最前沿的新闻资讯和知识内容
更多推荐
14
0- 0
已为社区贡献8条内容
所有评论(0)