4.1路由验证
4.1.1测试通过MySQL Router连接6446 RW端口，查看连接的是否为primary
[root@node4 mysqlrouter]# for ((i=0;i<p><strong>测试结果：</strong> 使用6446 RW端口会访问到后端InnoDB Cluster的primary</p><h4 id="strong-测试通过MySQL-Router连接-RO端口-查看连接的是否为seconde节点-rr算法是否生效-strong"><strong>4.1.2测试通过MySQL Router连接6447 RO端口，查看连接的是否为seconde节点，rr算法是否生效</strong></h4><pre class="brush:php;toolbar:false">[root@node4 mysqlrouter]# for ((i=0;i<p><strong>测试结果：</strong> 使用6447 RO端口会连接到后端InnoDB Cluster集群中所有的seconde节点，并且以rr(round-robin)的策略调度</p><h3 id="strong-高可用测试-strong"><strong>4.2高可用测试</strong></h3><h4 id="strong-模拟其中一台seconde节点宕机-查看MySQL-Router是否会自动取消该节点的路由-strong"><strong>4.2.1模拟其中一台seconde节点宕机，查看MySQL Router是否会自动取消该节点的路由</strong></h4><p>(1) 停掉一台seconde节点</p><pre class="brush:php;toolbar:false">[root@oratest52 ~]# systemctl status mysql
[root@oratest52 ~]# systemctl stop mysql
[root@oratest52 ~]# systemctl status mysql
(2) 在InnoDB Cluster中查看集群状态，可以看到oratest52已经处于MISSING状态
MySQL 172.16.9.51:3306 JS > cluster.status()
{
"clusterName": "st",
"defaultReplicaSet": {
"name": "default",
"primary": "oratest51:3306",
"ssl": "DISABLED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
"topology": {
"oratest51:3306": {
"address": "oratest61:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"oratest52:3306": {
"address": "oratest52:3306",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"shellConnectError": "MySQL Error 2003 (HY000): Can't connect to MySQL server on 'oratest52' (111)",
"status": "(MISSING)"
},
"test61:3306": {
"address": "test51:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "test61:3306"
}(3) 在MGR中查看GR状态，可以看到已经没有oratest52这台机器
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 48cdca42-a386-11e9-95f1-0050569d2587 | test61 | 3306 | ONLINE |
| group_replication_applier | e9ee5267-a386-11e9-ac35-0050569d9ef5 | oratest51 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
(4) 测试通过6447 RO端口连接，查看是否会自动剔除宕机的oratest52节点
[root@node4 ~]# for ((i=0;i<p><strong>测试结果：</strong> MySQL Router会自动剔除宕机的oratest52节点，read请求只会转发到后端正常的MySQL实例，过程无需人工干预。</p><h4 id="strong-启动上一步中停止的MySQL实例-确认MGR和InnoDB-Cluster一切正常后-查看MySQL-Router是否会自动加入这台已恢复的实例进行转发-strong"><strong>4.2.2启动上一步中停止的MySQL实例，确认MGR和InnoDB Cluster一切正常后，查看MySQL Router是否会自动加入这台已恢复的实例进行转发</strong></h4><p>(1) 启动oratest52的MySQL服务</p><pre class="brush:php;toolbar:false">[root@oratest52 ~]# systemctl start mysql
(2)启动MGR，并确认MGR状态正常
mysql> start group_replication;
Query OK, 0 rows affected (2.92 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 48cdca42-a386-11e9-95f1-0050569d2587 | test61 | 3306 | ONLINE |
| group_replication_applier | 50bc1160-a386-11e9-92c9-0050569dc0da | oratest52 | 3306 | ONLINE |
| group_replication_applier | e9ee5267-a386-11e9-ac35-0050569d9ef5 | oratest51 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
(3) 检查InnoDB Cluster是否正常
MySQL 172.16.9.51:3306 JS > cluster.status()
{
"clusterName": "st",
"defaultReplicaSet": {
"name": "default",
"primary": "oratest51:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"oratest51:3306": {
"address": "oratest61:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"oratest52:3306": {
"address": "oratest52:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"test61:3306": {
"address": "test51:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "test61:3306"
}(4) 查看恢复正常的实例，能不能自动加入MySQL Router进行转发，可以看到oratest52可以被正常调度
[root@node4 mysqlrouter]# for ((i=0;i<p><strong>测试结果：</strong> 恢复正常的MySQL实例可以自动加入MySQL Router并进行转发，无需人工干预。</p><h4 id="strong-模拟停掉primary节点-测试MySQL-Router如何处理发送RW端口的请求-MySQL-Router怎样获取新的primary节点-strong"><strong>4.2.3模拟停掉primary节点，测试MySQL Router如何处理发送RW端口的请求，MySQL Router怎样获取新的primary节点</strong></h4><p>(1) 当前primary节点是oratest51，在primary节点上停止MySQL，确认停止成功</p><pre class="brush:php;toolbar:false">[root@oratest51 data]# systemctl status mysql
[root@oratest51 data]# systemctl stop mysql
[root@oratest51 data]# systemctl status mysql
[root@oratest51 data]# ps -ef |grep mysql
(2) 查看InnoDB Cluster状态，可以看到oratest51已经处于MISSING状态，primary节点已经切换到了test61
MySQL 172.16.9.61:3306 JS > cluster.status();
{
"clusterName": "st",
"defaultReplicaSet": {
"name": "default",
"primary": "test61:3306",
"ssl": "DISABLED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
"topology": {
"oratest51:3306": {
"address": "oratest51:3306",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"shellConnectError": "MySQL Error 2003 (HY000): Can't connect to MySQL server on 'oratest51' (111)",
"status": "(MISSING)"
},
"oratest52:3306": {
"address": "oratest52:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"test61:3306": {
"address": "test61:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "test61:3306"
}(3) 测试连接到RW 6446端口，查看是否可以正常连接到新的primary节点test61，并且写入数据否正常
[root@node2 mysqlrouter]# for ((i=0;i<p><strong>测试结果：</strong> 当primary节点挂掉之后，MySQL Router会自动去连接新的primary节点</p><h4 id="strong-启动上一步中停掉的oratest-将状态恢复正常-测试MySQL-Router能不能自动加入这台已恢复的节点并调度-strong"><strong>4.2.4启动上一步中停掉的oratest51，将状态恢复正常，测试MySQL Router能不能自动加入这台已恢复的节点并调度</strong></h4><p>(1) 启动oratest51，将状态恢复正常</p><pre class="brush:php;toolbar:false">[root@oratest51 ~]# systemctl start mysql
[root@oratest51 ~]# systemctl status mysql
[root@oratest51 ~]# mysql -uroot -p123456
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID |MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+------------+-------------+--------------+
| group_replication_applier | 48cdca42-a386-11e9-95f1-0050569d2587 |test61 | 3306 | ONLINE |
| group_replication_applier | 50bc1160-a386-11e9-92c9-0050569dc0da |oratest52 | 3306 | ONLINE |
| group_replication_applier | e9ee5267-a386-11e9-ac35-0050569d9ef5 |oratest51 | 3306 | ONLINE |
+---------------------------+--------------------------------------+------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> quit
[root@oratest51 local]# mysqlsh
MySQL JS > shell.connect('root@172.16.9.51:3306')
MySQL 172.16.9.51:3306 JS > var cluster = dba.getCluster()
MySQL 172.16.9.51:3306 JS > cluster.status();
{
"clusterName": "st",
"defaultReplicaSet": {
"name": "default",
"primary": "test61:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"oratest51:3306": {
"address": "oratest51:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"oratest52:3306": {
"address": "oratest52:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"test61:3306": {
"address": "test61:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "test61:3306"
}(2) 测试RO端口的Router调度
[root@node4 mysqlrouter]# for ((i=0;i<p><strong>测试结果：</strong> 恢复正常的实例加入集群后，会自动加入到MySQL Router并进行调度</p><h2 id="strong-五-运维相关-strong"><strong>五、运维相关</strong></h2><h3 id="strong-数据库节点变更-strong"><strong>5.1数据库节点变更</strong></h3><p>MySQL Router在初始化配置的时候是连接到集群节点读取集群的元数据的。如果在集群中新增或减少节点，需要同步更新MySQL Router的配置，否则无法生效</p><pre class="brush:php;toolbar:false"># mysqlrouter --bootstrap 172.16.9.51:3306 --directory /data/mysqlrouter --user=root --conf-use-sockets --force
更新了MySQL Router的配置的配置，需要重启MySQL Router：
#systemctl restart mysqlrouter
#systemctl status mysqlrouter
六、问题汇总
6.1初始化MySQL Router前，需要停止group_replication
报错如下
[root@oratest51 data]# mysqlrouter --bootstrap root@172.16.9.51:3310 --directory /data/mysqlrouter --conf-use-sockets --user=root
Please enter MySQL password for root:
Error: Unable to connect to the metadata server: Error connecting to MySQL server at 172.16.9.51:3310: Can't connect to MySQL server on '172.16.9.51' (111) (2003)
原因： 需要配置连接用户的权限
解决办法： 给连接用户配置所有权限，如下：
mysql> grant all privileges on *.* to root@'%' identified by "123456";
mysql> flush privileges;
[root@oratest51 data]# mysqlrouter --bootstrap root@172.16.9.51:3306 --directory /data/mysqlrouter --conf-use-sockets --user=root
Please enter MySQL password for root:
Error: Expected MySQL Server '172.16.9.51:3306' to contain the metadata of MySQL InnoDB Cluster, but the schema does not exist.
Checking version of the metadata schema failed with: Error executing MySQL query: Table 'mysql_innodb_cluster_metadata.schema_version' doesn't exist (1146)
See https://dev.mysql.com/doc/refman/en/mysql-innodb-cluster-creating.html for instructions on setting up a MySQL Server to act as an InnoDB Cluster Metadata server
原因： 没有创建InnoDB Cluster集群，创建InnoDB Cluster集群后会自动生成这张表。mysql_innodb_cluster_metadata.schema_version表的用途是MySQL Router在进行调度分配的时候，需要读取这张表的内容来做调度策略。
解决办法： 创建InnoDB Cluster集群
6.4启动MySQL Router hang住不动，查看日志报错Error: bootstrap_server_addresses is not allowed when dynamic state file is used
具体如下：
[root@node2 log]# tailf /root/mysqlrouter/log/mysqlrouter.log
2019-08-02 15:37:52 routing INFO [7f9721e2d700] [routing:st_default_ro] started: listening using /root/mysqlrouter/mysqlro.sock
2019-08-02 15:37:52 routing INFO [7f972162c700] [routing:st_default_rw] started: listening using /root/mysqlrouter/mysql.sock
2019-08-02 15:37:52 routing INFO [7f9720e2b700] [routing:st_default_x_ro] started: listening using /root/mysqlrouter/mysqlxro.sock
2019-08-02 15:37:52 routing INFO [7f9713fff700] [routing:st_default_x_rw] started: listening on 0.0.0.0:64460, routing strategy = first-available
2019-08-02 15:37:52 routing INFO [7f9713fff700] [routing:st_default_x_rw] started: listening using /root/mysqlrouter/mysqlx.sock
2019-08-02 15:37:52 routing INFO [7f9713fff700] [routing:st_default_x_rw] stopped
2019-08-02 15:37:52 routing INFO [7f9721e2d700] [routing:st_default_ro] stopped
2019-08-02 15:37:52 routing INFO [7f972162c700] [routing:st_default_rw] stopped
2019-08-02 15:37:52 routing INFO [7f9720e2b700] [routing:st_default_x_ro] stopped
2019-08-02 15:37:52 main ERROR [7f9726f1b880] Error: bootstrap_server_addresses is not allowed when dynamic state file is used
原因： 开启了dynamic_state
解决办法： 在配置文件中注释掉dynamic_state所在行，例如下列第11行
1 # File automatically generated during MySQL Router bootstrap
2 [DEFAULT]
3 user=root
4 logging_folder=/root/mysqlrouter/log
5 runtime_folder=/root/mysqlrouter/run
6 data_folder=/root/mysqlrouter/data
7 keyring_path=/root/mysqlrouter/data/keyring
8 master_key_path=/root/mysqlrouter/mysqlrouter.key
9 connect_timeout=15
10 read_timeout=30
11 #dynamic_state=/root/mysqlrouter/data/state.json
6.5启动MySQL Router hang住不动，查看日志报错Unable to fetch live group_replication member data from any server in replicaset 'default'
具体如下：
[root@node2 log]# tailf /root/mysqlrouter/log/mysqlrouter.log
2019-08-02 15:46:41 metadata_cache WARNING [7f3030405700] While updating metadata, could not establish a connection to replicaset 'default' through test61:3306
2019-08-02 15:46:51 metadata_cache WARNING [7f3030405700] While updating metadata, could not establish a connection to replicaset 'default' through oratest52:3306
2019-08-02 15:47:01 metadata_cache WARNING [7f3030405700] While updating metadata, could not establish a connection to replicaset 'default' through oratest51:3306
2019-08-02 15:47:01 metadata_cache ERROR [7f3030405700] Unable to fetch live group_replication member data from any server in replicaset 'default'
原因： MySQL Router所在节点没有配置本机DNS解析
解决办法： 在/etc/hosts中添加MGR实例的解析
七、个人总结
【相关推荐：mysql视频教程】
MySQL：世界で最も人気のあるデータベースの紹介Apr 12, 2025 am 12:18 AM
MySQLの重要性：データストレージと管理Apr 12, 2025 am 12:18 AM
なぜMySQLを使用するのですか？利点と利点Apr 12, 2025 am 12:17 AM
InnoDBロックメカニズム（共有ロック、排他的ロック、意図ロック、レコードロック、ギャップロック、次のキーロック）を説明します。Apr 12, 2025 am 12:16 AM
貧弱なMySQLクエリパフォーマンスの一般的な原因は何ですか？Apr 12, 2025 am 12:11 AM
複数の単一列インデックスに対して複合インデックスをいつ使用する必要がありますか？Apr 11, 2025 am 12:06 AM
MySQLでスロークエリを識別して最適化する方法は？ （スロークエリログ、Performance_schema）Apr 10, 2025 am 09:36 AM
MySQLおよびSQL：開発者にとって不可欠なスキルApr 10, 2025 am 09:30 AM
