MySQL8.4 InnoDB Cluster高可用集群使用指南

簡介

高可用方案

Orchestrator：可視化 Web 界面管理 MySQL 拓撲結構，并且兼容多種復制架構（異步、半同步、GTID），提供自動和手動的故障轉移。但是8.0.21后 MySQL 更新了主從復制相關命令，Orchestrator無法支持以后的版本。適用于大規模 MySQL 復制拓撲管理。
MHA：主要用于傳統一主多從結構，提供較快的故障切換時間，具備Binlog差異合并功能，能減少數據丟失。對5.7 版本支持較好。適用于小規模主從復制，并且希望低成本實現高可用。
InnoDB Cluster： MySQL 官方提供的高可用集群解決方案，基于Group Replication組件，實現多主同步復制，并結合 MySQL Router 提供連接路由。官方維護，對新版本支持較好。適用于高一致性、自動故障恢復的高可用需求。

InnoDB Cluster簡介

InnoDB Cluster至少由三個MySQL Server實例組成，它提供高可用性和擴展功能。 InnoDB Cluster 使用以下 MySQL 技術：

MySQL Shell，它是 MySQL 的高級客戶端和代碼編輯器。通過 MySQL Shell 提供的 AdminAPI 來管理MGR集群，它封裝了MGR 的創建、啟動和管理過程，簡化了手動配置。

MySQL server 和 Group Replication,，使一組MySQL實例能夠提供高可用性。 InnoDB Cluster 提供了一種替代的、易于使用的編程方式來使用組復制。

MySQL Router，一種輕量級中間件，可在應用程序和 InnoDB Cluster 之間提供透明路由。

下圖顯示了這些技術如何協同工作的概述：

基于MySQL Group Replication 構建，提供自動成員管理、容錯、自動故障轉移等功能。 InnoDB Cluster通常以單主模式運行，具有一個主實例（讀寫）和多個輔助實例（只讀）。高級用戶還可以利用多主模式，其中所有實例都是主實例。您甚至可以在 InnoDB Cluster 在線時更改集群的拓撲，以確保盡可能高的可用性。

集群角色規劃

集群架構

節點規劃

操作系統類型	IP	主機名	mysql版本	部署服務
rockylinux8.10	192.168.10.201	mysql-node1	8.4.4	mysql server、mysql shell
rockylinux8.10	192.168.10.202	mysql-node2	8.4.4	mysql server、mysql shell
rockylinux8.10	192.168.10.203	mysql-node3	8.4.4	mysql server、mysql shell
rockylinux8.10	192.168.10.204	mysql-router1	8.4.4	mysql client、mysql router、keepalived
rockylinux8.10	192.168.10.205	mysql-router2	8.4.4	mysql client、mysql router、keepalived
VIP	192.168.10.200

高可用架構配置

準備工作

部署 Mysql

以下操作在mysql-node1、2、3 執行

具體可參考https://www.cuiliangblog.cn/detail/section/31461021

添加 hosts

每個節點添加 hosts 解析，否則會導致 mysql 節點間通信異常。

# vim /etc/hosts
192.168.10.201  mysql-node1
192.168.10.202  mysql-node2
192.168.10.203  mysql-node3
192.168.10.204  mysql-router1
192.168.10.205  mysql-router2

確保實例uuid唯一

[root@mysql-node1 ~]# cat /data/mysql/auto.cnf 
[auto]
server-uuid=08e9b249-0780-11f0-ba9b-000c29935ee4

如果克隆的虛擬機，實例 id 一致，可刪除該文件后重啟 mysql 服務，會生成新的 uuid 文件。

使用 MySQL Shell

以下操作在mysql-node1、2、3 執行

安裝 MySQL Shell

