mycat异机多库分表

来源：互联网发布：nba总决赛数据2016 编辑：程序博客网时间：2024/06/05 13:25

再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

阅读全文

0 0