为什么要用GreatSQL Shell构建MGR

只有 GreatSQL Shell 支持仲裁节点（投票节点）特性，MySQL Shell 社区版不支持。

下载MySQL Shell for GreatSQL

wget https://product.greatdb.com/GreatSQL-8.0.32-25/greatsql-shell-8.0.32-25-glibc2.28-x86_64.tar.xz
复制

安装GreatSQL Shell

tar -xvf greatsql-shell-8.0.32-25-glibc2.28-x86_64.tar.xz
mv greatsql-shell-8.0.32-25-glibc2.28-x86_64 /data/greatsql-shell
echo 'export PATH=$PATH:/data/GreatSQL/bin:/data/greatsql-shell/bin' >> ~/.bash_profile
source  ~/.bash_profile
复制

使用GreatSQL Shell构建MGR

[root@GreatSQL01 bin]# mysqlsh -S  /data/GreatSQL/data/mysql.sock root@localhost
Could not find platform independent libraries <prefix>
Could not find platform dependent libraries <exec_prefix>
Consider setting $PYTHONHOME to <prefix>[:<exec_prefix>]
Python path configuration:
  PYTHONHOME = (not set)
  PYTHONPATH = (not set)
  program name = '/data/greatsql-shell/bin/mysqlsh'
  isolated = 0
  environment = 1
  user site = 1
  import site = 1
  sys._base_executable = '/data/greatsql-shell/bin/mysqlsh'
  sys.base_prefix = '/usr'
  sys.base_exec_prefix = '/usr'
  sys.executable = '/data/greatsql-shell/bin/mysqlsh'
  sys.prefix = '/usr'
  sys.exec_prefix = '/usr'
  sys.path = [
    '/usr/lib64/python38.zip',
    '/usr/lib64/python3.8',
    '/usr/lib64/lib-dynload',
  ]
Fatal Python error: init_fs_encoding: failed to get the Python codec of the filesystem encoding
Python runtime state: core initialized
ModuleNotFoundError: No module named 'encodings'

Current thread 0x00007f2460d2e440 (most recent call first):
<no Python frame>
[root@GreatSQL01 bin]# 
复制

出现ModuleNotFoundError: No module named 'encodings’报错，安装python38解决

yum  install -y python38
复制

继续

[root@GreatSQL01 data]#  mysqlsh -S  /data/GreatSQL/data/mysql.sock root@localhost
Please provide the password for 'root@localhost': *************
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@localhost'
WARNING: Found errors loading plugins, for more details look at the log at: /root/.mysqlsh/mysqlsh.log
MySQL Error 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@GreatSQL01 data]# 
复制

查看报错日志/root/.mysqlsh/mysqlsh.log

2024-09-30 06:06:39: Error: Error loading Python file '/data/greatsql-shell/lib/mysqlsh/plugins/plugins_plugin/init.py':
        Execution failed: 
Traceback (most recent call last):
  File "init.py", line 29, in <module>
  File "/data/greatsql-shell/lib/mysqlsh/python-packages/mysqlsh/plugin_manager/__init__.py", line 24, in <module>
    from .general import VERSION
  File "/data/greatsql-shell/lib/mysqlsh/python-packages/mysqlsh/plugin_manager/general.py", line 22, in <module>
    import certifi
ModuleNotFoundError: No module named 'certifi'

2024-09-30 06:06:39: Warning: Found errors loading plugins, for more details look at the log at: /root/.mysqlsh/mysqlsh.log
2024-09-30 06:09:42: Info: Connecting to MySQL at: mysql://root@localhost
复制

出现ModuleNotFoundError: No module named 'certifi’报错，用pip安装certifi解决

[root@GreatSQL01 data]# python3.8 -m pip install certifi
WARNING: Running pip install with root privileges is generally not a good idea. Try `python3.8 -m pip install --user` instead.
Collecting certifi
  Downloading https://files.pythonhosted.org/packages/12/90/3c9ff0512038035f59d279fddeb79f5f1eccd8859f06d6163c58798b9487/certifi-2024.8.30-py3-none-any.whl (167kB)
     |████████████████████████████████| 174kB 65kB/s 
Installing collected packages: certifi
Successfully installed certifi-2024.8.30
[root@GreatSQL01 data]# 
复制

继续

