MySQL使用Mycat实现分库分表-读写分离

Mycat

Mycat介绍

什么是Mycat?

官方网站：http://www.mycat.org.cn/

Mycat架构

Mycat核心概念

• Schema：由它指定逻辑数据库（相当于MySQL的database数据库）
• Table：逻辑表（相当于MySQL的table表）
• DataNode：真正存储数据的物理节点
• DataHost：存储节点所在的数据库主机（指定MySQL数据库的连接信息）
• User：Mycat的用户（类似于MySQL的用户，支持多用户）

MyCat主要解决的问题

• 海量数据存储
• 查询优化

MyCat对多数据库的支持

Mycat分片策略

Mycat支持水平分片和垂直分片：

• 水平分片：一个表格的数据分割到多个节点上，按照行分隔
• 垂直分片：一个数据库中多个表格A、B、C，A存储到节点1上，B存储到节点2上，C存储到节点3上
  
  
  Mycat通过定义表的分片规则来实现分片，每个表格可以捆绑一个分片规则，每个分片规则指定一个分片字段并绑定一个函数，来实现动态分片算法。
• Schema：逻辑库，与MySQL中的database（数据库）对应，一个逻辑库中定义了所包含的 Table
• Table：表，即物理数据库中存储的某一张表，与传统数据不同，这里的表格需要声明其所存储的逻辑数据节点DataNode，在此可以指定表的分片规则。
• DataNode：Mycat的逻辑数据节点，是存放table的具体物理节点，也称为分片节点，通过DataHost关联到后端某个具体数据库上
• DataHost：定义某个物理库的访问地址，用于捆绑到DataNode上

Mycat安装

注意：需要先安装JDK（操作系统如果是64位，必须安装64位的JDK）

• 第一步，下载MyCat

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

• 第二步，解压缩，得到Mycat目录

tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

• 第三步，进入mycat/bin，启动MyCat

# 启动命令
./mycat start
# 停止命令
./mycat stop
# 重启命令
./mycat restart
# 查看状态
./mycat  status

• 第四步，访问Mycat：使用mysql的客户端直接连接mycat服务，默认端口号为【8066】
  查看 server.xml，mycat的root账户密码默认为123456
  
  于是可以通过命令：mysql -uroot -p123456 -h192.168.254.132 -P8066 访问mycat了：
  

Mycat分片

配置schema.xml

schema.xml介绍

schema.xml作为Mycat中重要的配置文件之一，管理着Mycat的逻辑库、表、分片规则、DataNode以及DataHost之间的映射关系。弄懂这些配置，是正确使用Mycat的前提。

• schema 标签用于定义Mycat实例中的逻辑库
• table 标签定义了Mycat中的逻辑表
• dataNode 标签定义了Mycat中的数据节点，也就是我们通常所说的数据分片
• dataHost 标签在mycat逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写分离配置和心跳语句

schema.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<table name="item" dataNode="dn1,dn2,dn3" rule="mod-long" />
	</schema>
    <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="0"
         writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="192.168.254.128:3306" user="root" password="yw@910714" />
	</dataHost>
<mycat:schema>

配置server.xml

server.xml介绍

server.xml几乎保存了所有 mycat 需要的系统配置信息，最常用的是在此配置的用户名、密码及权限。

server.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
		<property name="defaultSqlParser">druidparser</property>
	</system>
	<user name="mycat">
		<property name="password">mycat</property>
		<property name="schemas">TESTDB</property>
	</user>
</mycat:server>

配置rule.xml

rule.xml介绍

rule.xml 里面就定义了我们队表进行拆分锁涉及到的规则定义，我们可以灵活的对表使用不同的分片算法，或者对表使用相同的算法但具体参数不同。这个文件里面主要有 tableRule 和 function 这两个标签，在具体使用过程中此配置文件可用于不用修改，使用默认即可。

rule.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
	<tableRule name="sharding-by-intfile">
		<rule>
			<columns>sharding_id</columns>
			<algorithm>hash-int</algorithm>
		</rule>
	</tableRule>
	<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
		<property name="mapFile">partition-hash-int.txt</property>
	</function>
</mycat:rule>

tableRule 标签配置说明：

• name：属性指定唯一的名字，用于标识不同的表规则
• rule：指定对物理表中的哪一列进行拆分和使用什么路由算法
• columns：指定要拆分的列名字
• algorithm：使用 function 标签中的 name 属性，连接表规则和具体路由算法。当然，多个表规则可以连接到同一个路由算法上，table标签内使用，让逻辑表使用这个规则进行分片。

