mysql，mycat的demo

一直都是使用公司架构师提供的mycat，今天得空自己尝试了以下：

1.首先安装jdk（官网建议1.7及以上版本），mysql（我安装的是5.7.19-0ubuntu0.16.04.1，设置账号密码为root，123456，与mycat保持一致，后面避免修改配置文件），mycat（我安装的是Mycat-server-1.6-RELEASE-20161028204710-linux.tar）；

以上可直接去官网下载；

2.创建三个数据库，暂且名称定为db1,db2,db3，与mycat的配置一致，省去了修改被指文件的工作；

3.启动mysql，启动mycat，分别查看是否启动成功，如果启动mycat时报错，java.net.MalformedURLException，应该是因为主机名没有绑定，可以去配置文件/etc/hosts种在127.0.0.1  后面增加  主机名，在启动mycat应该就不会报错了；

4.执行命令：mysql -uroot -p123456 -h127.0.0.1 -P8066 -DTESTDB,进入mycat命令行界面；

创建Travelrecord表：

create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);

插入数据

mysql> insert into travelrecord(id,user_id,traveldate,fee,days)  values(1,'Victor',20160101,100,10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into travelrecord(id,user_id,traveldate,fee,days)  values(5000001,'Job',20160102,100,10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into travelrecord(id,user_id,traveldate,fee,days)  values(10000001,'Slow',20160103,100,10);
Query OK, 1 row affected (0.00 sec)

 

至于ID为什么取三个值，这个与conf目录下autopartition-long.txt的定义有关，这个文件主要定义auto-sharding-long的规则。

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

下面来看看分片的效果：

mysql> select * from db1.travelrecord;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | Victor  | 2016-01-01 |  100 |   10 |
+----+---------+------------+------+------+
row in set (0.00 sec)

mysql> select * from db2.travelrecord;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 5000001 | Job     | 2016-01-02 |  100 |   10 |
+---------+---------+------------+------+------+
row in set (0.00 sec)

mysql> select * from db3.travelrecord;
+----------+---------+------------+------+------+
| id       | user_id | traveldate | fee  | days |
+----------+---------+------------+------+------+
| 10000001 | Slow    | 2016-01-03 |  100 |   10 |
+----------+---------+------------+------+------+
row in set (0.00 sec)

如果想看MyCAT具体会将数据分配到哪个节点上，可通过路由分析。语法其实蛮简单，就是SQL语句前加上explain语句。

mysql> explain create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                                   |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| dn1       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn2       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn3       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
rows in set (0.00 sec)

mysql>  explain insert into travelrecord(id,user_id,traveldate,fee,days)  values(1,'Victor',20160101,100,10);
+-----------+----------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                          |
+-----------+----------------------------------------------------------------------------------------------+
| dn1       | insert into travelrecord(id,user_id,traveldate,fee,days)  values(1,'Victor',20160101,100,10) |
+-----------+----------------------------------------------------------------------------------------------+
row in set (0.01 sec)

mysql> explain insert into travelrecord(id,user_id,traveldate,fee,days)  values(5000001,'Job',20160102,100,10);
+-----------+-------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                             |
+-----------+-------------------------------------------------------------------------------------------------+
| dn2       | insert into travelrecord(id,user_id,traveldate,fee,days)  values(5000001,'Job',20160102,100,10) |
+-----------+-------------------------------------------------------------------------------------------------+
row in set (0.00 sec)

mysql> explain insert into travelrecord(id,user_id,traveldate,fee,days)  values(10000001,'Slow',20160103,100,10);
+-----------+---------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                               |
+-----------+---------------------------------------------------------------------------------------------------+
| dn3       | insert into travelrecord(id,user_id,traveldate,fee,days)  values(10000001,'Slow',20160103,100,10) |
+-----------+---------------------------------------------------------------------------------------------------+
row in set (0.00 sec)

 

关于配置文件，conf目录下主要以下三个需要熟悉。

server.xml是Mycat服务器参数调整和用户授权的配置文件

schema.xml是逻辑库定义和表以及分片定义的配置文件

rule.xml是分片规则的配置文件

 

以上为我搭建demo，更多的使用限制和经验需要大家自己慢慢积累，我在这里就不赘述了