物理库建表
在192.168.101.147单库dankufenbiao上建立5个分表Person1,Person2,Person3,Person4,Person5
USE `dankufenbiao`;
/*Table structure for table `Person1` */
DROP TABLE IF EXISTS `Person1`;
CREATE TABLE `Person1` (
`Id` int(11) NOT NULL,
`Name` varchar(50) DEFAULT NULL,
`UserId` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Table structure for table `Person2` */
DROP TABLE IF EXISTS `Person2`;
CREATE TABLE `Person2` (
`Id` int(11) NOT NULL,
`Name` varchar(50) DEFAULT NULL,
`UserId` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Table structure for table `Person3` */
DROP TABLE IF EXISTS `Person3`;
CREATE TABLE `Person3` (
`Id` int(11) NOT NULL,
`Name` varchar(50) DEFAULT NULL,
`UserId` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Table structure for table `Person4` */
DROP TABLE IF EXISTS `Person4`;
CREATE TABLE `Person4` (
`Id` int(11) NOT NULL,
`Name` varchar(50) DEFAULT NULL,
`UserId` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Table structure for table `Person5` */
DROP TABLE IF EXISTS `Person5`;
CREATE TABLE `Person5` (
`Id` int(11) NOT NULL,
`Name` varchar(50) DEFAULT NULL,
`UserId` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
配置mycat server.xml
加入一个逻辑库
<user name="root">
<property name="password">123456</property>
<property name="schemas">Test1,dankufenbiao</property>
<!--dankufenbiao为测试单库分表的逻辑库-->
</user>
此处dankufenbiao 为我们新加的一个逻辑库
配置mycat schema.xml
<schema name="Test1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<schema name="dankufenbiao" checkSQLschema="false" sqlMaxLimit="100">
<table name="Person" subTables="Person$1-5" dataNode="dn2" rule="dankufenbiao-mod-long" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="Test1" />
<dataNode name="dn2" dataHost="localhost2" database="dankufenbiao" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM1" url="192.168.101.148:3306" user="root"
password="">
<readHost host="hostS1" url="192.168.101.147:3306" user="root" password="123456" />
<readHost host="hostS2" url="192.168.101.149:3306" user="root" password="123456" />
</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="hostS3" url="192.168.101.147:3306" user="root" password="123456" />
</dataHost>
配置rule.xml
<tableRule name="dankufenbiao-mod-long">
<rule>
<columns>UserId</columns>
<algorithm>dankufenbiao-mod-long</algorithm>
</rule>
</tableRule>
<function name="dankufenbiao-mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">5</property>
</function>
测试
EXPLAIN INSERT INTO person (`Id`,`Name`,`UserId` ) VALUES(14,'2345623',14)
node | sql |
---|---|
dn2 | INSERT INTO Person5 (Id, Name, UserId) VALUES (14, ‘23223’, 14) |
EXPLAIN INSERT INTO person (`Id`,`Name`,`UserId` ) VALUES(11,'2345623',11)
node | sql |
---|---|
dn2 | INSERT INTO Person2 (Id, Name, UserId) VALUES (11, ‘23223’, 11) |
EXPLAIN explain select * from Person;
node | sql |
---|---|
dn2 | SELECT * FROM Person1 LIMIT 100 |
dn2 | SELECT * FROM Person2 LIMIT 100 |
dn2 | SELECT * FROM Person3 LIMIT 100 |
dn2 | SELECT * FROM Person4 LIMIT 100 |
dn2 | SELECT * FROM Person5 LIMIT 100 |
坑
启动时报:Startup failed: Timed out waiting for a signal from the JVM.
可修改conf/wrapper.conf中设置如下参数:
设置超时时间为2小时wrapper.startup.timeout=7200
设置心跳超时时间为1小时wrapper.ping.timeout=36001.6的版本 单表配置rule mod-long时会报一个 count数量小于node数量的错误
升级版本1.6.5