[root@mysql-node1 ~]# wget https://repo.mysql.com//mysql84-community-release-el8-1.noarch.rpm
[root@mysql-node1 ~]# rpm -ivh mysql84-community-release-el8-1.noarch.rpm 
[root@mysql-node1 ~]# dnf search mysql-shell
MySQL 8.4 LTS Community Server                                                                                                                            252 kB/s | 844 kB     00:03    
MySQL Connectors Community                                                                                                                                105 kB/s | 150 kB     00:01    
MySQL Tools 8.4 LTS Community                                                                                                                             211 kB/s | 414 kB     00:01    
=============================================================================== 名稱 精準匹配：mysql-shell ===============================================================================
mysql-shell.x86_64 : Command line shell and scripting environment for MySQL
============================================================================= 名稱 和 概況 匹配：mysql-shell =============================================================================
mysql-shell-debugsource.x86_64 : Debug sources for package mysql-shell
[root@mysql-node1 ~]# dnf install -y mysql-shell
[root@mysql-node1 ~]# mysqlsh --version
mysqlsh   Ver 8.4.4 for Linux on x86_64 - for MySQL 8.4.4 (MySQL Community Server (GPL))

mysqlsh 登錄節點

方式一：交互模式連接

[root@mysql-node1 ~]# mysqlsh
MySQL Shell 8.4.4Copyright (c) 2016, 2025, 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  SQL > \connect root@127.0.0.1
Creating a session to 'root@127.0.0.1'
Please provide the password for 'root@127.0.0.1': *******
Save password for 'root@127.0.0.1'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 8 (X protocol)
Server version: 8.4.4 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.MySQL  127.0.0.1:33060+ ssl  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0007 sec)

方式二：命令行參數連接

[root@mysql-node1 ~]# mysqlsh -h127.0.0.1 -P3306 -uroot -p123.com
MySQL Shell 8.4.4Copyright (c) 2016, 2025, 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.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'root@127.0.0.1:3306'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 10
Server version: 8.4.4 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.MySQL  127.0.0.1:3306 ssl  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0007 sec)

方式三：使用 python/JavaScript模式連接

[root@mysql-node1 ~]# mysqlsh --py root@127.0.0.1
MySQL Shell 8.4.4Copyright (c) 2016, 2025, 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.
Creating a session to 'root@127.0.0.1'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 11 (X protocol)
Server version: 8.4.4 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.MySQL  127.0.0.1:33060+ ssl  Py > session = shell.get_session() # 獲取當前會話MySQL  127.0.0.1:33060+ ssl  Py > schemas = session.get_schemas() # 獲取數據庫列表MySQL  127.0.0.1:33060+ ssl  Py > print(schemas) # 打印數據庫列表
[<Schema:information_schema>, <Schema:mysql>, <Schema:performance_schema>, <Schema:sys>]

交互模式切換

 MySQL  127.0.0.1:33060+ ssl  Py > \js
Switching to JavaScript mode...MySQL  127.0.0.1:33060+ ssl  JS > \py
Switching to Python mode...MySQL  127.0.0.1:33060+ ssl  Py > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.MySQL  127.0.0.1:33060+ ssl  SQL > \q
Bye!

創建 InnoDB Cluster

以下操作在mysql-node1、2、3 依次執行

修改 MySQL 配置(一主多從)

[root@mysql-node1 ~]# vim /etc/my.cnf
bind-address = 0.0.0.0
server-id = 1  #服務器的唯一 ID，其他兩臺依次設置為2和3
log_bin = mysql-bin # 開啟binlog日志
binlog_expire_logs_seconds=604800 # [可選]設置日志文件保留的時長，單位是秒(默認不刪除文件)
binlog_format = ROW # 指定 Binlog 的記錄格式
gtid_mode = ON # 啟用 GTID
enforce-gtid-consistency = ON # 強制所有事務使用 GTID
loose-group_replication_bootstrap_group = OFF # 是否 啟動時自動引導 組復制
loose-group_replication_start_on_boot = ON # MySQL 啟動時自動加入 組復制集群
loose-group_replication_recovery_get_public_key = 1 # 啟用組復制的安全認證
loose-group_replication_ip_whitelist = 192.168.10.0/24 # 允許加入集群的 IP 地址段
loose-group_replication_local_address = "192.168.10.201:33061" # 本機ip地址
loose-group_replication_group_seeds = "192.168.10.201:33061,192.168.10.202:33061,192.168.10.203:33061" # 集群ip地址
loose-group_replication_single_primary_mode = ON # 是否啟用 單主模式
loose-group_replication_enforce_update_everywhere_checks = OFF # 控制數據一致性檢查，多主模式時開啟

