使用 MySQL Router 实现高可用、负载均衡、读写分离

原创 已于 2025-04-17 16:35:23 修改 · 4w 阅读 · 95 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#MySQL Router

于 2018-07-18 19:25:04 首次发布

目录

一、MySQL Router 简介

1. 功能

2. 架构

3. 使用

二、安装配置

1. 下载二进制安装包

2. 解压缩

3. 在资源文件中添加执行文件路径

4. 验证安装

5. 配置 Router

6. 启动 Router 服务

三、自动失败切换

四、负载均衡

五、读写分离

六、多实例

1. 环境

2. 配置文件

3. 功能测试

参考：

一、MySQL Router 简介

        MySQL Router 是 MySQL 官方提供的一个轻量级中间件，可以在应用程序与 MySQL 服务器之间提供透明的路由访问，主要用以解决 MySQL 主从库集群的高可用、负载均衡、易扩展等问题。Router 可以与 MySQL Fabric 无缝连接，允许 Fabric 存储和管理用于路由的高可用数据库服务器组，使管理 MySQL 服务器组更加简单。

        MySQL Router 是一个可执行文件，可以与应用程序在同一平台上运行，也可以单独部署。虽然 MySQL Router 是 InnoDB Cluster（MySQL 7.X）的一部分，MySQL 5.6 等版本数据库仍然可以使用 Router 作为其中间代理层。MySQL Router 的配置文件中包含有关如何执行路由的信息。它与 MySQL 服务器的配置文件类似，也是由多个段组成，每个段中包含相关配置选项。

        MySQL Router 是 MySQL Proxy 的替代方案，MySQL 官方不建议将 MySQL Proxy 用于生产环境，并且已经不提供 MySQL Proxy 的下载。

1. 功能

        Router 作为一个流量转发层，位于应用与 MySQL 服务器之间，其功能类似于 LVS。MySQL Servers 作为 Router 的“downstream”（NAT 模式），应用不再直连 MySQL Servers，而是与 Router 相连。根据 Router 的配置，将会把应用程序的读写请求转发给下游的 MySQL Servers。

        当下游有多个 MySQL Server，无论主、从，Router 可以对读写请求进行负载均衡。当下游某个 Server 失效时，Router 可以将其从 Active 列表中移除，当其 online 后再次加入 Active 列表，即提供了 Failover 特性。

        当 MySQL Servers 集群拓扑变更时，比如增减 Slaves 节点，只需要修改 Router 的配置即可，无需修改应用中数据库连接配置，因为应用配置的为 Router 地址而非 MySQL Servers 的原始地址，即数据库集群对应用来说是透明的。如果 MySQL Servers 为 5.7+ 版本，且构建为 InnoDB Cluster 模式，那么 Router 还能基于 metaCache（metaServers）机制，感知 MySQL Servers 的主从切换、从库增减等集群拓扑变更，而且基于变更能够实现 Master 自动切换、Slaves 列表自动装配等。比如 Master 失效后，Cluster 将会自动选举一个新的 Master，此时 Router 不需要任何调整、可以自动发现此新 Master 进而继续为应用服务。

        考虑到 Router 独立部署可能引入“额外的部署成本”、“性能降级”、“连接数上限”等问题，通常建议基于“Agent”方式部署，即将 Router 与应用部署在机器上。Router 通常用于解决“MySQL集群规模性迁移”，比如跨机房部署、流量迁移、异构兼容，或者解决 MySQL 集群规模性宕机时快速切换等。

        Router 中间件本身不会对请求“拆包”（unpackage），所以无法在 Router 中间件上实现比如“SQL审计”、“隔离”、“限流”、“分库分表”等功能。但是 Router 提供了 plugin（C 语言）机制，用户可以开发自己的 plugin 来扩展 Router 的额外特性。

2. 架构

        MySQL Router 是一个单独的应用程序，其体系结构如图1 所示。

图1

       

        在图1 的最左边是连接到 Router 的应用程序，最右侧是两个连接目的地，即 MySQL Fabric 和 MySQL Cluster。中间的 Router 架构分为三层，从上至下依次为插件层、核心层和 Harness。插件层是对应用开放的接口，mysqlrouter 程序是 Router 的核心，而该程序时基于 MySQL Harness 实现的。Router 采用模块化设计，并在实现功能时利用此架构。MySQL Harness 是一个基础模块，提供日志、配置重载、插件管理等功能。当前 Router 提供 Connection Routing 和 Fabric Cache 两个插件，未来功能将与这两个插件一起出现。

        如图1 中的箭头所示，应用程序连接 Router，如果连接路由插件，Router 从配置文件中读取目标并重定向到列表中的一个服务器。如果使用 Fabric Cache，需在 Router 的路由策略中指定 Fabric 安装的 URL。这种情况下，应用程序连接到 Router，然后 Router 将从 Fabric 获取目标列表，然后将连接重定向到列表中的一个服务器。

