MySQL读写分离

读写分离实现方式：
1）配置多数据源；
2）使用mysql的proxy中间件代理工具；
第一种方式中，数据库和Application是有一定侵入性的，即我们的数据库更换时，application中的配置文件是需要手动修改的。而第二种方式中，我们可选择mysql proxy固定连接一个数据库，即使数据库地址更换也无需更换项目中的数据库连接配置。
同样，在开始配置实现MySQL读写分离之前，我们会遇到一个选型问题，那就是在诸多的MySQL的proxy中间件工具中，如mysql-proxy、atlas、cobar、mycat、tddl、tinnydbrouter和mysql router等，我们该如何取舍呢？所以在择工具实现前，我们先对以上的proxy中间件做一个简单的优劣介绍，以便我们根据不同的场景选择。

1.MySQL的proxy中间件工具优劣
以下主要对比MyCat和MySQL Router。

1.1 MyCat
是基于阿里巴巴的Cobar方案优化而来，支持半自动化分片，join。为什么叫"半自动化"呢？因为需要DBA对每个表的分片策略进行配置和干涉。
优点：

功能较丰富，对读写分离和分库分表都有支持；
易用，且对原有的应用系统侵入比较小，系统改造比较易于实现；
支持故障切换；
不足：

在整个系统中，MyCat作为一个单节点来路由其他数据库，在数据库比较多的情况下，MyCat本身的CPU性能压力会越来越大。因此，在生产系统中，MyCat不可避免的会需要一些高可用的手段；
同样，由于MyCat本身需要解析sql，也需要合并各个数据库返回的结果，本身CPU消耗会比较高，当达到一定临界点时，CPU可能会不堪重负。
为此，在数据库较多的情况下，生产环境下的部署可能是这样的：

1.2 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的下载。

优点：

类似于nginx，位于Application与MySQL Server之间。Application不再直连MySQL Server，而是与Router相连，根据Router的配置，将会把应用程序的读、写请求转发给下游的MySQL Server；
支持故障切换：当下游某个Server失效时，Router可以将其从Active列表中移除，当其online后再次加入Active列表，即提供了Failover特性；
当MySQL Server集群拓扑变更时，比如增减Slaves节点，只需要修改Router配置即可，无需修改应用中的数据库连接配置；
如果MySQL Servers为5.7+版本，且构建为InnoDB Cluster模式，那么Router还能基于metaCache（metaServers）机制，感知MySQL Servers的主从切换、从库增减等集群拓扑变更，而且基于变更能够实现Master自动切换、Slaves列表自动装配等。比如Master失效后，Cluster将会自动选举一个新的Master，此时Router不需要任何调整、可以自动发现此新Master进而继续为应用服务。
不足：

Router中间件本身不会对请求“拆包”（unpackage），所以无法在Router中间件上实现比如“SQL审计”、“隔离”、“限流”、“分库分表”等功能。但是Router提供了plugin（C语言）机制，我们可以开发自己的plugin来扩展Router的额外特性；
数据存储在内存中，数据量较大时，硬件需求会提升；
在非InnoDB Cluster架构模式下，如果主从库拓扑变更，需要手动修改Router配置。且Router不支持“reload”，修改配置后需要重启，这在一定程度上会影响Application的服务可用性。
对比以上两种proxy工具，本文选择了MyCat实现。

2.MyCat实现MySQL读写分离
实验环境

服务器名称 版本 MySQL版本 IP
MyCat代理中间件 Centos7.3 - 192.168.ww.ww
主数据库 Centos7.3 5.7 192.168.xx.xx
从数据库 Centos7.3 5.7 192.168.yy.yy
2.1 安装MyCat
安装JDK
因为MyCat是用java语言编写的，需要JDK支持，JDK安装可参考此博客：点此查看。

安装MyCat
本文下载的版本为Mycat-server-1.6.5-release-20180122220033-linux.tar.gz，点击下载

将压缩包用xftp上传到服务器/usr/local/下并解压

cd /usr/local/
tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

配置环境变量vim /etc/profile 在文件末尾加入如下代码，并保存：

MYCAT_HOME=/usr/local/mycat
PATH=$MYCAT_HOME/bin:$PATH
export MYCAT_HOME PATH

使配置文件生效source /etc/profile；

