mycat同机多库分表

一个逻辑表分到一台主机的多个数据库中。

建表

在一台主机上见3个数据库，在三个数据库中分别建立TongPerson表

USE `tongjifenku1`;

/*Table structure for table `TongPerson` */

DROP TABLE IF EXISTS `TongPerson`;

CREATE TABLE `TongPerson` (
  `Id` int(11) NOT NULL,
  `Name` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,
  `UserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

USE `tongjifenku2`;

/*Table structure for table `TongPerson` */

DROP TABLE IF EXISTS `TongPerson`;

CREATE TABLE `TongPerson` (
  `Id` int(11) NOT NULL,
  `Name` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,
  `UserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

USE `tongjifenku3`;

/*Table structure for table `TongPerson` */

DROP TABLE IF EXISTS `TongPerson`;

CREATE TABLE `TongPerson` (
  `Id` int(11) NOT NULL,
  `Name` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,
  `UserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

配置server.xml

给root用增加逻辑库tongjifenbiao

<user name="root" defaultAccount="true">
       <property name="password">123456</property>
       <property name="schemas">Test1,dankufenbiao,tongjifenbiao</property>
</user>

配置scheam.xml

<schema name="tongjifenbiao" checkSQLschema="false" sqlMaxLimit="100">
        <table name="TongPerson"  dataNode="dn3,dn4,dn5" rule="tongjifenbiao-mod-long" />
</schema>
<dataNode name="dn3" dataHost="localhost3" database="tongjifenku1" />
<dataNode name="dn4" dataHost="localhost3" database="tongjifenku2" />
<dataNode name="dn5" dataHost="localhost3" database="tongjifenku3" />
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" 
         dbDriver="native" switchType="1" slaveThreshold="100">
         <heartbeat>select user()</heartbeat>

         <writeHost host="hostS4" url="192.168.101.149:3306" user="root" password="123456" />
</dataHost>

配置rule.xml

<dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" 
         dbDriver="native" switchType="1" slaveThreshold="100">
         <heartbeat>select user()</heartbeat>

         <writeHost host="hostS4" url="192.168.101.149:3306" user="root" password="123456" />
</dataHost>
<function name="tongjifenbiao-mod-long" class="io.mycat.route.function.PartitionByMod">
         <!-- how many data nodes -->
        <property name="count">3</property>
</function>

验证

EXPLAIN INSERT INTO TongPerson (`Id`,`Name`,`UserId` ) VALUES(11,'2345623',11);

DATA_NODE	SQL
dn5	INSERT INTO TongPerson (Id,Name,UserId ) VALUES(11,’2345623’,11)

EXPLAIN INSERT INTO TongPerson (`Id`,`Name`,`UserId` ) VALUES(11,'2345623',12);

DATA_NODE	SQL
dn3	INSERT INTO TongPerson (Id,Name,UserId ) VALUES(11,’2345623’,12)

EXPLAIN INSERT INTO TongPerson (`Id`,`Name`,`UserId` ) VALUES(11,'2345623',13);

DATA_NODE	SQL
dn4	INSERT INTO TongPerson (Id,Name,UserId ) VALUES(11,’2345623’,13)

EXPLAIN select * from TongPerson 

DATA_NODE	SQL
dn3	SELECT * FROM TongPerson LIMIT 100
dn4	SELECT * FROM TongPerson LIMIT 100
dn5	SELECT * FROM TongPerson LIMIT 100