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应用程序，连接路由节点，且任意宕掉其中一个或两个实例节点后，应用程序不受影响。