MySQL Router实现MySQL的读写分离

https://www.cnblogs.com/f-ck-need-u/p/9276639.html

https://www.cnblogs.com/f-ck-need-u/p/9276639.html

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

以下是实验环境。

角色名主机IPMySQL版本数据状态MySQL Router192.168.100.21MySQL 5.7.22无master192.168.100.22MySQL 5.7.22全新实例slave1192.168.100.23MySQL 5.7.22全新实例slave2192.168.100.24MySQL 5.7.22全新实例

因为后端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。

12tarxfmysqlrouter-2.1.6-linux-glibc2.12-x86-64bit.tar.gzmvmysqlrouter-2.1.6-linux-glibc2.12-x86-64bit/usr/local/mysqlrouter

这就完了，就这么简单。

解压二进制包后，解压目录下有以下几个文件。

12[root@s1mr]#lsbindataincludelibrunshare

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

share目录下有示例配置文件和示例SysV风格的启动脚本，但是很不幸该脚本基于debian平台，在redhat系列上需要修改和安装一些东西才能使用。所以后文我自己写了一个centos下的SysV脚本。

12[root@s1mr]#lsshare/doc/mysqlrouter/License.txtREADME.txtsample_mysqlrouter.confsample_mysqlrouter.init

最后，将主程序添加到PATH环境变量中。

123echo"PATH=$PATH:/usr/local/mysqlrouter/bin">/etc/profile.d/mysqlrouter.shchmod+x/etc/profile.d/mysqlrouter.shsource/etc/profile.d/mysqlrouter.sh

2.2 启动并测试MySQL Router

以下是上述实验环境的配置文件，这里只有一个master节点192.168.100.22:3306，如果有多个写节点(master)，则使用逗号分隔各节点。关于配置文件，后文会解释。

19[DEFAULT]config_folder=/etc/mysqlrouterlogging_folder=/usr/local/mysqlrouter/logruntime_folder=/var/run/mysqlrouter[logger]level=INFO[routing:slaves]bind_address=192.168.100.21:7001destinations=192.168.100.23:3306,192.168.100.24:3306mode=read-onlyconnect_timeout=1[routing:masters]bind_address=192.168.100.21:7002destinations=192.168.100.22:3306mode=read-writeconnect_timeout=2

然后在MySQL Router所在的机器上创建上面使用的目录。

shell>mkdir/etc/mysqlrouter/usr/local/mysqlrouter/log/var/run/mysqlrouter

这样就可以启动MySQL Router来提供服务了(启动之前，请确保后端MySQL已被配置好主从复制)。

12[root@s1mr]#mysqlrouter&[1]16122

查看监听状态。这里监听的两个端口7001和7002是前端连接MySQL Router用的，它们用来接收前端发送的SQL请求，并按照读、写规则，将SQL请求路由到后端MySQL主从节点。

11[root@s1mr]#netstat-tnlpActiveInternetconnections(onlyservers)ProtoRecv-QSend-QLocalAddressForeignAddressStatePID/Programnametcp000.0.0.0:60320.0.0.0:*LISTEN1231/proxysqltcp000.0.0.0:60330.0.0.0:*LISTEN1231/proxysqltcp000.0.0.0:220.0.0.0:*LISTEN1152/sshdtcp00192.168.100.21:70010.0.0.0:*LISTEN16122/mysqlroutertcp00127.0.0.1:250.0.0.0:*LISTEN2151/mastertcp00192.168.100.21:70020.0.0.0:*LISTEN16122/mysqlroutertcp600:::22:::*LISTEN1152/sshdtcp600::1:25:::*LISTEN2151/master

查看日志：

1234[root@s1mr]#cat/usr/local/mysqlrouter/log/mysqlrouter.log2018-07-0710:14:29INFO[7f8a8e253700][routing:slaves]started:listeningon192.168.100.21:7001;read-only2018-07-0710:14:29INFO[7f8a8ea54700][routing:masters]started:listeningon192.168.100.21:7002;read-write

最后进行测试即可。测试前，先在后端Master上授权MySQL Router节点允许连接，它将会复制到两个slave节点上。

mysql>grantallon*.*toroot@'192.168.100.%'identifiedby'P@ssword1!';