function 标签配置说明：

• name：指定算法的名字
• class：指定路由算法具体的类的名字
• property：为具体的算法需要用到的一些属性

十个常用的分片规则

一、枚举法

<tableRule name="sharding-by-intfile">
	<rule>
		<columns>user_id</columns>
		<algorithm>hash-int</algorithm>
	</rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
	<!-- mapFile：指定分片函数需要的配置文件名称 -->
	<property name="mapFile">partition-hash-int.txt</property>
	<!-- type：默认值为0，0表示Integer，非零表示string -->
	<property name="type">0</property>
	<!-- defaultNode：指定默认节点，小于0表示不设置默认节点，大于等于0表示设置默认节点，0代表节点1 -->
	<property name="defaultNode">0</property>
</function>

• 默认节点的作用：枚举分片时，如果碰到不识别的枚举值，就让它路由到默认节点
• 如果不配置默认节点（default的值小于0），碰到不识别的枚举值就会报错：can‘t find datanode for sharding column:column_name val:ffffffff
• partition-hash-int.txt 配置：

10000 = 0
10010 = 1

二、固定分片hash算法

<tableRule name="rule1">
	<rule>
		<columns>user_id</columns>
		<algorithm>func1</algorithm>
	</rule>
</tableRule>
<function name="func1" class="io.mycat.route.function.PartitionByLong">
	<!-- partitionCount：指定分片个数列表 -->
	<property name="partitionCount">2,1</property>
	<!-- partitionLength：分片范围列表，分区长度：默认为最大2^n = 1024，即最大支持 1024 分区 
	约束：count,length：两个数组的长度必须是一致的。1024 = sum(count[i] * length[i])
	-->
	<property name="partitionLength">256,512</property>
</function>

• 示例：

@Test
public void testPartition() {
	// 本例的分区策略：希望将数据水平分成3份，前两份各占25%，第三份占50%（非均匀分区）
	// |<----------------------1024--------------------->|
	// |<----256---->|<----256---->|<--------512-------->|
	// | partition0  | partition1  |     partition2      |
	// | 共2份，故count[0] = 2      | 共1份，故 count[1] = 1|
	int[] count = new int[] {2, 1};
	int[] length = new int[] {256, 512};
	PartitionUtil pu = new PartitionUtil(count, length);

	// 下面代码演示分别以 offerId字段或memberId字段根据上述分区策略拆分的分配结果
	int DEFAULT_STR_HEAD_LEN = 8;	// cobar默认会配置为此值
	long offerId = 12345;
	String memberId = "qiushuo";

	// 若根据offerId分配，partNo1将等于0，即按照上述分区策略，offerId为12345时将会被分配到partition0中
	int partNo1 = pu.partition(offerId);
	// 若根据memberId分配，partNo2将等于2，即按照上述分区策略，memberId为qiushuo时将会被分配到partition2中
	int partNo2 = pu.partition(memberId, 0, DEFAULT_STR_HEAD_LEN);

	Assert.assertEquals(0, partNo1);
	Assert.assertEquals(2, partNo2);
}

• 如果需要平均分配设置——平均分为4分片，partitionCount*partitionLength = 1024

<function name="func1" class="io.mycat.route.function.PartitionByLong">
	<property name="partitionCount">4</property>
	<property name="partitionLength">256</property>
</function>

三、范围约定

<tableRule name="auto-sharding-long">
	<rule>
		<columns>user_id</columns>
		<algorithm>rang-long</algorithm>
	</rule>
</tableRule>
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
	<property name="mapFile">autopartition-long.txt</property>
</function>

• autopartition-long.txt文件内容：所有的节点配置都是从0开始，及0代表节点1，此配置非常简单，即预先制定可能的id范围对应某个分片

# range start-end, data node index
# K = 1000, M = 10000
0-500M = 0
500M-1000M = 1
1000M-1500M = 2
# 或者以下写法
# 0-10000000 = 0
# 10000001-20000000 = 1

四、求模法

<tableRule name="mod-long">
	<rule>
		<columns>user_id</columns>
		<algorithm>mod-long</algorithm>
	</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
	<!-- count：节点数量 -->
	<property name="count">3</property>
</function>

五、日期列分区法

<tableRule name="sharding-by-date">
	<rule>
		<columns>user_id</columns>
		<algorithm>sharding-by-date</algorithm>
	</rule>
