热门标签
热门文章
- 1旷野之间8 - LLMOps 与 MLOps操作化 AI 模型
- 2delphi7无法编译已经使用的单元怎么解决_使用Python3.7结合Redisearch代替ElasticSearch实现的全文检索功能服务...
- 3如何免费使用GPT-4o？国内AI大模型相比又怎样？汇总来了！_免费大模型4.0
- 4OJ1000--1005（c语言）
- 5深入理解MySQL索引：原理、数据结构与优化策略_mysql索引策略
- 6python通过pypiwin32操作PPT_python播放ppt并打印页码
- 7Spring Cloud Eureka 简介_springcloud的eureka是什么意思
- 8网站及服务器工作原理,HTTP协议及其工作原理介绍
- 9力扣-2600-k件物品最大和（python）_leetcode2600-python
- 10DNS服务的部署_域名服务器部署
当前位置: article > 正文
MySQL InnoDB Cluster 高可用集群部署
作者：代码探险家 | 2024-08-20 08:23:34
赞
踩
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
- 1
- 2
- 3
- 4
- 5
分别在每个节点配置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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
添加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
- 1
- 2
- 3
安装innodb集群
以下操作在所有innodb节点执行。
在安装过程中，您需要为MySQL root用户设置密码。
apt update -y
apt install -y mysql-server mysql-shell
- 1
- 2
查看安装版本
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))
- 1
- 2
- 3
- 4
- 5
在innodb01节点执行
连接到mysql-shell
root@mysql-innodb01:~# mysqlsh
- 1
连接到数据库，输入数据库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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
或者直接连接
mysqlsh -uroot -pMysql@123456 -h localhost
- 1
查看数据库信息
MySQL localhost:33060+ ssl SQL > \sql
MySQL localhost:33060+ ssl SQL > show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.0011 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
或者切换到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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
为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) Cancel Please 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]: y Creating 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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
或者使用以下命令
MySQL localhost:33060+ ssl JS > dba.configureInstance('root@localhost:3306', {clusterAdmin: "'icadmin'@'%'", clusterAdminPassword: 'icadminPassw0rd!'});
- 1
查看数据库信息，查看本节点数据库实例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.0 Error 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.0 Connection 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
查看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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
查看生成的mysqld-auto.cnf配置文件
root@innodb01:~# cat /var/lib/mysql/mysqld-auto.cnf
- 1
其中确认参数如下
gtid_mode": {"Value": "ON"
server_id": {"Value": "2728360213
- 1
- 2
重新连接到集群账号
root@mysql-innodb01:~# fg
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 12 (X protocol)
Server version: 8.4.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
检查实例配置，确认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 detected Checking instance configuration... Instance configuration is compatible with InnoDB cluster The instance 'mysql-innodb01:3306' is valid for InnoDB Cluster usage. { "status": "ok" } MySQL localhost:33060+ ssl JS >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
连接到innodb02
连接到mysql-shell
root@mysql-innodb02:~# mysqlsh
MySQL Shell 8.4.0
Copyright (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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
连接到数据库
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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
为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) Cancel Please 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]: y Creating 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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
连接到集群账号
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.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
检查实例
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 detected Checking instance configuration... Instance configuration is compatible with InnoDB cluster The instance 'mysql-innodb02:3306' is valid for InnoDB Cluster usage. { "status": "ok" } MySQL localhost:33060+ ssl JS >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
连接到innodb03
root@innodb03:~# mysqlsh
MySQL Shell 8.4.0
Copyright (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.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
连接到数据库账号
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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
为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) Cancel Please 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]: y Creating 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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
连接到集群账号
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.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
检查实例
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 detected Checking instance configuration... Instance configuration is compatible with InnoDB cluster The instance 'mysql-innodb03:3306' is valid for InnoDB Cluster usage. { "status": "ok" } MySQL localhost:33060+ ssl JS >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
创建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:3306 Instance 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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
查看集群状态
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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
添加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:3306 Instance 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% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed NOTE: 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.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
检查集群状态
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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
在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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
在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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
添加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:3306 Instance 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% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed NOTE: 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.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
查看集群状态
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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
关键字段如下:
"status": "OK" 表示集群状态是正常的
"topologyMode": "Single-Primary" 表示是单主模式
"mode": "R/W" 表示可读可写
"mode": "R/O" 表示只读
- 1
- 2
- 3
- 4
查看库及表
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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
查看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 >
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
至此，3节点mysql innodb集群部署已经完成。
部署mysql-router
安装mysql-router
以下操作在所有mysql-router节点执行
apt install -y mysql-router mysql-client
- 1
查看安装版本
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:~#
- 1
- 2
- 3
- 4
- 5
- 6
修改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,
}
- 1
- 2
- 3
- 4
- 5
- 6
重新加载apparmor服务
systemctl reload apparmor.service
- 1
连接innodb集群
mysqlrouter --bootstrap icadmin@mysql-innodb01:3306 --directory /var/lib/mysqlrouter \
--conf-bind-address 0.0.0.0 --user=mysqlrouter
- 1
- 2
命令输出示例
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.conf InnoDB 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
查看监听端口
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))
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
手动临时启动服务
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
- 1
- 2
- 3
- 4
- 5
重新查看监听端口，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#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
查看配置文件监听端口
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
查看缓存的集群信息
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:~#
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
禁用默认的systemd服务，配置mysql-router服务开机自启动
systemctl stop mysqlrouter.service
rm -rf /etc/init.d/mysqlrouter
systemctl daemon-reload
- 1
- 2
- 3
新建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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
启动服务
systemctl enable --now mysqlrouter.service
- 1
连接数据库
创建示例用户
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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
通过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 - GPL Copyright (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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
通过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 - GPL Copyright (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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
部署负载局衡器
以下操作在所有lb节点执行。
配置主机名
hostnamectl set-hostname lb01
hostnamectl set-hostname lb02
- 1
- 2
安装haproxy和keepalived
apt install -y haproxy keepalived
- 1
创建haproxy配置文件，两个节点配置相同
root@lb01:~# cat /etc/haproxy/haproxy.cfg # HAProxy Configuration for lb01 global log /dev/log local0 log /dev/log local1 notice chroot /var/lib/haproxy stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners stats timeout 30s user haproxy group haproxy daemon defaults log global mode tcp option tcplog option dontlognull timeout connect 5000 timeout client 50000 timeout server 50000 frontend db_rw_front bind *:6446 default_backend db_rw_back backend db_rw_back balance roundrobin server router01 192.168.72.41:6446 check server router02 192.168.72.42:6446 check frontend db_ro_front bind *:6447 default_backend db_ro_back backend db_ro_back balance roundrobin server router01 192.168.72.41:6447 check server router02 192.168.72.42:6447 check
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
创建keepalived配置文件，两个节点配置相同
root@lb01:~# cat /etc/keepalived/keepalived.conf # Keepalived Configuration for lb01 global_defs { router_id 51 script_user root enable_script_security } vrrp_script check_haproxy { script "/usr/bin/killall -0 haproxy" interval 2 fall 2 # require 2 failures for KO rise 2 # require 2 successes for OK } vrrp_instance db_vip { state BACKUP interface ens33 virtual_router_id 51 priority 100 nopreempt advert_int 1 authentication { auth_type PASS auth_pass secret456 } virtual_ipaddress { 192.168.72.200 } track_script { check_haproxy } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
启动服务
systemctl restart keepalived.service
systemctl restart haproxy.service
- 1
- 2
验证通过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 - GPL
Copyright (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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
验证通过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 - GPL
Copyright (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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
集群角色管理
切换为多个Primary模式
MySQL localhost:33060+ ssl JS > herc.switchToMultiPrimaryMode()
- 1
切换为单个Primary模式
MySQL localhost:33060+ ssl JS > herc.switchToSinglePrimaryMode()
- 1
指定节点切换为Primary模式
MySQL localhost:33060+ ssl JS > herc.setPrimaryInstance('mysql-innodb02')
- 1
从集群删除节点
MySQL localhost:33060+ ssl JS > herc.removeInstance('mysql-innodb03')
- 1
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/代码探险家/article/detail/1006109
推荐阅读
相关标签
