MySQL MGR 借助 Router 实现自动故障转移

MySQL Router 是 MySQL 官方提供的一个轻量级中间件，是 InnoDB Cluster 的一部分，可在应用程序和后端MySQL服务器之间提供透明路由。主要用以解决 MySQL 主从库集群的高可用、负载均衡、易扩展等问题。Router 作为一个流量转发层，位于应用与 MySQL 服务器之间，其功能类似于 LVS。

Router 解决了什么问题

MySQL MGR 只是数据同步集群，不是负载均衡 / 代理集群，MGR 本身不提供客户端自动故障转移！

注：当主库发生故障，集群会自动选举一个新的主库，新主库的IP地址和端口都可能发生变化。如果应用直连，需要复杂的逻辑来自行感知故障并重新建立连接，否则就会连接失败。实现效果：主库宕机 → 驱动自动发现 → 自动连接新主库 → 业务恢复

而借助 Router 可以为 MGR 集群提供一个虚拟IP作为应用程序单一连接点，通过这个单一的连接点实现负载均衡、读写分离、故障转移等功能

注：MySQL Router自身存在单点故障隐患，官方推荐在每个应用程序所在机器上部署Router（性能损耗小于1%），程序连接本机 Router 以降低网络延迟、提升性能；也可以在Router的上层在搭建Keepalived作为高可用。

当后端 MySQL 实例挂掉之后，无需人工干预，MySQL Router 会自动剔除挂了的 MySQL 实例，当挂了的MySQL实例恢复后会自动加入 MySQL Router 的后端服务器，也无需人工干预。

安装配置

资源规划：

ip地址	主机名	OS版本	MySQL版本	sql port	权重	角色
192.168.31.101	mgr101	Centos 7.9 x64	8.0.32	3406	50	mgr节点、mysql-shell 8.0.46
192.168.31.102	mgr102	Centos 7.9 x64	8.0.32	3406	40	mgr节点
192.168.31.103	mgr103	Centos 7.9 x64	8.0.32	3406	30	mgr节点
192.168.31.104	-	Ubuntu 24.04.3	-	-	-	应用服务器、mysql-router 8.0.46

注：务必注意版本兼容性，MySQL 8.0 推荐使用 MySQL Shell 8.0.46、MySQL Router 8.0.46。若 MySQL 8.0 使用 MySQL Router 8.4.6（这个针对MySQL 8.4）则可能会遇到莫名其妙的问题，比如服务正常但端口未监听

1.搭建 MGR 集群

第1步，安装 MySQL 8.0 并搭建 MGR 集群（单主模式），过程省略...

$ mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;"
+----------------+-------------+--------------+-------------+
| MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+----------------+-------------+--------------+-------------+
| 192.168.31.101 |        3406 | ONLINE       | PRIMARY     |
| 192.168.31.102 |        3406 | ONLINE       | SECONDARY   |
| 192.168.31.103 |        3406 | ONLINE       | SECONDARY   |
+----------------+-------------+--------------+-------------+

第2步，所有节点：修改配置文件，启用 SSL/TLS

注1：MySQL 8.4 及以后版本，默认不再启用认证插件 mysql_native_password，而使用 caching_sha2_password 推荐使用SSL/TLS安全连接。

注2：同时也要为 MGR 启用 SSL参数，否则 MGR 集群重启之后从库节点状态变为 RECOVERING，报错 Authentication requires secure connection. Error_code: MY-002061

$ cat /data/mysql_3406/my_3406.cnf | grep ssl
ssl=on
ssl-ca=/data/mysql_3406/var/ca.pem
ssl-cert=/data/mysql_3406/var/server-cert.pem
ssl-key=/data/mysql_3406/var/server-key.pem
loose-group_replication_recovery_use_ssl=ON
loose_group_replication_ssl_mode=REQUIRED
loose_group_replication_ssl_ca=/data/mysql_3406/var/ca.pem
loose_group_replication_ssl_cert=/data/mysql_3406/var/server-cert.pem
loose_group_replication_ssl_key=/data/mysql_3406/var/server-key.pem

第3步，所有节点：重启 MGR（注意顺序）

#.1.先停止从库2、3
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"stop group_replication;"
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown

