GreatSQL从单机到MGR扩展纪实

请继续丶任性

一、前言

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

服务器角色如下
IP端口主机名作用172.17.140.253310gdb1原单机172.17.140.243310gdb2MGR扩展节点1172.17.139.1643310gdb3MGR扩展节点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进行测试。

1. $ 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目录，解压安装

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

复制代码

结果如下所示

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

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

复制代码

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

1. [client]
2. socket = /data/dbdata/data3310/mysql3310.sock
4. [mysqld]
5. report_host=172.17.139.164
6. report_port=3310
7. user=mysql
8. basedir = /data/mysql
9. datadir = /data/dbdata/data3310/data
10. log_error=/data/dbdata/data3310/log/error3310.log
11. port = 3310
12. socket = /data/dbdata/data3310/mysql3310.sock
13. server-id = 253310
14. default_authentication_plugin=mysql_native_password
15. back_log = 5000
16. binlog_format = row
17. character_set_server = utf8
18. enforce_gtid_consistency = 1
19. expire_logs_days = 7
20. federated = 1
21. gtid_mode = on
22. innodb_buffer_pool_instances = 8
23. innodb_buffer_pool_size = 2G
24. innodb_change_buffering = all
25. innodb_doublewrite = true
26. innodb_file_per_table = 1
27. innodb_flush_log_at_trx_commit = 1
28. innodb_flush_method = O_DIRECT
29. innodb_io_capacity = 8000
30. #innodb_locks_unsafe_for_binlog = 1
31. innodb_log_buffer_size = 256M
32. innodb_log_file_size = 1G
33. innodb_log_files_in_group = 4
34. innodb_print_all_deadlocks = on
35. innodb_read_io_threads = 16
36. innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:8192M
37. innodb_thread_concurrency = 64
38. innodb_write_io_threads = 16
39. interactive_timeout = 3600
40. lock_wait_timeout = 600
41. log_bin = mysql-bin
42. log_bin_trust_function_creators = 1
43. log_slave_updates = 1
44. log_timestamps = SYSTEM
45. long_query_time = 1
46. lower_case_table_names = 1
47. master_info_repository = TABLE
48. max_allowed_packet = 16M
49. max_connections = 20480
50. max_prepared_stmt_count = 1048576
51. net_read_timeout = 10000
52. net_write_timeout = 10000
53. open_files_limit = 1000000
54. relay_log = mysql-relay
55. relay_log_info_repository = TABLE
56. skip_external_locking = 1
57. skip_name_resolve = 1
58. skip_slave_start = 1
59. slave_parallel_type = LOGICAL_CLOCK
60. slave_parallel_workers = 16
61. slave_pending_jobs_size_max = 2147483648
62. slave_preserve_commit_order = on
63. slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
64. slow_query_log = on
65. sort_buffer_size = 2M
66. ssl = OFF
67. sync_binlog = 1
68. table_definition_cache = 5000
69. table_open_cache = 5000
70. thread_cache_size = 3000
71. tmpdir = /data/dbdata/data3310/tmp
72. transaction_isolation = READ-COMMITTED
73. wait_timeout = 31536000
74. plugin_dir = /opt/software/mysql/lib/plugin
75. transaction_write_set_extraction=XXHASH64
76. binlog_transaction_dependency_tracking=WRITESET

复制代码

初始化实例，并启动

