mycat单库分表

物理库建表
在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=3600

1.6的版本 单表配置rule mod-long时会报一个 count数量小于node数量的错误

升级版本1.6.5

https://blog.csdn.net/baogeda/article/details/78425813