mycat异机多库分表

最新推荐文章于 2025-10-13 20:48:20 发布

原创最新推荐文章于 2025-10-13 20:48:20 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mycat

mycat 专栏收录该内容

5 篇文章

订阅专栏

再147，148，149三台主机上分别见数据库duokufenbiao,同事在这个数据库下建表YiPerson

CREATE DATABASE /*!32312 IF NOT EXISTS*/`duokufenbiao` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `duokufenbiao`;

/*Table structure for table `YiPerson` */

DROP TABLE IF EXISTS `YiPerson`;

CREATE TABLE `YiPerson` (
  `Id` INT(11) NOT NULL,
  `Name` VARCHAR(50) DEFAULT NULL,
  `UserId` INT(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

配置server.xml

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

</user>

配置schema.xml

<schema name="yijifenbiao" checkSQLschema="false" sqlMaxLimit="100">
        <table name="YiPerson"  dataNode="dn6,dn7,dn8" rule="tongjifenbiao-mod-long" />
</schema>
<dataNode name="dn6" dataHost="localhost2" database="duokufenbiao" />
<dataNode name="dn7" dataHost="localhost3" database="duokufenbiao" />
<dataNode name="dn8" dataHost="localhost4" database="duokufenbiao" />
<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>
<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>
<dataHost name="localhost4" 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.148:3306" user="root" password="" />
</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 YiPerson (`Id`,`Name`,`UserId` ) VALUES(11,'2345623',12);

DATA_NODE	SQL
dn6	INSERT INTO YiPerson (`Id`,`Name`,`UserId` ) VALUES(11,’2345623’,12)

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

DATA_NODE	SQL
dn7	INSERT INTO YiPerson (`Id`,`Name`,`UserId` ) VALUES(11,’2345623’,13)

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

DATA_NODE	SQL
dn8	INSERT INTO YiPerson (`Id`,`Name`,`UserId` ) VALUES(11,’2345623’,14)

EXPLAIN select * from YiPerson

DATA_NODE	SQL
dn6	SELECT * FROM YiPerson LIMIT 100
dn7	SELECT * FROM YiPerson LIMIT 100
dn8	SELECT * FROM YiPerson LIMIT 100