</tableRule>
<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
	<!-- dateFormat：日期格式 -->
	<property name="dateFormat">yyyy-MM-dd</property>
	<!-- sBeginDate：开始日期 -->
	<property name="sBeginDate">2014-01-01</property>
	<!-- sPartionDay：分区天数，即默认从头开始日期算起，分隔10天一个分区 -->
	<property name="sPartionDay">10</property>
</function>

六、通配取模

<tableRule name="sharding-by-pattern">
	<rule>
		<columns>user_id</columns>
		<algorithm>sharding-by-pattern</algorithm>
	</rule>
</tableRule>
<function name="sharding-by-pattern" class="io.mycat.route.function.PartitionByPattern">
	<!-- patternValue：求模基数 -->
	<property name="patternValue">256</property>
	<!-- defaultNode：默认节点，如果不配置默认，则默认是0，即第一个节点 -->
	<property name="defaultNode">2</property>
	<!-- mapFile：配置文件路径 -->
	<property name="mapFile">partition-pattern.txt</property>
</function>

• partition-pattern.txt文件内容：

# id partition range start-end, data node index
# first host configuration
1-32 = 0
33-64 = 1
65-96 = 2
97-128 = 3
# second host configuration
129-160 = 4
161-192 = 5
193-224 = 6
225-256 = 7
0-0 = 7

• 配置文件中， 1-32代表id%256后分布的范围，如果在1-32则在分区1，其他类推
• 如果id非数字数据，则会分配在 defaultNode 默认节点

七、ASCII码求模通配

<tableRule name="sharding-by-prefixpattern">
	<rule>
		<columns>user_id</columns>
		<algorithm>sharding-by-prefixpattern</algorithm>
	</rule>
</tableRule>
<function name="sharding-by-prefixpattern" class="io.mycat.route.function.PartitionByPrefixPattern">
	<!-- patternValue：求模基数 -->
	<property name="patternValue">256</property>
	<!-- prefixLength：ASCII截取的位数 -->
	<property name="prefixLength">5</property>
	<!-- mapFile：配置文件路径 -->
	<property name="mapFile">partition-pattern.txt</property>
</function>

ASCII编码：

• 48-57:0-9阿拉伯数字
• 64、65-90：@、A-Z
• 97-122：a-z

• partition-pattern.txt文件内容：

# range start-end, data node index
# first host configuration
1-4 = 0
5-8= 1
9-12= 2
13-16= 3
# second host configuration
17-20= 4
21-24= 5
25-28= 6
29-32= 7
0-0 = 7

• 配置文件中， 1-32代表id%256后分布的范围，如果在1-32则在分区1，其他类推
• 此种方法类似于方式六，只不过采取的是将列中前prefixLength位所有ASCII码的和 与 patternValue 进行求模，即sum%patternValue，获取的值在通配范围内的，即分片数。

八、编程指定

<tableRule name="sharding-by-substring">
	<rule>
		<columns>user_id</columns>
		<algorithm>sharding-by-substring</algorithm>
	</rule>
</tableRule>
<function name="sharding-by-substring" class="io.mycat.route.function.PartitionDirectBySubString">
	<!-- startIndex：字符串截取的起始索引位置 -->
	<property name="startIndex">0</property>
	<!-- size：截取的位数 -->
	<property name="size">2</property>
	<!-- partitionCount：分区数量 -->
	<property name="partitionCount">8</property>
	<!-- defaultPartition：默认分区 -->
	<property name="defaultPartition">0</property>
</function>

• 此方法为直接根据字符子串（必须是数字）计算分区号（由应用传递参数，显示指定分区号），例如：id=05-100000002
• 在此配置中代表根据id中从 startIndex = 0开始，截取size=2位数字即05，05就是获取的分区，如果没传默认分配到defaultPartition

九、字符串拆分hash解析

<tableRule name="sharding-by-stringhash">
	<rule>
		<columns>user_id</columns>
		<algorithm>sharding-by-stringhash</algorithm>
	</rule>
</tableRule>
<function name="sharding-by-stringhash" class="io.mycat.route.function.PartitionByString">
	<!-- length：代表字符串hash求模基数 -->
	<property name="length">512</property>
	<!-- count：分区数 -->
	<property name="count">2</property>
	<!-- hashSlice：hash预算位，即根据子字符串 hash 运算 -->
	<property name="hashSlice">0:2</property>
</function>

“2” ==> (0, 2)
“1:2” ==> (1, 2)
“1:” ==> (1, 0)
“-1” ==> (-1, 0)
“:-1” ==> (0, -1)
“:” ==> (0, 0)

• 示例：