#.2.再停止主库1
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"stop group_replication;"
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown

#.3.先启动主库1，引导组复制
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> set @@global.group_replication_bootstrap_group=on;
mysql> start group_replication;
mysql> set @@global.group_replication_bootstrap_group=off;
mysql> exit

#.4.再启动从库2、3，加入组复制
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"start group_replication;"

#.5.任意节点：确认节点信息
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;"

2.将 MGR 集群升级为 InnoDB Cluster

第4步，MGR任一节点：登录主库并创建集群管理专用账户，示例账号 dba_cluster 密码 Admin_147

注：该账号要有 WITH GRANT OPTION 权限，否则会报错 Access denied

#.1.确认主库节点
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;"

#.2.定义主库信息
port="3406"
mysql_m="192.168.31.101"

#.3.在主库上创建专用账户,推荐使用 caching_sha2_password 插件
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"DROP USER IF EXISTS 'dba_cluster'@'192.168.31.%';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"CREATE USER IF NOT EXISTS 'dba_cluster'@'192.168.31.%' IDENTIFIED with caching_sha2_password BY 'Admin_147';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"GRANT ALL PRIVILEGES ON *.* TO 'dba_cluster'@'192.168.31.%' WITH GRANT OPTION;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"FLUSH PRIVILEGES;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"select user,host,plugin,account_locked from mysql.user;"

第5步，MGR任一节点：安装 mysql-shell 8.0.46

#.针对 CentOS 7.9
cd /opt/
yum install -y libyaml
wget -c https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.46-1.el7.x86_64.rpm
rpm -ivh mysql-shell-8.0.46-1.el7.x86_64.rpm

#.查看版本
mysqlsh --version

第6步，同一节点：利用 mysql-shell 创建一个名为 mgr_cluster 的集群

[root@mgr101 ~]# mysqlsh --uri dba_cluster@192.168.31.101:3406 --password=Admin_147 --js
 MySQL  JS > dba.configureInstance('dba_cluster@192.168.31.101:3406')
 MySQL  JS > shell.connect('dba_cluster@192.168.31.101:3406')
 MySQL  JS > var cluster = dba.createCluster('mgr_cluster')
 MySQL  JS > cluster.status()

第7步，同一节点：确保所有节点都是 ONLINE 状态

注：执行 dba.configureInstance 是为了修复 NOTE: The required parallel-appliers settings are not enabled on the instance.

 MySQL  JS > dba.configureInstance('dba_cluster@192.168.31.102:3406')
 MySQL  JS > dba.configureInstance('dba_cluster@192.168.31.103:3406')
 MySQL  JS > cluster.status()
 MySQL  JS > \q

附：连接到 mgr_cluster 集群的方式

