mysql-shell部署MGR

一、下载安装mysql-shell

# 下载安装mysql-shell
cd /opt/
wget https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz
tar -zxvf mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/
cd /usr/local/
unlink /usr/bin/mysql-secret-store-login-path 2>/dev/null
unlink /usr/bin/mysqlsh 2>/dev/null
ln -s /usr/local/mysql-shell-8.0.26-linux-glibc2.12-x86-64bit mysql-shell
ln -s /usr/local/mysql-shell/bin/* /usr/bin/

二、提前准备账号

-- 提前准备好账号（三个节点都需要操作）
set sql_log_bin=0;
create user 'mgradmin'@'127.0.0.1' identified by '1SIrjg41xBfxe1yxuLVf';
create user 'mgradmin'@'192.168.29.131' identified by '1SIrjg41xBfxe1yxuLVf';
create user 'mgradmin'@'192.168.29.132' identified by '1SIrjg41xBfxe1yxuLVf'; 
create user 'mgradmin'@'192.168.29.133' identified by '1SIrjg41xBfxe1yxuLVf';
create user 'mgradmin'@'192.168.29.150' identified by '1SIrjg41xBfxe1yxuLVf'; 

grant all privileges on *.* to  'mgradmin'@'127.0.0.1' WITH GRANT OPTION;
grant all privileges on *.* to  'mgradmin'@'192.168.29.131' WITH GRANT OPTION;
grant all privileges on *.* to  'mgradmin'@'192.168.29.132' WITH GRANT OPTION;
grant all privileges on *.* to  'mgradmin'@'192.168.29.133' WITH GRANT OPTION;
grant all privileges on *.* to  'mgradmin'@'192.168.29.150' WITH GRANT OPTION;
set sql_log_bin=1;

登录测试

# 1SIrjg41xBfxe1yxuLVf
mysqlsh 'mgradmin'@'192.168.29.131':3306 --js --quiet-start=2 -C required
mysqlsh 'mgradmin'@'192.168.29.132':3306 --js --quiet-start=2 -C required
mysqlsh 'mgradmin'@'192.168.29.133':3306 --js --quiet-start=2 -C required
#mysqlsh 'mgradmin'@'192.168.29.150':3306 --js --quiet-start=2 -C required
mysqlsh 'mgradmin'@'127.0.0.1':3306     --js --quiet-start=2 -C required

具体示例

[root@mgr131 opt]# mysqlsh 'mgradmin'@'192.168.29.131':3306
Please provide the password for 'mgradmin@192.168.29.131:3306': ********************
Save password for 'mgradmin@192.168.29.131:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.26

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'mgradmin@192.168.29.131:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 13
Server version: 8.0.25-15 GreatSQL, Release 15, Revision c7feae175e0
No default schema selected; type \use <schema> to set one.
 MySQL  192.168.29.131:3306 ssl  JS > \q
Bye!

三、配置VIP

-- 配置 vip
# 添加vip
/sbin/ip addr add 192.168.29.150/255.255.255.0 dev ens33
# 删除vip
#/sbin/ip addr del 192.168.29.150/255.255.255.0 dev ens33

cat >>/etc/rc.local<<"EOF"
# 添加vip
#/sbin/ip addr add 192.168.29.150/255.255.255.0 dev ens33
# 删除vip
#/sbin/ip addr del 192.168.29.150/255.255.255.0 dev ens33
EOF

四、预检查

# 预检查
mysqlsh 'mgradmin'@'192.168.29.131':3306 --js --quiet-start=2 -C required
dba.checkInstanceConfiguration('mgradmin@192.168.29.131:3306')

# mysqlsh 'mgradmin'@'192.168.29.132':3306 --js --quiet-start=2 -C required
dba.checkInstanceConfiguration('mgradmin@192.168.29.132:3306')

# mysqlsh 'mgradmin'@'192.168.29.133':3306 --js --quiet-start=2 -C required
dba.checkInstanceConfiguration('mgradmin@192.168.29.133:3306')

4.1、预检详解结果-192.168.29.131

[root@mgr131 ~]# mysqlsh 'mgradmin'@'192.168.29.131':3306 --js --quiet-start=2 -C required
 MySQL  192.168.29.131:3306 ssl  JS > dba.checkInstanceConfiguration('mgradmin@192.168.29.131:3306')
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as mgr131:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

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

{
    "status": "ok"
}
 MySQL  192.168.29.131:3306 ssl  JS >

4.2、预检详解结果-192.168.29.132

MySQL  192.168.29.131:3306 ssl  JS > dba.checkInstanceConfiguration('mgradmin@192.168.29.132:3306')
Validating MySQL instance at mgr132:3306 for use in an InnoDB cluster...

This instance reports its own address as mgr132:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

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

{
    "status": "ok"
}
 MySQL  192.168.29.131:3306 ssl  JS >

4.3、预检详解结果-192.168.29.133

 MySQL  192.168.29.131:3306 ssl  JS > dba.checkInstanceConfiguration('mgradmin@192.168.29.133:3306')
Validating MySQL instance at mgr133:3306 for use in an InnoDB cluster...

This instance reports its own address as mgr133:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

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

{
    "status": "ok"
}
 MySQL  192.168.29.131:3306 ssl  JS >

5、再次确认结果

# 执行dba.configureInstance()后再次检查实例配置的输出如下：1SIrjg41xBfxe1yxuLVf
dba.configureInstance('mgradmin@192.168.29.131:3306')
dba.configureInstance('mgradmin@192.168.29.132:3306')
dba.configureInstance('mgradmin@192.168.29.133:3306')
dba.configureInstance('mgradmin@192.168.29.150:3306')

再次确认结果示例

MySQL 192.168.29.131:3306 ssl JS > dba.configureInstance('mgradmin@192.168.29.131:3306')
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as mgr131:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

The instance 'mgr131:3306' is valid to be used in an InnoDB cluster.
The instance 'mgr131:3306' is already ready to be used in an InnoDB cluster.

Successfully enabled parallel appliers.
MySQL 192.168.29.131:3306 ssl JS >

MySQL 192.168.29.131:3306 ssl JS > dba.configureInstance('mgradmin@192.168.29.132:3306')
Configuring MySQL instance at mgr132:3306 for use in an InnoDB cluster...

This instance reports its own address as mgr132:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

The instance 'mgr132:3306' is valid to be used in an InnoDB cluster.
The instance 'mgr132:3306' is already ready to be used in an InnoDB cluster.

Successfully enabled parallel appliers.
MySQL 192.168.29.131:3306 ssl JS >

MySQL 192.168.29.131:3306 ssl JS > dba.configureInstance('mgradmin@192.168.29.133:3306')
Configuring MySQL instance at mgr133:3306 for use in an InnoDB cluster...

This instance reports its own address as mgr133:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

The instance 'mgr133:3306' is valid to be used in an InnoDB cluster.
The instance 'mgr133:3306' is already ready to be used in an InnoDB cluster.

Successfully enabled parallel appliers.
MySQL 192.168.29.131:3306 ssl JS >

View Code

6、创建MGR集群

# 创建MGR集群：1SIrjg41xBfxe1yxuLVf
# 连接主节点(my.cnf中不要设置过滤同步)
mysqlsh 'mgradmin'@'192.168.29.131':3306 --js --quiet-start=2 -C required
dba.createCluster('clsmgr')
var cluster = dba.getCluster()

# 添加节点
cluster.addInstance('mgradmin@192.168.29.132:3306')
cluster.addInstance('mgradmin@192.168.29.133:3306')

创建示例

MySQL 192.168.29.131:3306 ssl JS > dba.createCluster('clsmgr')
A new InnoDB cluster will be created on instance '192.168.29.131:3306'.

Validating instance configuration at 192.168.29.131:3306...

This instance reports its own address as mgr131:3306

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

Creating InnoDB cluster 'clsmgr' on 'mgr131: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.

<Cluster:clsmgr>
MySQL 192.168.29.131:3306 ssl JS > var cluster = dba.getCluster()
MySQL 192.168.29.131:3306 ssl JS > cluster.addInstance('mgradmin@192.168.29.132:3306')

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

mgr132:3306 has the following errant GTIDs that do not exist in the cluster:
e4634df8-3675-11ec-b1ed-000c29f3d030:1

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of mgr132: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): C
Validating instance configuration at 192.168.29.132:3306...

This instance reports its own address as mgr132:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mgr132:33061'. 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: mgr132:3306 is being cloned from mgr131:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed

NOTE: mgr132:3306 is shutting down...

* Waiting for server restart... ready
* mgr132:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 1.13 GB transferred in 4 sec (283.60 MB/s)

State recovery already finished for 'mgr132:3306'

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

MySQL 192.168.29.131:3306 ssl JS >
MySQL 192.168.29.131:3306 ssl JS > cluster.addInstance('mgradmin@192.168.29.133:3306')

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

mgr133:3306 has the following errant GTIDs that do not exist in the cluster:
1b1c1c45-3676-11ec-b21b-000c29bdf16c:1

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of mgr133: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): C
Validating instance configuration at 192.168.29.133:3306...

This instance reports its own address as mgr133:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mgr133:33061'. 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.

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

NOTE: mgr133:3306 is shutting down...

* Waiting for server restart... ready
* mgr133:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 1.13 GB transferred in 4 sec (283.60 MB/s)

State recovery already finished for 'mgr133:3306'

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

MySQL 192.168.29.131:3306 ssl JS >

创建mgr集群细节

7、查看集群状态

 MySQL  192.168.29.131:3306 ssl  JS > cluster.status()
{
    "clusterName": "clsmgr", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mgr131:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mgr131:3306": {
                "address": "mgr131:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "mgr132:3306": {
                "address": "mgr132:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            },
            "mgr133:3306": {
                "address": "mgr133:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mgr131:3306"
}
 MySQL  192.168.29.131:3306 ssl  JS >

8、查看成员角色

root@mysqldb 05:15:  [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1b1c1c45-3676-11ec-b21b-000c29bdf16c | mgr133      |        3306 | ONLINE       | SECONDARY   | 8.0.25         |
| group_replication_applier | 4887f813-34ff-11ec-9a33-000c29d3a9e1 | mgr131      |        3306 | ONLINE       | PRIMARY     | 8.0.25         |
| group_replication_applier | e4634df8-3675-11ec-b1ed-000c29f3d030 | mgr132      |        3306 | ONLINE       | SECONDARY   | 8.0.25         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

root@mysqldb 05:15:  [(none)]>

9、传统方式切换一：单主-多主

mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT group_replication_switch_to_multi_primary_mode();
mysql> SELECT * FROM performance_schema.replication_group_members;

10、传统方式切换一：多主-单主

SELECT * FROM performance_schema.replication_group_members;
SELECT group_replication_switch_to_single_primary_mode();
SELECT * FROM performance_schema.replication_group_members;

11、mysqlsh切换方式二：单主-多主

mysqlsh 'mgradmin'@'192.168.29.131':3306 --js --quiet-start=2 -C required
var cluster = dba.getCluster()
cluster.status()
cluster.switchToMultiPrimaryMode()

[root@mgr131 ~]# mysqlsh 'mgradmin'@'192.168.29.131':3306 --js --quiet-start=2 -C required
 MySQL  192.168.29.131:3306 ssl  JS > var cluster = dba.getCluster()
 MySQL  192.168.29.131:3306 ssl  JS > cluster.status()
{
    "clusterName": "clsmgr", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mgr133:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mgr131:3306": {
                "address": "mgr131:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "mgr132:3306": {
                "address": "mgr132:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "mgr133:3306": {
                "address": "mgr133:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mgr133:3306"
}
 MySQL  192.168.29.131:3306 ssl  JS > cluster.switchToMultiPrimaryMode()
Switching cluster 'clsmgr' to Multi-Primary mode...

Instance 'mgr133:3306' remains PRIMARY.
Instance 'mgr131:3306' was switched from SECONDARY to PRIMARY.
Instance 'mgr132:3306' was switched from SECONDARY to PRIMARY.

The cluster successfully switched to Multi-Primary mode.
 MySQL  192.168.29.131:3306 ssl  JS >

12、mysqlsh切换方式二：多主-单主

mysqlsh 'mgradmin'@'192.168.29.131':3306 --js --quiet-start=2 -C required
var cluster = dba.getCluster()
cluster.status()
cluster.switchToSinglePrimaryMode();

[root@mgr131 ~]# mysqlsh 'mgradmin'@'192.168.29.131':3306 --js --quiet-start=2 -C required
 MySQL  192.168.29.131:3306 ssl  JS > var cluster = dba.getCluster()
cluster.status() MySQL  192.168.29.131cluster.status()
{
    "clusterName": "clsmgr", 
    "defaultReplicaSet": {
        "name": "default", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mgr131:3306": {
                "address": "mgr131:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "mgr132:3306": {
                "address": "mgr132:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "mgr133:3306": {
                "address": "mgr133:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }
        }, 
        "topologyMode": "Multi-Primary"
    }, 
    "groupInformationSourceMember": "mgr131:3306"
}
 MySQL  192.168.29.131:3306 ssl  JS > cluster.switchToSinglePrimaryMode();
Switching cluster 'clsmgr' to Single-Primary mode...

Instance 'mgr133:3306' remains PRIMARY.
Instance 'mgr131:3306' was switched from PRIMARY to SECONDARY.
Instance 'mgr132:3306' was switched from PRIMARY to SECONDARY.

WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster().

WARNING: Existing connections that expected a R/W connection must be disconnected, i.e. instances that became SECONDARY.

The cluster successfully switched to Single-Primary mode.
 MySQL  192.168.29.131:3306 ssl  JS >

13、新增成员方法

# 新增成员，授权后
mysqlsh 'mgradmin'@'192.168.29.134':3306 --js --quiet-start=2 -C required
var cluster = dba.getCluster()
dba.checkInstanceConfiguration('mgradmin@192.168.29.134:3306')
dba.configureInstance('mgradmin@192.168.29.134:3306')
cluster.addInstance('mgradmin@192.168.29.134:3306')
cluster.status()

14、使用systemd管理mysql的启动停止等（由于实验环境发生变化，这里以GreatSQL为例）

# 使用systemd管理mysql启动停止等
cat <<"EOF" > /usr/lib/systemd/system/greatsql.service
[Unit]
Description=GreatSQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=notify
TimeoutSec=0
PermissionsStartOnly=true
#ExecStartPre=/usr/local/GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64/bin/mysqld_pre_systemd
ExecStart=/usr/local/GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64/bin/mysqld $MYSQLD_OPTS
EnvironmentFile=/etc/sysconfig/mysql
LimitNOFILE = 10000
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
EOF


cat <<"EOF" >/etc/sysconfig/mysql 
LD_PRELOAD=/usr/lib64/libjemalloc.so.1
EOF

cat <<"EOF" > /etc/rc.local
systemctl start greatsql
EOF

# 将 服务正式加载到 systemd 列表中
systemctl daemon-reload

# 启动服务
systemctl start greatsql

# 或者带上
systemctl start greatsql.service

#介绍systemctl主要几个的命令：

# 重载系统服务
systemctl daemon-reload
# 启用开机启动
systemctl enable greatsql
# 禁用开机启动
systemctl disable greatsql
# 启动服务
systemctl start greatsql
# 停止服务
systemctl stop greatsql
# 重启服务
systemctl restart greatsql
#查看服务状态
systemctl status greatsql

15、集群节点重启后，如何恢复

15.1、手工启动

-- 首先，在各个节点执行下面的SQL，确认各节点当前的事务执行情况：
-- 节点mgr131
root@mysqldb 17:49:  [(none)]> select RECEIVED_TRANSACTION_SET from performance_schema.replication_connection_status where  channel_name = 'group_replication_applier' union all  select variable_value from performance_schema.global_variables where  variable_name = 'gtid_executed'\G;
*************************** 1. row ***************************
RECEIVED_TRANSACTION_SET: 
*************************** 2. row ***************************
RECEIVED_TRANSACTION_SET: d5d60ed9-7165-11ec-8feb-000c29f3d030:1-50,
e4634df8-3675-11ec-b1ed-000c29f3d030:1
2 rows in set (0.00 sec)

ERROR: 
No query specified

root@mysqldb 17:49:  [(none)]> 

-- 节点mgr132
*************************** 1. row ***************************
RECEIVED_TRANSACTION_SET: 
*************************** 2. row ***************************
RECEIVED_TRANSACTION_SET: d5d60ed9-7165-11ec-8feb-000c29f3d030:1-50,
e4634df8-3675-11ec-b1ed-000c29f3d030:1
2 rows in set (0.01 sec)


-- 节点mgr133
root@mysqldb 17:52:  [(none)]> select RECEIVED_TRANSACTION_SET from performance_schema.replication_connection_status where  channel_name = 'group_replication_applier' union all  select variable_value from performance_schema.global_variables where  variable_name = 'gtid_executed'\G
*************************** 1. row ***************************
RECEIVED_TRANSACTION_SET: 
*************************** 2. row ***************************
RECEIVED_TRANSACTION_SET: d5d60ed9-7165-11ec-8feb-000c29f3d030:1-50,
e4634df8-3675-11ec-b1ed-000c29f3d030:1
2 rows in set (0.01 sec)

root@mysqldb 17:52:  [(none)]> 

-- 这里是一致的，如果不一致，就找最大的。例如：d5d60ed9-7165-11ec-8feb-000c29f3d030:1-51
-- 那就在这个:1-51所在的节点上，将该节点设置为引导模式，然后启动MGR服务：

root@mysqldb 17:56:  [(none)]> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

root@mysqldb 17:56:  [(none)]> start group_replication;
Query OK, 0 rows affected (2.19 sec)

root@mysqldb 17:57:  [(none)]> -- 启动完MGR后，记得立即将其设置为OFF
root@mysqldb 17:57:  [(none)]> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

root@mysqldb 17:57:  [(none)]> 

-- 在其他节点上，则直接启动MGR服务即可，切记无需再次设置引导模式，否则它就会变成一个全新的MGR集群的Primary节点了。

15.2、利用mysqlsh启动集群

首先利用systemd方式自启动mysql服务，注意如下配置。

[root@mgr131 ~]# cat /usr/lib/systemd/system/greatsql.service
[Unit]
Description=GreatSQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=notify
TimeoutSec=0
PermissionsStartOnly=true
#ExecStartPre=/usr/local/GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64/bin/mysqld_pre_systemd
ExecStart=/usr/local/GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64/bin/mysqld $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 10000
Restart=on-failure # 注意这里
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
[root@mgr131 ~]# cat /usr/lib/systemd/system/greatsql.service

[root@mgr133 ~]# mysqlsh 'mgradmin'@'192.168.29.133':3306 --js --quiet-start=2 -C required
 MySQL  192.168.29.133:3306 ssl  JS > dba.
checkInstanceConfiguration()       deploySandboxInstance()            session
configureInstance()                dropMetadataSchema()               startSandboxInstance()
configureLocalInstance()           getCluster()                       stopSandboxInstance()
configureReplicaSetInstance()      getReplicaSet()                    upgradeMetadata()
createCluster()                    help()                             verbose
createReplicaSet()                 killSandboxInstance()
deleteSandboxInstance()            rebootClusterFromCompleteOutage()
 MySQL  192.168.29.133:3306 ssl  JS > dba.rebootClusterFromCompleteOutage()
Restoring the default cluster from complete outage...

The instance 'mgr132:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The instance 'mgr131:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

mgr133:3306 was restored.
Rejoining 'mgr132:3306' to the cluster.
Rejoining instance 'mgr132:3306' to cluster 'clsmgr'...
The instance 'mgr132:3306' was successfully rejoined to the cluster.

Rejoining 'mgr131:3306' to the cluster.
Rejoining instance 'mgr131:3306' to cluster 'clsmgr'...
The instance 'mgr131:3306' was successfully rejoined to the cluster.

The cluster was successfully rebooted.

<Cluster:clsmgr>

　　查看状态。

 MySQL  192.168.29.133:3306 ssl  JS > var cluster = dba.get
getCluster()     getReplicaSet()
 MySQL  192.168.29.133:3306 ssl  JS > var cluster = dba.getCluster()
 MySQL  192.168.29.133:3306 ssl  JS > cluster.status()
{
    "clusterName": "clsmgr", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mgr133:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mgr131:3306": {
                "address": "mgr131:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "mgr132:3306": {
                "address": "mgr132:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "mgr133:3306": {
                "address": "mgr133:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mgr133:3306"
}
 MySQL  192.168.29.133:3306 ssl  JS >

可以看到，已经成功启动完毕！

posted @ 2021-10-29 15:06 davie2020 阅读(192) 评论(0) 编辑收藏举报

刷新页面返回顶部

davie2020

mysql-shell部署MGR

mysql-shell部署MGR

一、下载安装mysql-shell

二、提前准备账号

三、配置VIP

四、预检查

4.1、预检详解结果-192.168.29.131

4.2、预检详解结果-192.168.29.132

4.3、预检详解结果-192.168.29.133

5、再次确认结果

6、创建MGR集群

7、查看集群状态

8、查看成员角色

9、传统方式切换一：单主-多主

10、传统方式切换一：多主-单主

11、mysqlsh切换方式二：单主-多主

12、mysqlsh切换方式二：多主-单主

13、新增成员方法

14、使用systemd管理mysql的启动停止等（由于实验环境发生变化，这里以GreatSQL为例）

15、集群节点重启后，如何恢复

15.1、手工启动

15.2、利用mysqlsh启动集群

公告