重啟 mysql

[root@mysql-node1 ~]# systemctl restart mysqld

修改 MySQL 配置(多主模式)

[root@mysql-node1 ~]# vim /etc/my.cnf
bind-address = 0.0.0.0
server-id = 1  #服務器的唯一 ID，其他兩臺依次設置為2和3
log_bin = mysql-bin # 開啟binlog日志
binlog_expire_logs_seconds=604800 # [可選]設置日志文件保留的時長，單位是秒(默認不刪除文件)
binlog_format = ROW # 指定 Binlog 的記錄格式
gtid_mode = ON # 啟用 GTID
enforce-gtid-consistency = ON # 強制所有事務使用 GTID
loose-group_replication_bootstrap_group = OFF # 是否 啟動時自動引導 組復制
loose-group_replication_start_on_boot = ON # MySQL 啟動時自動加入 組復制集群
loose-group_replication_recovery_get_public_key = 1 # 啟用組復制的安全認證
loose-group_replication_ip_whitelist = 192.168.10.0/24 # 允許加入集群的 IP 地址段
loose-group_replication_local_address = "192.168.10.201:33061" # 本機ip地址
loose-group_replication_group_seeds = "192.168.10.201:33061,192.168.10.202:33061,192.168.10.203:33061" # 集群ip地址
loose-group_replication_single_primary_mode = OFF # 是否啟用 單主模式
loose-group_replication_enforce_update_everywhere_checks = ON # 多主模式，允許所有節點寫入

檢查并驗證配置文件

[root@mysql-node1 ~]# mysqlsh -- dba configure-instance --host=localhost --user=root --password='123.com'
Configuring local MySQL instance listening at port 3306 for use in an InnoDB Cluster...This instance reports its own address as mysql-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.applierWorkerThreads will be set to the default value of 4.The instance 'mysql-node1:3306' is valid for InnoDB Cluster usage.Successfully enabled parallel appliers.

創建 MGR 集群

僅在 mysql-node1 節點執行既可

[root@mysql-node1 ~]# mysqlsh -h127.0.0.1 -P3306 -uroot -p123.com
MySQL Shell 8.4.4Copyright (c) 2016, 2025, 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.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'root@127.0.0.1:3306'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 17
Server version: 8.4.4 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
# 切換到js模式MySQL  127.0.0.1:3306 ssl  SQL > \js
Switching to JavaScript mode...
# 創建集群MySQL  127.0.0.1:3306 ssl  JS > dba.createCluster('myCluster')
A new InnoDB Cluster will be created on instance 'mysql-node1:3306'.Validating instance configuration at 127.0.0.1:3306...This instance reports its own address as mysql-node1:3306Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql-node1:3306'. Use the localAddress option to override.* Checking connectivity and SSL configuration...Creating InnoDB Cluster 'myCluster' on 'mysql-node1: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.<Cluster:myCluster>

添加其他節點

依次添加 mysql-node2 和 mysql-node3 節點

 MySQL  127.0.0.1:3306 ssl  JS > var cluster = dba.getCluster()MySQL  127.0.0.1:3306 ssl  JS > cluster.addInstance('root@192.168.10.202')NOTE: The target instance 'mysql-node2:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'mysql-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 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'.Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): 
Validating instance configuration at 192.168.10.202:3306...This instance reports its own address as mysql-node2:3306Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql-node2:3306'. Use the localAddress option to override.* Checking connectivity and SSL configuration...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: mysql-node2:3306 is being cloned from mysql-node1:3306
** Stage DROP DATA: Completed
** Clone Transfer  FILE COPY  ############################################################  100%  CompletedPAGE COPY  ############################################################  100%  CompletedREDO COPY  ############################################################  100%  CompletedNOTE: mysql-node2:3306 is shutting down...* Waiting for server restart... ready 
* mysql-node2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 76.80 MB transferred in about 1 second (~76.80 MB/s)State recovery already finished for 'mysql-node2:3306'The instance 'mysql-node2:3306' was successfully added to the cluster.MySQL  127.0.0.1:3306 ssl  JS > cluster.addInstance('root@192.168.10.203')

