mysql8.0.26-cluster 单主与多主搭建
2022/3/1 19:25:37
本文主要是介绍mysql8.0.26-cluster 单主与多主搭建,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
先配置三台服务器
系统版本 cat /etc/redhat-release Rocky Linux release 8.5 (Green Obsidian) mysql版本 8.0.26 [root@zabbix-master1 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.100.6 zabbix-master1 192.168.100.11 zabbix-master2 192.168.100.14 zabbix-master3 #下载 mysql-shell wget https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.28-1.el8.x86_64.rpm [root@zabbix-master1 ~]# mysqlsh MySQL Shell 8.0.28 Copyright (c) 2016, 2022, 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. #我们依次输入cluster所有节点的地址 \c root@域名也可:Port MySQL JS > \c root@192.168.100.6:3306 Creating a session to 'root@192.168.100.6:3306' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 84 Please provide the password for 'root@192.168.100.6:3006': ************ Server version: 8.0.26 Source distribution No default schema selected; type \use <schema> to set one. MySQL 192.168.100.6:3306 ssl JS > #三台机器都连接过数据库后,再连接各自MySQL执行下面操作 MySQL 192.168.100.6:3306 ssl JS > dba.configureInstance(); Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster... This instance reports its own address as zabbix-master1: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. applierWorkerThreads will be set to the default value of 4. NOTE: Some configuration options need to be fixed: +----------------------------------------+---------------+----------------+----------------------------+ | Variable | Current Value | Required Value | Note | +----------------------------------------+---------------+----------------+----------------------------+ | binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable | | replica_parallel_type | DATABASE | LOGICAL_CLOCK | Update the server variable | | replica_preserve_commit_order | OFF | ON | Update the server variable | +----------------------------------------+---------------+----------------+----------------------------+ Do you want to perform the required configuration changes? [y/n]: y Configuring instance... The instance 'zabbix-master1:3306' was configured to be used in an InnoDB cluster. #检查节点配置实例状态是否为健康,用于加入cluster之前 MySQL 192.168.100.6:3306 ssl JS > dba.checkInstanceConfiguration("root@192.168.100.6:3306"); Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster... This instance reports its own address as zabbix-master1: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 'zabbix-master1:3306' is valid to be used in an InnoDB cluster. { "status": "ok" } #创建集群名称叫myCluster MySQL 192.168.100.6:3306 ssl JS > var cluster=dba.createCluster("myCluster"); A new InnoDB cluster will be created on instance '192.168.100.6:3306'. Validating instance configuration at 192.168.100.6:3306... This instance reports its own address as zabbix-master1:3306 Instance configuration is suitable. NOTE: Group Replication will communicate with other members using 'zabbix-master1:33061'. Use the localAddress option to override. Creating InnoDB cluster 'myCluster' on 'zabbix-master1: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 192.168.100.6:3306 ssl JS > cluster.addInstance("root@192.168.100.11:3306") WARNING: A GTID set check of the MySQL instance at 'zabbix-master2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster. zabbix-master2:3306 has the following errant GTIDs that do not exist in the cluster: ccb17a91-983c-11ec-a058-000c299ab833:1-5 WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of zabbix-master2: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 192.168.100.11:3306... This instance reports its own address as zabbix-master2:3306 Instance configuration is suitable. NOTE: Group Replication will communicate with other members using 'zabbix-master2: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: zabbix-master2:3306 is being cloned from zabbix-master1:3306 ** Stage DROP DATA: Completed ** Clone Transfer FILE COPY ============================================================ 0% In Progress PAGE COPY ============================================================ 0% Not Started REDO COPY ============================================================ 0% Not Started NOTE: zabbix-master2:3306 is shutting down... * Waiting for server restart... ready * zabbix-master2:3306 has restarted, waiting for clone to finish... ** Stage RESTART: Completed * Clone process has finished: 72.71 MB transferred in about 1 second (~72.71 MB/s) State recovery already finished for 'zabbix-master2:3306' The instance 'zabbix-master2:3306' was successfully added to the cluster. # 往集群里面添加节点 MySQL 192.168.100.6:3306 ssl JS > cluster.addInstance("root@192.168.100.14:3306") WARNING: A GTID set check of the MySQL instance at 'zabbix-master3:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster. zabbix-master3:3306 has the following errant GTIDs that do not exist in the cluster: 02c8d10a-983f-11ec-a9f2-000c2929b6f0:1-4 WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of zabbix-master3: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 192.168.100.14:3306... This instance reports its own address as zabbix-master3:3306 Instance configuration is suitable. NOTE: Group Replication will communicate with other members using 'zabbix-master3: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: zabbix-master3:3306 is being cloned from zabbix-master1:3306 ** Stage DROP DATA: Completed ** Clone Transfer FILE COPY ############################################################ 100% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed NOTE: zabbix-master3:3306 is shutting down... * Waiting for server restart... ready * zabbix-master3:3306 has restarted, waiting for clone to finish... ** Stage RESTART: Completed * Clone process has finished: 72.61 MB transferred in about 1 second (~72.61 MB/s) State recovery already finished for 'zabbix-master3:3306' The instance 'zabbix-master3:3306' was successfully added to the cluster. MySQL 192.168.100.6:3306 ssl JS > cluster.status(); { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "zabbix-master1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "zabbix-master1:3306": { "address": "zabbix-master1:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.26" }, "zabbix-master2:3306": { "address": "zabbix-master2:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.26" }, "zabbix-master3:3306": { "address": "zabbix-master3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.26" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "zabbix-master1:3306" } #进入mysql 查询集群状态 mysql> select * from performance_schema.replication_group_members;
dba.checkInstanceConfiguration("root@hostname:3306") #检查节点配置实例,用于加入cluster之前 dba.rebootClusterFromCompleteOutage('myCluster'); #重启 dba.dropMetadataSchema(); #删除schema var cluster = dba.getCluster('myCluster') #获取当前集群 cluster.checkInstanceState("root@hostname:3306") #检查cluster里节点状态 cluster.rejoinInstance("root@hostname:3306") #重新加入节点,我本地测试的时候发现rejoin一直无效,每次是delete后 addcluster.dissolve({force:true}) #删除集群 cluster.addInstance("root@hostname:3306") #增加节点 cluster.removeInstance("root@hostname:3306") #删除节点 cluster.removeInstance('root@host:3306',{force:true}) #强制删除节点 cluster.dissolve({force:true}) #解散集群 cluster.describe(); #集群描述 - ONLINE - 节点状态正常。 - OFFLINE - 实例在运行,但没有加入任何Cluster。 - RECOVERING - 实例已加入Cluster,正在同步数据。 - ERROR - 同步数据发生异常。 - UNREACHABLE - 与其他节点通讯中断,可能是网络问题,可能是节点crash。 - MISSING - 节点已加入集群,但未启动group replication #如果查询节点状态为: "status": "(MISSING)" #重新加入命令 shell.connect("root@node-1:3305"); cluster=dba.getCluster(); cluster.rejoinInstance("root@node-2:3305") #如果rejoinInstance失败,提示remove重新添加如下: cluster.removeInstance('root@node-1:3305'); cluster.addInstance('root@node-1:3305'); #切换到多主状态 MySQL 192.168.100.6:3306 ssl JS > cluster.switchToMultiPrimaryMode() Switching cluster 'myCluster' to Multi-Primary mode... Instance 'zabbix-master3:3306' was switched from SECONDARY to PRIMARY. Instance 'zabbix-master1:3306' remains PRIMARY. Instance 'zabbix-master2:3306' was switched from SECONDARY to PRIMARY. The cluster successfully switched to Multi-Primary mode. #多主
这篇关于mysql8.0.26-cluster 单主与多主搭建的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南
- 2024-10-22MySQL数据库入门教程:从安装到基本操作
- 2024-10-22MySQL读写分离入门教程:轻松实现数据库性能提升
- 2024-10-22MySQL分库分表入门教程
- 2024-10-22MySQL慢查询的诊断与优化指南
- 2024-10-22MySQL索引入门教程:快速理解与应用指南
- 2024-10-22MySQL基础入门教程:从安装到基本操作
- 2024-10-22MySQL数据库中的Binlog详解与操作教程
- 2024-10-12部署MySQL集群项目实战:新手入门教程