MySQL Router实现MySQL的读写分离

共计 9636 个字符，预计需要花费 25 分钟才能阅读完成。

1. 简介

MySQL Router 是 MySQL 官方提供的一个轻量级 MySQL 中间件，用于取代以前老版本的 SQL proxy。

既然 MySQL Router 是一个数据库的中间件，那么 MySQL Router 必须能够分析来自前面客户端的 SQL 请求是写请求还是读请求，以便决定这个 SQL 请求是发送给 master 还是 slave，以及发送给哪个 master、哪个 slave。这样，MySQL Router 就实现了 MySQL 的读写分离，对 MySQL 请求进行了负载均衡。

因此，MySQL Router 的前提是后端实现了 MySQL 的主从复制。

MySQL Router 很轻量级，只能通过不同的端口来实现简单的读 / 写分离，且读请求的调度算法只能使用默认的 rr(round-robin)，更多一点、更复杂一点的能力都不具备。所以，在实现 MySQL Router 时，需要自行配置好后端 MySQL 的高可用。高可用建议通过 Percona XtraDB Cluster 或 MariaDB Galera 或 MySQL 官方的 group replication 实现，如果实在没有选择，还可以通过 MHA 实现。

所以，一个简单的 MySQL Router 部署图如下。

本文将使用 MySQL Router 分别实现后端无 MySQL 主从高可用情形的读写分离，至于为什么不实现后端有 MySQL 高可用的读写分离情形。在我看来，MySQL Router 只是一个玩具，不仅功能少，而且需要在应用程序代码中指定读 / 写的不同端口(见后文关于配置文件的解释)，在实际环境中应该没人会这样用。

2. 配置 MySQL Router

以下是实验环境。

因为后端 MySQL 主从复制没有实现高可用，所以只有一个 master 节点负责写操作。

所有后端 MySQL 节点都是刚安装好的全新 MySQL 实例，所以直接开启主从复制即可。如果是已有数据的主从复制，需要先保证它们已同步好，方法见：将 slave 恢复到 master 指定的坐标。

2.1 安装 MySQL Router

二进制版 MySQL Router 下载地址：https://dev.mysql.com/downloads/router/
 rpm 仓库：http://repo.mysql.com/yum/mysql-tools-community/el/7/x86_64/

此处使用二进制版的 MySQL Router 2.1.6。
tar xf mysqlrouter-2.1.6-linux-glibc2.12-x86-64bit.tar.gz
mv mysqlrouter-2.1.6-linux-glibc2.12-x86-64bit /usr/local/mysqlrouter

这就完了，就这么简单。

解压二进制包后，解压目录下有以下几个文件。
[root@s1 mr]# ls
bin  data  include  lib  run  share

bin 目录下只有一个二进制程序 mysqlrouter，这也是 MySQL Router 的主程序。

share 目录下有示例配置文件和示例 SysV 风格的启动脚本，但是很不幸该脚本基于 debian 平台，在 RedHat 系列上需要修改和安装一些东西才能使用。所以后文我自己写了一个 CentOS 下的 SysV 脚本。
[root@s1 mr]# ls share/doc/mysqlrouter/
License.txt  README.txt  sample_mysqlrouter.conf  sample_mysqlrouter.init

最后，将主程序添加到 PATH 环境变量中。
echo “PATH=$PATH:/usr/local/mysqlrouter/bin” >/etc/profile.d/mysqlrouter.sh
chmod +x /etc/profile.d/mysqlrouter.sh
source /etc/profile.d/mysqlrouter.sh

2.2 启动并测试 MySQL Router

以下是上述实验环境的配置文件，这里只有一个 master 节点 192.168.100.22:3306，如果有多个写节点(master)，则使用逗号分隔各节点。关于配置文件，后文会解释。
[DEFAULT]
config_folder = /etc/mysqlrouter
logging_folder = /usr/local/mysqlrouter/log
runtime_folder = /var/run/mysqlrouter

[logger]
level = INFO