2.2配置MyCat
MyCat常用配置文件
文件位置都在mycat下的conf目录中：

文件	说明
server.xml	MyCat的配置文件，设置账号、参数等
schema.xml	MyCat对应的物理数据库和数据库表的设置
rule.xml	MyCat分片(分库分表)规则
wrapper.conf	MyCat启动日志信息

配置server.xml

server.xml中主要配置内容如下(此为默认配置)，其他部分默认即可

避免图片失效，多粘一份吧=-=！

<user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>

                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
 </user>

MyCat将多个MySQL集群整合起来对外提供服务，提供的服务接口仍然采用MySQL的形式。以上为MyCat对外的"虚拟数据库"配置文件。

以上片段为MyCat默认配置的两个虚拟用户，分别为用户名为root和用户名为user的两个虚拟用户；
默认用户为root用户，该用户没有配置readOnly的属性，为此拥有读写权限。而用户名为user的用户配置了readOnly的属性为true，为此只有读权限；
root的密码被设置为123456，而user的密码被设置为user；
两者使用的都是TESTDB逻辑库，TESTDB逻辑库的配置在schema.xml中
以上的用户名和密码我们都可以根据个人需求进行修改。

配置schema.xml

以下为schema.xml默认的配置文件(其实我删了一小部分schema中的table，因为我们目前做的只是读写分离，因此忽略此部分)：

<mycat:schema xmlns:mycat="http://io.mycat/">
        <!--逻辑数据库配置，name与server.xml中配置的数据库对应-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <!-- 如果只是做读写分离，那么我们就不需要配置这个table -->
                <!--<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />-->
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <!--设置实际服务器中数据库-->
        <dataNode name="dn1" dataHost="localhost1" database="db1" />
   
         <!--物理数据库配置-->
  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="localhost:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
                </writeHost>
                <writeHost host="hostS1" url="localhost:3316" user="root"
                                   password="123456" />
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>
</mycat:schema>

简单解释一下上面代码各参数的含义：

参数	说明
schema	数据库设置，此数据库为逻辑数据库，name与server.xml中的schema对应
dataNodel	分片信息，也就是分库相关配置
dataHost	物理数据库，真正存储数据的数据库

每个节点的属性详细说明：
schema

属性	说明
name	逻辑数据库名称，与server.xml中的schema对应
checkSQLschema	数据库前缀相关设置，建议看文档，这里暂时设为folse
sqlMaxLimit	select 时默认的limit，避免查询全表
dataNode	分库配置

table

属性	说明
name	表名，物理数据库中表名
dataNode	表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的name
primaryKey	主键字段名，自动生成主键时需要设置
autoIncrement	是否自增
rule	分片规则名

dataNode

属性	说明
name	节点名，与table中dataNode对应
dataHost	物理数据库名，与datahost中name对应
database	物理数据库中数据库名
database	数据库类型

dataHost

属性	说明
name	物理数据库名，与dataNode中dataHost对应
balance	负载均衡策略，0为不开启读写分离，1为开启读写分离
writeType	写入方式
dbType	数据库类型
heartbeat	心跳检测语句，注意语句结尾的分号要加

详细介绍以下几个属性值：

balance=“1”：全部的readHost与stand by writeHost参与select语句的负载均衡；
writeType=“0”：所有的写操作都发送到配置文件中的第一个writeHost。(注意：第一个writeHost故障切换到第二个后，即使之后修复了仍然维持第二个为写库)。推荐取值为0，不建议修改；
switchType=“1”：1为默认值，即自动切换。
罗列了这么多的属性意思，想必大家已经知道需要配置什么了吧！我们可以根据自己的需求来进行配置，那么接下来我以简单的读写分离来示例配置，以下介绍修改的地方：

在以上默认配置文件schema中并没有设置属性dataNode，为此我们加入dataNode=“dn1”，其中dn1对应部分中的name属性值；同时，将默认设置的table部分注释掉，最终schema剩余部分如下

<schema name="test" checkSQLschema="false" sqlMaxLimit="100"  dataNode="dn1" >
 <!-- 本文做的是单纯的读写分离配置为此此处不需要table ，将默认的table注释掉   
    <table name="travelrecord" dataNode="dn1" rule="auto-sharding-long" />
    -->    
