数据分片
数据分片是在读写分离的基础上进行多个主从,所以在之前的基础上我们进行另一对主从的配置
主库配置参考: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数据库有全部的数据