个人日记：MySQL InnoDB Cluster MySQL Router 配置

#当前的 MySQL InnoDB Cluster 运行非常正常（三节点 Single-Primary 模式），可以开始做高可用性、容错、权重和主从切换的实验。

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


#Group Replication 要求: 如果启用了 MySQL Group Replication，它要求每个表必须有明确的主键。
#在 host31（主节点）连接并尝试写入：
mysql -uroot -h10.0.0.31 -P3306 -pMySQL8.0
CREATE DATABASE testdb;

CREATE TABLE testdb.t1 (
id INT PRIMARY KEY
);

INSERT INTO testdb.t1 VALUES (2025);


mysql -uroot -h10.0.0.32 -P3306 -p
MySQL8.0
------------------------------------------------------------------------------------------------------------
mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id |
+------+
| 2025 |
+------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdb.t1 VALUES(2);
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>
------------------------------------------------------------------------------------------------------------

实验：Practice 14-2: Deploying MySQL Router and Testing the Cluster

MySQL Router是处于应用client和dbserver之间的轻量级代理程序，它能检测，分析和转发查询到后端[数据库]实例，并把结果返回给client。是mysql-proxy的一个替代品。其架构图和功能如下。

（1）Router实现读写分离，程序不是直接连接数据库IP，而是固定连接到mysql router。MySQL Router对前端应用是透明的。应用程序把MySQL Router当作是普通的mysql实例，把查询发给MySQL Router,而MySQL Router会把查询结果返回给前端的应用程序。

（2）从[数据库服务]器故障，业务可以正常运行。由MySQL Router来进行自动下线不可用服务器。程序配置不需要任何修改。

（3）主数据库故障，由MySQL Router来决定主从自动切换，业务可以正常访问。程序配置不需要做任何修改。

#MySQL Router 出于安全考虑，MySQL Router 禁止直接以 root 用户初始化（避免配置文件权限过高）
[root@pg137 ~]# cat /etc/passwd | grep mysql
mysql:x:996:1002::/home/mysql:/bin/false
mysql-pam:x:1002:1003::/home/mysql-pam:/bin/bash
[root@pg137 ~]# sudo usermod -s /bin/bash mysql
[root@pg137 ~]# cat /etc/passwd | grep mysql
mysql:x:996:1002::/home/mysql:/bin/bash
mysql-pam:x:1002:1003::/home/mysql-pam:/bin/bash

[root@pg137 ~]# su - mysql
Last login: Sun Jun 29 15:01:40 CST 2025 on pts/3
-bash-4.2$ whoami
mysql
-bash-4.2$

echo 'export PATH=$PATH:/usr/local/mysql/bin' >> ~/.bashrc # 对当前用户生效

echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
步骤 2：立即生效
bash
source ~/.bashrc # 当前用户
# 或
source /etc/profile # 全局生效

mysqlrouter --version

-bash-4.2$ mysql -uroot -h10.0.0.31 -P3306 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 178
Server version: 8.0.42 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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 * FROM mysql_innodb_cluster_metadata.schema_version;
+-------+-------+-------+
| major | minor | patch |
+-------+-------+-------+
| 2 | 2 | 0 |
+-------+-------+-------+
1 row in set (0.00 sec)

mysql> \q
Bye
-bash-4.2$ mysqlrouter --version
MySQL Router Ver 8.0.18 for el7 on x86_64 (MySQL Community - GPL)
-bash-4.2$
////////////////////////////////////////////////////////////////////////////////////////////////////////////
关键版本信息
MySQL Server版本: 8.0.42
InnoDB Cluster元数据版本: 2.2.0
MySQL Router版本: 8.0.18
问题诊断
版本冲突：
MySQL Router 8.0.18 最高仅支持元数据版本1.0.1
集群元数据版本是2.2.0，需要 Router 8.0.22+

# 对于CentOS/RHEL 7

# 验证版本
mysqlrouter --version
# 应显示: MySQL Router Ver 8.0.32 for el7 on x86_64

#手动下载并安装
wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-community-8.0.32-1.el7.x86_64.rpm
sudo rpm -ivh mysql-router-community-8.0.32-1.el7.x86_64.rpm