</schema>

将部分中的database属性值改为我们实际储存数据的数据库名称；默认配置中给我们设置了3个dataNode，本环境中只有一个主数据库和一个从数据库，为此我们只保留一个dataNode，如下

<!--其中database为这是连接的数据库名称，我配置的是我真实数据库中的spring数据库-->
<dataNode name="dn1" dataHost="localhost1" database="spring" />

部分的中的host属性值可改可不改，但是url需要改成我们真实数据库的地址，因为我们在主库中进行写操作，为此此处的url改为我们的主数据ip，即url=“192.168.xx.xx”；而user与password两个属性的属性值设置为连接主数据库的用户名和密码；同时，需要将balance的属性值改为1，即balance=“1”，若为0会在测试时发现读写都是在主库执行；

部分设置host属性值可改可不改；而url改为我们的从数据库ip，即url=“192.168.yy.yy”，user和password设置为连接从数据库的用户名和密码；

此处因为实验环境是一个主数据库，一个从数据库，为此这里只配置了一个WriteHost和一个readHost；在默认的配置文件中可以看到是可以配置多个的，我们将多余的一个writeHost，最终剩下如下部分

 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.xx.xx:3306" user="root"
                                   password="password">
                        <readHost host="hostS2" url="192.168.yy.yy:3306" user="root" password="password" />
                </writeHost>
 </dataHost>

log4j2.xml
将日志等级改为debug

<asyncRoot level="debug" includeLocation="true">
            <!--<AppenderRef ref="Console" />-->
            <AppenderRef ref="RollingFile"/>
</asyncRoot>

至此，整体配置已经完成了，我们开始进行测试！

3.测试配置是否成功
开启MyCat
我们要开启MyCat直接输入启动指令即可，后两条指令为我们停止和重启的时候使用；

cd /usr/local/mycat/bin
# 启动
./mycat start

#停止
./mycat stop

#重启
./mycat restart 

查看端口
其中9066为虚拟schema管理端口，用于查看MyCat运行的情况；
其中8066为虚拟schema登录端口，用于SQL管理，跟普通MySQL差不多

netstat -tnlp

登录MyCat读写分离服务

# 9066是管理端口
mysql -u root -p 123456 -h 127.0.0.1 -P 9066

查看心跳检测

show @@help; #查看帮助
show @@heartbeat; #查看心跳
#RS_CODE为1表示心跳正常

查看机器的读写分离配置情况

show @@datasource;

可以看到hostM1拥有W写权限，hostS2拥有R读权限

MyCat读写分离验证
登录到MyCat的SQL管理服务：

mysql -u root -p 123456 -h 127.0.0.1 -P 8066

可以用简单的指令查看当前数据库

show databases;
use xxx; # 其中xxx为刚才看到的数据库中的一个
show tables; 
select * from jerry; #为后续做验证准备，这个我们可以按照我们真实表来，此处因为我的数据库中有jerry表，所以以此来示例

验证部分
有两种思路来验证：
1） 在从数据中关闭slave(即关闭主从复制)；然后在mycat管理端中往某个表中插入一条数据；再使用select查询该表，可以看到查询出来的结果中并没有新的那条数据。（解释：因为关闭了主从复制，插入新数据在主库进行，而查询的是从库，为此不会查询到新插入的数据）；
2）不关闭slave的主从复制，直接在从库中修改表中的某个值，而主库的值不变，直接使用查询表数据时会发现查询出来的结果是从库表中的数据（可以根据改变的值对比看出）

本文主要使用第一种思路进行验证：

打开从数据库服务器，并进入mysql中，并停止上篇文章中配置的主从复制；

mysql -u root -p #进入从数据库
stop slave; #关闭主从复制

再回到我们的mycat安装的服务器中，在已登录的MyCat的SQL管理服务中进行插入一条数据，我的示例如下；

insert into jerry (name) values ('liang')；#我表id是自增的，所以只插入name
select * from jerry；#查看

可以发现并没有刚插入的数据，我们再打开主数据库，查看是否有更新；（是因为我多次测试，之前没把balance属性值设置为1，导致读写一直是在主库执行，为此主键已经到12了==！）

至此，读写分离验证成功了！对了，验证完记得到从数据库中start salve开启主从复制，避免以后忘了。