public class PartitionByStringTest{
	@Test
	public void test(){
		PartitionByString rule = new PartitionByString();
		String idVAl = null;
		rule.setPartitionLength("512");
		rule.setPArtitionCount("2");
		rule.init();
		rule.setaHashSlice("0:2");
		idVal = "0";
		Assert.assertEquals(rule, 0 == rule.calculate(idVal));
		idVal = "45a";
		Assert.assertEquals(rule, 1 == rule.calculate(idVal));

		rule = new new PartitionByString();
		rule.setPartitionLength("512");
		rule.setPArtitionCount("2");
		rule.init();
		rule.setaHashSlice("-4:0");
		idVal = "aaaabbb0000";
		Assert.assertEquals(rule, 0 == rule.calculate(idVal));
		idVal = "aaaabbb2359";
		Assert.assertEquals(rule, 0 == rule.calculate(idVal));
	}
}

十、一致性hash

<tableRule name="sharding-by-murmur">
	<rule>
		<columns>user_id</columns>
		<algorithm>sharding-by-murmur</algorithm>
	</rule>
</tableRule>
<function name="sharding-by-murmur" class="io.mycat.route.function.PartitionByMurmurHash">
	<!-- seed：默认是0 -->
	<property name="seed">0</property>
	<!-- count：要分区的数据库节点数量，必须指定，否则没法分片 -->
	<property name="count">2</property>
	<!-- virtualBucketTimes：一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
	<property name="virtualBucketTimes">160</property>
	<!---->
    <!--&lt;!&ndash; -->
	<!--weightMapFile：节点的权重，没有指定权重的节点默认是1。-->
	<!--以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值，所有的权重值必须是正整数，否则以1代替-->
	 <!--&ndash;&gt;-->
    <!--<property name="weightMapFile">weightMapFile</property>-->
    <!--&lt;!&ndash; -->
    <!--bucketMapPath：用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会被虚拟节点的murmur hash值与物理节点的映射按照行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西-->
     <!--&ndash;&gt;-->
    <!--<property name="bucketMapPath">/etc/mycat/bucketMapPath</property>-->
    <!---->
</function>

• 一致性 hash 预算有效解决了分布式数据的扩容问题，前 1-9 种 id 规则都多少存在数据扩容难题，而这第十种规则解决了数据扩容难点

测试分片

需求

• 把商品表分片存储到三个数据节点上
  
• 分片策略指定为“auto-sharding-long”
• 分片规则指定为“mod-long”

创建表

• 首先在 db1、db2、db3 上创建实际的物理表 item：

create table item(
	id int(11) not null,
	name varchar(20) default null,
	primary key(id)
) engine = InnoDB default charset = utf8;

分片测试

• 配置schema.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<table name="item" dataNode="dn1,dn2,dn3" rule="mod-long" />
	</schema>
    <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="0"
         writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="192.168.254.128:3306" user="root" password="yw@910714" />
	</dataHost>
<mycat:schema>

• 配置完成后，重新启动mycat，使用mysql客户端连接mycat
• 通过 mycat 客户端插入数据：

