GreatSQL从单机到MGR扩展纪实-Toy模板网

这篇具有很好参考价值的文章主要介绍了GreatSQL从单机到MGR扩展纪实。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、前言

原有的业务系统跑在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

结果如下所示

GreatSQL从单机到MGR扩展纪实
创建必要的目录，并修改属主

[#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集群事务冲突，导致无法加入

解决方法有两种：

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

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

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

手动从其他正常节点进行数据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 😃