查看集群狀態

 MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{"clusterName": "myCluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-node1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"mysql-node1:3306": {"address": "mysql-node1:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node2:3306": {"address": "mysql-node2:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node3:3306": {"address": "mysql-node3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "mysql-node1:3306"
}

從集群狀態信息可知：

**mysql-node1** 是 主節點（PRIMARY），負責讀寫請求。
**mysql-node2**** 和 ****mysql-node3** 是 從節點（SECONDARY），僅可讀（R/O）。
復制延遲 (replicationLag: applier_queue_applied) 表示 數據復制正常，從庫沒有明顯的同步延遲。

配置 MySQL Router

以下操作在mysql-router1、2 分別執行

安裝 MySQL Router

[root@mysql-router1 ~]# wget https://repo.mysql.com//mysql84-community-release-el8-1.noarch.rpm
[root@mysql-router1 ~]# rpm -ivh mysql84-community-release-el8-1.noarch.rpm 
[root@mysql-router1 ~]# dnf search mysql-router
上次元數據過期檢查：0:23:55 前，執行于 2025年03月06日 星期四 00時02分05秒。
================================================================================ 名稱 匹配：mysql-router =================================================================================
mysql-router-community.x86_64 : MySQL Router
[root@mysql-router1 ~]# dnf install -y mysql-router
[root@mysql-router1 ~]# mysqlrouter --version
MySQL Router  Ver 8.4.4 for Linux on x86_64 (MySQL Community - GPL)

初始化 MySQL Router

[root@mysql-router1 ~]# mysqlrouter --bootstrap root@192.168.10.201:3306 --user=mysqlrouter --directory=/etc/mysqlrouter
Please enter MySQL password for root: 
# Bootstrapping system MySQL Router 8.4.4 (MySQL Community - GPL) instance...- 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 /etc/mysqlrouter/mysqlrouter.confExisting configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'# 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.confInnoDB Cluster 'myCluster' can be reached by connecting to:## MySQL Classic protocol- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447
- Read/Write Split Connections: localhost:6450## MySQL X protocol- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

Mysql Router 端口說明

MySQL Classic Protocol（經典協議）

這是傳統的 MySQL 客戶端/服務器協議，用于 SQL 查詢和事務處理，主要適用于：

MySQL CLI (mysql 命令行工具)
PHP、Python、Java 等使用 mysql 連接庫的應用
傳統的 MySQL 連接方式 (mysql_native_password 認證)

相關端口

端口	連接模式	作用
`6446`	Read/Write	讀寫連接，適用于事務型應用（連接到 Primary 節點）
`6447`	Read-Only	只讀連接，適用于報表查詢（連接到 Secondary 節點）
`6450`	Read/Write Split	讀寫分離，自動選擇合適的后端（寫操作發送到 Primary，讀操作發送到 Secondary）

MySQL X Protocol

MySQL X Protocol 是 MySQL 的新一代協議，支持 JSON 文檔、NoSQL 查詢和 CRUD API，主要適用于：

MySQL Shell (mysqlsh)
X DevAPI（面向文檔存儲）
適用于 MySQL 8.0 以上版本的新應用場景

相關端口

端口	連接模式	作用
`6448`	Read/Write	讀寫連接，適用于 X DevAPI 的事務（連接到 Primary 節點）
`6449`	Read-Only	只讀連接，適用于 X DevAPI 讀操作（連接到 Secondary 節點）

啟動 MySQL Router

[root@mysql-router1 ~]# systemctl start mysqlrouter.service 
[root@mysql-router1 ~]# systemctl enable mysqlrouter.service 
Created symlink /etc/systemd/system/multi-user.target.wants/mysqlrouter.service → /usr/lib/systemd/system/mysqlrouter.service.
[root@mysql-router1 ~]# systemctl status mysqlrouter.service

配置 Keepalived

以下操作在mysql-router1、2 執行

安裝 keeplived

[root@mysql-router1 ~]# dnf install -y keepalived

配置 Keepalived

在 mysql-router1 配置 /etc/keepalived/keepalived.conf：

