mysql innodb cluster 集群断电重启

原创

wangwei-dba

修改于 2021-02-09 08:42:07

3.5K00

代码可运行

文章被收录于专栏：mysql-dbamysql-dba

运行总次数：0

代码可运行

mysql 8.018 innodb cluster

集群节点

172.16.2.62

172.16.2.76

172.16.2.77

innodb cluster 集群断电重启

手动启动集群

1.首先启动3个节点的mysql数据用mysqld_safe方式启动

命令如下：

/usr/local/mysql8.0.18/bin/mysqld_safe --defaults-file=/data/inndbcluster/mysql3310/my3310.cnf --user=mysql &

启动之后登录mysql一个节点的数据库(GTID最大的节点，也就是最新的节点)

/usr/local/mysql8.0.18/bin/mysql -S /tmp/mysql3310.sock -uroot -p

如下：

[root@java-db03 ~]# /usr/local/mysql8.0.18/bin/mysql -S /tmp/mysql3310.sock -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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.
启动第一个节点要开启参数group_replication_bootstrap_group
mysql>SET GLOBAL group_replication_bootstrap_group=ON;
mysql> start group_replication;
启动group_replication后然后关闭参数group_replication_bootstrap_group
mysql>SET GLOBAL group_replication_bootstrap_group=Off;

2.然后登入其他节点启动其他2个节点的group_replication

启动group_replication

mysql> start group_replication;

启动完毕后查询mgr集群状态（在任何一个节点都可以查询）

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 250a4342-26f3-11ea-9f36-005056a71582 | java-db03   |        3310 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | 2732c0d1-26f5-11ea-940a-005056a766be | java-db02   |        3310 | ONLINE       | SECONDARY   | 8.0.18         |
| group_replication_applier | 3bf45f79-26f5-11ea-9429-005056a75a27 | java-db     |        3310 | ONLINE       | SECONDARY   | 8.0.18         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

节点MEMBER_STATE为online代表正常，为其他状态需要看error排查或者联系dba处理

3.启动router

目前测试的router部署在172.16.2.62

/usr/local/mysqlrouter/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter.conf &
测试router链接 
[root@java-db ~]# /usr/local/mysql8.0.18/bin/mysql -h172.16.2.62 -P3306 -uroot -p'Guiji.admin.2019'
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 138893
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>

能连上代表正常

4.使用mysqlshell查看集群状态

mysqlshell安装在172.16.2.77

[root@java-db03 ~]# /usr/local/mysqlshell/bin/mysqlsh dbauser@172.16.2.77:3310
Please provide the password for 'dbauser@172.16.2.77:3310': **********
MySQL Shell 8.0.18
Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
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 'dbauser@172.16.2.77:3310'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 139579
Server version: 8.0.18 MySQL Community Server - GPL
No default schema selected; type \use  to set one.
 MySQL  172.16.2.77:3310 ssl  JS > 
 MySQL  172.16.2.77:3310 ssl  JS > var ww=dba.getCluster('wwcluster');
 MySQL  172.16.2.77:3310 ssl  JS > ww.status();
{
    "clusterName": "wwcluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "java-db03:3310", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "java-db02:3310": {
                "address": "java-db02:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.18"
            }, 
            "java-db03:3310": {
                "address": "java-db03:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.18"
            }, 
            "java-db:3310": {
                "address": "java-db:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.18"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "java-db03:3310"
}
 MySQL  172.16.2.77:3310 ssl  JS >

Cluster is ONLINE and can tolerate up to ONE failure 代表正常并且节点status都为online

使用dba.rebootClusterFromCompleteOutage()断电启动集群

[root@java-db03 local]#  /usr/local/mysqlshell/bin/mysqlsh dbauser@172.16.2.38:3310
Please provide the password for 'dbauser@172.16.2.38:3310': **********
MySQL Shell 8.0.18

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
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 'dbauser@172.16.2.38:3310'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 27
Server version: 8.0.18 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  172.16.2.38:3310 ssl  JS > 
 MySQL  172.16.2.38:3310 ssl  JS > 
 MySQL  172.16.2.38:3310 ssl  JS > 
 MySQL  172.16.2.38:3310 ssl  JS > 
 MySQL  172.16.2.38:3310 ssl  JS > 
 MySQL  172.16.2.38:3310 ssl  JS > var ww=dba.getCluster();
Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (RuntimeError)
 MySQL  172.16.2.38:3310 ssl  JS > 
 MySQL  172.16.2.38:3310 ssl  JS > 
 MySQL  172.16.2.38:3310 ssl  JS > var cluster = dba.rebootClusterFromCompleteOutage();
Reconfiguring the default cluster from complete outage...

The instance 'java-db02:3310' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The instance 'java-db01:3310' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The safest and most convenient way to provision a new instance is through
automatic clone provisioning, which will completely overwrite the state of
'172.16.2.38:3310' with a physical snapshot from an existing cluster member. To
use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental distributed state recovery may be safely used if you are sure
all updates ever executed in the cluster were done with GTIDs enabled, there
are no purged transactions and the new instance contains the same GTID set as
the cluster or a subset of it. To use this method by default, set the
'recoveryMethod' option to 'incremental'.

Incremental distributed state recovery was selected because it seems to be safely usable.

The cluster was successfully rebooted.

 MySQL  172.16.2.38:3310 ssl  JS >

集群正常重启：

可以采用滚动方式重启，一个节点一个节点的重启（和启动mysql单机一样）

停止mysql shutdown (不要kill -9)

单个节点登录mysql 不要用集群方式登陆

[root@java-db03 ~]# /usr/local/mysql8.0.18/bin/mysql -S /tmp/mysql3310.sock -uroot -p
mysql> shutdown;

然后启动节点：

/usr/local/mysql8.0.18/bin/mysqld_safe --defaults-file=/data/inndbcluster/mysql3310/my3310.cnf --user=mysql &

依次启动其他节点即可

启动完成后查询

mysql> 
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 250a4342-26f3-11ea-9f36-005056a71582 | java-db03   |        3310 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | 2732c0d1-26f5-11ea-940a-005056a766be | java-db02   |        3310 | ONLINE       | SECONDARY   | 8.0.18         |
| group_replication_applier | 3bf45f79-26f5-11ea-9429-005056a75a27 | java-db     |        3310 | ONLINE       | SECONDARY   | 8.0.18         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)