开源数据库中间件MyCat实现数据库读写分离、分表分库指南

IT米粉 数据和云 

前言

 

系统开发中,数据库是非常重要的一个点。除了程序的本身的优化,如:SQL语句优化、代码优化,数据库的处理本身优化也是非常重要的。主从、热备、分表分库等都是系统发展迟早会遇到的技术问题问题。MyCat是一个广受好评的数据库中间件,已经在很多产品上进行使用了。希望通过这篇文章的介绍,能学会MyCat的使用。

 

安装

 

MyCat官网:http://www.mycat.io/
可以了解下MyCat的背景和应用情况,这样使用起来比较有信心。

 

MyCat下载地址:http://dl.mycat.io/
官网有个文档,属于详细的介绍,初次入门,看起来比较花时间。

 

下载:
建议大家选择 1.6-RELEASE 版本,比较稳定。

 

安装:
根据不同的系统选择不同的版本。包括Linux、Windows、Mac,作者考虑还是非常周全的,当然,也有源码版的。(源码版的下载后,只要配置正确,就可以正常运行调试。)

 

开源数据库中间件MyCat实现数据库读写分离、分表分库指南

 

MyCat的安装其实只要解压下载的目录就可以了。

 

安装完成后,目录如下:

目录 说明
bin MyCat命令,启动、重启、停止等
catlet catlet为MyCat的一个扩展功能
conf MyCat 配置信息,重点关注
lib MyCat引用的jar包,MyCat是Java开发的
logs 日志文件,包括MyCat启动的日志和运行的日志。

 

配置

 

MyCat的配置文件都在conf目录里面,这里介绍几个常用的文件:

 

文件 说明
server.xml MyCat的配置文件,设置账号、参数等
schema.xml MyCat对应的物理数据库和数据库表的配置
rule.xml MyCat分片(分库分表)规则

 

MyCat的架构很好理解,MyCat是代理,MyCat后面就是物理数据库。和Web服务器的Nginx类似。对于使用者来说,访问的都是MyCat,不会接触到后端的数据库。

 

我们现在做一个主从、读写分离,简单分表的示例。结构如下图:

 

开源数据库中间件MyCat实现数据库读写分离、分表分库指南

 

服务器 IP 说明
MyCat 192.168.0.2 MyCat服务器,连接数据库时,连接此服务器
database1 192.168.0.3 物理数据库1,真正存储数据的数据库
database2 192.168.0.4 物理数据库2,真正存储数据的数据库

 

MyCat作为主数据库中间件,与代码是弱关联的,所以代码是不用修改的,使用MyCat后,连接数据库是不变的,默认端口是8066。连接方式和普通数据库一样,如:jdbc:mysql://192.168.0.2:8066/

 

server.xml

<user name="test">
        <property name="password">test</property>  
        <property name="schemas">lunch</property>  
        <property name="readOnly">false</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 登录的用户名,也就是连接MyCat的用户名
--password 登录的密码,也就是连接MyCat的密码
--schemas 数据库名,这里会和schema.xml中的配置关联,多个用逗号分开,例如需要这个用户需要管理两个数据库db1,db2,则配置db1,dbs
--privileges 配置用户针对表的增删改查的权限,具体见文档吧

 

这里配置了一个账号test,密码也是test,针对数据库lunch,读写权限都有,没有针对表做任何特殊的权限。

 

schema.xml

 

schema.xml是最主要的配置项,首先看我的配置文件。

 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<!-- 数据库配置,与server.xml中的数据库对应 -->
    <schema name="lunch" checkSQLschema="false" sqlMaxLimit="100">
        <table name="lunchmenu" dataNode="dn1"  />
        <table name="restaurant" dataNode="dn1"  />
        <table name="userlunch" dataNode="dn1"  />
        <table name="users" dataNode="dn1"  />
        <table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2"  rule="mod-long" />


    </schema>

<!-- 分片配置 -->
    <dataNode name="dn1" dataHost="test1" database="lunch" />
    <dataNode name="dn2" dataHost="test2" database="lunch" />

<!-- 物理数据库配置 -->
    <dataHost name="test1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostM1" url="192.168.0.2:3306" user="root" password="123456">  
        </writeHost>
    </dataHost>

    <dataHost name="test2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostS1" url="192.168.0.3:3306" user="root" password="123456">  
        </writeHost>
    </dataHost>

</mycat:schema>

 

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

 

每个节点的属性逐一说明:

 

schema:

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

 

table:

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

 

dataNode:

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

 

dataHost

属性 说明
name 物理数据库名,与dataNode中dataHost对应
balance 均衡负载的方式
writeType 写入方式
dbType 数据库类型
heartbeat 心跳检测语句,注意语句结尾的分号要加。

 

应用场景

 

数据库分表分库

 

配置如下:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<!-- 数据库配置,与server.xml中的数据库对应 -->
    <schema name="lunch" checkSQLschema="false" sqlMaxLimit="100">
        <table name="lunchmenu" dataNode="dn1"  />
        <table name="restaurant" dataNode="dn1"  />
        <table name="userlunch" dataNode="dn1"  />
        <table name="users" dataNode="dn1"  />
        <table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2"  rule="mod-long" />


    </schema>

<!-- 分片配置 -->
    <dataNode name="dn1" dataHost="test1" database="lunch" />
    <dataNode name="dn2" dataHost="test2" database="lunch" />

<!-- 物理数据库配置 -->
    <dataHost name="test1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostM1" url="192.168.0.2:3306" user="root" password="123456">  
        </writeHost>
    </dataHost>

    <dataHost name="test2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostS1" url="192.168.0.3:3306" user="root" password="123456">  
        </writeHost>
    </dataHost>