insert into item(id, name) values(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e'), (6, 'f');

• 分别查看db1、db2、db3中的item表的数据

db1	db2	db3

用户购物下单实践

需求

• 需求：把订单信息分片，商家要查询出售的订单，用户要查询自己的订单
• 表设计：用户、商家订单、订单明细、用户订单
• 分片规则：

“用户表”----user_id 取模；
“商家订单”----seller_user_id 取模；
“订单明细”----“商家订单” ER 分片；
“买家订单”----buyer_user_id 取模

服务器分配

• mysql

主	192.169.254.128		dn_master1
	从	192.169.254.129		dn_slave1
主	192.169.254.130		dn_master2
主	192.169.254.132		dn_master3

• mycat

192.168.254.132

业务表

• 分别在三台主机 dn_master1、dn_master2、dn_master3 分别创建 数据库 test_db，并在该数据库中创建如下表：

drop table if exists tb_user;
create table tb_user (
	login_name varchar(32) comment '登录名',
	user_id bigint comment '用户标识',
	type int comment '用户类型 1 商家，2 买家',
	passwd varchar(128) comment '密码',
	primary key(user_id)
) comment '用户表';

drop table if exists tb_seller_order;
create table tb_seller_order (
	seller_user_id bigint comment '商家标识',
	buyer_user_id bigint comment '用户标识',
	order_id bigint comment '订单标识',
	price bigint comment '价格',
	status int comment '状态',
	primary key(order_id)
) comment '商家订单表';

drop table if exists tb_order_detail;
create table tb_order_detail(
	seller_user_id bigint comment '商家标识',
	order_detail_id bigint comment '订单明细标识',
	order_id bigint comment '订单标识',
	goods_id bigint comment '商品标识',
	goods_name varchar(32) comment '商品名称',
	cnt int comment '数量',
	unit_price int comment '单价',
	primary key(order_detail_id)
)comment '订单明细';

drop table if exists tb_buyer_order;
create table tb_buyer_order (
	buyer_user_id bigint comment '用户标识',
	seller_user_id bigint comment '商家标识',
	order_id bigint comment '订单标识',
	price bigint comment '价格',
	status int comment '状态',
	primary key(order_id)
) comment '买家订单表';

表主键生成策略

使用mycat全局序列生成（mycat 全局序列：https://blog.csdn.net/convict_eva/article/details/51917499）

• 原理：在数据库中建立一张表，存放“名称”，“当前值”，“步长”（mycat 每次读取多少个 sequence）
• sequence 获取步骤：

* 当初次使用该 sequence 时，根据传入的 sequence 名称，从这个表中获取 current_value 和 increment 到 mycat 中，并将current_value 设置为 current_value + increment
* mycat 将本次读取到的 current_value + increment 作为本次的 sequence ，下次使用时自动加1，使用 increment 后，执行第一步操作
* mycat 维护这张张表，用到那些 sequence，往表中插入一条数据即可。若某次读取的 sequence 没有用完服务器就停止，则读过的 sequence 就不会再用，再从第一 步开始执行

• mycat 配置 server.xml：

<!-- 配置为1 ，表示使用数据库方式生成squence -->
<property name="sequnceHandlerType">1</property>

• sequence_db_conf.properties指定squence 所在结点：

# 注意：必须大写
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
TESTSEQ=dn1

• 数据初始化：

-- 创建表：
CREATE TABLE MYCAT_SEQUENCE (
   NAME VARCHAR (50) NOT NULL comment  "名称",
   current_value INT NOT NULL  comment  "当前值",
   increment INT NOT NULL DEFAULT 100  comment  "步长",
   PRIMARY KEY (NAME)
) ENGINE = INNODB ;

• 创建函数：

INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 100000, 100);

-- 取当前squence的值
DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE  FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) 
RETURNS varchar(64) CHARSET utf8
    DETERMINISTIC
BEGIN 
        DECLARE retval VARCHAR(64);
        SET retval="-999999999,null";  
        SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval 
          FROM MYCAT_SEQUENCE  WHERE name = seq_name;  
        RETURN retval ; 
END
;;
DELIMITER ;

-- 设置 sequence 值
DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64)
 CHARSET utf8
    DETERMINISTIC
BEGIN 
         UPDATE MYCAT_SEQUENCE  
                 SET current_value = current_value + increment 
                  WHERE name = seq_name;  
         RETURN mycat_seq_currval(seq_name);  
END
;;
DELIMITER ;

-- 取下一个sequence的值
DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) 
RETURNS varchar(64) CHARSET utf8
    DETERMINISTIC
BEGIN 
         UPDATE MYCAT_SEQUENCE  
                   SET current_value = value  
                   WHERE name = seq_name;  
         RETURN mycat_seq_currval(seq_name);  
END
;;
DELIMITER ;

• 注意：

* MYCAT_SEQUENCE 表和以上的 function，需要放在同一个节点上。这个节点就是 sequence_db_conf.properties 指定的数据库节点
* 远程创建function失败，需要对数据库做如下配置：
	/etc/my.cnf 下 my.ini 
	[mysqld]
	log_bin_trust_function_creators=1

• 在sequence_db_conf.properties文件中添加 mycat 全局序列：

# 注意：必须大写
#sequence stored in datanode
USER_ID_SQUE=dn_master1
ORDER_ID_SQUE=dn_master1
DETAIL_ID_SQUE=dn_master1
GLOBAL=dn_master1
# COMPANY=dn_master1
# CUSTOMER=dn_master1
# ORDERS=dn_master1
# TESTSEQ=dn_master1

• 在 dn_master1 节点执行：

insert into MYCAT_SEQUENCE(NAME, CURRENT_value, increment) values('USER_ID_SQUE', 1, 100), ('ORDER_ID_SQUE', 1, 100), ('DETAIL_ID_SQUE', 1, 100);

mysql> select * from mycat_sequence;
+----------------+---------------+-----------+
| NAME           | current_value | increment |
+----------------+---------------+-----------+
| DETAIL_ID_SQUE |             1 |       100 |
| GLOBAL         |        100000 |       100 |
| ORDER_ID_SQUE  |             1 |       100 |
| USER_ID_SQUE   |             1 |       100 |
+----------------+---------------+-----------+

