一:jdk安装
mycat是用Java开发,需要有JAVA运行环境,mycat依赖jdk1.7的环境,若本机没有,则需要先下载安装。
安装jdk,请参考:http://blog.csdn.net/yabingshi_tech/article/details/51498017
二:mycat安装
点击这里下载
tar xvf Mycat-server-1.4-release-20151019230038-linux.tar.gz
mv mycat/ /usr/local/
cd /usr/local/mycat/conf
vim wrapper.conf
修改java所在路径:
# Java Application
wrapper.java.command=/usr/local/jdk/bin/java
cd /usr/local/mycat/bin
[root@PC bin]# ./mycat start
Starting Mycat-server...
mycat 就已经启动了 端口8066。
[root@PC ~]# netstat -nltp | grep 8066
tcp 0 0 :::8066 :::* LISTEN 12742/java
可以通过mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB 访问mycat。
三:配置mysql
注意:若是LINUX版本的MYSQL,则需要设置为Mysql大小写不敏感,否则可能会发生表找不到的问题。
在MySQL的配置文件中my.cnf [mysqld] 中增加一行
lower_case_table_names = 1
然后重启mysql生效。
四:配置mycat连接mysql
修改MYCAT_HOME/conf/schema.xml文件,修改dataHost对应的连接信息(这里的用户名密码是你要访问的mysql的用户名密码):
修改前:
修改后如:
五:开放防火墙端口
如果启动了防火墙的话,需要开放该端口:
vi /etc/sysconfig/iptables
添加:
-A INPUT -m state --state NEW -m tcp -p tcp--dport 8066 -j ACCEPT
#重启防火墙
service iptables restart
六:分片实验
实验场景:
有个employee(id int,name varchar(40),deptno int)表。想根据其部门号将其分成几个子表。
6.1:创建分片规则
MYCAT常用的分片规则如下,另外还有一些其他分片方式这里不全部列举:
(1)分片枚举:sharding-by-intfile
(2)主键范围:auto-sharding-long
(3)一致性hash:sharding-by-murmur
(4)字符串hash解析:sharding-by-stringhash
(5)按日期(天)分片:sharding-by-date
(6)按单月小时拆分:sharding-by-hour
(6)自然月分片:sharding-by-month
这里使用分片枚举方式。
在这里,需定义三个值,规则均是在rule.xml中定义。
1. tableRule
2. function
3. mapFile
首先,定义tableRule
<tableRule name="sharding-by-intfile-USERDB-employee">
<rule>
<columns>deptno</columns>
<algorithm>hash-int-USERDB-employee</algorithm>
</rule>
</tableRule>
其次,定义function
<property name="mapFile">partition-hash-int-USERDB-employee.txt</property>
<property name="type">0</property>
<property name="defaultNode">0</property>
</function>
type默认值为0,0表示Integer,非零表示String。
defaultNode 默认节点:小于0表示不设置默认节点,大于等于0表示设置默认节点默认节点的作用:枚举分片时,如果碰到不识别的枚举值,就让它路由到默认节点。如果不配置默认节点(defaultNode值小于0表示不配置默认节点),碰到不识别的枚举值就会报错,like this:can’t find datanode for sharding column:column_name val:ffffffff
第三 创建partition-hash-int-USERDB-employee.txt文件
在conf路径下创建该文件,定义枚举的规则
vi partition-hash-int-USERDB-employee.txt
添加:
10=0
20=1
30=2
其中,部门号为10的数据会被分发到第一个节点中,部门号为20的数据分发被第二个节点中,部门号为30的数据第三个节点中。
6.2:新建逻辑库,逻辑表
schema 标签用于定义MyCat实例中的逻辑库,可以使用 schema 标签来划分这些不同的逻辑库。
schema里面可以指定逻辑表,dataNode,rule。
Mycat默认配置了逻辑库TESTDB。
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" ></table>
</schema>
这里配置一个逻辑库USERDB:
修改server.xml
添加:
<schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="employee" dataNode="dn10,dn11,dn12" rule="sharding-by-intfile-USERDB-employee"></table>
</schema>
6.3:配置逻辑库和物理库对应关系
由于配置文件里默认只配置了dn1,dn2,dn3这几个dataNode,因此需要额外配置下dn10,dn11,dn12对应的物理库:
添加:
<dataNode name="dn10" dataHost="localhost1" database="db10"/>
<dataNode name="dn11" dataHost="localhost1" database="db11"/>
<dataNode name="dn12" dataHost="localhost1" database="db12"/>
否则重启mycat后,日志里会报错
INFO | jvm 1 | 2016/08/18 17:14:02 | log4j:ERROR 2016-08-18 17:14:02 startup error
INFO | jvm 1 | 2016/08/18 17:14:02 | Caused by:org.opencloudb.config.util.ConfigException: dataNode 'dn10' is not found!
6.4:创建物理库
在mysql里创建dn10,dn11,dn12对应的数据库:
mysql> create database db10;
Query OK, 1 rowaffected (0.00 sec)
mysql> create database db11;
Query OK, 1 rowaffected (0.01 sec)
mysql> create database db12;
Query OK, 1 rowaffected (0.00 sec)
6.5:设置用户对应的逻辑库
修改server.xml,添加test用户对应的逻辑库:
如果不添加的话,用该用户访问mycat的该逻辑库时会报错:
mysql> use USERDB;
ERROR 1044 (HY000): Access denied for user 'test'to database 'USERDB'
修改完配置文件,重启mycat
[root@PC conf]# ../bin/mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
[root@PC conf]# ../bin/mycat start
Starting Mycat-server...
6.6:分片测试
连接mycat
mysql -utest -ptest -h127.0.0.1 -P8066 -DUSERDB
mysql> show tables;
+------------------+
| Tables in USERDB |
+------------------+
| employee |
+------------------+
1 row in set (0.00 sec)
mysql> select * from employee;
ERROR 1105 (HY000): Table 'db12. employee'doesn't exist
报错是因为我们还没有创建该表。
mysql> explain create table employee(id int,name varchar(40),deptno int);
+-----------+-----------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------+
| dn10 | create table employee(id int,name varchar(40),deptno int) |
| dn11 | create table employee(id int,name varchar(40),deptno int) |
| dn12 | create table employee(id int,name varchar(40),deptno int) |
+-----------+-----------------------------------------------------------++-----------+-----------------------------------------------+
3 rows in set (0.05 sec)
看到会分配到dn10,dn11,dn12里。
mysql> create table employee(id int,name varchar(40),deptno int);
Query OK, 0 rows affected (0.16 sec)
进mysql里可以看到新建的表了。如:
mysql> use db10;
Reading table information for completion oftable and column names
You can turn off this feature to get aquicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_db10 |
+----------------+
| employee |
+----------------+
1 row in set (0.00 sec)
#准备测试数据
连接mycat,插入
先查看下会插入到哪里:
mysql> explain insert into employee(id,name,deptno)values(1,'dan',10),(2,'jiao',20),(3,'song',30),(4,'yang',20),(5,'bm',10));
+-----------+----------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------------------+
| dn10 | INSERT INTO employee (id, name, deptno)
VALUES (1, 'dan', 10),
(5,'bm', 10) |
| dn11 | INSERT INTO employee (id, name, deptno)
VALUES (2, 'jiao', 20),
(4,'yang', 20) |
| dn12 | INSERT INTO employee (id, name, deptno)
VALUES (3, 'song', 30) |
+-----------+----------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
mysql> insert into employee(id,name,deptno)values(1,'dan',10),(2,'jiao',20),(3,'song',30),(4,'yang',20),(5,'bm',10));
Query OK, 5 rows affected (0.11 sec)
进mysql验证:
mysql> select * from db10.employee;
+------+------+--------+
| id | name | deptno |
+------+------+--------+
| 1 | dan | 10 |
| 5 | bm | 10 |
+------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from db11.employee;
+------+------+--------+
| id | name | deptno |
+------+------+--------+
| 2 | jiao | 20 |
| 4 | yang | 20 |
+------+------+--------+
2 rows in set (0.01 sec)
mysql> select * from db12.employee;
+------+------+--------+
| id | name | deptno |
+------+------+--------+
| 3 | song | 30 |
+------+------+--------+
1 row in set (0.00 sec)
七:在mycat里创建用户
修改MYCAT_HOME/conf/server.xml文件
仿照test用户创建一个新用户mycat:
修改后如下:
登录9066端口,使得配置生效:
reload @@config_all;
假如报错:
mysql> reload @@config_all;
ERROR 1003 (HY000): Reload config failure
说明配置文件配置有误。
八:mycat的分片join
Mycat目前版本支持跨分片的join,主要实现方式有四种。 全局表,ER分片,catletT(人工智能)和ShareJoin,ShareJoin在开发版中支持,前面三种斱方式1.3.0.1支持。
8.1 全局表
一个真实的业务系统中,往往存在多量的类似字典表的表格,这些表基本上很少变动,如图:
配置:
全局表配置比较简单,不用写Rule规则,如下配置即可:
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
在USERDB下配置一个全局表province:
vi schema.xml
添加:
<table name="province" dataNode="dn10,dn11,dn12" type="global"></table>
登陆9066端口,执行命令:reload @@config_all;
建表:
mysql -utest -ptest -h127.0.0.1 -P8066 -DUSERDB
mysql> create table province(id int,name varchar(30));
Query OK, 0 rows affected (0.19 sec)
mysql> insert into province(id,name)values(1,'beijing');
Query OK, 3 rows affected (0.11 sec)
mysql> select * from province limit 1;
+------+---------+
| id | name |
+------+---------+
| 1 | beijing |
+------+---------+
1 row in set (3.56 sec)
在mysql里的db10,db11,db12都能看到插入的数据:
mysql> select * from db12.province;
+------+---------+
| id | name |
+------+---------+
| 1 | beijing |
+------+---------+
1 row in set (0.00 sec)
mysql> select * from db11.province;
+------+---------+
| id | name |
+------+---------+
| 1 | beijing |
+------+---------+
1 row in set (0.01 sec)
mysql> select * from db10.province;
+------+---------+
| id | name |
+------+---------+
| 1 | beijing |
+------+---------+
1 row in set (0.00 sec)
8.2 ER join
基于E-R关系的数据分片策略,子表的记录与所关联的父表记录存放在同一个数据分片上。
分片在dn1,dn2上,orders依赖父表进行分片,两个表的关联关系为orders.customer_id=customer.id。示意图如下:
配置:
<tablen ame="customer" dataNode="dn1,dn2" rule="sharding-by-intfile">
<childTable name="orders" joinKey="customer_id" parentKey="id"/>
</table>
示例:
我先创建了一个分片规则:auto-sharding-long-custom
id属于0-1000范围内的在分区1里,1000-2000的在分区2里,2000-3000的在分片3里。
vi schema.xml
在USERDB处配置:
<table name="customer" dataNode="dn10,dn11,dn12" rule="auto-sharding-long-custom">
<childTable name="orders" joinKey="customer_id" parentKey="id"/>
</table>
登录9066端口,使得配置生效:
reload @@config_all;
[root@PC conf]# mysql -u test -ptest -P8066 -h 127.0.0.1 TESTDB
Reading table information for completion oftable and column names
You can turn off this feature to get aquicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.3 MyCatServer (OpenCloundDB)
Copyright (c) 2000, 2011, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql> use USERDB;
Reading table information for completion oftable and column names
You can turn off this feature to get aquicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables in USERDB |
+------------------+
| company |
| customer |
| employee |
| orders |
| province |
+------------------+
5 rows in set (0.01 sec)
建表:
mysql> create table customer(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.13 sec)
mysql> create table orders(id int,name varchar(30),customer_id int,constraint fk_companyid foreign key(customer_id)references customer(id));
Query OK, 0 rows affected (0.25 sec)
插入几条数据:
mysql>insert into customer(id,name) values(999,'dan'),(1000,'jiao'),(1003,'song'),(2002,'yang');
mysql> insert into orders(id,name,customer_id) values(1,'mirror',999),(2,'banana',2002),(3,'apple',1003),(4,'pear',2002);
ERROR 1064 (HY000): ChildTable multi insertnot provided
看来mycat子表不支持一次插入多条记录。
mysql> insert into orders(id,name,customer_id) values(1,'mirror',999);
Query OK, 1 row affected (0.07 sec)
mysql> insert into orders(id,name,customer_id) values(2,'banana',2002)
Query OK, 1 row affected (0.02 sec)
mysql> insert into orders(id,name,customer_id) values(3,'apple',1003);
Query OK, 1 row affected (0.02 sec)
mysql> insert into orders(id,name,customer_id)values(4,'pear',2002);
Query OK, 1 row affected (0.02 sec)
进入mysql验证:
mysql> select * from db10.customer;
+------+------+
| id | name |
+------+------+
| 999 | dan |
| 1000 | jiao |
+------+------+
2 rows in set (0.01 sec)
mysql> select * from db11.customer;
+------+------+
| id | name |
+------+------+
| 1003 | song |
+------+------+
1 row in set (0.00 sec)
mysql> select * from db12.customer;
+------+------+
| id | name |
+------+------+
| 2002 | yang |
+------+------+
1 row in set (0.00 sec)
mysql> select * from db12.orders;
+------+--------+-------------+
| id | name | customer_id |
+------+--------+-------------+
| 2 | banana | 2002 |
| 4 | pear | 2002 |
+------+--------+-------------+
2 rows in set (0.00 sec)
mysql> select * from db11.orders;
+------+-------+-------------+
| id | name | customer_id |
+------+-------+-------------+
| 3 | apple | 1003 |
+------+-------+-------------+
1 row in set (0.00 sec)
mysql> select * from db10.orders;
+------+--------+-------------+
| id | name | customer_id |
+------+--------+-------------+
| 1 | mirror | 999 |
+------+--------+-------------+
1 row in set (0.00 sec)
我们看到orders列customer_id对应的customer的id属于哪个分片,orders的那条记录就在哪个分片内。
可以在mycat上正常地联合查询:
mysql> select b.*,a.name as custome_name from customer a inner join orders b on a.id=b.customer_id;
+------+--------+-------------+--------------+
| id | name | customer_id |custome_name |
+------+--------+-------------+--------------+
| 2 | banana | 2002 |yang |
| 4 | pear | 2002 | yang |
| 1 | mirror | 999 |dan |
| 3 | apple | 1003 | song |
+------+--------+-------------+--------------+
4 rows in set (0.02 sec)
8.3 share join
ShareJoin是一个简单的跨分片Join,基于HBT的方式实现。 目前支持2个表的join,原理就是解析SQL语句,拆分成单表的SQL语句执行,然后把各个节点的数据汇集。配置支持任意配置的A,B表如:
A,B的dataNode相同
A,B的dataNode不同
示例:
我先创建了一个分片规则:auto-sharding-long-custom
id属于0-1000范围内的在分区1里,1000-2000的在分区2里。
新定义两个处于不同分片中的两个表
vi schema.xml
在USERDB处添加:
<table name="student" primaryKey="ID" dataNode="dn10,dn11" rule="auto-sharding-long-custom"></table>
<table name="score" primaryKey="ID" dataNode="dn11,dn12" rule="auto-sharding-long-custom"></table>
登录9066端口,使得配置生效:
reload @@config_all;
建表:
mysql> create table student(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.19 sec)
mysql> create table score(id int,studentid int,score int,constraint fk_studentid foreign key(studentid)references student(id));
Query OK, 0 rows affected (0.13 sec)
插入数据:
mysql> insert into student(id,name)values(1,'dan'),(1002,'jiao'),(88,'song');
Query OK, 3 rows affected (0.33 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into score(id,studentid,score) values(1,1,100);
Query OK, 1 row affected (0.07 sec)
mysql> insert into score(id,studentid,score) values(1008,88,90);
Query OK, 1 row affected (0.03 sec)
mysql> insert into score(id,studentid,score) values(8,1002,99);
Query OK, 1 row affected (0.01 sec)
进mysql查询:
mysql> select * from db10.student;
+----+------+
| id | name |
+----+------+
| 1 | dan |
| 88 | song |
+----+------+
2 rows in set (0.01 sec)
mysql> select * from db11.student;
+------+------+
| id | name |
+------+------+
| 1002 | jiao |
+------+------+
1 row in set (0.00 sec)
mysql> select * from db12.student;
ERROR 1146 (42S02): Table 'db12.student'doesn't exist
mysql> select * from db10.score;
ERROR 1146 (42S02): Table 'db10.score'doesn't exist
mysql> select * from db12.score;
+------+-----------+-------+
| id | studentid | score |
+------+-----------+-------+
| 1008 | 88 | 90 |
+------+-----------+-------+
1 row in set (0.00 sec)
mysql> select * from db11.score;
+------+-----------+-------+
| id | studentid | score |
+------+-----------+-------+
| 1 | 1 | 100 |
| 8 | 1002 | 99 |
+------+-----------+-------+
2 rows in set (0.00 sec)
不使用share join在mycat查询:
mysql> select * from student a inner join score b on a.id=b.studentid;
+------+------+------+-----------+-------+
| id | name | id | studentid | score|
+------+------+------+-----------+-------+
| 1002 | jiao | 8 | 1002 | 99 |
+------+------+------+-----------+-------+
1 row in set (0.02 sec)
只查到了db11分区里面的数据。
使用share join:
mysql>/*!mycat:catlet=demo.catlets.ShareJoin */ select * from student a inner join score b on a.id=b.studentid;
ERROR 1064 (HY000):java.lang.ClassNotFoundException: demo.catlets.ShareJoin
Share join只在开发版本中支持,我的是mycat 1.3,所以不支持。假如支持的话,可以查出匹配的三条记录,而不是上面的一条。
--本篇文章参考官方手册,
http://www.tuicool.com/articles/aUZVF3Q,
http://blog.sina.com.cn/s/blog_638b7ebb0102vv80.html,
http://www.cnblogs.com/lixiuran/p/4842888.html,