[root@mgr101 ~]# mysqlsh --uri dba_cluster@192.168.31.101:3406 --password=Admin_147 --js
 MySQL  JS > var cluster = dba.getCluster('mgr_cluster')
 MySQL  JS > cluster.status()
{
    "clusterName": "mgr_cluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.31.101:3406", 
        "ssl": "DISABLED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.31.101:3406": {
                "address": "192.168.31.101:3406", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "192.168.31.102:3406": {
                "address": "192.168.31.102:3406", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "192.168.31.103:3406": {
                "address": "192.168.31.103:3406", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "192.168.31.101:3406"
}
 MySQL  JS \q
Bye!

3.配置 MySQL Route

第8步，MGR任一节点：登录主库并创建 Router 专用账号，示例账号 dba_router 密码 Admin_147

注1：该账号要有 WITH GRANT OPTION 权限，否则会报错 Access denied

注2：必须启用 SSL 且使用 caching_sha2_password 插件，否则执行 mysqlrouter --bootstrap 会报错 Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. (2061)

#.1.确认主库节点
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;"

#.2.定义主库信息
port="3406"
mysql_m="192.168.31.101"

#.3.在主库上创建专用账户,推荐使用 caching_sha2_password 插件
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"DROP USER IF EXISTS 'dba_router'@'192.168.31.%';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"CREATE USER IF NOT EXISTS 'dba_router'@'192.168.31.%' IDENTIFIED with caching_sha2_password BY 'Admin_147';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"GRANT ALL PRIVILEGES ON *.* TO 'dba_router'@'192.168.31.%' WITH GRANT OPTION;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"FLUSH PRIVILEGES;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $mysql_m -P $port -e"select user,host,plugin,account_locked from mysql.user;"

第9步，应用节点：登录应用服务器（示例 192.168.31.104），下载安装 mysql-router 8.0.46

注：安装过程会创建 mysqlrouter 用户并注册 mysqlrouter 服务

#.下载解压
cd /opt/
wget -c https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-community_8.0.46-1ubuntu24.04_amd64.deb
apt install ./mysql-router-community_8.0.46-1ubuntu24.04_amd64.deb

#.查看版本
mysqlrouter --version

第10步，应用节点：使用 mysqlrouter --bootstrap 初始化 MySQL Router

#.参数 --user=mysqlrouter 让进程以 mysqlrouter 身份运行
mysqlrouter --bootstrap dba_router@192.168.31.101:3406 --user=mysqlrouter --force

第11步，应用节点：启动 MySQL Router

#.启动服务
systemctl restart mysqlrouter
systemctl status mysqlrouter

#.确认端口已监听
root@ubuntu:/opt# netstat -lnpt | grep mysqlrouter
tcp        0      0 0.0.0.0:8443        0.0.0.0:*       LISTEN      2201/mysqlrouter
tcp        0      0 0.0.0.0:6446        0.0.0.0:*       LISTEN      2201/mysqlrouter
tcp        0      0 0.0.0.0:6447        0.0.0.0:*       LISTEN      2201/mysqlrouter
tcp        0      0 0.0.0.0:6448        0.0.0.0:*       LISTEN      2201/mysqlrouter
tcp        0      0 0.0.0.0:6449        0.0.0.0:*       LISTEN      2201/mysqlrouter

#.查看日志
tail -100 /var/log/mysqlrouter/mysqlrouter.log

第12步，应用节点：测试连接 MySQL Router，默认 6446 / 6447 分别作为读写端口 / 只读端口

#.安装 mysql-client
apt -y update
apt -y install mysql-client-core-8.0

#.测试RW读写端口, 会访问到后端 InnoDB Cluster 的 PRIMARY
root@ubuntu:~# mysql -udba_router -pAdmin_147 -h192.168.31.104 -P6446 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| mgr101     |
+------------+

#.测试RW只读端口, 会访问到后端 InnoDB Cluster 的 SECONDARY
root@ubuntu:~# mysql -udba_router -pAdmin_147 -h192.168.31.104 -P6447 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| mgr102     |
+------------+

#.测试RW只读端口, 验证读负载均衡
root@ubuntu:~# for ((i=1;i<=6;i++));do mysql -h192.168.31.104 -udba_router -pAdmin_147 -P6447 -e"select @@hostname;" --skip-column-names 2>/dev/null; done
+--------+
| mgr103 |
+--------+
+--------+
| mgr102 |
+--------+
+--------+
| mgr103 |
+--------+
+--------+
| mgr102 |
+--------+
+--------+
| mgr103 |
+--------+
+--------+
| mgr102 |
+--------+

附：查看 mysqlrouter 的部分核心配置

root@ubuntu:~# cat /etc/mysqlrouter/mysqlrouter.conf
[metadata_cache:bootstrap]
cluster_type=gr
user=mysql_router1_x64z503
metadata_cluster=mgr_cluster

[routing:bootstrap_rw]
bind_port=6446
destinations=metadata-cache://mgr_cluster/?role=PRIMARY
routing_strategy=first-available

[routing:bootstrap_ro]
bind_port=6447
destinations=metadata-cache://mgr_cluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback

[routing:bootstrap_x_rw]
bind_port=6448
destinations=metadata-cache://mgr_cluster/?role=PRIMARY
routing_strategy=first-available

[routing:bootstrap_x_ro]
bind_port=6449
destinations=metadata-cache://mgr_cluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback

[http_server]
port=8443
ssl=1
ssl_cert=/var/lib/mysqlrouter/router-cert.pem
ssl_key=/var/lib/mysqlrouter/router-key.pem

#...其他省略...

附：查看此时 mysql 的账号清单

[root@mgr101 opt]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select user,host,plugin,account_locked from mysql.user;"
+--------------------------------+--------------+-----------------------+----------------+
| user                           | host         | plugin                | account_locked |
+--------------------------------+--------------+-----------------------+----------------+
| dba_admin                      | %            | mysql_native_password | N              |
| dba_repl                       | %            | mysql_native_password | N              |
| mysql_innodb_cluster_311013406 | %            | caching_sha2_password | N              |
| mysql_innodb_cluster_311023406 | %            | caching_sha2_password | N              |
| mysql_innodb_cluster_311033406 | %            | caching_sha2_password | N              |
| mysql_router1_x64z503          | %            | caching_sha2_password | N              |
| dba_cluster                    | 192.168.31.% | caching_sha2_password | N              |
| dba_router                     | 192.168.31.% | caching_sha2_password | N              |
| mysql.infoschema               | localhost    | caching_sha2_password | Y              |
| mysql.session                  | localhost    | caching_sha2_password | Y              |
| mysql.sys                      | localhost    | caching_sha2_password | Y              |
| root                           | localhost    | caching_sha2_password | N              |
+--------------------------------+--------------+-----------------------+----------------+

灾难演练

模拟主库宕机

第1步，[节点3]：查集群状态，当前主节点为节点1

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;
+----------------+-------------+--------------+-------------+
| MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+----------------+-------------+--------------+-------------+
| 192.168.31.101 |        3406 | ONLINE       | PRIMARY     |
| 192.168.31.102 |        3406 | ONLINE       | SECONDARY   |
| 192.168.31.103 |        3406 | ONLINE       | SECONDARY   |
+----------------+-------------+--------------+-------------+

第2步，[节点1]：停掉宕机主节点的mysql服务，模拟主节点宕机，此时会触发重新选举

[root@mgr101 ~]# mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown

第3步，[节点3]：查看集群状态，集群无宕机节点1、当前主库为节点2（节点1/2/3的权重依次为50/40/30）

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;
+----------------+-------------+--------------+-------------+
| MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+----------------+-------------+--------------+-------------+
| 192.168.31.102 |        3406 | ONLINE       | PRIMARY     |
| 192.168.31.103 |        3406 | ONLINE       | SECONDARY   |
+----------------+-------------+--------------+-------------+

第4步，[mysqlsh节点]：查看 mgr_cluster 集群状态

注：由于节点 mgr101 已宕机，登录该节点会报错，需要改为新的主节点 mgr102

[root@mgr102 ~]# mysqlsh --uri dba_cluster@192.168.31.101:3406 --password=Admin_147 --js
MySQL Error 2003 (HY000): Can't connect to MySQL server on '192.168.31.101:3406' (111)

[root@mgr102 ~]# mysqlsh --uri dba_cluster@192.168.31.102:3406 --password=Admin_147 --js
 MySQL  JS > var cluster = dba.getCluster('mgr_cluster')
 MySQL  JS > cluster.status()
{
    "clusterName": "mgr_cluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.31.102:3406", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE_PARTIAL", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.", 
        "topology": {
            "192.168.31.101:3406": {
                "address": "192.168.31.101:3406", 
                "memberRole": "SECONDARY", 
                "mode": "n/a", 
                "readReplicas": {}, 
                "role": "HA", 
                "shellConnectError": "MySQL Error 2003: Could not open connection to '192.168.31.101:3406': Can't connect to MySQL server on '192.168.31.101:3406' (111)", 
                "status": "(MISSING)"
            }, 
            "192.168.31.102:3406": {
                "address": "192.168.31.102:3406", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "192.168.31.103:3406": {
                "address": "192.168.31.103:3406", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "192.168.31.102:3406"
}
 MySQL  JS > \q
Bye!

第5步，应用节点：测试连接 MySQL Router，确认 RW 读写端口为 mgr102、RW 只读端口只有 mgr103

#.测试RW读写端口, 会自动去连接新的 PRIMARY
root@ubuntu:~# mysql -udba_router -pAdmin_147 -h192.168.31.104 -P6446 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| mgr102     |
+------------+

#.测试RW只读端口, 只会转发到正常的 SECONDARY 节点
root@ubuntu:~# for ((i=1;i<=6;i++));do mysql -h192.168.31.104 -udba_router -pAdmin_147 -P6447 -e"select @@hostname;" --skip-column-names 2>/dev/null; done
+--------+
| mgr103 |
+--------+
+--------+
| mgr103 |
+--------+
+--------+
| mgr103 |
+--------+
+--------+
| mgr103 |
+--------+
+--------+
| mgr103 |
+--------+
+--------+
| mgr103 |
+--------+

第6步，[节点1]：启动该节点的 mysql 服务，由于配置了“不自动启用组复制”，所以此时主节点仍为节点1，集群状态同第3步一样（省略...）

[root@mgr101 ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &

[root@mgr101 ~]# cat /data/mysql_3406/my_3406.cnf | egrep "(start_on_boot|bootstrap_group)"
loose-group_replication_start_on_boot=off
loose-group_replication_bootstrap_group=off

第7步，[节点1]：执行 START GROUP_REPLICATION; 将宕机节点重新加入集群，不会触发选举

[root@mgr101 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"START GROUP_REPLICATION;"

第8步，[节点3]：查看集群状态，所有节点online、当前主库仍为节点2

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;
+----------------+-------------+--------------+-------------+
| MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+----------------+-------------+--------------+-------------+
| 192.168.31.101 |        3406 | ONLINE       | SECONDARY   |
| 192.168.31.102 |        3406 | ONLINE       | PRIMARY     |
| 192.168.31.103 |        3406 | ONLINE       | SECONDARY   |
+----------------+-------------+--------------+-------------+

第9步，应用节点：测试连接 MySQL Router，确认 RW 读写端口为 mgr102、RW 只读端口 mgr103、mgr101

#.测试RW读写端口, 查看连接的是否为 PRIMARY
root@ubuntu:~# mysql -udba_router -pAdmin_147 -h192.168.31.104 -P6446 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| mgr102     |
+------------+

#.测试RW只读端口, 验证读负载均衡
root@ubuntu:~# for ((i=1;i<=6;i++));do mysql -h192.168.31.104 -udba_router -pAdmin_147 -P6447 -e"select @@hostname;" --skip-column-names 2>/dev/null; done
+--------+
| mgr101 |
+--------+
+--------+
| mgr103 |
+--------+
+--------+
| mgr101 |
+--------+
+--------+
| mgr103 |
+--------+
+--------+
| mgr101 |
+--------+
+--------+
| mgr103 |
+--------+

附录

1、如果要启用读写分离，应用程序通过 6446 / 6447 端口进行连接

#.写请求 → 6446 → 自动到主库
mysql -udba_router -pAdmin_147 -h192.168.31.104 -P6446

#.读请求 → 6447 → 自动到2台从库（轮询）
mysql -udba_router -pAdmin_147 -h192.168.31.104 -P6447

2、MySQL Router 8.0.46 疑似不支持 --directory 自定义配置，示例 /data 非挂载设备、亦非NFS共享，却一直报权限问题，未解决...

root@ubuntu:~# userdel -r mysqlrouter
root@ubuntu:~# useradd -r -s /sbin/nologin mysqlrouter
root@ubuntu:~# mkdir -p /data/mysqlrouter
root@ubuntu:~# chown -R mysqlrouter:mysqlrouter /data/mysqlrouter
root@ubuntu:~# chmod 777 /data/mysqlrouter
root@ubuntu:~# mysqlrouter --bootstrap dba_router@192.168.31.101:3406 --directory /data/mysqlrouter --user=mysqlrouter --force
Please enter MySQL password for dba_router: 
Error: Can't set ownership of file '/data/mysqlrouter' to the user 'mysqlrouter'. error: Permission denied. 
One possible reason can be that the root user does not have proper rights because of root_squash on the NFS share.

mgr借助router实现自动故障转移

MySQL MGR 借助 Router 实现自动故障转移

Router 解决了什么问题

安装配置

1.搭建 MGR 集群

2.将 MGR 集群升级为 InnoDB Cluster

3.配置 MySQL Route

灾难演练

模拟主库宕机

附录

results matching ""

No results matching ""