/*
Navicat MySQL Data Transfer
Source Server : mysql
Source Server Version : 50527
Source Host : localhost:3306
Source Database : db3
Target Server Type : MYSQL
Target Server Version : 50527
File Encoding : 65001
Date: 2016-01-27 15:48:58
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `company`
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
`ID` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of company
-- ----------------------------
-- ----------------------------
-- Table structure for `hotnews`
-- ----------------------------
DROP TABLE IF EXISTS `hotnews`;
CREATE TABLE `hotnews` (
`ID` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hotnews
-- ----------------------------
INSERT INTO `hotnews` VALUES ('2');
-- ----------------------------
-- Table structure for `travelrecord`
-- ----------------------------
DROP TABLE IF EXISTS `travelrecord`;
CREATE TABLE `travelrecord` (
`ID` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of travelrecord
-- ----------------------------
INSERT INTO `travelrecord` VALUES ('3');
分别在两个读写库中建立三个分片
schema.xml中配置读写分离
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema checkSQLschema="false" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<table dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<!-- global table is auto cloned to all defined data nodes ,so can join
with any table whose sharding node is in the same data node -->
<table primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table primaryKey="ID" type="global" dataNode="dn1,dn2" />
<!-- random sharding using mod sharind rule -->
<table primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="mod-long" />
<table primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile" />
<table primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile">
<childTable primaryKey="ID" joinKey="customer_id"
parentKey="id">
</childTable>
</table>
<table dataNode="dn1" />
</schema>
<dataNode dataHost="localhost1" database="db1" />
<dataNode dataHost="localhost1" database="db2" />
<dataNode dataHost="localhost1" database="db3" />
<dataHost maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="ip1:3306" user="root" password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="ip2:3306" user="root" password="123456" weight="1" />
</writeHost>
</dataHost>
</mycat:schema>
其中balance属性
负载均衡类型，目前取值有3种： 1. balance="0", 开启读写分离机制，所有读操作都发送到当前可用的writeHost上。 2. balance="1"，全部的readHost和stand by writeHost参与select语句的负载均衡 3. balance="2"，所有读操作都随机在writeHost、readhost上分发。 4. balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力
以上ip1,ip2分别填写真实地址
测试读写分离
启动mycat服务，
测试读数据：select * from travelrecord
可见数据是从读库中读取
测试写数据：insert into travelrecord (ID) values(88)
查看读库，没值，写库有一条88的记录