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_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 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、端口、用户名、密码指定数据库可以连接到不同的后端数据库，亲测 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;

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

参考文章：

https://proxysql.com/documentation/global-variables/mysql-variables/#mysql-set_query_lock_on_hostgroup

MySQL 5.7 基于组复制(MySQL Group Replication) - 运维小结 - 散尽浮华 - 博客园 (cnblogs.com)

【ProxySql】Mysql如何实现读写分离？看这一篇就够了-CSDN博客