mycat的入门教程（二）

WindySky 2017-07-21

展开全文

在上篇教程中初步介绍了mycat的使用，接下来搭建一个拆分的策略（取模还是按区间划分等）的入门教程来实现一个分库分表的功能

三张表users和test，三个数据库test，test2，dmeo（三个库在一个数据库实例上）
users表被分割到test,test2中存储。
test在dmeo中存储。

创建三个数据库，并创建对应的表

-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='用户测试表';1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

配置如下所示：
（1）rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www./licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://cat/">
    <tableRule name="rule1">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
    <function name="mod-long" class="cat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>
    </function> 
</mycat:rule>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

(2)schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://cat/">

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> 
        <table name="test" primaryKey="ID" type="global"  dataNode="dn1" />
        <table name="users" primaryKey="ID"  dataNode="dn2,dn3" rule="rule1"/> 
    </schema> 

    <dataNode name="dn1" dataHost="localhost1" database="dmeo" />
    <dataNode name="dn2" dataHost="localhost1" database="test" /> 
    <dataNode name="dn3" dataHost="localhost1" database="test2" />  

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="localhost:3306" user="root" password="root">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="127.0.0.1:3306" user="root" password="root" />
        </writeHost> 
    </dataHost> 
</mycat:schema>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22