一、前言

原有的业务系统跑在MySQL主从架构中，高可用通过脚本完成，但存在切换数据丢失和切换不及时风险，调研了高可用更稳定的MGR后，准备入手一试。本篇文章主要记录GreatSQL从单机扩展到MGR的详细过程，遇到的问题及解决方法。

二、基础环境

服务器角色如下

IP	端口	主机名	作用
172.17.140.25	3310	gdb1	原单机
172.17.140.24	3310	gdb2	MGR扩展节点1
172.17.139.164	3310	gdb3	MGR扩展节点2

本次验证使用的是GreatSQL8.0.32-24版本，相比MySQL官网的8.0.32，GreatSQL在性能、可靠性、易用性和安全方面，都有不错的提升，可以在后续环境中验证。具体提升点可以参考:GreatSQL简介[https://greatsql.cn/docs/8032/user-manual/2-about-greatsql/2-1-greatsql-brief-intro.html]

通过下面的命令下载GreatSQL8.0.32-24进行测试。

$ wget https://product.greatdb.com/GreatSQL-8.0.32-24/GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64-minimal.tar.xz 

三、单机搭建

3.1 单机搭建

将软件包，上传至172.17.140.25的/opt/software目录，解压安装

# cd /opt/software # xz -d GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64-minimal.tar.xz # tar xf GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64-minimal.tar # ln -s GreatSQL-8.0.32-24-Linux-glibc2.17-x86_64-minimal mysql 

结果如下所示

创建必要的目录，并修改属主

[#25#root@root /opt/software 09:55:30]25 mkdir -p /data/dbdata/data3310/{data,log,tmp} [#26#root@root /opt/software 09:55:41]26 chown -R mysql. /data/dbdata/data3310 [#27#root@root /opt/software 09:55:53]27 ll /data/dbdata/data3310 total 12 drwxr-xr-x 2 mysql mysql 4096 Apr 26 09:55 data drwxr-xr-x 2 mysql mysql 4096 Apr 26 09:55 log drwxr-xr-x 2 mysql mysql 4096 Apr 26 09:55 tmp [#28#root@root /opt/software 09:56:02]28 

创建配置文件/data/dbdata/data3310/my3310.cnf，配置文件中并没有与MGR相关的参数配置，内容如下

[client] socket = /data/dbdata/data3310/mysql3310.sock [mysqld] report_host=172.17.139.164 report_port=3310 user=mysql basedir = /data/mysql datadir = /data/dbdata/data3310/data log_error=/data/dbdata/data3310/log/error3310.log port = 3310 socket = /data/dbdata/data3310/mysql3310.sock server-id = 253310 default_authentication_plugin=mysql_native_password back_log = 5000 binlog_format = row character_set_server = utf8 enforce_gtid_consistency = 1 expire_logs_days = 7 federated = 1 gtid_mode = on innodb_buffer_pool_instances = 8 innodb_buffer_pool_size = 2G innodb_change_buffering = all innodb_doublewrite = true innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_io_capacity = 8000 #innodb_locks_unsafe_for_binlog = 1 innodb_log_buffer_size = 256M innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_print_all_deadlocks = on innodb_read_io_threads = 16 innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:8192M innodb_thread_concurrency = 64 innodb_write_io_threads = 16 interactive_timeout = 3600 lock_wait_timeout = 600 log_bin = mysql-bin log_bin_trust_function_creators = 1 log_slave_updates = 1 log_timestamps = SYSTEM long_query_time = 1 lower_case_table_names = 1 master_info_repository = TABLE max_allowed_packet = 16M max_connections = 20480 max_prepared_stmt_count = 1048576 net_read_timeout = 10000 net_write_timeout = 10000 open_files_limit = 1000000 relay_log = mysql-relay relay_log_info_repository = TABLE skip_external_locking = 1 skip_name_resolve = 1 skip_slave_start = 1 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 16 slave_pending_jobs_size_max = 2147483648 slave_preserve_commit_order = on slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN slow_query_log = on sort_buffer_size = 2M ssl = OFF sync_binlog = 1 table_definition_cache = 5000 table_open_cache = 5000 thread_cache_size = 3000 tmpdir = /data/dbdata/data3310/tmp transaction_isolation = READ-COMMITTED wait_timeout = 31536000 plugin_dir = /opt/software/mysql/lib/plugin transaction_write_set_extraction=XXHASH64 binlog_transaction_dependency_tracking=WRITESET 

初始化实例，并启动

[#34#root@root /opt/software 10:01:55]34 /opt/software/mysql/bin/mysqld --defaults-file=/data/dbdata/data3310/my3310.cnf --initialize-insecure [#35#root@root /opt/software 10:02:05]35 [#35#root@root /opt/software 10:02:25]35 /opt/software/mysql/bin/mysqld_safe --defaults-file=/data/dbdata/data3310/my3310.cnf & [1] 4038 [#36#root@root /opt/software 10:02:36]36 2023-04-26T02:02:37.359772Z mysqld_safe Logging to '/data/dbdata/data3310/log/error3310.log'. 2023-04-26T02:02:37.423502Z mysqld_safe Starting mysqld daemon with databases from /data/dbdata/data3310/data [#36#root@root /opt/software 10:02:39]36 ps -ef| grep 3310 root 4038 21630 1 10:02 pts/1 00:00:00 /bin/sh /opt/software/mysql/bin/mysqld_safe --defaults-file=/data/dbdata/data3310/my3310.cnf mysql 5167 4038 72 10:02 pts/1 00:00:05 /opt/software/mysql/bin/mysqld --defaults-file=/data/dbdata/data3310/my3310.cnf --basedir=/data/mysql --datadir=/data/dbdata/data3310/data --plugin-dir=/data/mysql/lib/plugin --user=mysql --log-error=/data/dbdata/data3310/log/error3310.log --open-files-limit=1000000 --pid-file=root.pid --socket=/data/dbdata/data3310/mysql3310.sock --port=3310root 5294 21630 0 10:02 pts/1 00:00:00 grep --color=auto 3310 [#37#root@root /opt/software 10:02:44]37 

通过进程信息可以确认，数据库启动完成。

3.2 测试数据构造

连接数据库，修改默认密码，并且创建部分测试数据

[#37#root@root /opt/software 10:06:12]37 /opt/software/mysql/bin/mysql --defaults-file=/data/dbdata/data3310/my3310.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c Copyright (c) 2021-2021 GreatDB Software Co., Ltd Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, 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> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> alter user user() identified by 'a123456'; Query OK, 0 rows affected (0.01 sec) mysql> create user m1 identified by 'a123456'; Query OK, 0 rows affected (0.01 sec) mysql> grant all on test.* to m1; Query OK, 0 rows affected (0.01 sec) mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> create table test.t1(id int primary key, cname varchar(10)); Query OK, 0 rows affected (0.03 sec) mysql> insert into test.t1 values(1,'a'),(2,'b'),(3,'c'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test.t1; +----+-------+ | id | cname | +----+-------+ | 1 | a | | 2 | b | | 3 | c | +----+-------+ 3 rows in set (0.00 sec) 

四、扩展节点搭建，MGR构建

4.1 扩展节点搭建

按照3.1章节步骤，分别在服务器172.17.140.24、172.17.139.164上初始化单机实例

4.2 在gdb1、gdb2、gdb3实例中，创建用于MGR同步数据的用户

[#41#root@root /opt/software 10:23:42]41 /opt/software/mysql/bin/mysql -S /data/dbdata/data3310/mysql3310.sock -pa123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c Copyright (c) 2021-2021 GreatDB Software Co., Ltd Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, 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> create user mgr_sync identified by '!QAZ2wsx'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* to mgr_sync; Query OK, 0 rows affected (0.01 sec) 

4.3 安装必要插件

在gdb1、gdb2、gdb3服务器实例中，安装下面插件

[#46#root@root /opt/software 10:37:08]46 /opt/software/mysql/bin/mysql -S /data/dbdata/data3310/mysql3310.sock -pa123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c Copyright (c) 2021-2021 GreatDB Software Co., Ltd Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, 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> install plugin group_replication soname 'group_replication.so'; Query OK, 0 rows affected (0.23 sec) mysql> install plugin clone soname 'mysql_clone.so'; Query OK, 0 rows affected (0.09 sec) 

4.4 配置并启动MGR

在三个节点中，进行如下参数配置

mysql> set persist group_replication_group_name='0762c38f-e3dd-11ed-9fb3-00163ece3e28'; mysql> set persist group_replication_group_seeds='172.17.140.25:13310,172.17.140.24:13310,172.17.139.164:13310'; mysql> CHANGE MASTER TO MASTER_USER='mgr_sync', MASTER_PASSWORD='!QAZ2wsx' FOR CHANNEL 'group_replication_recovery'; 

在三个节点中，对group_replication_local_address进行配置，配置规则见章节5.2 错误2

mysql> set persist group_replication_local_address='172.17.140.25:13310'; 

在已有数据节点gdb1设置引导参数，并且启动mgr

mysql> set global group_replication_bootstrap_group=on; Query OK, 0 rows affected (0.01 sec) mysql> start group_replication; Query OK, 0 rows affected (0.01 sec) mysql> set global group_replication_bootstrap_group=off; Query OK, 0 rows affected (0.01 sec) 

在扩展节点gdb2、gdb3启动mgr

mysql> start group_replication; Query OK, 0 rows affected (0.01 sec) 

五、日常运维

5.1 节点状态查询

mysql> 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 | 5301e31b-e3d6-11ed-bc14-00163ece3e28 | 172.17.140.25 | 3310 | ONLINE | PRIMARY | 8.0.32 | XCom | | group_replication_applier | e01908f9-e3d8-11ed-be2e-00163e02fd34 | 172.17.140.24 | 3310 | ONLINE | SECONDARY | 8.0.32 | XCom | | group_replication_applier | e0e5b2f9-e3d8-11ed-86cf-00163e073778 | 172.17.139.164 | 3310 | ONLINE | SECONDARY | 8.0.32 | XCom | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+ 3 rows in set (0.00 sec) 

5.2 节点复制通道状态查询

mysql> select * from replication_connection_status \G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: 0762c38f-e3dd-11ed-9fb3-00163ece3e28 SOURCE_UUID: 0762c38f-e3dd-11ed-9fb3-00163ece3e28 THREAD_ID: NULL SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000 RECEIVED_TRANSACTION_SET: 0762c38f-e3dd-11ed-9fb3-00163ece3e28:1-13, 5301e31b-e3d6-11ed-bc14-00163ece3e28:1-8 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: 0762c38f-e3dd-11ed-9fb3-00163ece3e28:11 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-04-26 11:38:02.990181 LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-04-26 11:38:02.990181 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2023-04-26 11:38:02.990295 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2023-04-26 11:38:02.990340 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 1 row in set (0.00 sec) 

更多运维信息详见https://gitee.com/GreatSQL/GreatSQL-Doc/tree/master/deep-dive-mgr

六、问题处理

6.1 异常1

提示信息：The group_replication_group_name option is mandatory

原因：变量group_replication_group_name必须在每个MGR成员中设置，并且保持一致

解决方法：如果是初始化集群，可以查询一个uuid()的值作为group_replication_group_name的值，如果是已经存在的集群，则到其他MGR节点查询该变量，并且在本节点设置

mysql> select uuid(); +--------------------------------------+ | uuid() | +--------------------------------------+ | 0762c38f-e3dd-11ed-9fb3-00163ece3e28 | +--------------------------------------+ 1 row in set (0.01 sec) mysql> set persist group_replication_group_name='0762c38f-e3dd-11ed-9fb3-00163ece3e28'; Query OK, 0 rows affected (0.00 sec) mysql> select @@group_replication_group_name; +--------------------------------------+ | @@group_replication_group_name | +--------------------------------------+ | 0762c38f-e3dd-11ed-9fb3-00163ece3e28 | +--------------------------------------+ 1 row in set (0.00 sec) 

6.2 异常2

提示信息：[GCS] Invalid hostname or IP address () assigned to the parameter local_node!

原因：变量group_replication_local_address必须在每个MGR成员中设置，并且每个成员的值不一样

解决方法：参数group_replication_local_address设置规则为IP:PORT，一般是设置为本机IP，然后在实例端口前面加1作为MGR监听端口，即文章中的实例端口为3310，此时MGR监听端口设置为13310

mysql> set persist group_replication_local_address='172.17.140.25:13310'; Query OK, 0 rows affected (0.00 sec) 

6.3 异常3

提示信息：[GCS] Unable to join the group: peers not configured.

原因：变量group_replication_group_seeds必须在每个MGR成员中设置，并且保持一致

解决方法：设置格式为所有节点的group_replication_local_address值使用逗号拼接上

mysql> set persist group_replication_group_seeds='172.17.140.25:13310,172.17.140.24:13310,172.17.139.164:13310'; Query OK, 0 rows affected (0.01 sec) 

6.4 异常4

提示信息：performance_schema.replication_group_members中节点状态一直处于RECOVERING，并且在error log中有如下信息

2023-04-26T11:06:32.048986+08:00 38 [ERROR] [MY-013117] [Repl] Slave I/O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: MY-013117 2023-04-26T11:06:32.179376+08:00 37 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.' 2023-04-26T11:06:32.179493+08:00 37 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_rep lication_recovery.' 

原因： MGR同步数据账户密码设置不正确，导致节点间无法通信

解决方法：通过下面的命令重新设置同步账号信息

mysql> CHANGE MASTER TO MASTER_USER='mgr_sync', MASTER_PASSWORD='!QAZ2wsx' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected (0.01 sec) 

6.5 异常5

提示信息：执行start group_replication命令返回失败，在错误日志中存在如下信息

2023-04-26T11:27:14.610060+08:00 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 0762c38f-e3dd-11ed-9fb3-00163ece3e28:1-7, 5301e31b-e3d6-11ed-bc14-00163ece3e28:1-8, e0e5b2f9-e3d8-11ed-86cf-00163e073778:1 > Group transactions: 0762c38f-e3dd-11ed-9fb3-00163ece3e28:1-7, 5301e31b-e3d6-11ed-bc14-00163ece3e28:1-8' 

原因：由于新加入的MGR节点存在本地事务，与MGR集群事务冲突，导致无法加入

解决方法有两种：

1. 确认集群为新节点，并且本地操作未修改数据，可以直接重置本节点的GTID，再启动即可

## 在新节点中执行如下命令 mysql> reset master; Query OK, 0 rows affected (0.03 sec) mysql> start group_replication; Query OK, 0 rows affected (2.06 sec) 

1. 手动从其他正常节点进行数据clone，clone完成后，实例会自动重启，自动加入mgr组

mysql> set global clone_valid_donor_list='172.17.140.25:3310'; Query OK, 0 rows affected (0.00 sec) mysql> set global super_read_only=off; Query OK, 0 rows affected (0.00 sec) mysql> clone instance from mgr_sync@172.17.140.25:3310 identified by '!QAZ2wsx'; Query OK, 0 rows affected (1.62 sec) mysql> Restarting mysqld... 2023-04-26T03:37:53.502149Z mysqld_safe Number of processes running now: 0 2023-04-26T03:37:53.511862Z mysqld_safe mysqld restarte 

---

Enjoy GreatSQL :)

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

社区有奖建议反馈： https://greatsql.cn/thread-54-1-1.html

社区博客有奖征稿详情： https://greatsql.cn/thread-100-1-1.html

（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）

技术交流群：

微信&QQ群：

QQ群：533341697

微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。