global_defs {script_user rootenable_script_security
}vrrp_script chk_router {script "/etc/keepalived/check_router.sh"interval 1weight -2
}vrrp_instance VI_1 {state MASTERinterface ens160virtual_router_id 51priority 100 advert_int 1authentication {auth_type PASSauth_pass mypassword}   virtual_ipaddress {192.168.10.200}   track_script {chk_router}   
}

在 mysql-router2 配置 /etc/keepalived/keepalived.conf：

global_defs {script_user rootenable_script_security
}vrrp_script chk_router {script "/etc/keepalived/check_router.sh"interval 1weight -2
}vrrp_instance VI_1 {state BACKUP # 改為備用interface ens160virtual_router_id 51priority 99 # 降低優先級advert_int 1authentication {auth_type PASSauth_pass mypassword}virtual_ipaddress {192.168.10.200}track_script {chk_router}
}

創建 MySQL Router 檢測腳本

[root@mysql-router1 ~]# cat > /etc/keepalived/check_router.sh << EOF
#!/bin/bash
if pgrep mysqlrouter > /dev/null; thenexit 0
elseexit 1
fi
EOF

賦予執行權限：

[root@mysql-router1 ~]# chmod +x /etc/keepalived/check_router.sh

啟動 Keepalived

[root@mysql-router1 ~]# systemctl enable keepalived
[root@mysql-router1 ~]# systemctl start keepalived

功能測試

測試MySQL Router高可用

在 mysql-router1 執行：

# 當前vip位于mysql-router1節點
[root@mysql-router1 ~]# ip a | grep 192.168.10inet 192.168.10.204/24 brd 192.168.10.255 scope global noprefixroute ens160inet 192.168.10.200/32 scope global ens160
# 停止mysqlrouter服務，模擬router1節點故障
[root@mysql-router1 ~]# systemctl stop mysqlrouter.service 
# 當前vip已經從mysql-router1節點移除。
[root@mysql-router1 ~]# ip a | grep 192.168.10inet 192.168.10.204/24 brd 192.168.10.255 scope global noprefixroute ens160

在 mysql-router2 執行：

# 當前vip位于mysql-router2節點
[root@mysql-router2 ~]# ip a | grep 192.168.10inet 192.168.10.205/24 brd 192.168.10.255 scope global noprefixroute ens160inet 192.168.10.200/32 scope global ens160

在 mysql-router1 執行：

# mysql-router1節點啟動服務，模擬故障恢復
[root@mysql-router1 ~]# systemctl start mysqlrouter.service 
# 當前vip繼續漂移到mysql-router1節點
[root@mysql-router1 ~]# ip a | grep 192.168.10inet 192.168.10.204/24 brd 192.168.10.255 scope global noprefixroute ens160inet 192.168.10.200/32 scope global ens160

測試MySQL高可用

使用客戶機連接 vip 的 6450 端口測試。

[root@tiaoban ~]# mysql -h 192.168.10.200 -P6450 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 8.4.4-router MySQL Community Server - GPLCopyright (c) 2000, 2025, 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 '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.00 sec)

停止 mysql-node1 節點，模擬主節點故障：

[root@mysql-node1 ~]# systemctl stop mysqld

