1.主机 规划列表、
| ip | hostname | 主机角色 | 安装软件 |
| 192.168.10.134 | db01 | master | mysql、myshell |
| 192.168.10.130 | db02 | slave | mysql 、myshell |
| 192.168.10.131 | db03 | slave | mysql、myshell |
| 192.168.10.132 | db04 | manager | myshell 、myrouter |
2.参数文件配置
这里db01、db02、db03、db04的参数文件配置可以用参照搭建MGR的参数文件配置
必须有的
server_id=1 ##这里每个mysql节点都要保持不一样 gtid_mode=ON enforce_gtid_consistency=ON binlog_checksum=NONE log_bin=binlog log_slave_updates=ON binlog_format=ROW master_info_repository=TABLE relay_log_info_repository=TABLE transaction_write_set_extraction=XXHASH64 loose-plugin_load_add='group_replication.so' loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # 这里的所有节点都要配置相同 loose-group_replication_start_on_boot=off loose-group_replication_local_address= "db01:33061" loose-group_replication_group_seeds= "db01:33061,db02:33061,db03:33061" loose-group_replication_bootstrap_group=off
default_authentication_plugin = mysql_native_password # 这里注意需要这个密码插件，不然会默认caching_sha2_password
3.创建用户
alter user 'root'@'localhost' identified by '123'; create user 'root'@'%' identified by '123'; grant all privileges on *.* to 'root'@'%' with grant option; flush privileges;
这里一个是修改root@'localhost'用户的密码，一个创建一个root@'%' 用户
4.所有节点的火墙和selinux
systemctl stop firewalld systemctl disabled firewalld # vi /etc/selinux/conf 将enfocing改成disabled # sestatus 查看
5.修改/etc/hosts文件(添加上)
192.168.10.134 db01 192.168.10.130 db02 192.168.10.131 db03 192.168.10.132 db04
6.启动mysql
/etc/init.d/mysqld start
7.每个节点用mysqlshell登录并进行检查
[root@db01 ~]# mysqlsh
MySQL Shell 8.0.25
Copyright (c) 2016, 2021, Oracle and/or its affiliates.
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'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 32
Server version: 8.0.25 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.checkInstanceConfiguration('root@localhost:3306')
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as db01: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.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
The instance 'db01:3306' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}
- shell.connect('root@localhost:3306') ---> 连接命令
- dba.checkInstanceConfiguration('root@localhost:3306') ---> 检查命令，该命令一般是检查mysql节点的配置参数(针对集群的参数)是否设置合理
8. dba.configureInstance()
该命令是针对上面的第7步，如果发现参数有设置不合理的或者设置错误，可以使用该命令进行修改，比如我这里修正其中一个节点
>dba.configureInstance('[email protected]:3306')
然后按照所给出的提示进行输入y，修改之后，Mysql会自动重启，然后重新输入dba.checkInstanceConfiguration('root@xxxx:3306')来检测，直到所有的状态都是
"status" : "ok"
9. 创建集群
创建集群之前，Mysql shell需要连接到一个主实例，也称为集群的种子实例，该实例会作为模版，用于该集群的其它实例的同步。
[root@db04 /]# mysqlsh
MySQL Shell 8.0.25
Copyright (c) 2016, 2021, Oracle and/or its affiliates.
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('[email protected]:3306')
Creating a session to '[email protected]:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 108
Server version: 8.0.25 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
<ClassicSession:[email protected]:3306>
MySQL 192.168.10.134:3306 ssl JS > var cluster = dba.createCluster('clusterTest')
这里是连接到192.168.10.134上面，然后，准备将其作为种子实例。第一次连接会提示输入密码并是否记住。连接成功，会显示connection id的值，然后再输入：
var cluster = dba.createCluster('custerTest') 来创建一个名为clusterTest的集群，等待集群创建完成。
集群创建完成可以通过命令：cluster.status() 命令查看集群的状态，此时会发现集群中只有一个节点就是192.168.10.134，该节点也是主节点
10. 添加另外实例到该集群中来
Mysql 192.168.10.134:3306 ssl JS > var cluster = dba.getCluster()
MySQL 192.168.10.134:3306 ssl JS > cluster.addInstance('root@db03:3306')
WARNING: A GTID set check of the MySQL instance at 'db03:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
db03:3306 has the following errant GTIDs that do not exist in the cluster:
c5d28887-db0b-11ed-8e2e-000c293f8c20:1-7
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of db03:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
Please select a recovery method [C]lone/[A]bort (default Abort): c
Validating instance configuration at db03:3306...
This instance reports its own address as db03:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db03:33061'. Use the localAddress option to override.
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.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: db03:3306 is being cloned from db02:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: db03:3306 is shutting down...
* Waiting for server restart... ready
* db03:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.20 MB transferred in about 1 second (~72.20 MB/s)
State recovery already finished for 'db03:3306'
db02节点也如上面添加一样。cluster.addInstance() 命令进行添加
上面提示选择一种恢复策略，用户决定实力如何恢复和集群同步所需的事务，提供了2种策略
- [C]lone：克隆，会完全复制种子实例，并且会删除本实例与种子实例的差异
- [I]ncremental recover：增量恢复，会讲种子实例种存在但本实例种不存在的数据同步到本实例，并且会保留本实例种存在但种子实例种不存在的数据。
- [A]bort：取消导入。
默认是克隆，可根据需求选择，这里选择第一项克隆，输入C
若从实例只参数集群，不做其他用途，则可以选克隆，若从实例除了集群还有其他作用，则需要选择增量。
11.查看集群状态
MySQL 192.168.10.134:3306 ssl JS > cluster.status()
{
"clusterName": "clusterTest",
"defaultReplicaSet": {
"name": "default",
"primary": "db01:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db01:3306": {
"address": "db01:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
},
"db02:3306": {
"address": "db02:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
},
"db03:3306": {
"address": "db03:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db01:3306"
}
上面字段解释：
- clusterName: 集群名称。
- defaultReplicaSet：集群设置。其下包含多个项及所有连接到此集群的实例设置。
-
primary：当前主实例的地址，仅在群集以单主模式运行时显示。若未显示，则群集将以多主模式运行。
-
ssl：群集是否使用安全连接。值为REQUIRED/DISABLED，默认为REQUIRED。可在createCluster()或addInstance()时设置memberSslMode从而更改该值。此参数返回的值对应于实例上的group_replication_ssl_mode服务器变量的值。
- status：集群状态。取值为：
- OK：所有节点都处于online状态，有冗余节点
- OK_PARTIAL：有节点不可用，但仍有冗余节点
- OK_NO_TOLERANCE：有足够的online节点，但没有冗余，例如：两个节点的cluster，其中一个挂了，集群就不可用了
- NO_QUORUM：有节点处于online状态，但达不到法定节点数，次状态下cluster无法写入，只能读取
- UNKNOWN：不是online或者recoverying状态，尝试连接其他实例查看状态
- UNavailable：组内节点全是offline状态，但实例在运行，可能实例重启还没有加入cluster
- topology：集群。其下每个实例的设置为：
- mode：模式。R/W为读写，R/O为只读
- role：角色。目前只有H/A
- status：实例状态。取值为：
- online：实例在线并参与集群
- offline：实例离线
- recovering：实例正在与集群同步，同步完成后将变成onLine
- unreachable：实例不可达，与集群无法通信
- Error：实例在回复阶段或应用事务时遇到错误，Error状态的实力气super_read_only将被设置为on
- Missing：已在元数据中注册但在实例集群中找不到实例
- version：实例的Mysql版本
- groupinformationSourceMember：集群的某些信息，默认显示集群创建实例的连接，可通过cluster.status({'extended'：value})传入额外的参数值来显示不同的信息，value的取值为：
- 0：默认值，禁用附加信息
- 1：包括有关组复制报告的组复制协议版本，组名称，集群成员UUID，集群成员角色和状态以及受防护系统变量列表的信息
- 2：包括有关连接和应用程序处理的事务的信息
-
12.配置Mysql Router(读写分离)
# 注册router到集群，生成myrouter目录，并生成启动程序和配置文件
mysqlrouter --bootstrap root@db01:3306 -d myrouter --user=root
[root@db04 ~]# mysqlrouter --bootstrap root@db01:3306 -d myrouter --user=root
Please enter MySQL password for root:
# Bootstrapping MySQL Router instance at '/root/myrouter'...
- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /root/myrouter/mysqlrouter.conf
# MySQL Router configured for the InnoDB Cluster 'clusterTest'
After this MySQL Router has been started with the generated configuration
$ mysqlrouter -c /root/myrouter/mysqlrouter.conf
the cluster 'clusterTest' 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:6448
- Read/Only Connections: localhost:6449
# 启动myrouter
在mysqlrouter目录下面会生成
[root@db04 ~]# cd myrouter/ [root@db04 myrouter]# ls data log mysqlrouter.conf mysqlrouter.key run start.sh stop.sh
脚本启动：./start.sh
# 验证连接router
a) 管理节点本机mysql-shell连接：
mysqlsh --uri root@locahost:6446
b) 管理节点本机mysql连接：
mysql -u root -h localhost -P 6446 -p # 连接读写节点
mysql -u root -h localhost -P 6447 -p # 连接只读节点
参考：https://blog.csdn.net/fyyyr/article/details/108019492
标签：16,cluster,Cluster,实例,InnoDB,MySQL,3306,集群,root From： https://www.cnblogs.com/zmc60/p/17344461.html