mycat分库分表（超详细）

引言

本文将从零开始进行分库分表，包括环境的安装；

正文

1，安装JDK，我的是1.8版本
2，安装MYSQL（必须5.5以上），我的是5.5版本
3，下载mycat

准备工作

创建三个数据库与三张表，用于测试

create database db01 ;
use db01;
 CREATE TABLE users ( 
    id INT NOT NULL AUTO_INCREMENT, 
    name varchar(50) NOT NULL default '', 
    indate DATETIME NOT NULL default '0000-00-00 00:00:00', 
    PRIMARY KEY (id) 
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8; 

create database db02;
use db02;
 CREATE TABLE item ( 
    id INT NOT NULL AUTO_INCREMENT, 
    value INT NOT NULL default 0, 
    indate DATETIME NOT NULL default '0000-00-00 00:00:00', 
    PRIMARY KEY (id) 
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
create database db03;
use db03;
CREATE TABLE item ( 
    id INT NOT NULL AUTO_INCREMENT, 
    value INT NOT NULL default 0, 
    indate DATETIME NOT NULL default '0000-00-00 00:00:00', 
    PRIMARY KEY (id) 
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

配置文件

建议备份后，我的配置全部复制过去，等熟练了自己再设置

schema.xml

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

 <!-- 设置表的存储方式.schema name="TESTDB" 与 server.xml中的 TESTDB 设置一致  --> 
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> 
        <table name="users" primaryKey="id"  dataNode="node_db01" /> 
        <table name="item" primaryKey="id" dataNode="node_db02,node_db03" rule="role1" /> 
 
    </schema> 
 
    <!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost --> 
    <dataNode name="node_db01" dataHost="dataHost01" database="db01" /> 
    <dataNode name="node_db02" dataHost="dataHost01" database="db02" /> 
    <dataNode name="node_db03" dataHost="dataHost01" database="db03" /> 
 
    <!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 --> 
    <dataHost name="dataHost01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> 
            <heartbeat>select user()</heartbeat> 
            <writeHost host="server1" url="127.0.0.1:3306" user="root" password="root"/> 
    </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

server.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.apache.org/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:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system> 
            <!--  
                <property name="processors">32</property> 
                <property name="processorExecutor">32</property>  
                <property name="bindIp">0.0.0.0</property>  
                <property name="frontWriteQueueSize">4096</property> 
                <property name="idleTimeout">300000</property> 
                <property name="mutiNodePatchSize">100</property> 
            --> 
                <property name="defaultSqlParser">druidparser</property> 
                <property name="mutiNodeLimitType">1</property> 
                <property name="serverPort">8066</property> 
                <property name="managerPort">9066</property>  
        </system> 
        <!-- 任意设置登陆 mycat 的用户名,密码,数据库  --> 
        <user name="test"> 
                <property name="password">test</property> 
                <property name="schemas">TESTDB</property> 
        </user> 
 
        <user name="user"> 
                <property name="password">user</property> 
                <property name="schemas">TESTDB</property> 
                <property name="readOnly">true</property> 
        </user> 
        <!--  
        <quarantine>  
           <whitehost> 
              <host host="127.0.0.1" user="mycat"/> 
              <host host="127.0.0.2" user="mycat"/> 
           </whitehost> 
       <blacklist check="false"></blacklist> 
        </quarantine> 
        --> 

</mycat:server>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

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.apache.org/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://io.mycat/">
	<tableRule name="role1">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
 
    <function name="mod-long" class="io.mycat.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
24

注意

这个是在bin目录下的startup_nowrap.bat（没有报内存错误可不修改），
改动的是**-Xms512M -Xmx512M**（原本是-Xms1G -Xmx2G）

REM check JAVA_HOME & java
set "JAVA_CMD=%JAVA_HOME%/bin/java"
if "%JAVA_HOME%" == "" goto noJavaHome
if exist "%JAVA_HOME%\bin\java.exe" goto mainEntry
:noJavaHome
echo ---------------------------------------------------
echo WARN: JAVA_HOME environment variable is not set. 
echo ---------------------------------------------------
set "JAVA_CMD=java"
:mainEntry
REM set HOME_DIR
set "CURR_DIR=%cd%"
cd ..
set "MYCAT_HOME=%cd%"
cd %CURR_DIR%
"%JAVA_CMD%" -server -Xms512M -Xmx512M -XX:MaxPermSize=64M  -XX:+AggressiveOpts -XX:MaxDirectMemorySize=1G -DMYCAT_HOME=%MYCAT_HOME% -cp "..\conf;..\lib\*" io.mycat.MycatStartup
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

测试

如果配置没问题就到测试阶段了

第一步

使用命令行，mycat的bin目录下启动startup_nowrap.bat

成功后可以在log文件下查看日志

第二步

找到mysql的bin目录，在命令行输入：mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB

第三步

依次执行SQL

insert into users(name,indate) values('kk',now());
insert into users(name,indate) values('ss',now());
insert into item(id,value,indate) values(1,100,now());
insert into item(id,value,indate) values(2,100,now());
最终看到：

第四步

查看数据库
db01中的users表

db02中的item表

db03中的item表

显示，插入的users表中的数据全部在db01中，而item表中的数据通过Id取模后均匀的分布在db02和db03中。这样就根据实际的路由策略进行了分表。