1. [#34#root@root /opt/software 10:01:55]34 /opt/software/mysql/bin/mysqld --defaults-file=/data/dbdata/data3310/my3310.cnf --initialize-insecure
2. [#35#root@root /opt/software 10:02:05]35
3. [#35#root@root /opt/software 10:02:25]35 /opt/software/mysql/bin/mysqld_safe --defaults-file=/data/dbdata/data3310/my3310.cnf &
4. [1] 4038
5. [#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'.
6. 2023-04-26T02:02:37.423502Z mysqld_safe Starting mysqld daemon with databases from /data/dbdata/data3310/data
8. [#36#root@root /opt/software 10:02:39]36 ps -ef| grep 3310
9. 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
10. 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
11. --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
12. [#37#root@root /opt/software 10:02:44]37

复制代码

通过进程信息可以确认，数据库启动完成。
3.2 测试数据构造

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

1. [#37#root@root /opt/software 10:06:12]37 /opt/software/mysql/bin/mysql --defaults-file=/data/dbdata/data3310/my3310.cnf
2. Welcome to the MySQL monitor.  Commands end with ; or \g.
3. Your MySQL connection id is 8
4. Server version: 8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c
6. Copyright (c) 2021-2021 GreatDB Software Co., Ltd
7. Copyright (c) 2009-2021 Percona LLC and/or its affiliates
8. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
10. Oracle is a registered trademark of Oracle Corporation and/or its
11. affiliates. Other names may be trademarks of their respective
12. owners.
14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16. mysql> select user();
17. +----------------+
18. | user()         |
19. +----------------+
20. | root@localhost |
21. +----------------+
22. 1 row in set (0.00 sec)
24. mysql> alter user user() identified by 'a123456';
25. Query OK, 0 rows affected (0.01 sec)
27. mysql> create user m1 identified by 'a123456';
28. Query OK, 0 rows affected (0.01 sec)
30. mysql> grant all on test.* to m1;
31. Query OK, 0 rows affected (0.01 sec)
33. mysql> create database test;
34. Query OK, 1 row affected (0.01 sec)
36. mysql> create table test.t1(id int primary key, cname varchar(10));
37. Query OK, 0 rows affected (0.03 sec)
39. mysql> insert into test.t1 values(1,'a'),(2,'b'),(3,'c');
40. Query OK, 3 rows affected (0.02 sec)
41. Records: 3  Duplicates: 0  Warnings: 0
43. mysql> select * from test.t1;
44. +----+-------+
45. | id | cname |
46. +----+-------+
47. |  1 | a     |
48. |  2 | b     |
49. |  3 | c     |
50. +----+-------+
51. 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同步数据的用户

1. [#41#root@root /opt/software 10:23:42]41 /opt/software/mysql/bin/mysql -S /data/dbdata/data3310/mysql3310.sock -pa123456
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. Welcome to the MySQL monitor.  Commands end with ; or \g.
4. Your MySQL connection id is 9
5. Server version: 8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c
7. Copyright (c) 2021-2021 GreatDB Software Co., Ltd
8. Copyright (c) 2009-2021 Percona LLC and/or its affiliates
9. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
11. Oracle is a registered trademark of Oracle Corporation and/or its
12. affiliates. Other names may be trademarks of their respective
13. owners.
15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
17. mysql> create user mgr_sync identified by '!QAZ2wsx';
18. Query OK, 0 rows affected (0.01 sec)
20. mysql> GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* to mgr_sync;
21. Query OK, 0 rows affected (0.01 sec)

复制代码

4.3 安装必要插件

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

1. [#46#root@root /opt/software 10:37:08]46 /opt/software/mysql/bin/mysql -S /data/dbdata/data3310/mysql3310.sock -pa123456
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. Welcome to the MySQL monitor.  Commands end with ; or \g.
4. Your MySQL connection id is 8
5. Server version: 8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c
7. Copyright (c) 2021-2021 GreatDB Software Co., Ltd
8. Copyright (c) 2009-2021 Percona LLC and/or its affiliates
9. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
11. Oracle is a registered trademark of Oracle Corporation and/or its
12. affiliates. Other names may be trademarks of their respective
13. owners.
15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
17. mysql> install plugin group_replication soname 'group_replication.so';
18. Query OK, 0 rows affected (0.23 sec)
20. mysql> install plugin clone soname 'mysql_clone.so';
21. Query OK, 0 rows affected (0.09 sec)

复制代码

4.4 配置并启动MGR

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

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

复制代码

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

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

复制代码

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

1. mysql> set global group_replication_bootstrap_group=on;
2. Query OK, 0 rows affected (0.01 sec)
4. mysql> start group_replication;
5. Query OK, 0 rows affected (0.01 sec)
7. mysql> set global group_replication_bootstrap_group=off;
8. Query OK, 0 rows affected (0.01 sec)

复制代码

在扩展节点gdb2、gdb3启动mgr

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

复制代码

五、日常运维

5.1 节点状态查询

1. mysql> select * from performance_schema.replication_group_members;
2. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
4. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
5. | group_replication_applier | 5301e31b-e3d6-11ed-bc14-00163ece3e28 | 172.17.140.25  |        3310 | ONLINE       | PRIMARY     | 8.0.32         | XCom                       |
6. | group_replication_applier | e01908f9-e3d8-11ed-be2e-00163e02fd34 | 172.17.140.24  |        3310 | ONLINE       | SECONDARY   | 8.0.32         | XCom                       |
7. | group_replication_applier | e0e5b2f9-e3d8-11ed-86cf-00163e073778 | 172.17.139.164 |        3310 | ONLINE       | SECONDARY   | 8.0.32         | XCom                       |
8. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
9. 3 rows in set (0.00 sec)

复制代码

5.2 节点复制通道状态查询

1. mysql> select * from replication_connection_status \G
2. *************************** 1. row ***************************
3.                                       CHANNEL_NAME: group_replication_applier
4.                                         GROUP_NAME: 0762c38f-e3dd-11ed-9fb3-00163ece3e28
5.                                        SOURCE_UUID: 0762c38f-e3dd-11ed-9fb3-00163ece3e28
6.                                          THREAD_ID: NULL
7.                                      SERVICE_STATE: ON
8.                          COUNT_RECEIVED_HEARTBEATS: 0
9.                           LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
10.                           RECEIVED_TRANSACTION_SET: 0762c38f-e3dd-11ed-9fb3-00163ece3e28:1-13,
11. 5301e31b-e3d6-11ed-bc14-00163ece3e28:1-8
12.                                  LAST_ERROR_NUMBER: 0
13.                                 LAST_ERROR_MESSAGE:
14.                               LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
15.                            LAST_QUEUED_TRANSACTION: 0762c38f-e3dd-11ed-9fb3-00163ece3e28:11
16. LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-04-26 11:38:02.990181
17. LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-04-26 11:38:02.990181
18.      LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2023-04-26 11:38:02.990295
19.        LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2023-04-26 11:38:02.990340
20.                               QUEUEING_TRANSACTION:
21.     QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
22.    QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
23.         QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
24. 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节点查询该变量，并且在本节点设置

1. mysql> select uuid();
2. +--------------------------------------+
3. | uuid()                               |
4. +--------------------------------------+
5. | 0762c38f-e3dd-11ed-9fb3-00163ece3e28 |
6. +--------------------------------------+
7. 1 row in set (0.01 sec)
9. mysql> set persist group_replication_group_name='0762c38f-e3dd-11ed-9fb3-00163ece3e28';
10. Query OK, 0 rows affected (0.00 sec)
12. mysql> select @@group_replication_group_name;
13. +--------------------------------------+
14. | @@group_replication_group_name       |
15. +--------------------------------------+
16. | 0762c38f-e3dd-11ed-9fb3-00163ece3e28 |
17. +--------------------------------------+
18. 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

1. mysql> set persist group_replication_local_address='172.17.140.25:13310';
2. 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值使用逗号拼接上

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

复制代码

6.4 异常4

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

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

复制代码

原因： MGR同步数据账户密码设置不正确，导致节点间无法通信
解决方法：通过下面的命令重新设置同步账号信息

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

复制代码

6.5 异常5

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

1. 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,
2. 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，再启动即可
  

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

复制代码

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

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

复制代码

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/17615989.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！