3. 使用

        正如前面提到的，在目前形式中，MySQL Router 最好与应用程序一起使用。也就是说，应该在运行应用程序的相同机器上安装 Router。虽然这不是强制要求，但建议采用这种做法。可以编写程序来监控 mysqlrouter 的执行，并在需要时重新启动它。例如，如果目标选项中的服务器列表已用尽，则可以使用新目标列表重新启动 Router，或重新启动 Router 以重试列表中的服务器。图2 说明了如何在应用程序中使用 Router。

图2

       

        可以在整个网络中运行多个 Router 实例。但是 MySQL 官方并没有提供 Router 集群的 HA，即每个 Router 节点均为独立，它们之间互不通信，无 Leader 角色，无选举机制。那么当某个 Router 节点失效，应用层面需要借助 MySQL Connector 的高级特性，比如：failover、loadbalance 等协议来实现 Failover 功能。简单而言，Router 中间件与 Connector 的高级协议互相协作，才能够实现请求在 Router 集群之间的负载均衡、Failover 等。

        MySQL Router 非常轻量级，与直连 Servers 相比，其性能损耗低于 1%。摆在 Router 面前的问题，是其对链接数的支撑能力，原则上我们一个 Router 节点限定在 500 个 TCP 链接。Router 本身 CPU、内存、磁盘消耗都极低，但是要求 Router 节点对网络 IO 的支撑能力应该较强。考虑到 Router 底层为“异步IO”，如果条件允许，应该构建在较高版本的 Linux 平台下，且给予合理的 CPU 资源。MySQL Router 在 2.1.4 版本以下，内核基于 select() IO 模型，存在连接数 500 上限、较大 SQL 请求导致 CPU 过高，以及并发连接过高时 Router 假死等问题，建议升级到2.1.6+。

        Router 对连接的管理是基于“粘性”方式，即应用与 Router 的一个 TCP 连接，将对应一个 Router 与 MySQL Servers 的连接，当应用与 Router 的连接失效时，Router 也将断开其与 MySQL Servers 的连接。只要 Router 上下游网络联通性正常，那么 Router 将不会主动断开与应用的连接，也不会切换其与 Servers 的连接。即当应用与 Router 创建一个新连接时，Router 将根据负载均衡算法，选择一个 Server 并与其建立连接，此后将唯一绑定，直到此 Server 失效时触发重新选择其它 Server。这就引入一个问题，如果某个连接上发生了“繁重”的 SQL 操作，那么将会导致下游 Server 伴随高负载而无法“负载均衡”。
    
        Router 对应用是透明的，开发与 Router 一起使用的应用程序不需要任何特殊的库或接口，所增加的工作只是维护 MySQL Router 实例。

二、安装配置

        环境
172.16.1.125：MySQL Router
172.16.1.126：MySQL Replication Master 
172.16.1.127：MySQL Replication Slave

        我们在 172.16.1.125 上安装配置 MySQL Router，172.16.1.126、172.16.1.127 为本例中要通过 Router 访问的两个 MySQL 数据库服务器地址。在本例中这两个 MySQL 服务器已经配置好主从复制，拓扑如图3 所示。

图3


1. 下载二进制安装包

        从“MySQL :: Download MySQL Router”页面选择下载的安装包，本例为 mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz。

2. 解压缩

tar xzf mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz 
mv mysql-router-2.1.6-linux-glibc2.12-x86-64bit mysql-router-2.1.6

3. 在资源文件中添加执行文件路径

        本例使用的资源文件为 .bashrc，内容如下：

[mysql@hdp2~]$more ~/.bashrc 
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
    . /etc/bashrc
fi

# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=

# User specific aliases and functions
export PATH=.:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/home/mysql/mysql-5.6.14/bin:/home/mysql/mysql-router-2.1.6/bin;
[mysql@hdp2~]$

        使资源配置生效：

source ~/.bashrc

4. 验证安装

[mysql@hdp2~]$mysqlrouter --help
MySQL Router v2.1.6 on Linux (64-bit) (GPL community edition)
Copyright (c) 2015, 2018, 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.

Start MySQL Router.

Configuration read from the following files in the given order (enclosed
in parentheses means not available for reading):
  (/home/mysql/mysql-router-2.1.6/bin/.././mysqlrouter.conf)
  (/home/mysql/.mysqlrouter.conf)
