mycat分库分表_mycat 分库分表-CSDN博客

本文链接：https://blog.csdn.net/liuxiansheng1228/article/details/79966981

mycat分库分表

一、环境

192.168.24.128 mycat

192.168.24.129 master

关闭防火墙、配置hosts文件

二、主数据库配置（24.129）

1. 在主数据库创建库表

mysql> create database db01;
mysql> create database db02;
mysql> create database db03;

CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT,  name varchar(50) NOT NULL default '',PRIMARY KEY (id))AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8; 

CREATE TABLE item (id INT NOT NULL AUTO_INCREMENT,value INT NOT NULL default 0,PRIMARY KEY (id))AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;  

CREATE TABLE item_detail (id INT NOT NULL AUTO_INCREMENT,value INT NOT NULL default 0,name varchar(50) NOT NULL default '',item_id INT NOT NULL,PRIMARY KEY (id),key (item_id))AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

这三个表每个库都创建一个

2. 分配root网段

grant all on *.* to root@'%' identified by '123.com';

3. 关闭防火墙或开启端口

systemctl stop firewalld.service

三、 mycat中间件配置(24.128)

1. 安装jdk

选择与操作系统位数匹配的版本

[root@mycat src]# tar zxf jdk-7u65-linux-x64.gz
[root@mycat src]# mv jdk1.7.0_65/ /usr/local/java

2. 安装mycat

[root@mycat src]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat src]# tar zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

3. 加载环境变量

[root@mycat src]# vim /etc/profile
export PATH=$PATH:/usr/local/java/bin
export JAVA_HOME=/usr/local/java
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:/usr/local/mycat/bin
[root@mycat src]# source /etc/profile

4. 配置server.xml

[root@mycat src]# vim /usr/local/mycat/conf/server.xml

5. 配置schema.xml

[root@mycat src]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <table name="users" primaryKey="id" type="global" dataNode="node_db01" />

                <table name="item" primaryKey="id" dataNode="node_db02,node_db03" rule="mod-long">
                        <childTable name="item_detail" primaryKey="id" joinKey="item_id" parentKey="id" />
                </table>
        </schema>

        <dataNode name="node_db01" dataHost="dataHost01" database="db01" />
        <dataNode name="node_db02" dataHost="dataHost01" database="db02" />
        <dataNode name="node_db03" dataHost="dataHost01" database="db03" />

        <dataHost name="dataHost01" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="server1" url="192.168.24.129:3306" user="root"
                                   password="123.com"/>

        </dataHost>
</mycat:schema>

6. 配置rule.xml，默认为分三片，需要修改

[root@mycat src]# vim /usr/local/mycat/conf/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="mod-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
        <function name="mod-long"
                class="io.mycat.route.function.PartitionByMod">
                <property name="count">2</property>
        </function>
</mycat:rule>

7. 配置wrapper.xml

8. 启动mycat

[root@mycat logs]# mycat start
Starting Mycat-server...
[root@mycat logs]# ss -anpt | grep java
LISTEN     0      1      127.0.0.1:32000                    *:*                   users:(("java",pid=42522,fd=4))
LISTEN     0      100         :::9066                    :::*                   users:(("java",pid=42522,fd=72))
LISTEN     0      50          :::54392                   :::*                   users:(("java",pid=42522,fd=53))
LISTEN     0      50          :::60956                   :::*                   users:(("java",pid=42522,fd=51))
LISTEN     0      50          :::1984                    :::*                   users:(("java",pid=42522,fd=52))
LISTEN     0      100         :::8066                    :::*                   users:(("java",pid=42522,fd=76))

四、验证

1. 从客户端登入并且插入数据

[root@localhost ~]# mysql -h 192.168.24.128 -P8066 -DTESTDB -uroot -p123.com
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> use TESTDB;
Database changed
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| item             |
| item_detail      |
| users            |
+------------------+
3 rows in set (0.00 sec)
mysql> insert into users(name) values('haha');
mysql> insert into item(id,value) values (1,10);
mysql> insert into item(id,value) values (2,20);
mysql> insert into item_detail(id,value,name,item_id) values(1,10,'wu',1); 
mysql> insert into item_detail(id,value,name,item_id) values(2,20,'kk',2); 
mysql> insert into item_detail(id,value,name,item_id) values(3,30,'kk',55); 
mysql> insert into item_detail(id,value,name,item_id) values(4,40,'kk',66);
mysql>  select * from users;
+----+------+
| id | name |
+----+------+
|  1 | haha |
+----+------+
1 row in set (0.07 sec)
mysql> select * from item;
+----+-------+
| id | value |
+----+-------+
|  2 |    20 |
|  1 |    10 |
+----+-------+
2 rows in set (0.09 sec)

mysql> select * from item_detail;
+----+-------+------+---------+
| id | value | name | item_id |
+----+-------+------+---------+
|  2 |    20 | kk   |       2 |
|  4 |    40 | kk   |      66 |
|  1 |    10 | wu   |       1 |
|  3 |    30 | kk   |      55 |
+----+-------+------+---------+
4 rows in set (0.00 sec)

2、登录主数据库查看数据表存储位置

mysql> select * from db01.users;
+----+------+
| id | name |
+----+------+
|  1 | haha |
+----+------+
1 row in set (0.00 sec)

mysql>  select * from db02.users;
Empty set (0.01 sec)

mysql> select * from db03.users;
Empty set (0.01 sec)

mysql> select * from db01.item_detail;
Empty set (0.01 sec)

mysql> select * from db02.item ;
+----+-------+
| id | value |
+----+-------+
|  2 |    20 |
+----+-------+
1 row in set (0.00 sec)
mysql> select * from db03.item;
+----+-------+
| id | value |
+----+-------+
|  1 |    10 |
+----+-------+
1 row in set (0.00 sec)

mysql> select * from db01.item_detail;
Empty set (0.00 sec)

mysql>  select * from db02.item_detail;
+----+-------+------+---------+
| id | value | name | item_id |
+----+-------+------+---------+
|  2 |    20 | kk   |       2 |
|  4 |    40 | kk   |      66 |
+----+-------+------+---------+
2 rows in set (0.00 sec)

mysql>  select * from db03.item_detail;
+----+-------+------+---------+
| id | value | name | item_id |
+----+-------+------+---------+
|  1 |    10 | wu   |       1 |
|  3 |    30 | kk   |      55 |
+----+-------+------+---------+
2 rows in set (0.00 sec)

通过使用mycat的hash分片规则，在主数据数据均衡存储，users定义在db01库，item与item_detail分布在db02，db03库，验证成功