翼度科技»论坛 编程开发 mysql 查看内容

GreatSQL从单机到MGR扩展纪实

8

主题

8

帖子

24

积分

新手上路

Rank: 1

积分
24
一、前言

原有的业务系统跑在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
  3. [mysqld]
  4. report_host=172.17.139.164
  5. report_port=3310
  6. user=mysql
  7. basedir = /data/mysql
  8. datadir = /data/dbdata/data3310/data
  9. log_error=/data/dbdata/data3310/log/error3310.log
  10. port = 3310
  11. socket = /data/dbdata/data3310/mysql3310.sock
  12. server-id = 253310
  13. default_authentication_plugin=mysql_native_password
  14. back_log = 5000
  15. binlog_format = row
  16. character_set_server = utf8
  17. enforce_gtid_consistency = 1
  18. expire_logs_days = 7
  19. federated = 1
  20. gtid_mode = on
  21. innodb_buffer_pool_instances = 8
  22. innodb_buffer_pool_size = 2G
  23. innodb_change_buffering = all
  24. innodb_doublewrite = true
  25. innodb_file_per_table = 1
  26. innodb_flush_log_at_trx_commit = 1
  27. innodb_flush_method = O_DIRECT
  28. innodb_io_capacity = 8000
  29. #innodb_locks_unsafe_for_binlog = 1
  30. innodb_log_buffer_size = 256M
  31. innodb_log_file_size = 1G
  32. innodb_log_files_in_group = 4
  33. innodb_print_all_deadlocks = on
  34. innodb_read_io_threads = 16
  35. innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:8192M
  36. innodb_thread_concurrency = 64
  37. innodb_write_io_threads = 16
  38. interactive_timeout = 3600
  39. lock_wait_timeout = 600
  40. log_bin = mysql-bin
  41. log_bin_trust_function_creators = 1
  42. log_slave_updates = 1
  43. log_timestamps = SYSTEM
  44. long_query_time = 1
  45. lower_case_table_names = 1
  46. master_info_repository = TABLE
  47. max_allowed_packet = 16M
  48. max_connections = 20480
  49. max_prepared_stmt_count = 1048576
  50. net_read_timeout = 10000
  51. net_write_timeout = 10000
  52. open_files_limit = 1000000
  53. relay_log = mysql-relay
  54. relay_log_info_repository = TABLE
  55. skip_external_locking = 1
  56. skip_name_resolve = 1
  57. skip_slave_start = 1
  58. slave_parallel_type = LOGICAL_CLOCK
  59. slave_parallel_workers = 16
  60. slave_pending_jobs_size_max = 2147483648
  61. slave_preserve_commit_order = on
  62. slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
  63. slow_query_log = on
  64. sort_buffer_size = 2M
  65. ssl = OFF
  66. sync_binlog = 1
  67. table_definition_cache = 5000
  68. table_open_cache = 5000
  69. thread_cache_size = 3000
  70. tmpdir = /data/dbdata/data3310/tmp
  71. transaction_isolation = READ-COMMITTED
  72. wait_timeout = 31536000
  73. plugin_dir = /opt/software/mysql/lib/plugin
  74. transaction_write_set_extraction=XXHASH64
  75. 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
  7. [#36#root@root /opt/software 10:02:39]36 ps -ef| grep 3310
  8. 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
  9. 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
  10. --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
  11. [#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
  5. Copyright (c) 2021-2021 GreatDB Software Co., Ltd
  6. Copyright (c) 2009-2021 Percona LLC and/or its affiliates
  7. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  8. Oracle is a registered trademark of Oracle Corporation and/or its
  9. affiliates. Other names may be trademarks of their respective
  10. owners.
  11. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  12. mysql> select user();
  13. +----------------+
  14. | user()         |
  15. +----------------+
  16. | root@localhost |
  17. +----------------+
  18. 1 row in set (0.00 sec)
  19. mysql> alter user user() identified by 'a123456';
  20. Query OK, 0 rows affected (0.01 sec)
  21. mysql> create user m1 identified by 'a123456';
  22. Query OK, 0 rows affected (0.01 sec)
  23. mysql> grant all on test.* to m1;
  24. Query OK, 0 rows affected (0.01 sec)
  25. mysql> create database test;
  26. Query OK, 1 row affected (0.01 sec)
  27. mysql> create table test.t1(id int primary key, cname varchar(10));
  28. Query OK, 0 rows affected (0.03 sec)
  29. mysql> insert into test.t1 values(1,'a'),(2,'b'),(3,'c');
  30. Query OK, 3 rows affected (0.02 sec)
  31. Records: 3  Duplicates: 0  Warnings: 0
  32. mysql> select * from test.t1;
  33. +----+-------+
  34. | id | cname |
  35. +----+-------+
  36. |  1 | a     |
  37. |  2 | b     |
  38. |  3 | c     |
  39. +----+-------+
  40. 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
  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.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  13. mysql> create user mgr_sync identified by '!QAZ2wsx';
  14. Query OK, 0 rows affected (0.01 sec)
  15. mysql> GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* to mgr_sync;
  16. 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
  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.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  13. mysql> install plugin group_replication soname 'group_replication.so';
  14. Query OK, 0 rows affected (0.23 sec)
  15. mysql> install plugin clone soname 'mysql_clone.so';
  16. 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)
  3. mysql> start group_replication;
  4. Query OK, 0 rows affected (0.01 sec)
  5. mysql> set global group_replication_bootstrap_group=off;
  6. 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)
  8. mysql> set persist group_replication_group_name='0762c38f-e3dd-11ed-9fb3-00163ece3e28';
  9. Query OK, 0 rows affected (0.00 sec)
  10. mysql> select @@group_replication_group_name;
  11. +--------------------------------------+
  12. | @@group_replication_group_name       |
  13. +--------------------------------------+
  14. | 0762c38f-e3dd-11ed-9fb3-00163ece3e28 |
  15. +--------------------------------------+
  16. 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)
  4. mysql> start group_replication;
  5. 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)
  3. mysql> set global super_read_only=off;
  4. Query OK, 0 rows affected (0.00 sec)
  5. mysql> clone instance from mgr_sync@172.17.140.25:3310 identified by '!QAZ2wsx';
  6. Query OK, 0 rows affected (1.62 sec)
  7. mysql> Restarting mysqld...
  8. 2023-04-26T03:37:53.502149Z mysqld_safe Number of processes running now: 0
  9. 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】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具