搭建MySQL（8.0版本）集群（MySQL-shell、MySQL-router）

　　MySQL搭建集群有多种方式，现在单独说说MGR方式的单主模式（一个主节点），MGR全称MySQL Group Replication（Mysql组复制）

一、环境准备

　　服务器：192.168.7.121、192.168.7.131、192.168.7.141，在三台服务器上分别安装MySQL及MySQL-shell

　　安装MySQL：https://www.cnblogs.com/javasl/p/14650316.html

　　安装MySQL-shell：https://www.cnblogs.com/javasl/p/14652345.html

二、MySQL配置

　　121服务器配置

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1 
gtid_mode=on
enforce_gtid_consistency=on
binlog_checksum=none
transaction_write_set_extraction = XXHASH64
loose-group_replication_recovery_use_ssl= ON
loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = '192.168.7.121:24901'
loose-group_replication_group_seeds = '192.168.7.121:24901,192.168.7.131:24902,192.168.7.141:24903'
loose-group_replication_bootstrap_group = OFF

　　131服务器配置

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
gtid_mode=on
enforce_gtid_consistency=on
binlog_checksum=none
log_slave_updates = ON
loose-group_replication_recovery_get_public_key= ON
loose-group_replication_recovery_use_ssl= ON
loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = '192.168.7.131:24902'
loose-group_replication_group_seeds = '192.168.7.121:24901,192.168.7.131:24902,192.168.7.141:24903'
loose-group_replication_bootstrap_group = OFF

　　141服务器配置

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=3
gtid_mode=on
enforce_gtid_consistency=on
binlog_checksum=none
log_slave_updates = ON
loose-group_replication_recovery_get_public_key= ON
loose-group_replication_recovery_use_ssl= ON
loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = '192.168.7.141:24903'
loose-group_replication_group_seeds = '192.168.7.121:24901,192.168.7.131:24902,192.168.7.141:24903'
loose-group_replication_bootstrap_group = OFF

三、服务器配置

　　1）映射3台服务器的主机名（每台服务器都如下配置）。文件路径：/etc/hosts

192.168.7.121 mysql121
192.168.7.131 mysql131
192.168.7.141 mysql141

　　2）关闭3台服务器的SELINUX（暂时简单粗暴的处理)。文件路径：/etc/selinux/config

SELINUX=disabled

　　3）关闭3台服务器的防火墙（暂时简单粗暴的处理）

systemctl disabled firewalld

　　4）设置3台服务器之间免秘钥登录。在121服务器上执行

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.7.131
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.7.141

　　服务器配置完重启一下。

四、创建集群

　　1、准备工作

　　1）每台机器的mysqlsh都连接一下3台机器的MySQL（三台服务器上都执行如下操作），以121举例。

[root@localhost ~]# mysqlsh
MySQL Shell 8.0.20

Copyright (c) 2016, 2020, 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 > \c root@mysql121:3306
Creating a session to 'root@mysql121:3306'
Please provide the password for 'root@mysql121:3306': **********
Save password for 'root@mysql121: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 37
Server version: 8.0.20 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  mysql121:3306 ssl  JS > \c root@mysql131:3306
Creating a session to 'root@mysql131:3306'
Please provide the password for 'root@mysql131:3306': **********
Save password for 'root@mysql131: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 12
Server version: 8.0.20 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  mysql131:3306 ssl  JS > \c root@mysql141:3306
Creating a session to 'root@mysql141:3306'
Please provide the password for 'root@mysql141:3306': **********
Save password for 'root@mysql141: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 12
Server version: 8.0.20 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  mysql141:3306 ssl  JS > 

　　2）3台服务器都执行如下操作，以121服务器举例。

MySQL  mysql121:3306 ssl  JS > dba.configureInstance();

MySQL  mysql121:3306 ssl  JS > dba.checkInstanceConfiguration("root@mysql121:3306");

　　2、创建集群

 　　1）安装组复制插件，3台服务器器都执行如下操作

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

　　2）创建集群（只在一台服务器执行即可）

MySQL  mysql141:3306 ssl  JS > var cluster = dba.createCluster('myCluster');

　　

　　备注：只在主节点执行，怎么判断哪台是主节点呢？先尝试执行，如果是只读的话，则换一台执行，或者在谁执行，谁是主节点？

　　3）添加121节点

cluster.addInstance('root@mysql121:3306');

　　

　　4）添加131节点，同理，如上，地址改为mysql131

　　5）查看集群状态

MySQL  mysql141:3306 ssl  JS > cluster.status()

 MySQL  mysql141:3306 ssl  JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql141:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql121:3306": {
                "address": "mysql121:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.20"
            }, 
            "mysql131:3306": {
                "address": "mysql131:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.20"
            }, 
            "mysql141:3306": {
                "address": "mysql141:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.20"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql141:3306"
}

　　6）查询节点信息（随便登录一个节点）

mysql> select * from performance_schema.replication_group_members;

　　

五、安装MySQL-router

　　1、下载：https://downloads.mysql.com/archives/router/

　　2、安装：rpm -ivh mysql-router-community-8.0.20-1.el7.x86_64.rpm

　　3、配置：vim /etc/mysqlrouter/mysqlrouter.conf。添加如下配置：

[routing:read_writer]
bind_address = 192.168.7.121
bind_port = 7001
mode = read-write
destinations = mysql121:3306,mysql131:3306,mysql141:3306
max_connections = 1024
protocol = classic

[routing:read_only]
bind_address = 192.168.7.121
bind_port = 7002
mode = read-only
destinations = mysql121:3306,mysql131:3306,mysql141:3306
max_connections = 1024
protocol = classic

　　4、启动：mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

六、测试集群

　　1、宕掉主节点A后，会从另外两个中选择出一个作为主节点，且A恢复后，不会成为主节点。

　　2、使用客户端工具Navicat可以连接路由节点，IP：192.168.7.121，端口7001，用户名密码与实例节点一致。

　　3、JavaWeb应用程序，连接路由节点，且任意宕掉其中一个或两个实例节点后，应用程序不受影响。