MySQL InnoDB Cluster 高可用集群部署
MySQL InnoDB Cluster 简介
官方文档：https://dev.mysql.com/doc/refman/8.4/en/mysql-innodb-cluster-introduction.html
本章介绍 MySQL InnoDB Cluster，它结合了 MySQL 技术，使您能够部署和管理完整的 MySQL 集成高可用性解决方案。
说明：InnoDB Cluster 不提供对 MySQL NDB Cluster 的支持。
InnoDB Cluster至少由三个MySQL Server实例组成，它提供高可用性和扩展功能。 InnoDB Cluster 使用以下 MySQL 技术：
- MySQL Shell，它是 MySQL 的高级客户端和代码编辑器。
- MySQL server 和 Group Replication,，使一组MySQL实例能够提供高可用性。 InnoDB Cluster 提供了一种替代的、易于使用的编程方式来使用组复制。
- MySQL Router，一种轻量级中间件，可在应用程序和 InnoDB Cluster 之间提供透明路由。
下图显示了这些技术如何协同工作的概述：
基于MySQL Group Replication 构建，提供自动成员管理、容错、自动故障转移等功能。 InnoDB Cluster通常以单主模式运行，具有一个主实例（读写）和多个辅助实例（只读）。高级用户还可以利用多主模式，其中所有实例都是主实例。您甚至可以在 InnoDB Cluster 在线时更改集群的拓扑，以确保尽可能高的可用性。
集群部署节点规划
必须部署至少三台MySQL服务器，用于mysql innodb节点，至少一台MySQL Router服务器。
本示例使用7台虚拟机进行部署。部署架构如下图所示：
节点规划清单如下：
| Host | IP | 操作系统 | 角色 |
|---|---|---|---|
| mysql-innodb01 | 192.168.72.50 | Ubuntu 22.04 | mysql innodb/mysql shell |
| mysql-innodb02 | 192.168.72.51 | Ubuntu 22.04 | mysql innodb/mysql shell |
| mysql-innodb03 | 192.168.72.52 | Ubuntu 22.04 | mysql innodb/mysql shell |
| mysql-router01 | 192.168.72.41 | Ubuntu 22.04 | mysql router/mysql client |
| mysql-router02 | 192.168.72.42 | Ubuntu 22.04 | mysql router/mysql client |
| lb01 | 192.168.72.33 | Ubuntu 22.04 | haproxy/keepalived |
| lb02 | 192.168.72.34 | Ubuntu 22.04 | haproxy/keepalived |
| mysql-db-vip | 192.168.72.200 | N/A | N/A |
以下操作在所有mysql-router节点及mysql-innodb节点执行。
分别在每个节点配置主机名
hostnamectl set-hostname mysql-innodb01
hostnamectl set-hostname mysql-innodb02
hostnamectl set-hostname mysql-innodb03
hostnamectl set-hostname mysql-router01
hostnamectl set-hostname mysql-router02
分别在每个节点配置hosts解析
cat << EOF > /etc/hosts
192.168.72.41 mysql-router01 mysql-router02
192.168.72.42 mysql-router02 mysql-router02
192.168.72.50 mysql-innodb01 mysql-innodb01
192.168.72.51 mysql-innodb02 mysql-innodb02
192.168.72.52 mysql-innodb03 mysql-innodb03
EOF
添加mysql官方软件源
下载地址：https://dev.mysql.com/downloads/repo/apt/
wget https://repo.mysql.com//mysql-apt-config_0.8.30-1_all.deb
dpkg -i mysql-apt-config_0.8.30-1_all.deb
apt update -y
安装innodb集群
以下操作在所有innodb节点执行。
在安装过程中，您需要为MySQL root用户设置密码。
apt update -y
apt install -y mysql-server mysql-shell
查看安装版本
root@mysql-innodb01:~# mysql -V
mysql Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)root@mysql-innodb01:~# mysqlsh -V
mysqlsh Ver 8.0.37 for Linux on x86_64 - for MySQL 8.0.37 (MySQL Community Server (GPL))
在innodb01节点执行
连接到mysql-shell
root@mysql-innodb01:~# mysqlsh
连接到数据库，输入数据库root用户密码
MySQL SQL > \connect root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': ************
Save password for 'root@localhost'? [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 13 (X protocol)
Server version: 8.4.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.MySQL localhost:33060+ ssl SQL >
或者直接连接
mysqlsh -uroot -pMysql@123456 -h localhost
查看数据库信息
MySQL localhost:33060+ ssl SQL > \sqlMySQL localhost:33060+ ssl SQL > show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.0011 sec)
或者切换到JS模式
MySQL localhost:33060+ ssl SQL > \js
Switching to JavaScript mode...MySQL localhost:33060+ ssl JS >
MySQL localhost:33060+ ssl JS > \sql show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.0011 sec)MySQL localhost:33060+ ssl JS >
为innodb集群创建独立账号icadmin，密码为icadminPassw0rd!
MySQL localhost:33060+ 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 innodb01: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.ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB Cluster with minimal required grants
3) Ignore and continue
4) CancelPlease select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: icadmin
Password for new account: icadmin
Confirm password: ***********applierWorkerThreads will be set to the default value of 4.NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+--------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
| server_id | 1 | <unique ID> | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: yCreating user icadmin
@%.
Account icadmin@% was successfully created.Configuring instance...
The instance 'mysql-innodb01:3306' was configured to be used in an InnoDB Cluster.
Restarting MySQL...
NOTE: MySQL server at mysql-innodb01:3306 was restarted.MySQL localhost:33060+ ssl JS >
或者使用以下命令
MySQL localhost:33060+ ssl JS > dba.configureInstance('root@localhost:3306', {clusterAdmin: "'icadmin'@'%'", clusterAdminPassword: 'icadminPassw0rd!'});
查看数据库信息，查看本节点数据库实例Uptime参数，确认已重启
MySQL localhost:33060+ ssl JS > \sql
Switching to SQL mode... Commands end with ;MySQL localhost:33060+ ssl SQL > \s
MySQL Shell version 8.4.0Error Retrieving Status: MySQL Error 2006: MySQL server has gone away
The global session got disconnected..
Attempting to reconnect to 'mysqlx://root@localhost:33060'..
The global session was successfully reconnected.MySQL localhost:33060+ ssl SQL > MySQL localhost:33060+ ssl SQL > \s
MySQL Shell version 8.4.0Connection Id: 9
Default schema:
Current schema:
Current user: root@localhost
SSL: Cipher in use: TLS_AES_128_GCM_SHA256 TLSv1.3
Using delimiter: ;
Server version: 8.4.0 MySQL Community Server - GPL
Protocol version: X protocol
Client library: 8.4.0
Connection: localhost via TCP/IP
TCP port: 33060
Server characterset: utf8mb4
Schema characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
Result characterset: utf8mb4
Compression: Enabled (DEFLATE_STREAM)
Uptime: 2 min 55.0000 sec
查看gtid_mode 已启用
MySQL localhost:33060+ ssl SQL > \js
Switching to JavaScript mode...MySQL localhost:33060+ ssl JS > \sql select @@gtid_mode, @@server_id
+-------------+-------------+
| @@gtid_mode | @@server_id |
+-------------+-------------+
| ON | 2728360213 |
+-------------+-------------+
1 row in set (0.0007 sec)
查看生成的mysqld-auto.cnf配置文件
root@innodb01:~# cat /var/lib/mysql/mysqld-auto.cnf
其中确认参数如下
gtid_mode": {"Value": "ON"
server_id": {"Value": "2728360213
重新连接到集群账号
root@mysql-innodb01:~# fgMySQL localhost:33060+ ssl JS > \connect icadmin@localhost
Creating a session to 'icadmin@localhost'
Please provide the password for 'icadmin@localhost': **********
Save password for 'icadmin@localhost'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 12 (X protocol)
Server version: 8.4.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
检查实例配置，确认status参数为ok。
MySQL localhost:33060+ ssl JS > dba.checkInstanceConfiguration()
Validating local MySQL instance listening at port 3306 for use in an InnoDB Cluster...This instance reports its own address as innodb01: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-innodb01:3306' is valid for InnoDB Cluster usage.{"status": "ok"
}MySQL localhost:33060+ ssl JS >
连接到innodb02
连接到mysql-shell
root@mysql-innodb02:~# mysqlsh
MySQL Shell 8.4.0Copyright (c) 2016, 2024, 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 >
连接到数据库
MySQL SQL > \connect root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': ************
Save password for 'root@localhost'? [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 9 (X protocol)
Server version: 8.4.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.MySQL localhost:33060+ ssl SQL >
为innodb集群创建独立账号icadmin，密码为icadminPassw0rd!
MySQL localhost:33060+ ssl SQL > \js
Switching to JavaScript mode...MySQL localhost:33060+ 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 innodb02: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.ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB Cluster with minimal required grants
3) Ignore and continue
4) CancelPlease select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: icadmin
Password for new account: ************
Confirm password: ************applierWorkerThreads will be set to the default value of 4.NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+--------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
| server_id | 1 | <unique ID> | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: yCreating user icadmin@%.
Account icadmin@% was successfully created.Configuring instance...
The instance 'mysql-innodb02:3306' was configured to be used in an InnoDB Cluster.
Restarting MySQL...
NOTE: MySQL server at mysql-innodb02:3306 was restarted.MySQL localhost:33060+ ssl JS >
连接到集群账号
MySQL localhost:33060+ ssl JS > \connect icadmin@localhost
Creating a session to 'icadmin@localhost'
Please provide the password for 'icadmin@localhost': ************
Save password for 'ic@localhost'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 9 (X protocol)
Server version: 8.4.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
检查实例
MySQL localhost:33060+ ssl JS > dba.checkInstanceConfiguration()
Validating local MySQL instance listening at port 3306 for use in an InnoDB Cluster...This instance reports its own address as mysql-innodb02: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-innodb02:3306' is valid for InnoDB Cluster usage.{"status": "ok"
}MySQL localhost:33060+ ssl JS >
连接到innodb03
root@innodb03:~# mysqlsh
MySQL Shell 8.4.0Copyright (c) 2016, 2024, 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@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': ************
Save password for 'root@localhost'? [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 9 (X protocol)
Server version: 8.4.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.MySQL localhost:33060+ ssl SQL >
为innodb集群创建独立账号icadmin，密码为icadminPassw0rd!
MySQL localhost:33060+ ssl SQL > \js
Switching to JavaScript mode...MySQL localhost:33060+ 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 mysql-innodb03: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.ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB Cluster with minimal required grants
3) Ignore and continue
4) CancelPlease select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: ic
Password for new account: **********
Confirm password: *********applierWorkerThreads will be set to the default value of 4.NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+--------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
| server_id | 1 | <unique ID> | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: yCreating user icadmin@%.
Account icadmin@% was successfully created.Configuring instance...
The instance 'mysql-innodb03:3306' was configured to be used in an InnoDB Cluster.
Restarting MySQL...
NOTE: MySQL server at mysql-innodb03:3306 was restarted.MySQL localhost:33060+ ssl JS >
连接到集群账号
MySQL localhost:33060+ ssl JS > \connect icadmin@localhost
Creating a session to 'icadmin@localhost'
Please provide the password for 'icadmin@localhost': **
Save password for 'icadmin@localhost'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 9 (X protocol)
Server version: 8.4.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
检查实例
MySQL localhost:33060+ ssl JS > dba.checkInstanceConfiguration()
Validating local MySQL instance listening at port 3306 for use in an InnoDB Cluster...This instance reports its own address as mysql-innodb03: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-innodb03:3306' is valid for InnoDB Cluster usage.{"status": "ok"
}MySQL localhost:33060+ ssl JS >
创建innodb集群
以下所有操作在innodb01节点执行
MySQL localhost:33060+ ssl JS > dba.createCluster('my_innodb_cluster')
A new InnoDB Cluster will be created on instance 'mysql-innodb01:3306'.Validating instance configuration at localhost:3306...This instance reports its own address as innodb01:3306Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql-innodb01:3306'. Use the localAddress option to override.* Checking connectivity and SSL configuration...Creating InnoDB Cluster 'my_innodb_cluster' on 'innodb01: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:my_innodb_cluster>MySQL localhost:33060+ ssl JS >
查看集群状态
MySQL localhost:33060+ ssl JS > dba.getCluster()
<Cluster:my_innodb_cluster>MySQL localhost:33060+ ssl JS > herc = dba.getCluster()
<Cluster:my_innodb_cluster>MySQL localhost:33060+ ssl JS > herc.status()
{"clusterName": "my_innodb_cluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-innodb01:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": {"innodb01:3306": {"address": "mysql-innodb01:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.0"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "mysql-innodb01:3306"
}MySQL localhost:33060+ ssl JS >
添加innodb02节点到集群
MySQL localhost:33060+ ssl JS > herc.addInstance('icadmin@mysql-innodb02')NOTE: The target instance 'mysql-innodb02: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 'innodb02: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): C
Validating instance configuration at innodb02:3306...This instance reports its own address as innodb02:3306Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql-innodb02: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: innodb02:3306 is being cloned from innodb01:3306
** Stage DROP DATA: Completed
** Clone Transfer FILE COPY ############################################################ 100% CompletedPAGE COPY ############################################################ 100% CompletedREDO COPY ############################################################ 100% CompletedNOTE: innodb02:3306 is shutting down...* Waiting for server restart... ready
* innodb02:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 74.14 MB transferred in about 1 second (~74.14 MB/s)State recovery already finished for 'mysql-innodb02:3306'The instance 'mysql-innodb02:3306' was successfully added to the cluster.
检查集群状态
MySQL localhost:33060+ ssl JS > herc.status()
{"clusterName": "my_innodb_cluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-innodb01:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": {"innodb01:3306": {"address": "mysql-innodb01:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.0"}, "innodb02:3306": {"address": "mysql-innodb02:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.0"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "mysql-innodb01:3306"
}MySQL localhost:33060+ ssl JS >
在innnodb01节点检查super_read_only参数，只读节点数为0
MySQL localhost:33060+ ssl JS > \sql select @@super_read_only;
+-------------------+
| @@super_read_only |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.0007 sec)
在innnodb02节点检查super_read_only参数，只读节点数为1
MySQL localhost:33060+ ssl JS > \sql select @@super_read_only;
+-------------------+
| @@super_read_only |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.0004 sec)
添加innodb03节点到集群
MySQL localhost:33060+ ssl JS > herc.addInstance('icadmin@mysql-innodb03')NOTE: The target instance 'mysql-innodb03: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-innodb03: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): C
Validating instance configuration at mysql-innodb03:3306...This instance reports its own address as mysql-innodb03:3306Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql-innodb03: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-innodb03:3306 is being cloned from mysql-innodb01:3306
** Stage DROP DATA: Completed
** Clone Transfer FILE COPY ############################################################ 100% CompletedPAGE COPY ############################################################ 100% CompletedREDO COPY ############################################################ 100% CompletedNOTE: innodb03:3306 is shutting down...* Waiting for server restart... ready
* innodb03:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 74.41 MB transferred in about 1 second (~74.41 MB/s)State recovery already finished for 'mysql-innodb03:3306'The instance 'mysql-innodb03:3306' was successfully added to the cluster.
查看集群状态
MySQL localhost:33060+ ssl JS > herc.status()
{"clusterName": "my_innodb_cluster", "defaultReplicaSet": {"name": "default", "primary": "mysql-innodb01:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": {"mysql-innodb01:3306": {"address": "mysql-innodb01:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.0"}, "mysql-innodb02:3306": {"address": "mysql-innodb02:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.0"}, "mysql-innodb03:3306": {"address": "mysql-innodb03:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.4.0"}}, "topologyMode": "Single-Primary"}, "groupInformationSourceMember": "innodb01:3306"
}MySQL localhost:33060+ ssl JS >
关键字段如下:
"status": "OK" 表示集群状态是正常的
"topologyMode": "Single-Primary" 表示是单主模式
"mode": "R/W" 表示可读可写
"mode": "R/O" 表示只读
查看库及表
MySQL localhost:33060+ ssl JS > \sql
Switching to SQL mode... Commands end with ;MySQL localhost:33060+ ssl SQL > show schemas;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
5 rows in set (0.0013 sec)MySQL localhost:33060+ ssl SQL > use mysql_innodb_cluster_metadata;
Default schema set to `mysql_innodb_cluster_metadata`.
Fetching global names, object names from `mysql_innodb_cluster_metadata` for auto-completion... Press ^C to stop.MySQL localhost:33060+ ssl mysql_innodb_cluster_metadata SQL > MySQL localhost:33060+ ssl mysql_innodb_cluster_metadata SQL > show tables;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| async_cluster_members |
| async_cluster_views |
| clusters |
| clusterset_members |
| clusterset_views |
| clustersets |
| instances |
| router_rest_accounts |
| routers |
| schema_version |
| v2_ar_clusters |
| v2_ar_members |
| v2_clusters |
| v2_cs_clustersets |
| v2_cs_members |
| v2_cs_router_options |
| v2_gr_clusters |
| v2_instances |
| v2_router_options |
| v2_router_rest_accounts |
| v2_routers |
| v2_this_instance |
+-----------------------------------------+
22 rows in set (0.0020 sec)MySQL localhost:33060+ ssl mysql_innodb_cluster_metadata SQL > use performance_schema;
Default schema set to `performance_schema`.
Fetching global names, object names from `performance_schema` for auto-completion... Press ^C to stop.MySQL localhost:33060+ ssl performance_schema SQL > show tables;
+------------------------------------------------------+
| Tables_in_performance_schema |
+------------------------------------------------------+
| accounts |
| binary_log_transaction_compression_stats |
| clone_progress |
| clone_status |
| cond_instances |
| data_lock_waits |
| data_locks |
| error_log |
| events_errors_summary_by_account_by_error |
| events_errors_summary_by_host_by_error |
| events_errors_summary_by_thread_by_error |
| events_errors_summary_by_user_by_error |
| events_errors_summary_global_by_error |
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name |
| events_stages_summary_by_thread_by_event_name |
| events_stages_summary_by_user_by_event_name |
| events_stages_summary_global_by_event_name |
| events_statements_current |
| events_statements_histogram_by_digest |
| events_statements_histogram_global |
| events_statements_history |
| events_statements_history_long |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name |
| events_statements_summary_by_program |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name |
| events_transactions_current |
| events_transactions_history |
| events_transactions_history_long |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name |
| events_transactions_summary_by_thread_by_event_name |
| events_transactions_summary_by_user_by_event_name |
| events_transactions_summary_global_by_event_name |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_by_user_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| global_status |
| global_variables |
| host_cache |
| hosts |
| innodb_redo_log_files |
| keyring_component_status |
| keyring_keys |
| log_status |
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
| metadata_locks |
| mutex_instances |
| objects_summary_global_by_type |
| performance_timers |
| persisted_variables |
| prepared_statements_instances |
| processlist |
| replication_applier_configuration |
| replication_applier_filters |
| replication_applier_global_filters |
| replication_applier_status |
| replication_applier_status_by_coordinator |
| replication_applier_status_by_worker |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_connection_configuration |
| replication_connection_status |
| replication_group_communication_information |
| replication_group_configuration_version |
| replication_group_member_actions |
| replication_group_member_stats |
| replication_group_members |
| rwlock_instances |
| session_account_connect_attrs |
| session_connect_attrs |
| session_status |
| session_variables |
| setup_actors |
| setup_consumers |
| setup_instruments |
| setup_meters |
| setup_metrics |
| setup_objects |
| setup_threads |
| socket_instances |
| socket_summary_by_event_name |
| socket_summary_by_instance |
| status_by_account |
| status_by_host |
| status_by_thread |
| status_by_user |
| table_handles |
| table_io_waits_summary_by_index_usage |
| table_io_waits_summary_by_table |
| table_lock_waits_summary_by_table |
| threads |
| tls_channel_status |
| user_defined_functions |
| user_variables_by_thread |
| users |
| variables_by_thread |
| variables_info |
+------------------------------------------------------+
118 rows in set (0.0031 sec)MySQL localhost:33060+ ssl performance_schema SQL >
查看replication_group_members
MySQL localhost:33060+ ssl performance_schema SQL > select * from replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 7149de5f-34f4-11ef-91fc-005056aaaba0 | mysql-innodb03 | 3306 | ONLINE | SECONDARY | 8.4.0 | MySQL |
| group_replication_applier | 7970842a-34f4-11ef-9af0-005056aafb5f | mysql-innodb02 | 3306 | ONLINE | SECONDARY | 8.4.0 | MySQL |
| group_replication_applier | 8091d83c-34f4-11ef-aac6-005056aa2dba | mysql-innodb01 | 3306 | ONLINE | PRIMARY | 8.4.0 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.0010 sec)MySQL localhost:33060+ ssl performance_schema SQL > select CHANNEL_NAME,MEMBER_ID,MEMBER_HOST,MEMBER_STATE,MEMBER_ROLE from replication_group_members;
+---------------------------+--------------------------------------+-------------+--------------+-------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_STATE | MEMBER_ROLE |
+---------------------------+--------------------------------------+-------------+--------------+-------------+
| group_replication_applier | 7149de5f-34f4-11ef-91fc-005056aaaba0 | mysql-innodb03 | ONLINE | SECONDARY |
| group_replication_applier | 7970842a-34f4-11ef-9af0-005056aafb5f | mysql-innodb02 | ONLINE | SECONDARY |
| group_replication_applier | 8091d83c-34f4-11ef-aac6-005056aa2dba | mysql-innodb01 | ONLINE | PRIMARY |
+---------------------------+--------------------------------------+-------------+--------------+-------------+
3 rows in set (0.0017 sec)MySQL localhost:33060+ ssl performance_schema SQL >
至此，3节点mysql innodb集群部署已经完成。
部署mysql-router
安装mysql-router
以下操作在所有mysql-router节点执行
apt install -y mysql-router mysql-client
查看安装版本
root@mysql-router01:~# mysqlrouter -V
MySQL Router Ver 8.0.37 for Linux on x86_64 (MySQL Community - GPL)root@mysql-router01:~# mysql -V
mysql Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)
root@mysql-router01:~#
修改apparmor配置，末尾添加如下内容
root@mysql-router01:~# nano /etc/apparmor.d/usr.bin.mysqlrouter
......
# Allow directory for MySQL InnoDB cluster/var/lib/mysqlrouter/ rw,/var/lib/mysqlrouter/** rw,
}
重新加载apparmor服务
systemctl reload apparmor.service
连接innodb集群
mysqlrouter --bootstrap icadmin@mysql-innodb01:3306 --directory /var/lib/mysqlrouter \
--conf-bind-address 0.0.0.0 --user=mysqlrouter
命令输出示例
root@mysql-router01:~# mysqlrouter --bootstrap ic@innodb01:3306 --directory /var/lib/mysqlrouter --conf-bind-address 0.0.0.0 --user=mysqlrouter --force
Please enter MySQL password for ic:
# Bootstrapping MySQL Router 8.4.0 (MySQL Community - GPL) instance at '/var/lib/mysqlrouter'...- 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 /var/lib/mysqlrouter/mysqlrouter.conf# MySQL Router configured for the InnoDB Cluster 'my_innodb_cluster'After this MySQL Router has been started with the generated configuration$ mysqlrouter -c /var/lib/mysqlrouter/mysqlrouter.confInnoDB Cluster 'my_innodb_cluster' 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
查看监听端口
root@router01:~/data# ss -antulp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
udp UNCONN 0 0 127.0.0.53%lo:53 0.0.0.0:* users:(("systemd-resolve",pid=823,fd=13))
udp UNCONN 0 0 127.0.0.1:323 0.0.0.0:* users:(("chronyd",pid=865,fd=5))
udp UNCONN 0 0 [::1]:323 [::]:* users:(("chronyd",pid=865,fd=6))
tcp LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:* users:(("systemd-resolve",pid=823,fd=14))
tcp LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=900,fd=3))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=900,fd=4))
手动临时启动服务
root@mysql-router01:~# cd /var/lib/mysqlrouter/
root@mysql-router01:/var/lib/mysqlrouter# ./start.sh
root@mysql-router01:/var/lib/mysqlrouter# PID 4310 written to '/var/lib/mysqlrouter/mysqlrouter.pid'
stopping to log to the console. Continuing to log to filelog
^C
重新查看监听端口，mysql router读写端口为6446，mysql router只读端口为6447
root@mysql-router01:/var/lib/mysqlrouter# ss -tulnp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
udp UNCONN 0 0 127.0.0.53%lo:53 0.0.0.0:* users:(("systemd-resolve",pid=823,fd=13))
udp UNCONN 0 0 127.0.0.1:323 0.0.0.0:* users:(("chronyd",pid=865,fd=5))
udp UNCONN 0 0 [::1]:323 [::]:* users:(("chronyd",pid=865,fd=6))
tcp LISTEN 0 1024 0.0.0.0:6448 0.0.0.0:* users:(("mysqlrouter",pid=4310,fd=12))
tcp LISTEN 0 1024 0.0.0.0:6449 0.0.0.0:* users:(("mysqlrouter",pid=4310,fd=14))
tcp LISTEN 0 1024 0.0.0.0:6450 0.0.0.0:* users:(("mysqlrouter",pid=4310,fd=16))
tcp LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:* users:(("systemd-resolve",pid=823,fd=14))
tcp LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=900,fd=3))
tcp LISTEN 0 128 0.0.0.0:8443 0.0.0.0:* users:(("mysqlrouter",pid=4310,fd=10))
tcp LISTEN 0 1024 0.0.0.0:6446 0.0.0.0:* users:(("mysqlrouter",pid=4310,fd=13))
tcp LISTEN 0 1024 0.0.0.0:6447 0.0.0.0:* users:(("mysqlrouter",pid=4310,fd=15))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=900,fd=4))
root@mysql-router01:/var/lib/mysqlrouter#
查看配置文件监听端口
root@mysql-router01:~# cat /var/lib/mysqlrouter/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://my_innodb_cluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://my_innodb_cluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic
查看缓存的集群信息
root@mysql-router01:~# cat /var/lib/mysqlrouter/data/state.json
{"metadata-cache": {"group-replication-id": "806533fa-36ac-11ef-9f9b-005056aaaba0","cluster-metadata-servers": ["mysql://mysql-innodb03:3306","mysql://mysql-innodb02:3306","mysql://mysql-innodb01:3306"]},"version": "1.0.0"
}root@mysql-router01:~#
禁用默认的systemd服务，配置mysql-router服务开机自启动
systemctl stop mysqlrouter.service
rm -rf /etc/init.d/mysqlrouter
systemctl daemon-reload
新建systemd文件
cat >/etc/systemd/system/mysqlrouter.service<<EOF
[Unit]
Description=MySQL Router
After=network.target
After=syslog.target
[Service]
Type=notify
User=mysqlrouter
Group=mysqlrouter
# Start main service
ExecStart=/usr/bin/mysqlrouter -c /var/lib/mysqlrouter/mysqlrouter.conf --user=mysqlrouter
# Sets open_files_limit
LimitNOFILE = 10000
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
启动服务
systemctl enable --now mysqlrouter.service
连接数据库
创建示例用户
root@mysql-innodb01:~# mysql -uroot -p
Enter password: mysql> create user app@'%' identified by 'App@123456';
Query OK, 0 rows affected (0.02 sec)mysql> grant all on *.* to app@'%';
Query OK, 0 rows affected (0.02 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)mysql>
通过mysql-router连接到读写数据库
root@mysql-router01:~# mysql -h127.0.0.1 -P 6446 -u app -p'App@123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 8.4.0-router MySQL Community Server - GPLCopyright (c) 2000, 2024, 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> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql-innodb01 |
+------------+
1 row in set (0.00 sec)mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)mysql>
通过mysql-router连接到只读数据库
root@mysql-router01:~# mysql -h127.0.0.1 -P 6447 -u app -p'App@123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 8.4.0-router MySQL Community Server - GPLCopyright (c) 2000, 2024, 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> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql-innodb02 |
+------------+
1 row in set (0.00 sec)mysql> create database mydb1;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>
mysql> select user, host from mysql.user;
+---------------------------------+-----------+
| user | host |
+---------------------------------+-----------+
| app | % |
| ic | % |
| mysql_innodb_cluster_2444823474 | % |
| mysql_innodb_cluster_2728360213 | % |
| mysql_innodb_cluster_3109234783 | % |
| mysql_router1_d99tpklyj1n3 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------------------------+-----------+
10 rows in set (0.00 sec)mysql> \q
Bye
部署负载局衡器
以下操作在所有lb节点执行。
配置主机名
hostnamectl set-hostname lb01
hostnamectl set-hostname lb02
安装haproxy和keepalived
apt install -y haproxy keepalived
创建haproxy配置文件，两个节点配置相同
root@lb01:~# cat /etc/haproxy/haproxy.cfg
# HAProxy Configuration for lb01
globallog /dev/log local0log /dev/log local1 noticechroot /var/lib/haproxystats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listenersstats timeout 30suser haproxygroup haproxydaemondefaultslog globalmode tcpoption tcplogoption dontlognulltimeout connect 5000timeout client 50000timeout server 50000frontend db_rw_frontbind *:6446default_backend db_rw_backbackend db_rw_backbalance roundrobinserver router01 192.168.72.41:6446 checkserver router02 192.168.72.42:6446 checkfrontend db_ro_frontbind *:6447default_backend db_ro_backbackend db_ro_backbalance roundrobinserver router01 192.168.72.41:6447 checkserver router02 192.168.72.42:6447 check
创建keepalived配置文件，两个节点配置相同
root@lb01:~# cat /etc/keepalived/keepalived.conf
# Keepalived Configuration for lb01
global_defs {router_id 51script_user rootenable_script_security
}vrrp_script check_haproxy {script "/usr/bin/killall -0 haproxy"interval 2fall 2 # require 2 failures for KOrise 2 # require 2 successes for OK
}vrrp_instance db_vip {state BACKUPinterface ens33virtual_router_id 51priority 100nopreemptadvert_int 1authentication {auth_type PASSauth_pass secret456}virtual_ipaddress {192.168.72.200}track_script {check_haproxy}
}
启动服务
systemctl restart keepalived.service
systemctl restart haproxy.service
验证通过VIP连接读写端口
root@mysql-router01:~# mysql -h192.168.72.200 -P 6446 -u app -p'App@123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 392427
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, 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>
验证通过VIP连接只读端口
root@mysql-router01:~# mysql -h192.168.72.200 -P 6447 -u app -p'App@123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, 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>
集群角色管理
切换为多个Primary模式
MySQL localhost:33060+ ssl JS > herc.switchToMultiPrimaryMode()
切换为单个Primary模式
MySQL localhost:33060+ ssl JS > herc.switchToSinglePrimaryMode()
指定节点切换为Primary模式
MySQL localhost:33060+ ssl JS > herc.setPrimaryInstance('mysql-innodb02')
从集群删除节点
MySQL localhost:33060+ ssl JS > herc.removeInstance('mysql-innodb03')