论坛问答 › GreatSQL专区 › docker-compose 部署mgr集群 ，mysql-router 路由 主节点无法恢复

openai 发表于 2024-10-16 14:01:28

docker-compose 部署mgr集群 ，mysql-router 路由 主节点无法恢复

采用 docker-compose 部署mgr集群 ，mysql-router 做高可用 ， 主节点无法恢复的问题 。

三台服务器 构建 mgr 一主 、一丛、一仲裁：

172.30.0.2greatsql-master
172.30.0.3 greatsql-slave
172.30.0.4 greatsql-arb



采用docker-compose 部署mgr集群 yml 配置文件内容如下，其实就说官方例子 GreatSQL-Docker 拷贝下来 增加了不同挂载目录 ，防止数据覆盖 。



[*]version: '2'

services:
greatsql-master:
    #image: greatsql/greatsql      #指定镜像
    image: registry.cn-beijing.aliyuncs.com/greatsql/greatsql      #指定镜像（阿里云ACR）
    container_name: greatsql-master            #设定容器名字
    hostname: greatsql-master                  #设定容器中的主机名
    networks:                     #指定容器使用哪个专用网络
      mgr_net:
      ipv4_address: 172.30.0.2    #设置容器使用固定IP地址，避免重启后IP变化
    restart: unless-stopped         #设定重启策略
    environment:                  #设置多个环境变量
      TZ: Asia/Shanghai             #时区
      MYSQL_ALLOW_EMPTY_PASSWORD: 1               #允许root账户空密码
      MYSQL_INIT_MGR: 1                           #初始化MGR集群
      #MYSQL_MGR_NAME: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"       #MGR group_name
      #MYSQL_MGR_VIEWID: "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1"   #MGR view_change_uuid
      MYSQL_MGR_LOCAL: '172.30.0.2:33061'         #当前MGR节点的local_address
      MYSQL_MGR_SEEDS: '172.30.0.2:33061,172.30.0.3:33061,172.30.0.4:33061'   #MGR集群seeds
      MYSQL_MGR_START_AS_PRIMARY: 1               #指定当前MGR节点为Primary角色
      MYSQL_MGR_ARBITRATOR: 0                     #指定当前MGR节点为Arbitrator角色，此时不能同时指定其为Primary/Secondary角色
      LOWER_CASE_TABLE_NAMES: 0                     #设定lower_case_table_names值，默认为0
      MAXPERF: 0
    volumes:
      - ./data_master:/data
      # - /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime

greatsql-slave:
    #image: greatsql/greatsql      #指定镜像
    image: registry.cn-beijing.aliyuncs.com/greatsql/greatsql      #指定镜像（阿里云ACR）
    container_name: greatsql-slave
    hostname: greatsql-slave
    networks:
      mgr_net:
      ipv4_address: 172.30.0.3
    restart: unless-stopped
    depends_on:
      - "greatsql-master"
    environment:
      TZ: Asia/Shanghai
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_INIT_MGR: 1
      MYSQL_MGR_LOCAL: '172.30.0.3:33061'
      MYSQL_MGR_SEEDS: '172.30.0.2:33061,172.30.0.3:33061,172.30.0.4:33061'
      MYSQL_MGR_START_AS_PRIMARY: 0
      MYSQL_MGR_ARBITRATOR: 0                     #既非Primary，也非Arbitrator，那么就是Secondary角色了
      LOWER_CASE_TABLE_NAMES: 0
      MAXPERF: 0
    volumes:
      - ./data_slave:/data
      # - /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime
greatsql-arb:
    #image: greatsql/greatsql      #指定镜像
    image: registry.cn-beijing.aliyuncs.com/greatsql/greatsql      #指定镜像（阿里云ACR）
    container_name: greatsql-arb
    hostname: greatsql-arb
    networks:
      mgr_net:
      ipv4_address: 172.30.0.4
    restart: unless-stopped
    depends_on:
      - "greatsql-slave"
    environment:
      TZ: Asia/Shanghai
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_INIT_MGR: 1
      MYSQL_MGR_LOCAL: '172.30.0.4:33061'
      MYSQL_MGR_SEEDS: '172.30.0.2:33061,172.30.0.3:33061,172.30.0.4:33061'
      MYSQL_MGR_ARBITRATOR: 1                   #指定当前MGR节点为Arbitrator角色，此时不能同时指定其为Primary/Secondary角色
      LOWER_CASE_TABLE_NAMES: 0
      MAXPERF: 0
    volumes:
      - ./data_arb:/data
      # - /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime
networks:
mgr_net:#创建独立MGR专属网络
    ipam:
      config:
      - subnet: 172.30.0.0/26
2、部署测试结果如下 ：

   
       从节点 greatsql-slave
和仲裁节点 greatsql-arb
都可以在停止后, 再次启动可以子自动恢复到集群 。
      
      部署日志：

   

