mysql 8.018 innodb cluster
集群节点
172.16.2.62
172.16.2.76
172.16.2.77
innodb cluster 集群断电重启
手动启动集群
1.首先启动3个节点的mysql数据用mysqld_safe方式启动
命令如下：
代码语言：javascript复制/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
如下：
代码语言：javascript复制[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
代码语言：javascript复制mysql> start group_replication;启动完毕后查询mgr集群状态（在任何一个节点都可以查询）
代码语言：javascript复制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
代码语言：javascript复制/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
代码语言：javascript复制[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()断电启动集群
代码语言：javascript复制[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 不要用集群方式登陆
代码语言：javascript复制[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 &
依次启动其他节点即可
启动完成后查询
代码语言：javascript复制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)代表启动完成