连上MySQL Router的7002端口，这个端口是负责写的端口。由于没有配置主从高可用，所以，简单测试下是否能写即可。

22[root@s1mr]#mysql-uroot-pP@ssword1!-h292.168.100.21-P7002-e'select@@server_id;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------------+|@@server_id|+-------------+|110|+-------------+[root@s1mr]#mysql-uroot-pP@ssword1!-h292.168.100.21-P7002-e'createdatabasemytest;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@s1mr]#mysql-uroot-pP@ssword1!-h292.168.100.21-P7002-e'showdatabases;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+--------------------+|Database|+--------------------+|information_schema||mysql||mytest||performance_schema||sys|+--------------------+

再测试下各slave节点，是否能实现rr调度算法的读请求的负载均衡。

27[root@s1mr]#mysql-uroot-pP@ssword1!-h292.168.100.21-P7001-e'select@@server_id;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------------+|@@server_id|+-------------+|120|+-------------+[root@s1mr]#mysql-uroot-pP@ssword1!-h292.168.100.21-P7001-e'select@@server_id;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------------+|@@server_id|+-------------+|130|+-------------+[root@s1mr]#mysql-uroot-pP@ssword1!-h292.168.100.21-P7001-e'showdatabases;'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+--------------------+|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]。片段名称区分大小写，且只支持单行"#"或";"注释，不支持行中、行尾注释。

以上面示例的配置文件为例。

19[DEFAULT]config_folder=/etc/mysqlrouterlogging_folder=/usr/local/mysqlrouter/logruntime_folder=/var/run/mysqlrouter[logger]level=INFO[routing:slaves]bind_address=192.168.100.21:7001destinations=192.168.100.23:3306,192.168.100.24:3306mode=read-onlyconnect_timeout=1[routing:masters]bind_address=192.168.100.21:7002destinations=192.168.100.22:3306mode=read-writeconnect_timeout=2

1.DEFAULT片段的配置。

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

例如：

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

2.logger片段的配置。

[logger]片段只有一个选项，设置日志的记录级别。

12[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指令指定的列表中。
示例如下：

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

一般来说，通过不同端口实现读/写分离，并非好方法，最大的原因是需要在应用程序代码中指定这些连接端口。但是，MySQL Router只能通过这种方式实现读写分离，所以MySQL Router拿来当玩具玩玩就好。

(2).destinations
定义routing规则的转发目标，格式为HOST:PORT，HOST可以是IP也可以是主机名，多个转发目标使用逗号分隔。如定义的目标列表是多个slave。

123456[routing:slaves]bind_address=192.168.100.21:7001destinations=192.168.100.23:3306,192.168.100.24:3306[routing:masters]bind_address=192.168.100.21:7002destinations=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上需要设置和安装一些东西，所以不用它，自己写个粗糙点的脚本即可。

74shell>vim/etc/init.d/mysqlrouter#!/bin/bash#chkconfig:-7830#Description:Start/StopMySQLRouterDAEMON=/usr/local/mysqlrouterproc=$DAEMON/bin/mysqlrouterDAEMON_OPTIONS="-c${DAEMON}/mysqlrouter.conf"./etc/init.d/functionsstart(){if[-e/var/lock/subsys/mysqlrouter];thenaction"MySQLRouterisworking"/bin/falseelse$proc$DAEMON_OPTIONS&&>/dev/nullretval=$?echoif[$retval-eq0];thentouch/var/lock/subsys/mysqlrouteraction"StartingMySQLRouter"/bin/trueelseecho"StartingMySQLRouterFailure"fifi}stop(){if[-e/var/lock/subsys/mysqlrouter];thenkillall$procretval=$?echoif[$retval-eq0];thenrm-f/var/lock/subsys/mysqlrouteraction"StopingMySQLRouter"/bin/truefielseaction"MySQLRouterisnotworking"/bin/falsefi}status(){if[-e/var/lock/subsys/mysqlrouter];thenecho"MySQLRouterisrunning"elseecho"MySQLRouterisnotrunning"fi}case"$1"instart)startsleep1;;stop)stopsleep1;;restart)stopstartsleep1;;status)status;;*)echo"Usage:$0{start|stop|status|restart}"retval=1;;esacexit$retval

然后赋予执行权限。

shell>chmod+x/etc/init.d/mysqlrouter