[root@pg137 opt]# mysqlrouter --version
MySQL Router Ver 8.0.18 for el7 on x86_64 (MySQL Community - GPL)
[root@pg137 opt]# type -a mysqlrouter
mysqlrouter is /usr/bin/mysqlrouter
mysqlrouter is /usr/local/mysql/bin/mysqlrouter
[root@pg137 opt]# /usr/local/mysql/bin/mysqlrouter --version
MySQL Router Ver 8.0.18 for el7 on x86_64 (MySQL Community - GPL)
[root@pg137 opt]# su - mysql
Last login: Sun Jun 29 15:06:49 CST 2025 on pts/3
-bash-4.2$ mysqlrouter --version
MySQL Router Ver 8.0.32 for Linux on x86_64 (MySQL Community - GPL)
////////////////////////////////////////////////////////////////////////////////////////////////////////////


#mysql user
#mysqlrouter --bootstrap 10.0.0.31 --directory=mysqlrouter

-bash-4.2$ mysqlrouter --bootstrap 10.0.0.31 --directory=mysqlrouter
Please enter MySQL password for root:
# Bootstrapping MySQL Router instance at '/home/mysql/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 /home/mysql/mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'myCluster'

After this MySQL Router has been started with the generated configuration

$ mysqlrouter -c /home/mysql/mysqlrouter/mysqlrouter.conf

InnoDB Cluster 'myCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections: localhost:6449

-bash-4.2$

#mysqlrouter -c /home/mysql/mysqlrouter/mysqlrouter.conf &

-bash-4.2$ mysqlrouter -c /home/mysql/mysqlrouter/mysqlrouter.conf &
[1] 24853
-bash-4.2$ mysqlsh --uri root@localhost:6446
Please provide the password for 'root@localhost:6446': ********
Save password for 'root@localhost:6446'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.4.5

Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@localhost:6446'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 249
Server version: 8.0.42 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:6446 ssl SQL >
MySQL localhost:6446 ssl SQL > SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| host31 |
+------------+
1 row in set (0.0010 sec)
MySQL localhost:6446 ssl SQL >

MySQL localhost:6446 ssl SQL > \sql
MySQL localhost:6446 ssl SQL > SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| host31 |
+------------+
1 row in set (0.0010 sec)
MySQL localhost:6446 ssl SQL > CREATE DATABASE clustertest;
Query OK, 1 row affected (0.0211 sec)
MySQL localhost:6446 ssl SQL > USE clustertest;
Default schema set to `clustertest`.
Fetching global names, object names from `clustertest` for auto-completion... Press ^C to stop.

MySQL localhost:6446 ssl clustertest SQL > CREATE TABLE tbl_cluster (
-> ID INT NOT NULL AUTO_INCREMENT,
-> SomeText VARCHAR(255),
-> PRIMARY KEY(ID)
-> );
Query OK, 0 rows affected (0.0374 sec)
MySQL localhost:6446 ssl clustertest SQL >
MySQL localhost:6446 ssl clustertest SQL > INSERT INTO tbl_cluster (SomeText) VALUES ('MySQL InnoDB Cluster');
Query OK, 1 row affected (0.0058 sec)
MySQL localhost:6446 ssl clustertest SQL >



-bash-4.2$ mysqlsh --uri root@localhost:6447
Please provide the password for 'root@localhost:6447': ********
Save password for 'root@localhost:6447'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.4.5

Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@localhost:6447'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 102
Server version: 8.0.42 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:6447 ssl SQL > SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| host33 |
+------------+
1 row in set (0.0009 sec)
MySQL localhost:6447 ssl SQL > USE clustertest;
Default schema set to `clustertest`.
Fetching global names, object names from `clustertest` for auto-completion... Press ^C to stop.
MySQL localhost:6447 ssl clustertest SQL > SELECT * FROM tbl_cluster;
+----+----------------------+
| ID | SomeText |
+----+----------------------+
| 1 | MySQL InnoDB Cluster |
+----+----------------------+
1 row in set (0.0011 sec)
MySQL localhost:6447 ssl clustertest SQL >

