Mycat 实现分库分表及读写分离
本文为博主原创，未经允许不得转载:
Mycat 官网： http://mycat.org.cn/
MyCat 权威指南 文档：http://www.mycat.org.cn/document/mycat-definitive-guide.pdf
一. 数据切分：垂直切分与横向切分
1.1 垂直切分
垂直切分：是按照不同的表来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；
垂直切分的优点：
1.拆分后业务清晰，拆分规则明确；
2.系统之间整合或扩展容易；
3.数据维护简单。
缺点：
1.部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度；
2.受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高；
3.事务处理复杂。
由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶 颈，所以就需要水平拆分来做解决。
1.2 横向切分
是根据 表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数 据的水平（横向）切分。
几种典型的分片规则包括：
1.按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中；
2.按照日期，将不同月甚至日的数据分散到不同的库中；
3.按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中。
切分原则都是根据业务找到适合的切分规则分散到不同的库。
优点：
1.拆分规则抽象好，join 操作基本可以数据库做；
2.不存在单库大数据，高并发的性能瓶颈；
3.应用端改造较少；
4.提高了系统的稳定性跟负载能力。
缺点：
1.拆分规则难以抽象；
2.分片事务一致性难以解决；
3.数据多次扩展难度跟维护量极大；
4.跨库 join 性能较差。
二. Mycat 简介
从定义和分类来看，它是一个开源的分布式数据库系统，是一个实现了 MySQL 协议的的 Server，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用 MySQL 原生（Native）协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信， 其核心功能是分表分库，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。
Mycat 发展到目前的版本，已经不是一个单纯的 MySQL 代理了，它的后端可以支持 MySQL、SQL Server、 Oracle、DB2、PostgreSQL 等主流数据库，也支持 MongoDB 这种新型 NoSQL 方式的存储。
Mycat 本身并不存储数据，数据是在后端的 MySQL 上存储的，因此数据可靠性 以及事务等都是 MySQL 保证的，
Mycat 就是一个近似等于 MySQL 的数据库服务器，你可以用连接 MySQL 的方式去连接 Mycat（除了端 口不同，默认的 Mycat 端口是 8066 而非 MySQL 的 3306，因此需要在连接字符串上增加端口信息），大多数 情况下，可以用你熟悉的对象映射框架使用 Mycat，但建议对于分片表，尽量使用基础的 SQL 语句，因为这样能 达到最佳性能，特别是几千万甚至几百亿条记录的情况下。
Mycat 是一个强大的数据库中间件，不仅仅可以用作读写分离、以及分表分库、容灾备份，而且可以用于多 租户应用开发、云平台基础设施、让你的架构具备很强的适应性和灵活性，借助于即将发布的 Mycat 智能优化模 块，系统的数据访问瓶颈和热点一目了然，根据这些统计分析数据，你可以自动或手工调整后端存储，将不同的 表映射到不同存储引擎上，而整个应用的代码一行也不用改变。
Mycat 是数据库中间件，就是介于数据库与应用之间，进行数据处理与交互的中间服务。
三.Mycat 原理
Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了 一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库， 并将返回的结果做适当的处理，最终再返回给用户。
当 Mycat 收到一个 SQL 时，会先解析这个 SQL，查找涉及到的表，然后看此表的定义，如果有分片规则， 则获取到 SQL 里分片字段的值，并匹配分片函数，得到该 SQL 对应的分片列表，然后将 SQL 发往这些分片去执 行，最后收集和处理所有分片返回的结果数据，并输出到客户端。以 select * from Orders where prov=?语句为 例，查到 prov=wuhan，按照分片函数，wuhan 返回 dn1，于是 SQL 就发给了 MySQL1，去取 DB1 上的查询 结果，并返回给用户。
四. 使用Mycat实现分库分表及读写分离
4.1 Mycat下载
下载地址： https://github.com/MyCATApache/Mycat-download
4.2 解压并修改配置文件
常用并修改配置文件介绍
-
server.xml：是Mycat服务器参数调整和用户授权的配置文件。
2. schema.xml：是逻辑库定义和表以及分片定义的配置文件。
3.rule.xml：是分片规则的配置文件，分片规则的具体一些参数信息单独存放为文件，也在这个目录下，配置文件修改需要重启MyCAT。
4. log4j.xml：日志存放在logs/log中，每天一个文件，日志的配置是在conf/log4j.xml中
5. autopartition-long.txt,partition-hash-int.txt,sequence_conf.properties，sequence_db_conf.properties：分片相关的id分片规则配置文件。
4.2.1 修改 server.xml 文件，并添加mycat 操作用户及权限等
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
<property name="useHandshakeV10">1</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property> <!--0 本地文件方式 1 数据库方式 2 时间戳方式-->
<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1开启 0关闭
-->
<property name="useOffHeapForMerge">1</property>
<!--
单位为m
-->
<property name="memoryPageSize">64k</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">false</property>
<!-- XA Recovery Log日志路径 -->
<!--<property name="XARecoveryLogBaseDir">./</property>-->
<!-- XA Recovery Log日志名称 -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
</system>
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着*-->
<!--例如<host host="127.0.0.*" user="root"/>-->
<!--例如<host host="127.0.*" user="root"/>-->
<!--例如<host host="127.*" user="root"/>-->
<!--例如<host host="1*7.*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<!--
<firewall>
<whitehost>
<host host="1*7.0.0.*" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<!--用户名是mycat-->
<user name="mycat" defaultAccount="true">
<!--密码是123456-->
<property name="password">123456</property>
<!---可访问的逻辑库有mycatdb-->
<property name="schemas">mycatdb</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">mycatdb</property> <!-- 对应schema.xml中标签schema的name属性 -->
<!--是否只读--> <property name="readOnly">true</property> </user> </mycat:server>
4.2.2 修改scheme.xml ，定义分库分表规则
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- checkSQLschema这个属性默认就是false，官方文档的意思就是是否去掉表前面的数据库的名称， ”select * from db1.testtable” ，设置为true就会去掉db1。但是如果db1的名称不是schema的名称，那么也不会被去掉， 因此官方建议不要使用这种语法。同时默认设置为false。--> <!-- sqlMaxLimit当该值设置为某个数值时。每条执行的 SQL 语句，如果没有加上 limit 语句，MyCat 也会自动的加上所对应的值。 例如设置值为 100，执行”select * from test_table”,则效果为“selelct * from test_table limit 100”. 注意:如果运行的 schema 为非拆分库的，那么该属性不会生效。--> <schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100"> <!-- name 该属性定义逻辑表的表名 --> <!-- dataNode 该属性定义这个逻辑表所属的 dataNode, 该属性的值需要和 dataNode 标签中 name 属性的值相互对应。 --> <!-- rule 该属性用于指定逻辑表要使用的规则名字，规则名字在 rule.xml 中定义，必须与 tableRule 标签中 name 属性属性值一一对应 --> <!-- ruleRequired 该属性用于指定表是否绑定分片规则，如果配置为 true，但没有配置具体 rule 的话 ，程序会报错。 --> <!-- primaryKey 该逻辑表对应真实表的主键， --> <!-- type 该属性定义了逻辑表的类型，目前逻辑表只有“全局表”和”普通表”两种类型。全局表定义type=”global”，不定义的就是普通表。 --> <!-- autoIncrement 主键是否自增长。 --> <!-- subTables 分表，分表目前不支持Join。 --> <!-- needAddLimit是否自动添加limit，默认是开启状态。关闭请谨慎。 --> <!--rule="mod-long" 指定规则在rule.xml中配置--> <table name="user" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" /> </schema> <!-- Name 定义数据节点的名字，这个名字需要是唯一的--> <!-- dataHost 该属性用于定义该分片属于哪个数据库实例 --> <!-- Database 该属性用于定义该分片属性哪个具体数据库实例上的具体库 --> <dataNode name="dn1" dataHost="localhost1" database="sharding1" /> <dataNode name="dn2" dataHost="localhost2" database="sharding2" /> <!-- name 唯一标识 dataHost 标签，供上层的标签使用--> <!-- maxCon 指定每个读写实例连接池的最大连接。 --> <!-- minCon 指定每个读写实例连接池的最小连接，初始化连接池的大小。 --> <!-- balance 负载均衡类型，目前的取值有4 种： “0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。 “1”，全部的 readHost 与 stand by writeHost（非主非从） 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。 ”2”，所有读操作都随机的在 writeHost、readhost 上分发。 ”3”，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压writeType 1. writeType=”0”, 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .2. writeType=”1”，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。默认0就好了！--> <!-- dbType 指定后端连接的数据库类型，目前支持二进制的 mysql 协议，还有其他使用 JDBC 连接的数据库。例如：mongodb、oracle、spark 等. --> <!-- dbDriver 指定连接后端数据库使用的 Driver，目前可选的值有 native 和 JDBC。使用 native 的话，因为这个值执行的是二进制的 mysql 协议，所以可以使用 mysql 和 maridb。其他类型的数据库则需要使用 JDBC 驱动来支持。 --> <!-- switchType “-1” 表示不自动切换； “1” 默认值，自动切换； “2” 基于 MySQL 主从同步的状态决定是否切换心跳语句为 show slave status； “3” 基于 MySQL galary cluster 的切换机制（适合集群）（1.4.1）心跳语句为 show status like ‘wsrep%’. -> <!-- tempReadHostAvailable 如果配置了这个属性 --> <!--writeHost 下面的 readHost 仍旧可用，默认 0 可配置(0,1)--> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <!--heartbeat 这个标签内指明用于和后端数据库进行心跳检查的语句.--> <!--例如：MYSQL 可以使用 select user()，Oracle 可以使用 select 1 from dual 等--> <heartbeat>select user()</heartbeat> <!--host 用于标识不同实例，一般 writeHost 我们使用*M1，readHost 我们用*S1。--> <!--url 后端实例连接地址。Native：地址：端口 JDBC：jdbc的url--> <!--password 后端存储实例需要的密码--> <!--user 后端存储实例需要的用户名字--> <!--weight 权重 配置在 readhost 中作为读节点的权重--> <!--usingDecrypt 是否对密码加密，默认0。具体加密方法看官方文档。--> <writeHost host="hostM1" url="47.94.158.155:3306" user="root" password="TJXtjx_19991007"> </writeHost> </dataHost> <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM2" url="47.94.158.155:3306" user="root" password="TJXtjx_19991007"> </writeHost> </dataHost> </mycat:schema>
4.2.3 修改rule.xml，指定分表策略
<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:rule SYSTEM "rule.dtd"> <mycat:rule xmlns:mycat="http://io.mycat/"> <tableRule name="rule1"> <rule> <columns>id</columns> <algorithm>func1</algorithm> </rule> </tableRule> <tableRule name="rule2"> <rule> <columns>user_id</columns> <algorithm>func1</algorithm> </rule> </tableRule> <tableRule name="sharding-by-intfile"> <rule> <columns>sharding_id</columns> <algorithm>hash-int</algorithm> </rule> </tableRule> <tableRule name="auto-sharding-long"> <rule> <columns>id</columns> <algorithm>rang-long</algorithm> </rule> </tableRule> <tableRule name="mod-long"> <rule> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <tableRule name="sharding-by-murmur"> <rule> <columns>id</columns> <algorithm>murmur</algorithm> </rule> </tableRule> <tableRule name="crc32slot"> <rule> <columns>id</columns> <algorithm>crc32slot</algorithm> </rule> </tableRule> <tableRule name="sharding-by-month"> <rule> <columns>create_time</columns> <algorithm>partbymonth</algorithm> </rule> </tableRule> <tableRule name="latest-month-calldate"> <rule> <columns>calldate</columns> <algorithm>latestMonth</algorithm> </rule> </tableRule> <tableRule name="auto-sharding-rang-mod"> <rule> <columns>id</columns> <algorithm>rang-mod</algorithm> </rule> </tableRule> <tableRule name="jch"> <rule> <columns>id</columns> <algorithm>jump-consistent-hash</algorithm> </rule> </tableRule> <function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash"> <property name="seed">0</property><!-- 默认是0 --> <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 --> <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 --> <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 --> <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 --> </function> <function name="crc32slot" class="io.mycat.route.function.PartitionByCRC32PreSlot"> </function> <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap"> <property name="mapFile">partition-hash-int.txt</property> </function> <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong"> <property name="mapFile">autopartition-long.txt</property> </function> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">3</property> </function> <function name="func1" class="io.mycat.route.function.PartitionByLong"> <property name="partitionCount">8</property> <property name="partitionLength">128</property> </function> <function name="latestMonth" class="io.mycat.route.function.LatestMonthPartion"> <property name="splitOneDay">24</property> </function> <function name="partbymonth" class="io.mycat.route.function.PartitionByMonth"> <property name="dateFormat">yyyy-MM-dd</property> <property name="sBeginDate">2015-01-01</property> </function> <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod"> <property name="mapFile">partition-range-mod.txt</property> </function> <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash"> <property name="totalBuckets">3</property> </function> </mycat:rule>
五. Mycat 常用命令
./mycat start 启动
./mycat stop 停止
./mycat status 查看启动状态
./mycat restart 重启服务
六。读写分离
原理：需要搭建主从模式，让主数据库（master）处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库（slave）处理 SELECT 查询操作。Mycat 配合数据库本身的复制功能，可以解决读写分离的问题。
修改 scheme.xml 文件
<dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100"> <heartbeat>show slave status</heartbeat> <writeHost host="hostM" url="192.168.25.111:3306" user="root" password="root"> <readHost host="hostS" url="192.168.25.222:3306" user="root" password="root" /> </writeHost> </dataHost>
(1)设置 balance="1"与writeType="0" Balance参数设置：
balance=“0”, 所有读操作都发送到当前可用的writeHost上。
balance=“1”，所有读操作都随机的发送到readHost。
balance=“2”，所有读操作都随机的在writeHost、readhost上分发
WriteType参数设置：
writeType=“0”, 所有写操作都发送到可用的writeHost上。
writeType=“1”，所有写操作都随机的发送到readHost。
writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。
readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1
(2)设置 switchType="2" 与slaveThreshold="100" switchType 目前有三种选择： -1：表示不自动切换 1 ：默认值，自动切换 2 ：基于MySQL主从同步的状态决定是否切换 Mycat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“
七.使用Navicat 连接mycat 验证