Plugins Path:
  /home/mysql/mysql-router-2.1.6/lib/mysqlrouter
Default Log Directory:
  /home/mysql/mysql-router-2.1.6
Default Persistent Data Directory:
  /home/mysql/mysql-router-2.1.6/data
Default Runtime State Directory:
  /home/mysql/mysql-router-2.1.6/run

......

        从 mysqlrouter 联机帮助的输出中，可以看到默认配置文件寻找路径及其顺序，插件路径、日志目录、持久化数据目录、运行时状态目录的缺省位置等重要信息。在后面的配置文件和服务启停文件中需要定义这些目录。注意，如果在 mysqlrouter 命令行使用 --config 或 -c 选项传入用户定义的配置文件，则不会加载默认配置文件。

5. 配置 Router

# 复制配置文件
cp /home/mysql/mysql-router-2.1.6/share/doc/mysqlrouter/sample_mysqlrouter.conf /etc/mysqlrouter.conf
cp /home/mysql/mysql-router-2.1.6/share/doc/mysqlrouter/sample_mysqlrouter.init /etc/init.d/mysqlrouter

# 修改属主为mysql
chown mysql:mysql /etc/mysqlrouter.conf
chown mysql:mysql /etc/init.d/mysqlrouter

# 变为可执行
chmod +x /etc/init.d/mysqlrouter

# 系统启动时自动执行
echo "/etc/init.d/mysqlrouter" >> /etc/rc.d/rc.local

# 建立日志目录
mkdir /home/mysql/mysql-router-2.1.6/log

         配置文件内容如下：

[mysql@hdp2~]$more /etc/mysqlrouter.conf
[DEFAULT]
# 日志路径
logging_folder = /home/mysql/mysql-router-2.1.6/log

# 插件路径
plugin_folder = /home/mysql/mysql-router-2.1.6/lib/mysqlrouter

# 配置路径
config_folder = /home/mysql/mysql-router-2.1.6/config

# 运行时状态路径
runtime_folder = /home/mysql/mysql-router-2.1.6/run

# 数据文件路径
data_folder = /home/mysql/mysql-router-2.1.6/data

[logger]
# 日志级别
level = INFO

# 以下选项可用于路由标识的策略部分
[routing:basic_failover]
# Router地址
bind_address = 172.16.1.125
# Router端口
bind_port = 7001
# 读写模式
mode = read-write
# 目标服务器
destinations = 172.16.1.126:3306,172.16.1.127:3306

[routing:load_balance]
bind_address = 172.16.1.125
bind_port = 7002
mode = read-only
destinations = 172.16.1.126:3306,172.16.1.127:3306

[mysql@hdp2~]$

        MySQL Router 的配置文件比较简单，大部分配置项的含义一目了然。上面的文件中配置了两条路由策略，一个用于失败切换，一个用于负载均衡，绑定端口分别是 7001 和 7002。值得一提的是 mode 参数，该参数的可选值为 read-write 或 read-only，但其实际作用并不是字面含义所示。

        对于 read-write 模式，将采用“首个可用”算法，优先使用第一个 Server，当第一个 Server（即172.16.1.126:3306）不可达时，将会 Failover 到第二个 Server（172.16.1.127:3306），依次进行。如果都不可达，那么此端口上的请求将会被中断，此端口将不能提供服务，且此时所属的路由策略将不可用。需要注意，此算法只遍历一次列表，即逐个验证 destinations中 的 Server，不会循环。一旦所有的 Servers 依次验证且不可用后，本条路由策略将不能继续服务，内置状态设定为 aborted，即使此后 Servers 恢复上线，也不能继续对 Client 提供服务，因为它不会与 Servers 保持心跳检测。对于 Router 而言，直接拒绝 Client 连接请求，只有重启 Router 节点才能解决。

        对于 read-only 模式：将采用“轮询”算法，依次选择 Server 新建连接，如果某个 Server 不可达，将会重试下一个 Server，如果所有的 Server 都不可达，那么此端口上的请求将中断，即读写操作将不可用。同时 Router 将会持续与每个 Server 保持心跳探测，当恢复后重新加入 Active 列表，此后那些新建连接请求将可以分发给此 Server。

        但是比较遗憾，Router 不会将已有的连接重新分配给“新加入”列表的 Server，比如 Router 有 2 个 Server 地址（S1,S2），某时刻 S1 不可达，那么在 S1 上粘性的客户端连接也将被断开，新建连接将会全部在 S2 上，此后 S1 恢复正常，那么在 S2 上的旧的连接将不会迁移到 S1 上，此时 S1 只会接收新的连接，如果没有新连接请求，那么 S1 将会在一段时间看起来是“不提供服务”的。为了解决此问题，我们要求 Connection Pool 有管理“连接生命周期”的相关控制，比如一个 connection 被创建 X 秒以后在返回连接池时应该被主动关闭，这个参数在 tomcat-jdbc-pool 中为“maxAge”。

        如果应用程序中，部署方式是单 Master、多 Slaves，我们完全可以在承接“master”请求的 Router 节点上，也配置为“read-only”模式，那么此单 master 节点失效重启后，可以不需要重启 Router 节点即可继续服务。因为 Router 不会对 TPC 拆包，所有“read-write”、“read-only”并不会干扰实际的 SQL 执行。严格来说，这两种 mode 映射两种“路由算法”：“首个可用”、“轮询”；除此之外，再无特殊含义。
    
        对于读写两种操作，因为 Router 不对请求拆包，所以它无法判断请求的读写类型。我们只能在配置文件中，分别为读、写设定不同的配置：使用不同的绑定端口。比如本例“7001”端口接收到的请求都会转发给 172.16.1.126:3306，当它不可用时，都会转发给 172.16.1.127:3306。“7002”端口接收的请求则会轮询转发给 172.16.1.126:3306 和 172.16.1.127:3306。
    
        MySQL Router 服务启停文件内容如下：