</mycat:schema>

 

我在192.168.0.2、192.168.0.3均有数据库lunch。

 

lunchmenu、restaurant、userlunch、users这些表都只写入节点dn1,也就是192.168.0.2这个服务,而dictionary写入了dn1、dn2两个节点,也就是192.168.0.2、192.168.0.3这两台服务器,分片的规则为:mod-long。

 

主要关注rule属性,rule属性的内容来源于rule.xml这个文件,MyCat支持10种分表分库的规则,基本能满足你所需要的要求,这个必须赞一个,其他数据库中间件好像都没有这么多。

 

table中的rule属性对应的就是rule.xml文件中tableRule的name,具体有哪些分表和分库的实现,建议还是看下文档。我这里选择的mod-long就是将数据平均拆分。因为我后端是两台物理库,所以rule.xml中mod-long对应的function count为2,见下面部分代码:

 
<tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>
    </function>

 

数据库读写分离

 

配置如下:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<!-- 数据库配置,与server.xml中的数据库对应 -->
    <schema name="lunch" checkSQLschema="false" sqlMaxLimit="100">
        <table name="lunchmenu" dataNode="dn1"  />
        <table name="restaurant" dataNode="dn1"  />
        <table name="userlunch" dataNode="dn1"  />
        <table name="users" dataNode="dn1"  />
        <table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1"  />


    </schema>

<!-- 分片配置 -->
    <dataNode name="dn1" dataHost="test1" database="lunch" />


<!-- 物理数据库配置 -->
    <dataHost name="test1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostM1" url="192.168.0.2:3306" user="root" password="123456">  
        <readHost host="hostM1" url="192.168.0.3:3306" user="root" password="123456">   
        </readHost>
        </writeHost>
    </dataHost>


</mycat:schema>

 

这样的配置与前一个示例配置改动如下:删除了table分配的规则,以及datanode只有一个,datahost也只有一台,但是writehost总添加了readhost,balance改为1,表示读写分离。

 

以上配置达到的效果就是102.168.0.2为主库,192.168.0.3为从库。

 

注意MyCat主从分离只是在读的时候做了处理,写入数据的时候,只会写入到writehost,需要通过MyCat的主从复制将数据复制到readhost,这个问题当时候我纠结了好久,数据写入writehost后,readhost一直没有数据,以为是自己配置的问题,后面才发现MyCat就没有实现主从复制的功能,毕竟数据库本身自带的这个功能才是最高效稳定的。

 

至于其他的场景,如同时主从和分表分库也是支持的,只要了解这个实现以后再去修改配置,都是可以实现的。而热备及故障专业官方推荐使用haproxy配合一起使用,大家可以试试。

 

使用

 

Mycat的启动也很简单,启动命令在Bin目录:

 
##启动
Mycat start

##停止
Mycat stop

##重启
Mycat restart

 

如果在启动时发现异常,在logs目录中查看日志。

  • Wrapper.log 为程序启动的日志,启动时的问题看这个;

  • Mycat.log 为脚本执行时的日志,SQL脚本执行报错后的具体错误内容,查看这个文件。mycat.log是最新的错误日志,历史日志会根据时间生成目录保存。

 

Mycat启动后,执行命令不成功,可能实际上配置有错误,导致后面的命令没有很好的执行。

 

Mycat带来的最大好处就是使用是完全不用修改原有代码的,在MyCat通过命令启动后,你只需要将数据库连接切换到MyCat的地址就可以了。如下面就可以进行连接了:

 
 mysql -h192.168.0.1 -P8806 -uroot -p123456

 

连接成功后可以执行SQL脚本了。

 

所以,可以直接通过SQL管理工具(如:navicat、datagrip)连接,执行脚本。我一直用datagrip来进行日常简单的管理,这个很方便。

 

MyCat还有一个管理的连接,端口号是9906。

 

 mysql -h192.168.0.1 -P9906 -uroot -p123456

 

连接后可以根据管理命令查看MyCat的运行情况,当然,喜欢UI管理方式的人,可以安装一个MyCat-Web来进行管理。

 

简而言之,开发中使用MyCat和直接使用MySQL机会没有差别。

 

常见问题

 

使用MyCat后总会遇到一些坑,我在此列举一下,希望能与大家有共鸣:

 

  • MyCat是不是配置以后,就能完全解决分表分库和读写分离问题?
    MyCat配合数据库本身的复制功能,可以解决读写分离的问题,但是针对分表分库的问题,不是完美的解决。或者说,至今为止,业界没有完美的解决方案。
    分表分库写入能完美解决,但是,不能完美解决主要是联表查询的问题,MyCat支持两个表联表的查询,多余两个表的查询不支持。 很多数据库中间件关于分表分库后查询的问题,都是需要自己实现的,而且节本都不支持联表查询,MyCat已经算做得非常先进了。
    分表分库的后联表查询问题,通过合理数据库设计来避免。

  • MyCat支持哪些数据库,其他平台如 .NET、PHP能用吗?
    官方说了,支持的数据库包括MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库。
    尽量用MySQL,我试过SQL Server,会有些小问题,因为部分语法有点差异。

  • MyCat非JAVA平台如 .NET、PHP能用吗?
    可以用。这一点MyCat做的也很棒。

 

参考

《Mycat权威指南》:http://www.mycat.io/document/Mycat_V1.6.0.pdf

原文:http://www.cnblogs.com/joylee/p/7513038.html

 

 

上一篇:1369 xth 砍树


下一篇:基于MYCAT中间件实现MYSQL读写分离