查看集群狀態，node1 離線，node3 提升為主節點。

 MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{"clusterName": "myCluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-node3:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE_PARTIAL", "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.", "topology": {"mysql-node1:3306": {"address": "mysql-node1:3306", "memberRole": "SECONDARY", "mode": "n/a", "readReplicas": {}, "role": "HA", "shellConnectError": "MySQL Error 2003: Could not open connection to 'mysql-node1:3306': Can't connect to MySQL server on 'mysql-node1:3306' (111)", "status": "(MISSING)"}, "mysql-node2:3306": {"address": "mysql-node2:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node3:3306": {"address": "mysql-node3:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}}, "topologyMode": "Single-Primary"}, : "mysql-node3:3306"
}

客戶端訪問數據庫服務未中斷，可正常讀寫

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.00 sec)

測試主從同步

客戶端連接主節點寫入數據測試：

[root@tiaoban ~]# mysql -h 192.168.10.200 -P6450 -u root -p
# 創建數據庫
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
# 查看數據庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
# 切換數據庫
mysql> use test_db;
Database changed
# 創建表
mysql> CREATE TABLE `t_test` (`id` int(11) NOT NULL,`age` int(11) DEFAULT NULL,`score` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 3 warnings (0.01 sec)# 插入表數據
mysql> INSERT INTO `t_test` VALUES (1, 2, 1);
Query OK, 1 row affected (0.01 sec)
# 查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
# 查看表數據
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
+-----+------+-------+
2 rows in set (0.00 sec)

檢查從節點是否同步成功：

[root@mysql-node3 ~]# mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)
# 切換數據庫
mysql> use test_db;
Database changed
# 查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
# 查詢表數據
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
+-----+------+-------+
2 rows in set (0.00 sec)

至此，主從同步驗證完成

測試讀寫分離

客戶端連接數據庫 6446 讀寫端口測試

[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6446 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node1     0
[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6446 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node1     0

客戶端連接數據庫 6447 只讀端口測試

[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6447 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node2     1
[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6447 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node3     1

客戶端連接數據庫 6450 讀寫分離端口測試

[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6450 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node2     1
[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6450 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node3     1

Mysql 集群維護

集群節點擴容

準備工作，具體可參考上文

部署MySQL
部署MySQL Shell
更新所有節點hosts

更新 mysql4 配置文件

[root@mysql-node4 ~]# vim /etc/my.cnf
[mysqld]
bind-address = 0.0.0.0
server_id=4                        # 唯一的服務器 ID，不能和現有節點沖突
log_bin=mysql-bin                  # 啟用二進制日志
binlog_expire_logs_seconds=604800  # [可選]設置日志文件保留的時長，單位是秒(默認不刪除文件)
binlog_format=ROW                  # 確保 binlog 使用 ROW 模式
gtid_mode=ON                       # 啟用 GTID
enforce-gtid-consistency=ON        # 強制 GTID 一致性
[root@mysql-node4 ~]# systemctl restart mysqld

InnoDB Cluster 集群添加節點

[root@mysql-node1 ~]# mysqlsh -h127.0.0.1 -P3306 -uroot -p123.com
# 切換到js模式
MySQL  127.0.0.1:3306 ssl  SQL > \js
Switching to JavaScript mode...
# 獲取集群MySQL  127.0.0.1:3306 ssl  JS > var cluster = dba.getCluster();
# 查看集群狀態MySQL  127.0.0.1:3306 ssl  JS > cluster.status();
{"clusterName": "myCluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-node2:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"mysql-node1:3306": {"address": "mysql-node1:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node2:3306": {"address": "mysql-node2:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node3:3306": {"address": "mysql-node3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "mysql-node2:3306"
}
# 檢查擴容機器是否符合加入，如果不符合根據提示修改配置。MySQL  127.0.0.1:3306 ssl  JS > dba.checkInstanceConfiguration('root@mysql-node4:3306');
Please provide the password for 'root@mysql-node4:3306': *******
Save password for 'root@mysql-node4:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Validating MySQL instance at mysql-node4:3306 for use in an InnoDB Cluster...This instance reports its own address as mysql-node4: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 detectedChecking instance configuration...
Instance configuration is compatible with InnoDB clusterThe instance 'mysql-node4:3306' is valid for InnoDB Cluster usage.{"status": "ok"
}
# 加入集群MySQL  127.0.0.1:3306 ssl  JS > cluster.addInstance('root@mysql-node4:3306');
# 查看集群狀態MySQL  127.0.0.1:3306 ssl  JS > cluster.status();
{"clusterName": "myCluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-node2:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"mysql-node1:3306": {"address": "mysql-node1:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node2:3306": {"address": "mysql-node2:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node3:3306": {"address": "mysql-node3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node4:3306": {"address": "mysql-node4:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "mysql-node2:3306"
}

更新 MySQL Router

[root@mysql-router1 ~]# mysqlrouter --bootstrap root@192.168.10.201:3306 --user=mysqlrouter --directory=/etc/mysqlrouter
[root@mysql-router1 ~]# systemctl restart mysqlrouter

集群節點刪除

[root@mysql-node1 ~]# mysqlsh -h127.0.0.1 -P3306 -uroot -p123.com
# 切換到js模式
MySQL  127.0.0.1:3306 ssl  SQL > \js
Switching to JavaScript mode...
# 獲取集群
MySQL  127.0.0.1:3306 ssl  JS > var cluster = dba.getCluster();
# 查看集群狀態
MySQL  127.0.0.1:3306 ssl  JS > cluster.status();
# 定義要移除的節點主機名
MySQL  127.0.0.1:3306 ssl  JS > var memberToBeRemoved = "mysql-node4";
# 刪除節點
MySQL  127.0.0.1:3306 ssl  JS > cluster.removeInstance(memberToBeRemoved);
# 查看集群狀態
MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{"clusterName": "myCluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-node2:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"mysql-node1:3306": {"address": "mysql-node1:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node2:3306": {"address": "mysql-node2:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node3:3306": {"address": "mysql-node3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "mysql-node2:3306"
}

集群角色與模式切換

連接集群

[root@mysql-node1 ~]# mysqlsh -h127.0.0.1 -P3306 -uroot -p123.com
# 切換到js模式
MySQL  127.0.0.1:3306 ssl  SQL > \js
Switching to JavaScript mode...
# 獲取集群
MySQL  127.0.0.1:3306 ssl  JS > var cluster = dba.getCluster();
# 查看集群狀態
MySQL  127.0.0.1:3306 ssl  JS > cluster.status();

切換為多個Primary模式

MySQL  127.0.0.1:3306 ssl  JS > cluster.switchToMultiPrimaryMode()
Switching cluster 'myCluster' to Multi-Primary mode...Instance 'mysql-node2:3306' remains PRIMARY.
Instance 'mysql-node1:3306' was switched from SECONDARY to PRIMARY.
Instance 'mysql-node3:3306' was switched from SECONDARY to PRIMARY.The cluster successfully switched to Multi-Primary mode.
# 查看集群狀態
MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{"clusterName": "myCluster", "defaultReplicaSet": {"name": "default", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"mysql-node1:3306": {"address": "mysql-node1:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node2:3306": {"address": "mysql-node2:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node3:3306": {"address": "mysql-node3:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}}, "topologyMode": "Multi-Primary"}, "groupInformationSourceMember": "mysql-node1:3306"
}

切換為單個Primary模式

MySQL  127.0.0.1:3306 ssl  JS > cluster.switchToSinglePrimaryMode()
Switching cluster 'myCluster' to Single-Primary mode...Instance 'mysql-node1:3306' remains PRIMARY.
Instance 'mysql-node2:3306' was switched from PRIMARY to SECONDARY.
Instance 'mysql-node3:3306' was switched from PRIMARY to SECONDARY.WARNING: Existing connections that expected a R/W connection must be disconnected, i.e. instances that became SECONDARY.The cluster successfully switched to Single-Primary mode.
MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{"clusterName": "myCluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-node1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"mysql-node1:3306": {"address": "mysql-node1:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node2:3306": {"address": "mysql-node2:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node3:3306": {"address": "mysql-node3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "mysql-node1:3306"
}

指定節點切換為Primary模式

MySQL  127.0.0.1:3306 ssl  JS > cluster.setPrimaryInstance('mysql-node2')
Setting instance 'mysql-node2' as the primary instance of cluster 'myCluster'...Instance 'mysql-node3:3306' remains SECONDARY.
Instance 'mysql-node1:3306' was switched from PRIMARY to SECONDARY.
Instance 'mysql-node2:3306' was switched from SECONDARY to PRIMARY.The instance 'mysql-node2' was successfully elected as primary.
# 查看集群狀態
MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{"clusterName": "myCluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-node2:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"mysql-node1:3306": {"address": "mysql-node1:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node2:3306": {"address": "mysql-node2:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}, "mysql-node3:3306": {"address": "mysql-node3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.4"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "mysql-node2:3306"
}

查看更多

崔亮的博客-專注devops自動化運維，傳播優秀it運維技術文章。更多原創運維開發相關文章，歡迎訪問https://www.cuiliangblog.cn