[mysql@hdp2~]$more /etc/init.d/mysqlrouter
#! /bin/bash
#
# mysqlrouter This shell script takes care of starting and stopping
# the MySQL Router
#
# chkconfig: 2345 66 34
# description: MySQL Router
# processname: mysqlrouter
# config: /etc/mysqlrouter/mysqlrouter.ini
# pidfile: /var/run/mysqlrouter/mysqlrouter.pid
#
# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

#
# Maintainer: MySQL Release Engineering <mysql-build@oss.oracle.com>
#

# Source function library
. /etc/rc.d/init.d/functions

# Source networking configuration
. /etc/sysconfig/network

# add general install path
base_dir=/home/mysql/mysql-router-2.1.6
# fix exec path
exec=${base_dir}/bin/mysqlrouter
prog=mysqlrouter
piddir=${base_dir}/run
pidfile=${piddir}/mysqlrouter.pid
logdir=${base_dir}/log
logfile=$logdir/mysqlrouter.log
lockfile=/var/lock/subsys/$prog

# add conf path
conf=/etc/mysqlrouter.conf

start () {
[ -d $piddir ] || mkdir -p $piddir
chown mysql:mysql $piddir
[ -d $logdir ] || mkdir -p $logdir
chown mysql:mysql $logdir
[ -e $logfile ] || touch $logfile
chown mysql:mysql $logfile
export ROUTER_PID=$pidfile
# add opt -c to resolv mysqlrouter.ini
daemon --user mysql $exec -c $conf >/dev/null 2>&1 &              #
ret=$?
if [ $ret -eq "0" ]; then
action $"Starting $prog: " /bin/true
touch /var/lock/subsys/$prog
else
action $"Starting $prog: " /bin/false
fi
return $ret
}

stop () {
[ -f /var/lock/subsys/$prog ] || return 0
killproc mysqlrouter >/dev/null 2>&1
ret=$?
if [ $ret -eq "0" ]; then
rm -f $pidfile
rm -f /var/lock/subsys/$prog
action $"Stopping $prog: " /bin/true
else
ation $"Stopping $prog: " /bin/false
fi
}

restart () {
stop
start
}

condrestart () {
[ -e /var/lock/subsys/$prog ] && restart || return 0
}

case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$pidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?
[mysql@hdp2~]$ 

        当程序意外被 KILL 后，可能残留有相关程序运行标识，需要先：

rm -f $pidfile
rm -f /var/lock/subsys/$prog

        再启动，否则程序会提示有一个实例运行而不能运行该服务。

        很多程序需要判断是否当前已经有一个实例在运行，$pidfile 就是让程序判断是否有实例运行的标志。比如说 xinetd，如果存在这个文件，表示已经有 xinetd 在运行了，否则就是没有。当然程序里面还要有相应的判断措施来真正确定是否有实例在运行。通常与 $pidfile 配套的还有 /var/run 目录，用来存放对应实例的 PID，如果写脚本的话，会发现这两个结合起来可以很方便的判断出许多服务是否在运行，运行的相关信息等等。

        实际上，判断是否上锁就是判断 /var/lock/subsys/$prog 这个文件，所以文件存在与否也就隐含了是否上锁。而这个文件的内容并不能表示一定上锁了，因为很多服务在启动脚本里用 touch 来创建这个加锁文件，在系统结束时该脚本负责清除锁，这本身就不可靠，比如意外失败导致锁文件仍然存在。所以脚本里一般结合 PID 文件，如果有 PID 文件的话，从 PID 文件里得到该实例的 PID，然后用 ps 目录测试是否存在该 PID，从而判断是否真正有这个实例在运行，更加稳妥的方法是用进程通讯，不过这样的话单单靠脚本就做不到了。