实验：Practice 14-3: Testing High Availability 

[root@pg137 opt]# id
uid=0(root) gid=0(root) groups=0(root) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[root@pg137 opt]#
[root@pg137 opt]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
779577fd3499 container-registry.oracle.com/mysql/community-server:8.0 "/entrypoint.sh mysq…" 6 hours ago Up 5 hours 3306/tcp, 33060-33061/tcp mysql33
b6946a9873bb container-registry.oracle.com/mysql/community-server:8.0 "/entrypoint.sh mysq…" 6 hours ago Up 5 hours 3306/tcp, 33060-33061/tcp mysql32
0ae04a459017 container-registry.oracle.com/mysql/community-server:8.0 "/entrypoint.sh mysq…" 6 hours ago Up 6 hours 3306/tcp, 33060-33061/tcp mysql31
[root@pg137 opt]# docker stop mysql31
mysql31
[root@pg137 opt]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
779577fd3499 container-registry.oracle.com/mysql/community-server:8.0 "/entrypoint.sh mysq…" 6 hours ago Up 5 hours 3306/tcp, 33060-33061/tcp mysql33
b6946a9873bb container-registry.oracle.com/mysql/community-server:8.0 "/entrypoint.sh mysq…" 6 hours ago Up 5 hours 3306/tcp, 33060-33061/tcp mysql32

#mysqlsh --uri root@localhost:6447

[root@pg137 opt]# su - mysql
Last login: Sun Jun 29 15:22:52 CST 2025 on pts/3
-bash-4.2$ mysqlsh --uri root@localhost:6447
MySQL Shell 8.4.5

Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@localhost:6447'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 125
Server version: 8.0.42 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:6447 ssl SQL >

MySQL localhost:6447 ssl SQL > \js
Switching to JavaScript mode...
MySQL localhost:6447 ssl JS > var cluster = dba.getCluster("myCluster")
MySQL localhost:6447 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "host32:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE_PARTIAL",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.",
"topology": {
"host31:3306": {
"address": "host31:3306",
"memberRole": "SECONDARY",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"shellConnectError": "MySQL Error 2003: Could not open connection to 'host31:3306': Can't connect to MySQL server on 'host31:3306' (113)",
"status": "(MISSING)"
},
"host32:3306": {
"address": "host32:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.42"
},
"host33:3306": {
"address": "host33:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.42"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "host32:3306"
}
MySQL localhost:6447 ssl JS >

#***The cluster has elected MySQL server running on host32 as the new primary.
#***host31 reports as MISSING and the cluster, having only two available members, cannot tolerate another instance failure.

#启动数据库mysql131
[root@pg137 ~]# docker start mysql31
mysql31
[root@pg137 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
779577fd3499 container-registry.oracle.com/mysql/community-server:8.0 "/entrypoint.sh mysq…" 6 hours ago Up 5 hours 3306/tcp, 33060-33061/tcp mysql33
b6946a9873bb container-registry.oracle.com/mysql/community-server:8.0 "/entrypoint.sh mysq…" 6 hours ago Up 5 hours 3306/tcp, 33060-33061/tcp mysql32
0ae04a459017 container-registry.oracle.com/mysql/community-server:8.0 "/entrypoint.sh mysq…" 6 hours ago Up 2 seconds 3306/tcp, 33060-33061/tcp mysql31
[root@pg137 ~]#


#检查cluster状态
MySQL localhost:6447 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "host32:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"host31:3306": {
"address": "host31:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.42"
},
"host32:3306": {
"address": "host32:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.42"
},
"host33:3306": {
"address": "host33:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.42"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "host32:3306"
}
MySQL localhost:6447 ssl JS >
#***The MySQL server running in host31 is back online, and the cluster is tolerant of a single failure again. host32 is still the primary (read-write) instance.


# cluster.dissolve()
#解散 InnoDB 集群,连接到读写实例，例如单个主集群中的主集群,并使用命令。
#This removes all metadata and configuration associated with the cluster, and disables Group Replication on the instances. Any data that was replicated between the instances is not removed.