GreatSQL从单机到MGR扩展纪实

heatdog

发布于 2023-8-30 11:55

浏览

0收藏

一、前言

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

三、单机搭建

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 mysql1.
2.
3.
4.

结果如下所示

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 1.
2.
3.
4.
5.
6.
7.
8.

创建配置文件/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=WRITESET1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.

初始化实例，并启动

[#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 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

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

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

四、扩展节点搭建，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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

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';1.
2.
3.

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

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

在已有数据节点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)1.
2.
3.
4.
5.
6.
7.
8.

在扩展节点gdb2、gdb3启动mgr

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

五、日常运维

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)1.
2.
3.
4.
5.
6.
7.
8.
9.

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

六、问题处理

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

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)1.
2.

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)1.
2.

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.'1.
2.
3.
4.

原因： 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)1.
2.

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'1.
2.

原因：由于新加入的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)1.
2.
3.
4.
5.
6.

手动从其他正常节点进行数据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 restarte1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

Enjoy GreatSQL :)

文章转载自公众号：GreatSQL社区

分类

数据库

标签

GreatSQL

已于2023-8-30 11:55:36修改

51CTO

51CTO博客

51CTO学堂