[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
mode = read-only
connect_timeout = 1

[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306
mode = read-write
connect_timeout = 2

然后在 MySQL Router 所在的机器上创建上面使用的目录。
shell> mkdir /etc/mysqlrouter /usr/local/mysqlrouter/log /var/run/mysqlrouter

这样就可以启动 MySQL Router 来提供服务了(启动之前，请确保后端 MySQL 已被配置好主从复制)。
[root@s1 mr]# mysqlrouter &
[1] 16122

查看监听状态。这里监听的两个端口 7001 和 7002 是前端连接 MySQL Router 用的，它们用来接收前端发送的 SQL 请求，并按照读、写规则，将 SQL 请求路由到后端 MySQL 主从节点。
[root@s1 mr]# netstat -tnlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address        Foreign Address  State  PID/Program name
tcp        0      0 0.0.0.0:6032        0.0.0.0:*        LISTEN  1231/proxysql   
tcp        0      0 0.0.0.0:6033        0.0.0.0:*        LISTEN  1231/proxysql   
tcp        0      0 0.0.0.0:22          0.0.0.0:*        LISTEN  1152/sshd       
tcp        0      0 192.168.100.21:7001  0.0.0.0:*        LISTEN  16122/mysqlrouter
tcp        0      0 127.0.0.1:25        0.0.0.0:*        LISTEN  2151/master     
tcp        0      0 192.168.100.21:7002  0.0.0.0:*        LISTEN  16122/mysqlrouter
tcp6      0      0 :::22                :::*            LISTEN  1152/sshd       
tcp6      0      0 ::1:25              :::*            LISTEN  2151/master     

查看日志：
[root@s1 mr]# cat /usr/local/mysqlrouter/log/mysqlrouter.log
2018-07-07 10:14:29 INFO  [7f8a8e253700] [routing:slaves] started: listening on 192.168.100.21:7001; read-only

2018-07-07 10:14:29 INFO  [7f8a8ea54700] [routing:masters] started: listening on 192.168.100.21:7002; read-write

最后进行测试即可。测试前，先在后端 Master 上授权 MySQL Router 节点允许连接，它将会复制到两个 slave 节点上。
mysql> grant all on *.* to root@’192.168.100.%’ identified by ‘P@ssword1!’;

连上 MySQL Router 的 7002 端口，这个端口是负责写的端口。由于没有配置主从高可用，所以，简单测试下是否能写即可。
[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e ‘select @@server_id;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+————-+
| @@server_id |
+————-+
|        110 |
+————-+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e ‘create database mytest;’
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e ‘show databases;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+——————–+
| Database          |
+——————–+
| information_schema |
| mysql              |
| mytest            |
| performance_schema |
| sys                |
+——————–+

再测试下各 slave 节点，是否能实现 rr 调度算法的读请求的负载均衡。
[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e ‘select @@server_id;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+————-+
| @@server_id |
+————-+
|        120 |
+————-+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e ‘select @@server_id;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+————-+
| @@server_id |
+————-+
|        130 |
+————-+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7001 -e ‘show databases;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+——————–+
| Database          |
+——————–+
| information_schema |
| mysql              |
| mytest            |
| performance_schema |
| sys                |
+——————–+

显然，测试的结果一切正常。

这样看来 MySQL Router 好简单，确实好简单。只需提供一个合理的配置文件，一切都完成了。那么，下面解释下 MySQL Router 的配置文件。

3.MySQL Router 的配置文件解释

MySQL Router 的配置文件也很简单，需要配置的项不多。

mysql router 默认会寻找安装目录下的 ”mysqlrouter.conf” 和家目录下的 ”.mysqlrouter.conf”。也可以在二进制程序 mysqlrouter 命令下使用 ”-c” 或者 ”–config” 手动指定配置文件。

MySQL router 的配置文件是片段式的，常用的就 3 个片段：[DEFAULT]、[logger]、[routing:NAME]。片段名称区分大小写，且只支持单行 ”#” 或 ”;” 注释，不支持行中、行尾注释。

以上面示例的配置文件为例。
[DEFAULT]
config_folder = /etc/mysqlrouter
logging_folder = /usr/local/mysqlrouter/log
runtime_folder = /var/run/mysqlrouter

[logger]
level = INFO

[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
mode = read-only
connect_timeout = 1

[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306
mode = read-write
connect_timeout = 2

1.DEFAULT 片段的配置。

[DEFAULT]片段通常配置配置文件的目录、日志的目录、MySQL router 运行时的目录(如 pid 文件)。

例如：
[DEFAULT]
config_folder=/etc/mysqlrouter  # 指定额外的配置文件目录，该目录下的 conf 文件都会被加载
logging_folder=/usr/local/mysqlrouter/log  # 指定日志目录，日志文件名为 mysqlrouter.log
runtime_folder=/var/run/mysqlrouter        # 指定运行时目录，默认为 /run/mysqlrouter

2.logger 片段的配置。

[logger]片段只有一个选项，设置日志的记录级别。
[logger]
level=debug  # 有 debug、info(默认)、warning、error、fatal，不区分大小写

3.routing 片段的配置。

[routing:NAME]是 MySQL router 主要部分，设置不同的路由实例，其中 NAME 可以随意命名。如[routing:slaves]、[routing:masters]。

在 routing 配置片段，可以设置的选项包括：
•(1).bind_address 和 bind_port
 bind_address 和 bind_port 是 mysql router 监听前端 SQL 请求的地址和端口。其中端口是 MySQL Router 要求强制提供的，但可以不用 bind_port 绑定，因为它可用通过 bind_address 的 IP:PORT 格式指定。
 一个 routing 规则中只能设置一个地址监听指令，但可以通过 ”0.0.0.0″ 来监听主机上所有的地址。如果没有提供监听地址，则默认监听 127.0.0.1。
 另外，监听地址不能出现在 destinations 指令指定的列表中。
 示例如下：

[routing:slaves]
bind_port = 7001
[routing:slaves]
bind_address = 192.168.100.21
bind_port = 7001
[routing:slaves]
bind_address = 192.168.100.21:7001

一般来说，通过不同端口实现读 / 写分离，并非好方法，最大的原因是需要在应用程序代码中指定这些连接端口。但是，MySQL Router 只能通过这种方式实现读写分离，所以 MySQL Router 拿来当玩具玩玩就好。
•(2).destinations
定义 routing 规则的转发目标，格式为 HOST:PORT，HOST 可以是 IP 也可以是主机名，多个转发目标使用逗号分隔。如定义的目标列表是多个 slave。
[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306,192.168.100.100:3306
•(3).mode
 MySQL router 提供两种 mode：read-only 和 read-write。这两种方式会产生不同的转发调度方式。◦设置为 read-write，常用于设置 destinations 为 master 时，实现 master 的高可用。◦调度方式：当 MySQL router 第一次收到客户端请求时，会将请求转发给 destinations 列表中的第一个目标，第二次收到客户端请求还是会转发给第一个目标，只有当第一个目标联系不上 (如关闭了 MySQL 服务、宕机等) 才会联系第二个目标，如果所有目标都联系不上，MySQL Router 会中断。这种调度方式被称为 ”first-available”。

◦当联系上了某一个目标时，MySQL Router 会将其缓存下来，下次收到请求还会继续转发给该目标。既然是缓存的目标，就意味着在 MySQL Router 重启之后就会失效。

◦所以通过 MySQL Router 实现读写分离的写时，可以设置多个 master，让性能好的 master 放在 destinations 列表的第一个位置，其他的 master 放在后面的位置作为备用 master。

◦设置为 read-only，常用于设置 destinations 为 slave 时，实现 MySQL 读请求负载均衡。◦调度方式：当 MySQL route 收到客户端请求时，会从 destinations 列表中的第一个目标开始向后轮询(round-robin)，第一个请求转发给第一个目标，第二个请求转发给第二个目标，转发给最后一个目标之后的下一个请求又转发给第一个目标。如果第一个目标不可用，会依次向后检查，直到目标可用，如果所有目标都不可用，则 MySQL Router 中断。
◦那些不可用的目标会暂时被隔离，并且 mysql router 会不断的检查它们的状况，当重新可用时会重新加入到目标列表。

•(4).connect_timeout
 MySQL Router 联系 destinations 的超时时间，默认为 1 秒，值的范围为 1 -65536。应该尽量设置值小点，免得等待时间过长。
 对于 read-write 模式，可以将超时时间设置的稍长一点点，防止误认为主 master 不可用而去联系备 master。
 对于 read-only 模式，可以将超时时间设置的稍短一点点，因为这种模式下是 destinations 列表轮询的，即使误判了影响也不会太大。

•(5). 其他选项
 还能设置一些其他的指令，如使用的协议、最大请求数等，但是都可以不用设置使用默认值，它们都是 MySQL Router 结合 MySQL 优化过的一些选项，本身已经较完美了。

配置文件大概就这些内容，配置好后，记得先创建 default 片段中涉及到的目录。之后就可以启动 mysql router 提供读 / 写分离服务了。

4. 为 MySQL Router 提供 SysV 脚本

MySQL Router 只提供了一个主程序(bin 目录下的 mysqlrouter)，且该程序只能启动，没有停止选项，所以只能使用 kill 命令来杀掉进程。

MySQL Router 也提供了示例启动脚本，该脚本在位置为 $basedir/share/doc/mysqlrouter/sample_mysqlrouter.init，但是该脚本是基于 Debian 平台的，在 CentOS 上需要设置和安装一些东西，所以不用它，自己写个粗糙点的脚本即可。
shell> vim /etc/init.d/mysqlrouter
#!/bin/bash

# chkconfig: – 78 30
# Description: Start / Stop MySQL Router

DAEMON=/usr/local/mysqlrouter
proc=$DAEMON/bin/mysqlrouter
DAEMON_OPTIONS=”-c ${DAEMON}/mysqlrouter.conf”

. /etc/init.d/functions

start() {
    if [-e /var/lock/subsys/mysqlrouter]; then
        action “MySQL Router is working” /bin/false
    else
        $proc $DAEMON_OPTIONS & &>/dev/null
        retval=$?
        echo
    if [$retval -eq 0]; then
            touch /var/lock/subsys/mysqlrouter
        action “Starting MySQL Router” /bin/true
        else
        echo “Starting MySQL Router Failure”
        fi
    fi
}
   
stop() {
    if [-e /var/lock/subsys/mysqlrouter]; then
        killall $proc
        retval=$?
        echo
        if [$retval -eq 0]; then
            rm -f /var/lock/subsys/mysqlrouter
            action “Stoping MySQL Router” /bin/true
        fi
    else
        action “MySQL Router is not working” /bin/false
    fi
}

status() {
    if [-e /var/lock/subsys/mysqlrouter]; then
        echo “MySQL Router is running”
    else
        echo “MySQL Router is not running”
    fi
}

case “$1” in
    start)
        start
        sleep 1
        ;;
    stop)
        stop
        sleep 1
        ;;
    restart)
        stop
        start
        sleep 1
        ;;
    status)
        status
        ;;
    *)
        echo “Usage: $0 {start|stop|status|restart}”
        retval=1
        ;;
esac

exit $retval 

然后赋予执行权限。
shell> chmod +x /etc/init.d/mysqlrouter

：