2024-09-30 06:21:21: Info: Loading startup files...
2024-09-30 06:21:21: Info: Loading plugins...
2024-09-30 06:21:21: Error: Error loading Python file '/data/greatsql-shell/lib/mysqlsh/plugins/debug/init.py':
        Execution failed: 
Traceback (most recent call last):
  File "init.py", line 26, in <module>
  File "/data/greatsql-shell/lib/mysqlsh/plugins/debug/collect_diagnostics.py", line 28, in <module>
    import yaml
ModuleNotFoundError: No module named 'yaml'

2024-09-30 06:21:21: Warning: Found errors loading plugins, for more details look at the log at: /root/.mysqlsh/mysqlsh.log
2024-09-30 06:21:30: Info: Connecting to MySQL at: mysql://root@localhost
[root@GreatSQL01 data]# 
复制

出现ModuleNotFoundError: No module named 'yaml’报错，使用pip安装pyyaml解决

[root@GreatSQL01 data]# python3.8 -m pip install pyyaml
WARNING: Running pip install with root privileges is generally not a good idea. Try `python3.8 -m pip install --user` instead.
Collecting pyyaml
  Downloading https://files.pythonhosted.org/packages/fd/7f/2c3697bba5d4aa5cc2afe81826d73dfae5f049458e44732c7a0938baa673/PyYAML-6.0.2-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (746kB)
     |████████████████████████████████| 747kB 16kB/s 
Installing collected packages: pyyaml
Successfully installed pyyaml-6.0.2
[root@GreatSQL01 data]# 
复制

mysqlsh -S /data/GreatSQL/data/mysql.sock root@localhost登录老是报错MySQL Error 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)，使用mysqlsh -S /data/GreatSQL/data/mysql.sock -u root -p登录方式解决

[root@GreatSQL01 .mysqlsh]# mysqlsh -S /data/GreatSQL/data/mysql.sock -u root -p
Please provide the password for 'root@/data%2FGreatSQL%2Fdata%2Fmysql.sock': *************
Save password for 'root@/data%2FGreatSQL%2Fdata%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): yes

MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@/data%2FGreatSQL%2Fdata%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 18
Server version: 8.0.32-26 GreatSQL, Release 26, Revision a68b3034c3d
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS > 
复制

dba.configureInstance()初始化节点

执行 dba.configureInstance() 命令开始检查当前实例是否满足安装MGR集群的条件，如果不满足可以直接配置成为MGR集群的一个节点：

 MySQL  localhost  JS >  dba.configureInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 192.168.116.41:3306

ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel

Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: GreatSQL
Password for new account: *********
Confirm password: *********

applierWorkerThreads will be set to the default value of 4.

The instance '192.168.116.41:3306' is valid to be used in an InnoDB cluster.

Cluster admin user 'GreatSQL'@'%' created.
The instance '192.168.116.41:3306' is already ready to be used in an InnoDB cluster.

Successfully enabled parallel appliers.
 MySQL  localhost  JS > 
复制

同样的方法使用dba.configureInstance() 在192.168.116.42，192.168.116.43

用同样方法先用 root 账号分别登入到另外两个节点，完成节点的检查并创建最小权限级别用户（此过程略过。。。注意各节点上创建的用户名、密码都要一致）

参数 group_replication_communication_stack 的默认值是 XCOM。但是在利用 GreatSQL Shell 的 create_cluster() 函数创建并初始化 MGR 集群时，参数 communicationStack 默认值则是 MYSQL，这里存在差异。因此，建议在这里显式指定 communicationStack 参数值为 XCOM。

c = dba.createCluster('MGR1', {"communicationStack": "xcom"});
复制

因目前采用 MYSQL 协议可能存在风险，所以建议采用 XCOM 协议。

创建集群

完成检查并创建完新用户后，退出当前的管理员账户，并用新创建的MGR专用账户登入，准备初始化创建一个新集群：

[root@GreatSQL01 .mysqlsh]# mysqlsh --uri GreatSQL@192.168.116.41:3306
Please provide the password for 'GreatSQL@192.168.116.41:3306': *********
Save password for 'GreatSQL@192.168.116.41:3306'? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 '\?' for help; '\quit' to exit.
Creating a Classic session to 'GreatSQL@192.168.116.41:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 20
Server version: 8.0.32-26 GreatSQL, Release 26, Revision a68b3034c3d
No default schema selected; type \use <schema> to set one.
 MySQL  192.168.116.41:3306 ssl  JS > 
 MySQL  192.168.116.41:3306 ssl  JS > 
 MySQL  192.168.116.41:3306 ssl  JS > var c = dba.createCluster('MGR1', {"communicationStack": "xcom"});
