mysql集群（四）——mycat中间件（数据分片）

数据分片

数据分片是在读写分离的基础上进行多个主从，所以在之前的基础上我们进行另一对主从的配置
主库配置参考：mysql集群（一）——主库配置
从库配置参考：mysql集群（二）——从库配置

2、mycat配置

（1）schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="itcast" checkSQLschema="false" sqlMaxLimit="100">
               <table name="tb_ad" dataNode="dn1,dn2" rule="mod-long" />
        </schema>
        <dataNode name="dn1" dataHost="cluster1" database="itcast" />
  		<dataNode name="dn2" dataHost="cluster2" database="itcast" />
        <dataHost name="cluster1" maxCon="1000" minCon="10" balance="3" writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
               <heartbeat>select user()</heartbeat>
      <writeHost host="W1" url="47.114.1.149:3316" user="root" password="123456">
<readHost host="W1R1" url="47.114.1.149:3317" user="root"password="123456"/>
</writeHost>
   </dataHost>
<dataHost name="cluster2" maxCon="1000" minCon="10" balance="3"
   writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
    <writeHost host="W1" url="47.114.1.149:3326" user="root" password="123456">
<readHost host="W1R1" url="47.114.1.149:3327" user="root"password="123456"/>
</writeHost>
    </dataHost>
</mycat:schema>

注意：<?xml version="1.0"?>需要顶格，并且是1.0版本。现在都是默认2.0
（2）rule.xml
设置mycat分片为两个

3、第二个集群配置

在主数据库中建库之后执行创建语句

CREATE TABLE `tb_ad` (
  `id` bigint(20) NOT NULL,
  `type` int(10) DEFAULT NULL COMMENT '广告类型',
  `title` varchar(100) DEFAULT NULL COMMENT '描述',
  `url` varchar(200) DEFAULT NULL COMMENT '图片URL地址',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在mycat数据库中执行插入数据语句

INSERT INTO `tb_ad` VALUES ('1', '1', 'UniCity万科天空之城', 'http://itcast-haoke.oss-cn-qingdao.aliyuncs.com/images/2018/11/26/15432029097062227.jpg', '2018-11-26 11:28:49', '2018-11-26 11:28:51');
INSERT INTO `tb_ad` VALUES ('2', '2', 'UniCity万科之城', 'http://itcast-haoke.oss-cn-qingdao.aliyuncs.com/images/2018/11/26/15432029097062227.jpg', '2018-11-26 11:28:49', '2018-11-26 11:28:51');
INSERT INTO `tb_ad` VALUES (’3', '3', 'UniCity天空之城', 'http://itcast-haoke.oss-cn-qingdao.aliyuncs.com/images/2018/11/26/15432029097062227.jpg', '2018-11-26 11:28:49', '2018-11-26 11:28:51');
INSERT INTO `tb_ad` VALUES ('4', '4', '万科天空之城', 'http://itcast-haoke.oss-cn-qingdao.aliyuncs.com/images/2018/11/26/15432029097062227.jpg', '2018-11-26 11:28:49', '2018-11-26 11:28:51');

可以发现master01和master02中分别有数据产生,mycat中的itcast数据库有全部的数据