mycat分库分表

原创 2018年04月16日 21:40:24

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库,验证成功

mycat 分库分表

  • 2017年03月27日 18:42
  • 4.29MB
  • 下载

Mycat分库分表

mycat分片规则 规则种类,在rule.xml文件中sharding-by-intfile 分片枚举 rule1 固定分片hash算法 auto-sharding-long 范围约定 mod-...
  • jaysonhu
  • jaysonhu
  • 2016-10-21 17:12:54
  • 1429

学习mycat实现分库分表

近期学习一下mycat,简单说一些mycat,mycat是一个实现分库分表的中间件,它对外就是一个虚拟的数据库节点,但是它后面连着很多节点,其实是他自己实现了分库和分表,把分库和分表的复杂的操作没有交...
  • lilovfly
  • lilovfly
  • 2016-11-20 14:27:20
  • 693

Mycat 入门 分库分表

对mycat 几天的学习和了解,基本有了大致的方向,今天就把学习到的分享下: 主要的配置文件:server.xml 、 schema.xml、rule.xml 环境搭建 JDK 部署(因为myc...
  • junxiang_0612
  • junxiang_0612
  • 2017-02-16 11:29:24
  • 662

MyCAT分库分表——概述和基本使用教程

转自:http://www.blogjava.net/amigoxie/archive/2014/12/24/421788.html 1、   MyCAT概述 1.1 背景 随着传统的数据库技术...
  • mengtianbao
  • mengtianbao
  • 2016-10-19 22:15:24
  • 5442

Mycat小试牛刀之读写分离、分库分表实践篇

  • 2018年03月03日 02:28
  • 1.41MB
  • 下载

Mycat 数据库分库分表中间件.pdf

  • 2018年02月08日 14:13
  • 9.28MB
  • 下载

MySQL 高可用:mysql+mycat实现数据库分片(分库分表)

什么是MYCAT: 一个彻底开源的,面向企业应用开发的大数据库集群 支持事务、ACID、可以替代MySQL的加强版数据库 一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群 一个...
  • kk185800961
  • kk185800961
  • 2016-04-13 22:18:51
  • 32912

Mycat 读写分离+分库分表

上次进过GTID复制的学习记录,已经搭建好了主从复制的服务器,现在利用现有的主从复制环境,加上正在研究的Mycat,实现了主流分布式数据库的测试 Mycat就不用多介绍了,可以实现很...
  • benluobobo
  • benluobobo
  • 2016-04-08 21:06:29
  • 23860
收藏助手
不良信息举报
您举报文章:mycat分库分表
举报原因:
原因补充:

(最多只允许输入30个字)