[+] Building 0.0s (0/0)                                                                                                                                                                        docker:default
[+] Running 4/4
✔ Network docker-compose_mgr_netCreated                                                                                                                                                               0.2s
✔ Container greatsql-master       Created                                                                                                                                                               0.7s
✔ Container greatsql-slave      Created                                                                                                                                                               0.5s
✔ Container greatsql-arb          Created                                                                                                                                                               0.5s
Attaching to greatsql-arb, greatsql-master, greatsql-slave
greatsql-master| Initializing database
greatsql-slave   | Initializing database
greatsql-arb   | Initializing database
greatsql-master| Database initialized
greatsql-master| MySQL init process in progress...
greatsql-arb   | Database initialized
greatsql-arb   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-slave   | Database initialized
greatsql-arb   | MySQL init process in progress...
greatsql-slave   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-slave   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-slave   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-slave   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-slave   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-slave   | MySQL init process in progress...
greatsql-master| MySQL init process in progress...
greatsql-arb   | MySQL init process in progress...
greatsql-slave   | MySQL init process in progress...
greatsql-master| mysql: Using a password on the command line interface can be insecure.
greatsql-arb   | mysql: Using a password on the command line interface can be insecure.
greatsql-slave   | MySQL init process in progress...
greatsql-slave   | MySQL init process in progress...
greatsql-master| Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
greatsql-master| Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
greatsql-master| Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
greatsql-slave   | mysql: Using a password on the command line interface can be insecure.
greatsql-arb   | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
greatsql-arb   | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
greatsql-arb   | Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
greatsql-master| Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
greatsql-master| Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
greatsql-master| Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
greatsql-master| mysql: Using a password on the command line interface can be insecure.
greatsql-arb   | Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
greatsql-arb   | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
greatsql-arb   | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
greatsql-arb   | mysql: Using a password on the command line interface can be insecure.
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
greatsql-master|
greatsql-master| /greatsql-init.sh: ignoring /docker-entrypoint-initdb.d/*
greatsql-master|
greatsql-arb   |
greatsql-arb   | /greatsql-init.sh: ignoring /docker-entrypoint-initdb.d/*
greatsql-arb   |
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
greatsql-slave   | mysql: Using a password on the command line interface can be insecure.
greatsql-slave   |
greatsql-slave   | /greatsql-init.sh: ignoring /docker-entrypoint-initdb.d/*
greatsql-slave   |
greatsql-master|
greatsql-master| MySQL init process done. Ready for start up.
greatsql-master|
greatsql-arb   |
greatsql-arb   | MySQL init process done. Ready for start up.
greatsql-arb   |
greatsql-arb   | /greatsql-init.sh: line 330: [: -eq: unary operator expected
greatsql-arb   | MAXPERF invalid
greatsql-master| MAXPERF invalid
greatsql-slave   |
greatsql-slave   | MySQL init process done. Ready for start up.
greatsql-slave   |
greatsql-slave   | MAXPERF invalid


进入 greatsql-master 查看整个 mgr 集群情况
docker exec -it greatsql-master bash
bash-4.4# mysql
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.32-26 GreatSQL (GPL), Release 26, Revision a68b3034c3d

Copyright (c) 2021-2024 GreatDB Software Co., Ltd
Copyright (c) 2009-2024 Percona LLC and/or its affiliates
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.

(Wed Oct 16 13:47:13 2024)[(none)]> ^C
^C
(Wed Oct 16 13:47:23 2024)[(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME            | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4e79de51-8b81-11ef-a19d-0242ac1e0002 | 172.30.0.2|      3306 | ONLINE       | PRIMARY   | 8.0.32         | XCom                     |
| group_replication_applier | 4f937f88-8b81-11ef-a301-0242ac1e0003 | 172.30.0.3|      3306 | ONLINE       | SECONDARY   | 8.0.32         | XCom                     |
| group_replication_applier | 508fae3b-8b81-11ef-a1fe-0242ac1e0004 | 172.30.0.4|      3306 | ONLINE       | ARBITRATOR| 8.0.32         | XCom                     |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.03 sec)



停止 greatsql-master


GreatSQL-Docker/GreatSQL/docker-compose$ docker-compose -f mgr-3nodes.yml stop greatsql-master
[+] Stopping 1/1
✔ Container greatsql-masterStopped                                 

进入 slave 容器查看情况 ：

发现从节点变成主节点 ：初始化页面可以看到 相关 启动 、停止信息



再次启动 greatsql-master
docker-compose -f mgr-3nodes.yml start greatsql-master
[+] Running 1/1
✔ Container greatsql-masterStarted                           


greatsql-master|
greatsql-master| /greatsql-init.sh: ignoring /docker-entrypoint-initdb.d/*
greatsql-master|
greatsql-arb   |
greatsql-arb   | /greatsql-init.sh: ignoring /docker-entrypoint-initdb.d/*
greatsql-arb   |
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
greatsql-slave   | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
greatsql-slave   | mysql: Using a password on the command line interface can be insecure.
greatsql-slave   |
greatsql-slave   | /greatsql-init.sh: ignoring /docker-entrypoint-initdb.d/*
greatsql-slave   |
greatsql-master|
greatsql-master| MySQL init process done. Ready for start up.
greatsql-master|
greatsql-arb   |
greatsql-arb   | MySQL init process done. Ready for start up.
greatsql-arb   |
greatsql-arb   | /greatsql-init.sh: line 330: [: -eq: unary operator expected
greatsql-arb   | MAXPERF invalid
greatsql-master| MAXPERF invalid
greatsql-slave   |
greatsql-slave   | MySQL init process done. Ready for start up.
greatsql-slave   |
greatsql-slave   | MAXPERF invalid
greatsql-master exited with code 0 关闭退出
greatsql-master| MAXPERF invalid启动
可以看到greatsql-master 启动相关信息 ，并再次查看进入greatesql-slave 节点查看信息 ，发现之前掉线的的主节点没有恢复到mgr集群里面来 。

docker exec -it greatsql-slave bash
bash-4.4# mysql
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.32-26 GreatSQL (GPL), Release 26, Revision a68b3034c3d

Copyright (c) 2021-2024 GreatDB Software Co., Ltd
Copyright (c) 2009-2024 Percona LLC and/or its affiliates
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.

(Wed Oct 16 13:50:49 2024)[(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME            | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4f937f88-8b81-11ef-a301-0242ac1e0003 | 172.30.0.3|      3306 | ONLINE       | PRIMARY   | 8.0.32         | XCom                     |
| group_replication_applier | 508fae3b-8b81-11ef-a1fe-0242ac1e0004 | 172.30.0.4|      3306 | ONLINE       | ARBITRATOR| 8.0.32         | XCom                     |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.09 sec)

(Wed Oct 16 13:50:57 2024)[(none)]> ^C
^C
(Wed Oct 16 13:54:54 2024)[(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME            | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4f937f88-8b81-11ef-a301-0242ac1e0003 | 172.30.0.3|      3306 | ONLINE       | PRIMARY   | 8.0.32         | XCom                     |
| group_replication_applier | 508fae3b-8b81-11ef-a1fe-0242ac1e0004 | 172.30.0.4|      3306 | ONLINE       | ARBITRATOR| 8.0.32         | XCom                     |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.01 sec)
进入 greatesql-master 里面查看集群信息：发现该节点信息任然为 PRIMARY无法自动更改为从节点 。


docker exec -it greatsql-master bash
bash-4.4# mysql
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.32-26 GreatSQL (GPL), Release 26, Revision a68b3034c3d

Copyright (c) 2021-2024 GreatDB Software Co., Ltd
Copyright (c) 2009-2024 Percona LLC and/or its affiliates
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.

(Wed Oct 16 13:56:37 2024)[(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME            | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4e79de51-8b81-11ef-a19d-0242ac1e0002 | 172.30.0.2|      3306 | ONLINE       | PRIMARY   | 8.0.32         | XCom                     |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.05 sec)



是否有办法解决这种问题 ，因为在宿主机上面部署mgr集群 、后续升级维也特别麻烦 ；如果采用K8S 部署项目条件达不到 。
请问采用docker-compose 部署这种方案是否有办法解决这种问题 。

驭无殇1998 发表于 2024-10-16 14:12:47

如果你只是测试环境使用docker部署个人感觉是没问题的。

但是生产也使用docker的方式进行部署的话，不太推荐，还是建议直接部署到宿主机比较好维护，关于升级的话，如果是mgr集群，不是修改特别大的版本升级的话，直接替换安装包，滚动升级的方式也是很方便的

yejr 发表于 2024-10-16 15:18:02

GreatSQL docker镜像的逻辑是只支持预设指定主从节点，不支持切换后继续保持新的主从关系。也即利用docker-compose构建好一个MGR集群后，只能整体重启后还维持预设指定的拓扑关系，不支持在运行过程中发生主从切换后，再次重启还能继续维持这个主从拓扑。

具体的处理逻辑见 https://gitee.com/GreatSQL/GreatSQL-Docker/blob/greatsql-8.0.32-26/GreatSQL/greatsql-init.sh 。

如果需要实现上述需求得自己重新封装处理哈。

openai 发表于 2024-10-16 15:19:59

驭无殇1998 发表于 2024-10-16 14:12
如果你只是测试环境使用docker部署个人感觉是没问题的。

但是生产也使用docker的方式进行部署的话，不太推 ...

非常感谢:handshake

查看完整版本: docker-compose 部署mgr集群 ，mysql-router 路由 主节点无法恢复