• 重新启动mycat，测试：登录到mycat 服务器

mysql> mysql -uroot -pyw@910714 -h192.168.254.132 -P8066 -DTESTDB
# 查看序列
mysql> select next value for MYCATSEQ_USER_ID_SQUE;
mysql> select next value for MYCATSEQ_ORDER_ID_SQUE;
mysql> select next value for MYCATSEQ_DETAIL_ID_SQUE;

rule.xml 配置

• 配置 function 标签：找到 function 标签 name=“mode-long” 的 function 配置，修改总节点数为3（和使用的 mysql 节点数一致）。

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

• 配置 tableRule 标签：注意事项：name要全局唯一

<!-- user分片测试 -->
<tableRule name="tb_user_mode-long">
	<rule>
		<!-- 分片使用的字段 -->
		<columns>user_id</columns>
		<!-- 分片使用的方法，对应 function 名称 -->
		<algorithm>mod-long</algorithm>
	</rule>
</tableRule>
<!-- 卖家订单分片测试 -->
<tableRule name="seller_order_mode-long">
	<rule>		
		<columns>seller_user_id</columns>		
		<algorithm>mod-long</algorithm>
	</rule>
</tableRule>
<!-- 买家订单分片测试 -->
<tableRule name="buyer_order_mode-long">
	<rule>		
		<columns>buyer_user_id</columns>		
		<algorithm>mod-long</algorithm>
	</rule>
</tableRule>

schema.xml 配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<table name="tb_a" dataNode="dn_master1" />
		<!-- 全局表，使用type属性指定，多个节点要都执行建表语句，所有节点的数据一致 -->
		<table name="tb_global_test" dataNode="dn_master1, dn_master2, dn_master3" type="global"/>
		<!-- 配置表所在的分片节点，指定主键和分片规则，指定主键是为了使用主键查询时 mycat 什么缓存主键对应的dn，提高查询效率 -->
		<table name="tb_user" rule="tb_user_mode-long" primaryKey="user_id" dataNode="dn_master1, dn_master2, dn_master3"/>
		<table name="tb_seller_order" rule="seller_order_mode-long" primaryKey="order_id" dataNode="dn_master1, dn_master2, dn_master3">
			<!-- 配置ER分片，子表的存储依赖于主表，并且物理上紧邻存放 -->
			<childTable name="tb_order_detail" primaryKey="order_detail_id" joinKey="order_id" parentKey="order_id"/>
		</table>
		<table name="tb_buyer_order" rule="buyer_order_mode-long" primaryKey="order_id" dataNode="dn_master1, dn_master2, dn_master3"/>
	</schema>
	<dataNode name="dn_master1" dataHost="master1" database="test_db" />
	<dataNode name="dn_master2" dataHost="master2" database="test_db" />
 	<dataNode name="dn_master3" dataHost="master3" database="test_db" />
 	<dataHost name="master1" maxCon="1000" minCon="10" balance="3"
         writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <!-- 主从心跳语句配置 -->
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="192.168.254.128:3306" user="root" password="yw@910714">
			<!-- 从库 -->
			<readHost host="hostS2" url="192.168.254.129:3306" user="root" password="yw@910714"/>
		</writeHost>
	</dataHost>

	<dataHost name="master2" maxCon="1000" minCon="10" balance="0"
         writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM2" url="192.168.254.130:3306" user="root" password="yw@910714" />
	</dataHost>

	<dataHost name="master3" maxCon="1000" minCon="10" balance="0"
         writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM2" url="192.168.254.132:3306" user="root" password="yw@910714" />
	</dataHost>
<mycat:schema>

测试

• 重启mycat，使用 MySQL 客户端连接到 mycat，并执行建表语句

插入 user表 数据

INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-1',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-2',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-3',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-4',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-5',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-6',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-7',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-8',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-9',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-10',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-11',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-12',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-13',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-14',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-15',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-16',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-17',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-18',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-19',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-20',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-21',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-22',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-23',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');

• 192.168.254.128上的数据为：

mysql> select * from tb_user;
+------------+---------+------+----------+
| login_name | user_id | type | passwd   |
+------------+---------+------+----------+
| name-2     |     102 |    1 | passwd-A |
| name-5     |     105 |    1 | passwd-A |
| name-8     |     108 |    1 | passwd-A |
| name-11    |     111 |    1 | passwd-A |
| name-14    |     114 |    1 | passwd-A |
| name-17    |     117 |    1 | passwd-A |
| name-20    |     120 |    1 | passwd-A |
| name-23    |     123 |    1 | passwd-A |
+------------+---------+------+----------+
8 rows in set (0.00 sec)