6. 启动 Router 服务

        用 root 用户执行服务启动命令：

[root@hdp2~]#service mysqlrouter start
Starting mysqlrouter (via systemctl):                      [  OK  ]
[root@hdp2~]#

        查看日志文件，显示两个路由策略的监听器已经启动。

[mysql@hdp2~]$more /home/mysql/mysql-router-2.1.6/log/mysqlrouter.log 
2018-07-18 17:04:11 INFO    [7fa3437fb700] [routing:load_balance] started: listening on 172.16.1.125:7002; read-only
2018-07-18 17:04:11 INFO    [7fa343ffc700] [routing:basic_failover] started: listening on 172.16.1.125:7001; read-write
[mysql@hdp2~]$

        如果在启动服务时出现类似 log_daemon_msg: command not found 这样的错误，按如下步骤处理。

  • 安装 redhat-lsb-core。
yum -y install redhat-lsb-core
  • 编辑 /usr/lib/lsb/init-functions 文件，在文件最后添加如下内容。
log_daemon_msg () {
    # Dummy function to be replaced by LSB library.
 
    echo $@
}
log_progress_msg() {
   echo $@
}
log_end_msg () {
    # Dummy function to be replaced by LSB library.
 
    if test "$1" != "0"; then
      echo "Error with $DESCRIPTION: $NAME"
    fi
    return $1
}

三、自动失败切换

        当 172.16.1.126 可用时，对于 7001 端口的请求，会全部发送到 172.16.1.126。对 7002 端口的请求，会轮询发送给 172.16.1.126 和 172.16.1.127。

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

        当 172.16.1.126 不可用时，对于 7001 端口的请求，会自动切换到 172.16.1.127，而对于 7002 端口的请求，会全部转移到 172.16.1.127。

        杀掉 172.16.1.126 的进程：

pkill -9 mysqld

        查看路由的目标服务器：

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

        当 172.16.1.126 再次可用时（假设复制已经重新搭建，主从角色已经互换，172.16.1.127 为master，172.16.1.126 为slave），对于 7001 端口的请求，还是会路由到 172.16.1.127，而不会自动转到 172.16.1.126。而对于 7002 端口的请求，会自动继续轮询发送给 172.16.1.126、172.16.1.127 两个服务器。

        启动 172.16.1.126 的 mysql 服务：

service mysql start

        查看路由的目标服务器：

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

        此时重启 mysqlrouter 服务，回到初始状态，对于 7001 端口的请求，只会路由到 172.16.1.126。对 7002 端口的请求路由策略不变，会轮询发送给 172.16.1.126 和 172.16.1.127。

        在 172.16.1.125 上重启 mysqlrouter 服务：

service mysqlrouter restart

        查看路由的目标服务器：

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+

        注意，此时 172.16.1.126 在复制中的角色依然是 slave，但只有它接受读写请求，实际上是以 172.16.1.126 作为复制的 master，这次 Router 的重启已经破坏了复制的数据一致性，因此这种情况下需要重新手工搭建复制互换角色。

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7001 -e "use test; create table t1 (a int); insert into t1 values (1);"
mysql: [Warning] Using a password on the command line interface can be insecure.

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id';select * from test.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+------+
| a    |
+------+
|    1 |
+------+

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id';select * from test.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 127   |
+---------------+-------+
ERROR 1146 (42S02) at line 1: Table 'test.t1' doesn't exist

验证read-only模式下的写请求：
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "insert into test.t1 values (2);"
mysql: [Warning] Using a password on the command line interface can be insecure.

C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.125 -P7002 -e "show variables like 'server_id';select * from test.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+------+
| a    |
+------+
|    1 |
|    2 |
+------+

四、负载均衡

        从上面的实验可以看出，在一主一从的配置中，只要将读请求发送到 7002 端口，请求会被轮询发送到两个 MySQL 服务器，从而达到读负载均衡的目的。对于读写负载均衡，则需要配置双主复制，然后将两个 MySQL 服务器都放到 read-only 下，例如两台 MySQL 服务器互为主从的拓扑结构，只需要配置如下一条路由策略即可。

