mycat单库分表

来源：互联网发布：淘宝有多少假货编辑：程序博客网时间：2024/06/14 16:45

物理库建表

在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

阅读全文

0 0