innodb cluster是基于组复制来实现的。
搭建一套MySQL的高可用集群innodb。
实验环境：
| IP | 主机名 | 系统 | 软件 |
| 192.168.91.46 | master | RHEL7.4 | mysqlshell8.0.17，mysqlrouter8.0.17，mysql8.0.17 |
| 192.168.91.35 | node1 | RHEL7.4 | mysql8.0.17，mysqlshell8.0.17 |
| 192.168.91.36 | node2 | RHEL7.4 | mysql8.0.17，mysqlshell8.0.17 |
[root@master thunder]# rpm -ivh mysql-community-common-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-common-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing... 1:mysql-community-common-8.0.17-1.e################################# [100%]
[root@master thunder]# rpm -ivh mysql-community-libs-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-libs-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing... 1:mysql-community-libs-8.0.17-1.el7################################# [100%]
[root@master thunder]# rpm -ivh mysql-community-client-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-client-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing... 1:mysql-community-client-8.0.17-1.e################################# [100%]
[root@master thunder]# rpm -ivh mysql-community-server-8.0.17-1.el7.x86_64.rpm
warning: mysql-community-server-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing... 1:mysql-community-server-8.0.17-1.e################################# [100%]
[root@master thunder]# more /var/log/mysqld.log |grep password
2019-09-10T03:42:07.276423Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: *gYT4CrqCFTr
[root@master thunder]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 8.0.17
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> alter user 'root'@'localhost' identified by 'kavl7kAkkle!'; Query OK, 0 rows affected (0.01 sec)
组复制的部署：
master：
server_id = 100 #服务ID
gtid_mode = ON #全局事务
enforce_gtid_consistency = ON #强制GTID的一致性
log-slave-updates=on
master_info_repository = TABLE #将master.info元数据保存在系统表中
relay_log_info_repository = TABLE #将relay.info元数据保存在系统表中
binlog_checksum = NONE #禁用二进制日志事件校验
log_slave_updates = ON #级联复制
log_bin = binlog #开启二进制日志记录
binlog_format= ROW #以行的格式记录
transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名
loose-group_replication_start_on_boot = off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_local_address = 'master:33061' #以本机端口33061接受来自组中成员的传入连接 根据实际情况填写
loose-group_replication_group_seeds ='master:33061, node1:33062, node2:33063' #组中成员访问表 根据实际情况填写
loose-group_replication_bootstrap_group = off #不启用引导组
重启数据库：
[root@master ~]# systemctl restart mysqld
[root@node1 ~]# systemctl restart mysqld
[root@node2 ~]# systemctl restart mysqld
安装插件（三台都需要安装）：
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
-- 查看group replication组件
mysql> show plugins;
master操作：
mysql> set SQL_LOG_BIN=0; #停掉日志记录
mysql> create user repl@'192.168.91.%' identified with mysql_native_password by 'kavl7kAkkle!';
mysql> grant replication slave on *.* to repl@'192.168.91.%'
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1; #开启日志记录
mysql> change master to master_user='repl',master_password='kavl7kAkkle!' for channel 'group_replication_recovery'; #构建group replication集群
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
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 | e784156a-daa5-11e9-9184-000c29094ab4 | master | 3306 | ONLINE | PRIMARY | 8.0.17 |
+------------------------
node1和node2上操作：
mysql> set SQL_LOG_BIN=0; #停掉日志记录
mysql> create user repl@'192.168.91.%' identified with mysql_native_password by 'kavl7kAkkle!';
mysql> grant replication slave on *.* to repl@'192.168.91.%'
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1; #开启日志记录
mysql> change master to master_user='repl',master_password='kavl7kAkkle!' for channel 'group_replication_recovery'; #构建group replication集群
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected (0.01 sec)
mysql> reset master;
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.02 sec)
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 | e784156a-daa5-11e9-9184-000c29094ab4 | master | 3306 | ONLINE | PRIMARY | 8.0.17 |
| group_replication_applier | ed5719f8-daa5-11e9-b9f5-000c29824893 | node2 | 3306 | ONLINE | SECONDARY | 8.0.17 |
| group_replication_applier | f8cad554-dae8-11e9-84d3-000c29641ef8 | node1 | 3306 | ONLINE | SECONDARY | 8.0.17 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
至此组搭建完成，组中有三个成员。
集群部署：（部署之前停了各个节点的组复制）
以下是部署的步鄹
一，本地节点配置
二，创建集群
三，添加节点
四，MySQLrouter配置
五，测试
六，查看集群状态
七，故障模拟
一，本地节点配置（三个节点都需要配置）
master节点：
[root@master ~]# mysqlsh
MySQL Shell 8.0.17
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.
MySQL JS > shell.connect('root@localhost:3306');
Creating a session to 'root@localhost:3306'
Please provide the password for 'root@localhost:3306': ************
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 29
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
<ClassicSession:root@localhost:3306>
MySQL localhost:3306 ssl JS >
MySQL localhost:3306 ssl JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as master: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.
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: clusteradmin
Password for new account: ************
Confirm password: ************
The instance 'localhost:3306' is valid for InnoDB cluster usage.
The MySQL instance at 'localhost:3306' currently has the super_read_only system
variable set to protect it from inadvertent updates from applications. You must
first unset it to be able to perform any changes to this instance.
For more information see:
https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_super_read_only.
NOTE: There are open sessions to 'localhost:3306'.
You may want to kill these sessions to prevent them from performing unexpected updates:
1 open session(s) of 'root@localhost'.
Do you want to disable super_read_only and continue? [y/N]: y
Disabled super_read_only on the instance 'localhost:3306'
Cluster admin user 'clusteradmin'@'%' created.
Enabling super_read_only on the instance 'localhost:3306'
The instance 'localhost:3306' is already ready for InnoDB cluster usage.
node1节点：
[root@node1 ~]# mysqlsh
MySQL Shell 8.0.17
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.
MySQL JS > shell.connect('root@localhost:3306');
Creating a session to 'root@localhost:3306'
Please provide the password for 'root@localhost:3306': ************
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 26
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
<ClassicSession:root@localhost:3306>
MySQL localhost:3306 ssl JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as node1: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.
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: clusteradmin
Password for new account: ************
Confirm password: ************
The instance 'localhost:3306' is valid for InnoDB cluster usage.
The MySQL instance at 'localhost:3306' currently has the super_read_only system
variable set to protect it from inadvertent updates from applications. You must
first unset it to be able to perform any changes to this instance.
For more information see:
https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_super_read_only.
NOTE: There are open sessions to 'localhost:3306'.
You may want to kill these sessions to prevent them from performing unexpected updates:
1 open session(s) of 'root@localhost'.
Do you want to disable super_read_only and continue? [y/N]: y
Disabled super_read_only on the instance 'localhost:3306'
Cluster admin user 'clusteradmin'@'%' created.
Enabling super_read_only on the instance 'localhost:3306'
The instance 'localhost:3306' is already ready for InnoDB cluster usage.
node2节点：
[root@node2 ~]# mysqlsh
MySQL Shell 8.0.17
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.
MySQL JS > shell.connect('root@localhost:3306');
Creating a session to 'root@localhost:3306'
Please provide the password for 'root@localhost:3306': ************
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 28
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
<ClassicSession:root@localhost:3306>
MySQL localhost:3306 ssl JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as node2: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.
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: clusteradmin
Password for new account: ************
Confirm password: ************
The instance 'localhost:3306' is valid for InnoDB cluster usage.
The MySQL instance at 'localhost:3306' currently has the super_read_only system
variable set to protect it from inadvertent updates from applications. You must
first unset it to be able to perform any changes to this instance.
For more information see:
https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_super_read_only.
NOTE: There are open sessions to 'localhost:3306'.
You may want to kill these sessions to prevent them from performing unexpected updates:
1 open session(s) of 'root@localhost'.
Do you want to disable super_read_only and continue? [y/N]: y
Disabled super_read_only on the instance 'localhost:3306'
Cluster admin user 'clusteradmin'@'%' created.
Enabling super_read_only on the instance 'localhost:3306'
The instance 'localhost:3306' is already ready for InnoDB cluster usage.
二，创建集群
master：
[root@master ~]# mysqlsh MySQL Shell 8.0.17
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.
MySQL JS >shell.connect('clusteradmin@localhost:3306');
Creating a session to 'clusteradmin@localhost:3306'
Please provide the password for 'clusteradmin@localhost:3306': ************
Save password for 'clusteradmin@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 33
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
<ClassicSession:clusteradmin@localhost:3306>
MySQL localhost:3306 ssl JS >
MySQL localhost:3306 ssl JS > var cluster = dba.createCluster('myCluster');
A new InnoDB cluster will be created on instance 'localhost:3306'.
Disabling super_read_only mode on instance 'localhost:3306'.
Validating instance at localhost:3306...
This instance reports its own address as master:3306
Instance configuration is suitable.
Creating InnoDB cluster 'myCluster' on 'localhost: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.
MySQL localhost:3306 ssl JS >
三，添加节点（在master上创建的节点，需要把node1和node2节点添加到集群中）
MySQL localhost:3306 ssl JS > cluster.addInstance('clusteradmin@node1:3306');
Please provide the password for 'clusteradmin@node1:3306': ************
Save password for 'clusteradmin@node1:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
The safest and most convenient way to provision a new instance is through
automatic clone provisioning, which will completely overwrite the state of
'node1:3306' 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.
Validating instance at node1:3306...
This instance reports its own address as node1:3306
Instance configuration is suitable.
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.
Incremental distributed state recovery is now in progress.
* Waiting for distributed recovery to finish...
NOTE: 'node1:3306' is being recovered from 'master:3306'
* Distributed recovery has finished
The instance 'node1:3306' was successfully added to the cluster.
MySQL localhost:3306 ssl JS > cluster.addInstance('clusteradmin@node2:3306');
Please provide the password for 'clusteradmin@node2:3306': ************
Save password for 'clusteradmin@node2:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
The safest and most convenient way to provision a new instance is through
automatic clone provisioning, which will completely overwrite the state of
'node2:3306' 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.
Validating instance at node2:3306...
This instance reports its own address as node2:3306
Instance configuration is suitable.
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.
Incremental distributed state recovery is now in progress.
* Waiting for distributed recovery to finish...
NOTE: 'node2:3306' is being recovered from 'node1:3306'
* Distributed recovery has finished
The instance 'node2:3306' was successfully added to the cluster.
MySQL localhost:3306 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "master:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"master:3306": {
"address": "master:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node1:3306": {
"address": "node1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node2:3306": {
"address": "node2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "master:3306"
}
innodb cluster已经建立完成。
连接到数据库中查看组的状态:(组复制已经自动建立了)
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 | 019d2bbf-ea6f-11e9-bb30-000c29094ab4 | master | 3306 | ONLINE | PRIMARY | 8.0.17 |
| group_replication_applier | 6291b32c-eab0-11e9-b1a4-000c29641ef8 | node1 | 3306 | ONLINE | SECONDARY | 8.0.17 |
| group_replication_applier | 6386617e-eab0-11e9-9a4a-000c29824893 | node2 | 3306 | ONLINE | SECONDARY | 8.0.17 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
建立测试数据库:(在主上创建数据库)
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> create user 'yingyong'@'192.168.91.%' identified with mysql_native_password by 'kavl7kAkkle!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on test.* to 'yingyong'@'192.168.91.%';
Query OK, 0 rows affected (0.01 sec)
四，MySQLrouter配置
4.1 安装mysqlrouter
[root@master mysql_rpm]# rpm -ivh mysql-router-community-8.0.17-1.el7.x86_64.rpm
warning: mysql-router-community-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-router-community-8.0.17-1.e################################# [100%]
4.2 启动router：
[root@master mysql_rpm]# mysqlrouter &
[1] 7492
[root@master mysql_rpm]# logging facility initialized, switching logging to loggers specified in configuration
[root@master mysql_rpm]# ps -ef|grep mysqlrouter
root 7492 4249 0 17:14 pts/1 00:00:00 mysqlrouter
root 7498 4249 0 17:14 pts/1 00:00:00 grep --color=auto mysqlrouter
4.3 Router连接集群：
[root@master mysql_rpm]# sudo mysqlrouter --bootstrap clusteradmin@localhost --user=mysqlrouter
Please enter MySQL password for clusteradmin:
# Bootstrapping system MySQL Router instance...
- Checking for old Router accounts
- No prior Router accounts found
- Creating mysql account mysql_router1_fe5ofbd3r0kv@'%' for cluster management
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf
# MySQL Router configured for the InnoDB cluster 'myCluster'
After this MySQL Router has been started with the generated configuration
$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
the cluster 'myCluster' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470
Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
[root@master ~]# systemctl start mysqlrouter
[root@master ~]# ps -ef|grep mysqlrouter
root 7492 4249 0 Oct09 pts/1 00:00:00 mysqlrouter
mysqlro+ 8065 1 7 Oct09 ? 05:08:03 /usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
root 63786 4249 0 16:31 pts/1 00:00:00 grep --color=auto mysqlrouter
[root@master ~]# netstat -tlunp|grep mysqlrouter
tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 8065/mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 8065/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 8065/mysqlrouter
tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 8065/mysqlrouter
五，测试
[root@master ~]# mysqlsh --uri yingyong@192.168.91.46:6446
Please provide the password for 'yingyong@192.168.91.46:6446': ************
Save password for 'yingyong@192.168.91.46:6446'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.17
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 'yingyong@192.168.91.46:6446'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 105
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL 192.168.91.46:6446 ssl JS > sql
Switching to SQL mode... Commands end with ;
MySQL 192.168.91.46:6446 ssl SQL > select @@hostname;
+------------+
| @@hostname |
+------------+
| master |
+------------+
1 row in set (0.0001 sec)
MySQL 192.168.91.46:6446 ssl SQL > select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.0004 sec)
[root@master ~]# mysqlsh --uri yingyong@192.168.91.46:6447
Please provide the password for 'yingyong@192.168.91.46:6447': ************
Save password for 'yingyong@192.168.91.46:6447'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.17
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 'yingyong@192.168.91.46:6447'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 47
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL 192.168.91.46:6447 ssl JS > sql
Switching to SQL mode... Commands end with ;
MySQL 192.168.91.46:6447 ssl SQL > select @@hostname;
+------------+
| @@hostname |
+------------+
| node2 |
+------------+
1 row in set (0.0003 sec)
MySQL 192.168.91.46:6447 ssl SQL > select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.0005 sec)
六，查看集群状态
[root@master ~]# mysqlsh
MySQL Shell 8.0.17
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.
MySQL JS > shell.connect('clusteradmin@192.168.91.35:3306');
Creating a session to 'clusteradmin@192.168.91.35:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 62
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
<ClassicSession:clusteradmin@192.168.91.35:3306>
MySQL 192.168.91.35:3306 ssl JS > var cluster=dba.getCluster();
MySQL 192.168.91.35:3306 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "master:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"master:3306": {
"address": "master:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node1:3306": {
"address": "node1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node2:3306": {
"address": "node2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "master:3306"
}
MySQL 192.168.91.35:3306 ssl JS >
[root@master ~]# mysqlsh --uri clusteradmin@192.168.91.46:6446
Please provide the password for 'clusteradmin@192.168.91.46:6446': ************
Save password for 'clusteradmin@192.168.91.46:6446'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.17
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 'clusteradmin@192.168.91.46:6446'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 105150
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL 192.168.91.46:6446 ssl JS > var cluster=dba.getCluster();
MySQL 192.168.91.46:6446 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "master:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"master:3306": {
"address": "master:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node1:3306": {
"address": "node1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node2:3306": {
"address": "node2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "master:3306"
}
MySQL 192.168.91.46:6446 ssl JS >
七，故障模拟：
停掉节点：
[root@master ~]# systemctl stop mysqld
[root@master ~]# mysqlsh --uri clusteradmin@192.168.91.46:6446
MySQL Shell 8.0.17
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 'clusteradmin@192.168.91.46:6446'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 127
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL 192.168.91.46:6446 ssl JS > var cluster=dba.getCluster();
MySQL 192.168.91.46:6446 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "node1:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
"topology": {
"master:3306": {
"address": "master:3306",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"shellConnectError": "MySQL Error 2003 (HY000): Can't connect to MySQL server on 'master' (111)",
"status": "(MISSING)"
},
"node1:3306": {
"address": "node1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node2:3306": {
"address": "node2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "node1:3306"
}
MySQL 192.168.91.46:6446 ssl JS >
mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 6291b32c-eab0-11e9-b1a4-000c29641ef8 | node1 | 3306 | ONLINE | PRIMARY | 8.0.17 |
| group_replication_applier | 6386617e-eab0-11e9-9a4a-000c29824893 | node2 | 3306 | ONLINE | SECONDARY | 8.0.17 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
恢复节点：
[root@master ~]# systemctl start mysqld
[root@master ~]# mysqlsh --uri clusteradmin@192.168.91.46:6446
MySQL Shell 8.0.17
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 'clusteradmin@192.168.91.46:6446'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 699
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL 192.168.91.46:6446 ssl JS > var cluster=dba.getCluster();
MySQL 192.168.91.46:6446 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "node1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"master:3306": {
"address": "master:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node1:3306": {
"address": "node1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"node2:3306": {
"address": "node2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "node1:3306"
}
MySQL 192.168.91.46:6446 ssl JS >
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 | 019d2bbf-ea6f-11e9-bb30-000c29094ab4 | master | 3306 | ONLINE | SECONDARY | 8.0.17 |
| group_replication_applier | 6291b32c-eab0-11e9-b1a4-000c29641ef8 | node1 | 3306 | ONLINE | PRIMARY | 8.0.17 |
| group_replication_applier | 6386617e-eab0-11e9-9a4a-000c29824893 | node2 | 3306 | ONLINE | SECONDARY | 8.0.17 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)
完满完成，欢迎指教学习！！！！