Mycat(Windows环境)(1) 初入门
Mycat 是SQL集群中间件,基于阿里开源的Cobar产品而研发,Cobar的稳定性、可靠性、优秀的架构和性能
1. MyCat准备
- 安装 mysql
安装jdk 1.8
这是 jdk 下载地址.
安装完成,配好环境变量
下载maycat
这是 mycat 下载地址.
mycat 下载目录
2. 数据库准备
create database db1;
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
username varchar(32) NOT NULL default '',
passwork varchar(32) NOT NULL default '',
time DATETIME NOT NULL default '1971-01-01 01:00:00',
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
create database db2;
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
oid varchar(32) NOT NULL default '',
money decimal(19,4) NOT NULL default 0,
state TINYINT NOT NULL default 0,
time DATETIME NOT NULL default '1971-01-01 01:00:00',
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
create database db3;
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
oid varchar(32) NOT NULL default '',
money decimal(19,4) NOT NULL default 0,
state TINYINT NOT NULL default 0,
time DATETIME NOT NULL default '1971-01-01 01:00:00',
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
3. MyCat配置
MyCat配置 官方文档.
mycat 的配置文件在conf下:
- 为实现下图:
配置如下:
- server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<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">123456</property>
<property name="schemas">JGDB</property>
</user>
<user name="user">
<property name="password">123456</property>
<property name="schemas">JGDB</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
- 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="order_rule">
<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>
- schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 设置表的存储方式.schema name="JGDB" 与 server.xml中的 JGDB 设置一致 -->
<schema name="JGDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="users" primaryKey="id" dataNode="db1" />
<table name="orders" primaryKey="id" dataNode="db2,db3" rule="order_rule" />
</schema>
<!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->
<dataNode name="db1" dataHost="host1" database="db1" />
<dataNode name="db2" dataHost="host1" database="db2" />
<dataNode name="db3" dataHost="host1" database="db3" />
<!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->
<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat></heartbeat>
<writeHost host="server1" url="127.0.0.1:3306" user="root" password="xinfu978"/>
</dataHost>
</mycat:schema>
4. 测试
- 使用 navicat连接 mycat
插入users表数据测试
- 往users 插入两个用户
- 查看物理库 db1 的users表是否有数据
测试成功
- 插入order表数据测试(是否id单数在db3,双数在db2)
往orders 插入4条数据
- 查看物理库 db2,db3 的 orders 表是否有对的数据
–db2 数据符合
–db3 数据符合