[routing:load_balance]
bind_address = 172.16.1.125
bind_port = 7001
mode = read-only
destinations = 172.16.1.126:3306,172.16.1.127:3306

        虽然叫 read-only 模式，但这只是指出路由方式为“轮询”。正如上面测试看到的，两个服务器会以轮询方式进行读写，也就实现了最简单读写负载均衡。

五、读写分离

        从上面的实验可以看出，在一主一从的配置中，只要将写请求发送到 7001 端口，读请求 7002 端口，就可实现读写分离。正常情况下，master 接收写请求，master 和 slave 接收读请求。如果 master 宕机，所有读写请求都切换到 slave 一台服务器上。

六、多实例

1. 环境

172.16.1.125：3306、3307 端口对应两个 MySQL 实例，均为 master；MySQL Router；绑定 VIP 172.16.1.100。
172.16.1.126：3306、3307 端口对应两个 MySQL 实例，分别是 172.16.1.125 上 3306、3307 两个 MySQL 实例的 slave。

2. 配置文件

[root@hdp2/data2]#more /etc/mysqlrouter.conf 
[DEFAULT]
logging_folder = /home/mysql/mysql-router-2.1.6/log
plugin_folder = /home/mysql/mysql-router-2.1.6/lib/mysqlrouter
config_folder = /home/mysql/mysql-router-2.1.6/config
runtime_folder = /home/mysql/mysql-router-2.1.6/run
data_folder = /home/mysql/mysql-router-2.1.6/data

[logger]
level = INFO

[routing:db1_write]
bind_address = 172.16.1.100
bind_port = 33060
mode = read-write
destinations = 172.16.1.125:3306,172.16.1.126:3306

[routing:db1_read]
bind_address = 172.16.1.100
bind_port = 33061
mode = read-only
destinations = 172.16.1.126:3306

[routing:db2_write]
bind_address = 172.16.1.100
bind_port = 33070
mode = read-write
destinations = 172.16.1.125:3307,172.16.1.126:3307

[routing:db2_read]
bind_address = 172.16.1.100
bind_port = 33071
mode = read-only
destinations = 172.16.1.126:3307

3. 功能测试

（1）在 172.16.1.125 上绑定VIP

/sbin/ifconfig ens32:1 172.16.1.100

（2）客户端用 VIP 访问数据库

C:\WINDOWS\system32>mysql -uroot -p123456 -P33060 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 125   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33061 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33070 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 125   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33071 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

C:\WINDOWS\system32>

（3）停止两个 master

mysqladmin -uroot -p123456 -P3306 -h127.0.0.1 shutdown
mysqladmin -uroot -p123456 -P3307 -h127.0.0.1 shutdown

（4）客户端用 VIP 访问数据库

C:\WINDOWS\system32>mysql -uroot -p123456 -P33060 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33061 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33070 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33071 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

C:\WINDOWS\system32>

（5）重新启动两个 master

mysqld_safe --defaults-file=/home/mysql/mysql-5.6.14/my.cnf &
mysqld_safe --defaults-file=/home/mysql/mysql-5.6.14/my_2.cnf &

（6）重启 MySQL Router

service mysqlrouter restart

（7）客户端用 VIP 访问数据库

C:\WINDOWS\system32>mysql -uroot -p123456 -P33060 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 125   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33061 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33070 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 125   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

C:\WINDOWS\system32>mysql -uroot -p123456 -P33071 -h172.16.1.100 -e "show variables like 'server_id'; show variables like 'port'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 126   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+

C:\WINDOWS\system32>