• 192.168.254.130上的数据为：

mysql> select * from tb_user;
+------------+---------+------+----------+
| login_name | user_id | type | passwd   |
+------------+---------+------+----------+
| name-3     |     103 |    1 | passwd-A |
| name-6     |     106 |    1 | passwd-A |
| name-9     |     109 |    1 | passwd-A |
| name-12    |     112 |    1 | passwd-A |
| name-15    |     115 |    1 | passwd-A |
| name-18    |     118 |    1 | passwd-A |
| name-21    |     121 |    1 | passwd-A |
+------------+---------+------+----------+
7 rows in set (0.00 sec)

• 192.168.254.132上的数据为：

mysql> select * from tb_user;
+------------+---------+------+----------+
| login_name | user_id | type | passwd   |
+------------+---------+------+----------+
| name-1     |     101 |    1 | passwd-A |
| name-4     |     104 |    1 | passwd-A |
| name-7     |     107 |    1 | passwd-A |
| name-10    |     110 |    1 | passwd-A |
| name-13    |     113 |    1 | passwd-A |
| name-16    |     116 |    1 | passwd-A |
| name-19    |     119 |    1 | passwd-A |
| name-22    |     122 |    1 | passwd-A |
+------------+---------+------+----------+
8 rows in set (0.00 sec)

下单测试

• 插入 买家用户表 数据

INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('buyer-1',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('buyer-2',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('buyer-3',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A');

• 然后查看插入的数据是否按照 id 取模分片， 所有用户如下：

mysql> select * from tb_user order by login_name;
+------------+---------+------+----------+
| login_name | user_id | type | passwd   |
+------------+---------+------+----------+
| buyer-1    |     124 |    2 | passwd-A |
| buyer-2    |     125 |    2 | passwd-A |
| buyer-3    |     126 |    2 | passwd-A |
| name-1     |     101 |    1 | passwd-A |
| name-10    |     110 |    1 | passwd-A |
| name-11    |     111 |    1 | passwd-A |
| name-12    |     112 |    1 | passwd-A |
| name-13    |     113 |    1 | passwd-A |
| name-14    |     114 |    1 | passwd-A |
| name-15    |     115 |    1 | passwd-A |
| name-16    |     116 |    1 | passwd-A |
| name-17    |     117 |    1 | passwd-A |
| name-18    |     118 |    1 | passwd-A |
| name-19    |     119 |    1 | passwd-A |
| name-2     |     102 |    1 | passwd-A |
| name-20    |     120 |    1 | passwd-A |
| name-21    |     121 |    1 | passwd-A |
| name-22    |     122 |    1 | passwd-A |
| name-23    |     123 |    1 | passwd-A |
| name-3     |     103 |    1 | passwd-A |
| name-4     |     104 |    1 | passwd-A |
| name-5     |     105 |    1 | passwd-A |
| name-6     |     106 |    1 | passwd-A |
| name-7     |     107 |    1 | passwd-A |
| name-8     |     108 |    1 | passwd-A |
| name-9     |     109 |    1 | passwd-A |
+------------+---------+------+----------+
26 rows in set (0.05 sec)

• 下单：

SELECT NEXT VALUE FOR MYCATSEQ_ORDER_ID_SQUE;
INSERT INTO `tb_seller_order`(`seller_user_id`,`buyer_user_id`,`order_id`,`price`)
VALUES (225,248,201,1222);
INSERT INTO `tb_order_detail`
(`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`unit_price`)
VALUES (225, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,201,11,'goods_name',1,1220);
INSERT INTO `tb_order_detail`
(`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`unit_price`)
VALUES (225, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,201,11,'goods_name2',1,2);

说明：商家 225 在192.168.254.128上，tb_seller_order表根据 seller_user_id取模分片，所有此订单数据存储在与 user id为225的商家同意分片 tb_order_detail 表使用的是与 tb_seller_order ER分片，还有 order_id关联，所以 tb_order_detail 存储的分片与相同的 order_id 的tb_seller_order的数据在同一分片。

• 再测试一条数据：user_id为 238 的用户存储在 192.168.254.130 分片上。

INSERT INTO `tb_seller_order`(`seller_user_id`,`buyer_user_id`,`order_id`,`price`)
VALUES (238,248,203,1222);
INSERT INTO `tb_order_detail`
(`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`unit_price`)
VALUES (238, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,203,11,'goods_name',1,1220);
INSERT INTO `tb_order_detail`
(`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`unit_price`)
VALUES (238, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,203,11,'goods_name2',1,2);

• 测试结果：

mysql> select * from tb_user;
+------------+---------+------+----------+
| login_name | user_id | type | passwd   |
+------------+---------+------+----------+
| name-1     |     101 |    1 | passwd-A |
| name-4     |     104 |    1 | passwd-A |
| name-7     |     107 |    1 | passwd-A |
| name-10    |     110 |    1 | passwd-A |
| name-13    |     113 |    1 | passwd-A |
| name-16    |     116 |    1 | passwd-A |
| name-19    |     119 |    1 | passwd-A |
| name-22    |     122 |    1 | passwd-A |
| buyer-2    |     125 |    2 | passwd-A |
| name-3     |     103 |    1 | passwd-A |
| name-6     |     106 |    1 | passwd-A |
| name-9     |     109 |    1 | passwd-A |
| name-12    |     112 |    1 | passwd-A |
| name-15    |     115 |    1 | passwd-A |
| name-18    |     118 |    1 | passwd-A |
| name-21    |     121 |    1 | passwd-A |
| buyer-1    |     124 |    2 | passwd-A |
| name-2     |     102 |    1 | passwd-A |
| name-5     |     105 |    1 | passwd-A |
| name-8     |     108 |    1 | passwd-A |
| name-11    |     111 |    1 | passwd-A |
| name-14    |     114 |    1 | passwd-A |
| name-17    |     117 |    1 | passwd-A |
| name-20    |     120 |    1 | passwd-A |
| name-23    |     123 |    1 | passwd-A |
| buyer-3    |     126 |    2 | passwd-A |
+------------+---------+------+----------+
26 rows in set (0.01 sec)

mysql> select * from tb_seller_order;
+----------------+---------------+----------+-------+--------+
| seller_user_id | buyer_user_id | order_id | price | status |
+----------------+---------------+----------+-------+--------+
|            225 |           248 |      201 |  1222 |   NULL |
|            238 |           248 |      203 |  1222 |   NULL |
+----------------+---------------+----------+-------+--------+
2 rows in set (0.02 sec)

mysql> select * from tb_order_detail;
+----------------+-----------------+----------+----------+-------------+------+------------+
| seller_user_id | order_detail_id | order_id | goods_id | goods_name  | cnt  | unit_price |
+----------------+-----------------+----------+----------+-------------+------+------------+
|            225 |             101 |      201 |       11 | goods_name  |    1 |       1220 |
|            225 |             102 |      201 |       11 | goods_name2 |    1 |          2 |
|            238 |             103 |      203 |       11 | goods_name  |    1 |       1220 |
|            238 |             104 |      203 |       11 | goods_name2 |    1 |          2 |
+----------------+-----------------+----------+----------+-------------+------+------------+
4 rows in set (0.02 sec)

Mycat读写分离

• Mycat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制
• 数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于MySQL来说，标准的读写分离是主从模式，一个写节点 Master 后面跟着多个读节点，读节点的数量取决于系统的压力，通常是1-3个读节点的配置
  
• Mycat实现的读写分离和自动切换机制，需要MySQL的主从复制机制配合。

Mycat配置

Mycat 1.4 支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

<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.254.128" user="root" password="yw@910714">
		<readHost host="hostS" url="192.168.254.129" user="root" password="yw@910714"/>
	</writeHost>
</dataHost>

（1）设置balance="1"与writeType="0"

• balance负载均衡类型参数设置：

* balance = "0"：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
* balance = "1"：所有读操作都随机的发送到 readHost，全部的readHost与stand by writeHost参与select语句的负载均衡
* balance = "2"：所有读操作都随机在 writeHost、readHost 上分发
* balance = "3"：所有读请求随机分发到writeHost对应的readHost执行，writeHost不负担读压力

• writeType负载均衡类型参数设置：

* writeType = "0"：所有写操作都发送到当前可用的 writeHost 上，当第一个writeHost宕机时，切换到第二个writeHost，重新启动后以切换后的为准，切换记录在配置文件：dnindex.properties中
* writeType = "1"：所有写操作都随机的发送到 writeHost
* writeType = "2"：尚未实现，所有写操作都随机在 writeHost、readHost 上分发

• readHost是从属于writeHost的，即意味着它从那个 writeHost 获取同步数据，因此，当它所属的 writeHost 宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考虑，目前 mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个 writeHost 并设置balance = 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_Runing”三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。