A new InnoDB Cluster will be created on instance '192.168.116.41:3306'.

Validating instance configuration at 192.168.116.41:3306...

This instance reports its own address as 192.168.116.41:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '192.168.116.41:3306'. Use the localAddress option to override.

Creating InnoDB Cluster 'MGR1' on '192.168.116.41:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.


复制

执行 addInstance() 添加另外两个节点

MySQL  192.168.116.41:3306 ssl  JS > c.addInstance('GreatSQL@192.168.116.42:3306'); 

WARNING: A GTID set check of the MySQL instance at '192.168.116.42:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

192.168.116.42:3306 has the following errant GTIDs that do not exist in the cluster:
5fa2fefd-7e37-11ef-9e13-000c2927509d:1

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of 192.168.116.42:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

Please select a recovery method [C]lone/[A]bort (default Abort): Clone
Validating instance configuration at 192.168.116.42:3306...

This instance reports its own address as 192.168.116.42:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '192.168.116.42:3306'. Use the localAddress option to override.

A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: 192.168.116.42:3306 is being cloned from 192.168.116.41:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: 192.168.116.42:3306 is shutting down...

* Waiting for server restart... ready 
* 192.168.116.42:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 74.00 MB transferred in about 1 second (~74.00 MB/s)

Incremental state recovery is now in progress.

* Waiting for distributed recovery to finish...
NOTE: '192.168.116.42:3306' is being recovered from '<NULL>:0'
* Distributed recovery has finished

The instance '192.168.116.42:3306' was successfully added to the cluster.

 MySQL  192.168.116.41:3306 ssl  JS > c.addInstance('GreatSQL@192.168.116.43:3306');

WARNING: A GTID set check of the MySQL instance at '192.168.116.43:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

192.168.116.43:3306 has the following errant GTIDs that do not exist in the cluster:
6f8dd4a8-7e37-11ef-bf56-000c29807526:1

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of 192.168.116.43:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

Please select a recovery method [C]lone/[A]bort (default Abort): Clone
Validating instance configuration at 192.168.116.43:3306...

This instance reports its own address as 192.168.116.43:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '192.168.116.43:3306'. Use the localAddress option to override.

A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: 192.168.116.43:3306 is being cloned from 192.168.116.42:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: 192.168.116.43:3306 is shutting down...

* Waiting for server restart... ready 
* 192.168.116.43:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 74.00 MB transferred in about 1 second (~74.00 MB/s)

State recovery already finished for '192.168.116.43:3306'

The instance '192.168.116.43:3306' was successfully added to the cluster.

 MySQL  192.168.116.41:3306 ssl  JS > 
复制

查看集群状态

现在，一个有这三节点的MGR集群已经部署完毕，来确认下：

MySQL  192.168.116.41:3306 ssl  JS > c.describe()
{
    "clusterName": "MGR1", 
    "defaultReplicaSet": {
        "name": "default", 
        "topology": [
            {
                "address": "192.168.116.41:3306", 
                "label": "192.168.116.41:3306", 
                "role": "HA"
            }, 
            {
                "address": "192.168.116.42:3306", 
                "label": "192.168.116.42:3306", 
                "role": "HA"
            }, 
            {
                "address": "192.168.116.43:3306", 
                "label": "192.168.116.43:3306", 
                "role": "HA"
            }
        ], 
        "topologyMode": "Single-Primary"
    }
}
 MySQL  192.168.116.41:3306 ssl  JS > c.status()
{
    "clusterName": "MGR1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.116.41:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.116.41:3306": {
                "address": "192.168.116.41:3306", 
                "instanceErrors": [
                    "WARNING: Detected an unused recovery account: mysql_innodb_cluster_3306. Use Cluster.rescan() to clean up."
                ], 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "192.168.116.42:3306": {
                "address": "192.168.116.42:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "192.168.116.43:3306": {
                "address": "192.168.116.43:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "192.168.116.41:3306"
}
 MySQL  192.168.116.41:3306 ssl  JS > 
复制