参考：

    MySQL 与 MySQL Router：负载均衡和读写分离的终极指南
    java专栏
    09-05 889
    大家好呀！今天我们就来聊聊 MySQL Router —— MySQL 官方提供的一个强大的工具，它可以帮助我们实现数据库的负载均衡和读写分离。如果你的应用程序需要处理大量的数据请求，那么掌握 MySQL Router 将会大大提升你的数据库管理技能！小伙伴们，我们现在已经一起完成了 MySQL Router 的配置和使用过程！通过创建一个简单的配置文件，我们学习了如何实现读写分离和负载均衡。如果你想要更进一步，可以尝试添加更多的实例，甚至可以考虑构建自己的数据库集群。
    【MySQL8入门到精通】高可用-MySQLRouter读写分离&负载均衡
    IT邦德
    12-31 5095
    本文详细阐述了MySQL1主2从实现读写分离&负载均衡的高可用搭建
    【mysql集群之组复制】
    qq_62331653的博客
    08-26 1168
    提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，这是一种内置的自动裂脑保护机制MGR由组通信系统( Group Communication System ，GCS ) 协议支持。MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩展的解决方案。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致。
    mysql-router：数据库中间件，高效路由管理
    最新发布
    gitblog_00958的博客
    05-13 780
    mysql-router：数据库中间件，高效路由管理 mysql-router MySQL Router是一个开源的MySQL路由代理，用于在Kubernetes集群中管理MySQL数据库。 - 功能：MySQL路由代理；Kubernetes集群管理；MySQL数据库管理。 - 特点：易于使用；支持多种云供应商；...
    mysqlrouter 读写分离
    u010648194的博客
    04-02 1628
    一、mysqlrouter原理 mysqlrouter会提供一个ip和两个端口来供客户端读写分离，mysql的master主机和slave从机相对客户端app来说是透明的，不建议使用mysqlrouter做高可靠，因为router缺点就是，写的主机挂了，备机会顶上，备机如果也挂了，mysqlrouter也就挂了，而且主机如果现在好了，也不会自动的添加到mysqlrouter中重新使用，mysqlrouter的优点是做负载均衡很方便，配置很简单。 一、安装mysqlrouter ① 准备...
    mysql router_MySQL Router实现MySQL的读写分离
    weixin_39611389的博客
    01-18 191
    CC语言开发MySQL Router实现MySQL的读写分离 1.简介MySQL Router是MySQL官方提供的一个轻量级MySQL中间件，用于取代以前老版本的SQL proxy。既然MySQL Router是一个数据库的中间件，那么MySQL Router必须能够分析来自前面客户端的SQL请求是写请求还是读请求，以便决定这个SQL请求是发送给master还是slave，...
    【MySQL】——mysql 中间件mysql-router
    汤庆
    07-27 2105
    一、前言 MySQL Router最早是作为MySQL-Proxy的替代方案出现的。作为一个轻量级中间件，MySQL Router可在应用程序和后端MySQL服务器之间提供透明路由和负载均衡，从而有效提高MySQL数据库服务的高可用性与可伸缩行。MySQL Router 2.0是其初始版本，适用于MySQL Fabric用户，但已被弃用，不再支持。MySQL Router 2.1为支持MySQL InnoDB Cluster而引入，MySQL Router 8.0则是MySQL Router...
    使用MySqlrouter实现MYSQL读写分离
    MappleZF的博客
    05-27 1605
    使用MySqlrouter实现MYSQL读写分离 文章目录使用MySqlrouter实现MYSQL读写分离一、MYSQL读写分离1.1 读写分离概述：1.2 部署示例结构：1.3 部署mysqlrouter1.4 mysqlrouter读测试1.5 mysqlrouter写测试 一、MYSQL读写分离 1.1 读写分离概述： ​ 鉴于数据量增多，单机的数据库支撑业务有压力的情况下，进行了读写分离的初步部署； ​ 目前读写分离的中间件比较多，比如有美团的DBProxy、360的Alt
    mysql router开源_mysql router读写分离
    weixin_29699727的博客
    01-19 138
    cmake .. -DINSTALL_LAYOUT=STANDALONE-DCMAKE_INSTALL_PREFIX=/opt/mysqlroutermake installmkdir -p/opt/mysqlrouter/logs[root@router~]#/opt/mysqlrouter/bin/mysqlrouter-hMySQLRouter v2.0.3 on Linux (64-b...
    Keepalived+HAProxy实现MySQL高可用负载均衡的配置
    12-15
    【MySQL高可用负载均衡配置】 在复杂的IT环境中，确保数据库系统的高可用性和负载均衡是至关重要的。在本文中，我们将探讨如何利用Keepalived和HAProxy这两个工具来实现MySQL数据库的高可用负载均衡，特别是在使用了...
    mysql构建主从结构，mysql负载均衡和读写分离
    wangyue123com的专栏
    11-29 1334
    mysql构建主从结构，mysql读写分离一、修改mysql配置二、配置mysql主从连接1. 主mysql数据库操作2. 主mysql数据库操作三、出现的一些问题1. 重启主库后，从库数据不跟着同步了。2. 无法连接主库。3. 遇到其他不明错误。四、读写分离1. Mycat实现读写分离2. MySQL Router方式3. 代码层实现读写分离 一、修改mysql配置 现有两台虚拟机mysql版本都是5.7 master(主)： 192.168.23.133 slave(从)： 192.168.23.134
    mysql 主从 MySQLroute_MySQL Router实现MySQL的读写分离
    weixin_39824834的博客
    01-28 557
    MySQL Router实现MySQL的读写分离发布时间：2020-08-11 00:59:33来源：ITPUB博客阅读：86作者：18141908802MySQL Router实现MySQL的读写分离https://www.cnblogs.com/f-ck-need-u/p/9276639.htmlhttps://www.cnblogs.com/f-ck-need-u/p/9276639.htm...
    MySQL Router实现MySQL的读写分离
    weixin_34212189的博客
    07-07 261
    1.简介 MySQL Router是MySQL官方提供的一个轻量级MySQL中间件，用于取代以前老版本的SQL proxy。 既然MySQL Router是一个数据库的中间件，那么MySQL Router必须能够分析来自前面客户端的SQL请求是写请求还是读请求，以便决定这个SQL请求是发送给master还是slave，以及发送给哪个master、哪个slave。这样，MySQL Router就实...
    mysql 高可用工具_mysql router高可用（官方工具）
    weixin_40001048的博客
    01-19 466
    1.Mysql route介绍1.1.什么是mysql routeMySQL Router是处于应用client和dbserver之间的轻量级代理程序，它能检测，分析和转发查询到后端数据库实例，并把结果返回给client。是mysql-proxy的一个替代品。其架构图和功能如下。(1)Router实现读写分离，程序不是直接连接数据库IP，而是固定连接到mysql router。MySQL Rout...
    mysql router用得多吗_mysql router怎么样
    weixin_39688687的博客
    01-19 290
    MySQL Router用于取代MySQL Proxy，建议MySQL Router与应用程序部署在一台机器。应用程序像访问MySQL一样访问MySQL Proxy，由MySQL Proxy将数据转发给后端的MySQL。支持各种操作系统。工作模式read-write用于多主服务器，个人感觉也可用于主备，用于多主可以避免脑裂问题，但实际上多主只有一个为Active状态。并且MySQL的主从和多主复制...
    MySQL 中间件 MySQL-Router
    weixin_68398469的博客
    08-24 1952
    MySQL Router 是 MySQL 官方提供的一款中间件软件，它主要用于实现数据库的路由和负载均衡功能。MySQL Router 可以帮助开发者更轻松地管理复杂的数据库架构，尤其是在需要处理多个数据库实例或者需要实现读写分离等场景下非常有用。
    mysql router负载均衡_MySQL Router实现高可用、负载均衡、读写分离
    weixin_39604139的博客
    01-21 434
    MySQL Router简介MySQL Router是MySQL官方提供的一个轻量级中间件，可以在应用程序与MySQL服务器之间提供透明的路由方式。主要用以解决MySQL主从库集群的高可用、负载均衡、易扩展等问题。Router可以与MySQL Fabric无缝连接，允许Fabric存储和管理用于路由的高可用数据库服务器组，使管理MySQL服务器组更加简单。MySQL Router是一个可执行文件，...
    高性能mysql之MySQL Router基于端口实现读写分离
    weixin_46074899的博客
    08-19 1037
    文章目录1.MySQL Router的介绍1.1MySQL Router简介1.2为什么要用MySQL Router？1.3MySQL Router关系图2.MySQL Router的使用 1.MySQL Router的介绍 1.1MySQL Router简介 MySQL Router是MySQL官方提供的一个轻量级中间件，是InnoDB Cluster的一部分，可在应用程序和后端MySQL服务器之间提供透明路由。主要用以解决MySQL主从库集群的高可用、负载均衡、易扩展等问题。Router作为一个流量转发
    LAMP架构之7——MySQL-Router(读写分离层)
    Zhaohui_Zhang的博客
    01-21 845
    一、mysql路由器——读写分离层 1、安装Mysql Router 安装包名称为< mysql-router-community-8.0.21-1.el7.x86_64.rpm >，安装完成后进入到</etc/mysqlrouter>目录下更改配置文件。 2、 更改配置<vim /etc/mysqlrouter/mysqlrouter.conf > [routing:ro] bind_address = 0.0.0.0 bind_port = 700.
    评论 6
    添加红包

    请填写红包祝福语或标题

    个

    红包个数最小为10个

    元

    红包金额最低5元

    当前余额3.43元 前往充值 >
    需支付：10.00元
    成就一亿技术人!
    领取后你会自动成为博主和红包主的粉丝 规则
    hope_wisdom
    发出的红包
    实付元
    使用余额支付
    点击重新获取
    扫码支付
    钱包余额 0

    抵扣说明：

    1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
    2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

    余额充值