数据库MySQL嘟嘟程序猿

MySQL读写分离

2019-06-04  本文已影响137人  Jerry_Liang

实现MySQL读写分离的前提是我们已经将MySQL主从复制配置完毕，可参考我上一篇关于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对每个表的分片策略进行配置和干涉。
优点：

不足：

为此，在数据库较多的情况下，生产环境下的部署可能是这样的：


部署图
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的下载。

优点：

不足：

对比以上两种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对外的"虚拟数据库"配置文件。

以上的用户名和密码我们都可以根据个人需求进行修改。

配置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 物理数据库中数据库名

dataHost

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

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

罗列了这么多的属性意思，想必大家已经知道需要配置什么了吧！我们可以根据自己的需求来进行配置，那么接下来我以简单的读写分离来示例配置，以下介绍修改的地方：

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

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


 <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 -u root -p #进入从数据库
stop slave; #关闭主从复制

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

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

验证结果图

至此，读写分离验证成功了！第二种小伙伴们可以亲自去尝试一下。对了，验证完记得到从数据库中start salve开启主从复制，避免以后忘了。

参考资料

https://segmentfault.com/a/1190000009520414
https://www.cnblogs.com/joylee/p/7513038.html
https://www.2cto.com/database/201709/676648.html

上一篇 下一篇

猜你喜欢

热点阅读