server.xml
是MyCAT对外的“虚拟数据库”配置文件。所谓的“虚拟数据库”是说,MyCAT将多个Mysql集群整合起来对外提供服务,提供服务的接口仍然采用Mysql的形式,因此,通过仿造Mysql接口,让调用程序以为自己是在访问Mysql数据库,就是所谓的“虚拟数据库”。server.xml
的主要内容如下(已删除原有的注释),
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser </property>
</system>
<user name="test">
<property name="password">test </property>
<property name="schemas">TESTDB </property>
</user>
<user name="user">
<property name="password">user </property>
<property name="schemas">TESTDB </property>
<property name="readOnly">true </property>
</user>
</mycat:server>
该配置文件很容易读懂,表明该虚拟数据库有一个schema,TESTDB
;有2个用户test
和user
,密码分别是test
和user
,user
用户是只读的,test
用户未设置只读;默认的SQL解析器是druidparser
。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="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
<!-- random sharding using mod sharind rule -->
<table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="mod-long" />
<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
rule="mod-long" /> -->
<table name="employee" primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile">
<childTable name="orders" primaryKey="ID" joinKey="customer_id"
parentKey="id">
<childTable name="order_items" joinKey="order_id"
parentKey="id" />
</childTable>
<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
parentKey="id" />
</table>
<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="master" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="master" database="db1" />
<dataNode name="dn2" dataHost="master" database="db2" />
<dataNode name="dn3" dataHost="master" database="db3" />
<dataHost name="master" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="173.16.80.70:3307" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
<writeHost host="hostS1" url="173.16.80.70:3305" user="root"
password="root"/>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
</mycat:schema>
version= "1.0"?>
"schema.dtd">
mycat= "http://org.opencloudb/">
name= "TESTDB" checkSQLschema= "false" sqlMaxLimit= "100">
name= "travelrecord" dataNode= "dn1,dn2,dn3" rule= "auto-sharding-long"
name= "company" primaryKey= "ID" type= "global" dataNode= "dn1,dn2,dn3"
name= "goods" primaryKey= "ID" type= "global" dataNode= "dn1,dn2"
<!-- random sharding using mod sharind rule -->
name= "hotnews" primaryKey= "ID" dataNode= "dn1,dn2,dn3"
rule= "mod-long"
name= "dual" primaryKey= "ID" dataNode= "dnx,dnoracle2" type= "global"
needAddLimit= "false"/> <table name= "worker" primaryKey= "ID" dataNode= "jdbc_dn1,jdbc_dn2,jdbc_dn3"
rule= "mod-long"
name= "employee" primaryKey= "ID" dataNode= "dn1,dn2"
rule= "sharding-by-intfile"
name= "customer" primaryKey= "ID" dataNode= "dn1,dn2"
rule= "sharding-by-intfile">
name= "orders" primaryKey= "ID" joinKey= "customer_id"
parentKey= "id">
name= "order_items" joinKey= "order_id"
parentKey= "id"
</childTable>
name= "customer_addr" primaryKey= "ID" joinKey= "customer_id"
parentKey= "id"
</table>
name= "oc_call" primaryKey= "ID" dataNode= "dn1$0-743" rule= "latest-month-calldate"
/> -->
</schema>
name= "dn1$0-743" dataHost= "localhost1" database= "db$0-743"
/> -->
name= "dn1" dataHost= "localhost1" database= "db1"
name= "dn2" dataHost= "localhost1" database= "db2"
name= "dn3" dataHost= "localhost1" database= "db3"
name= "dn4" dataHost= "sequoiadb1" database= "SAMPLE"
name= "jdbc_dn1" dataHost= "jdbchost" database= "db1"
name= "jdbc_dn2" dataHost= "jdbchost" database= "db2"
name= "jdbc_dn3" dataHost= "jdbchost" database= "db3"
name= "localhost1" maxCon= "1000" minCon= "10" balance= "0"
writeType= "0" dbType= "mysql" dbDriver= "native" switchType= "1" slaveThreshold= "100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
host= "hostM1" url= "localhost:3306" user= "root"
password= "123456">
<!-- can have multi read hosts -->
</writeHost>
host= "hostS1" url= "localhost:3316" user= "root"
password= "123456"
host= "hostM2" url= "localhost:3316" user= "root" password= "123456"/> -->
</dataHost>
</mycat:schema>
这个配置文件稍微复杂一些,主要分3块。第1块是schema
块,主要描述了虚拟数据库的schemaTESTDB
中有哪些表,每个表分布在哪些数据节点上,分布的方法采用哪种算法。例如<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
,表示travelrecord
表分布在dn1,dn2,dn3
这3个节点上,分布的方法采用auto-sharding-long
算法。第2块是dataNode
,表示该数据库有哪些数据节点,以及这些数据节点实际对应的数据服务器和数据库名,这里配置了3个节点dn1,dn2,dn3
,都是在localhost1
服务器上,数据库名分别是db1,db2,db3
,其实,这也正是前面schema
块中用到的。第3块是dataHost
,这部分是实际的数据库服务器配置,这里配置了2个Mysql数据库,hostM1
和hostS1
,地址分别在“localhost:3306”,用户名都是root
,密码是123456
,并且指定了心跳是select user()
。有了这些信息之后,我们就可以根据自己的需要来进行设置。例如,我的数据库地址不在localhost
密码也不是123456
,安装在这里,
于是重设MyCAT中这部分配置如下,
host= "hostM1" url= "workstation:3306" user= "root"
password= "111111">
host= "hostS1" url= "workstation:3316" user= "root"
password= "111111"
启动MyCAT之前,需要先检查一些配置:
java的版本需要是1.7或以上;Mysql的配置文件需要加一行lower_case_table_names = 1
[mysqld]
hostM1
hostS1
db1,db2,db3
ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0
MYCAT_HOME
bin
一些文章1中说,还需要创建mycat用户和用户组,实际中我发现这不是必须的。之后就可以启动MyCAT了,
start
Starting Mycat- server...
之后就可以登陆MyCAT了,可以使用mysql的客户端像登陆mysql那样登陆,如下,
如果登陆成功,可以建表了,建表语句23与普通sql一样,
> create table travelrecord (id bigint not null primary key,user_id varchar( 100),traveldate DATE, fee decimal,days int);
0 rows affected ( 0.77
>
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id | bigint( 20) | NO | PRI | NULL | |
| user_id | varchar( 100) | YES | | NULL | |
| traveldate | date | YES | | NULL | |
| fee | decimal( 10, 0) | YES | | NULL | |
| days | int( 11) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set ( 0.00
> create table abc (id bigint not null primary key, name varchar( 100));
1064 (HY000): op table not in schema ----ABC
表名用小写 大写会出问题导致mycat是小写表名而mysql是大写形成找不到表又存在表不能删除和改。出问题可以在mysql上修改而不能在mycat上修正
注意:如果建立的表之前没有在schema.xml中定义,那么不可以建立此表。
建表成功后,就可以插入数据了,而且还可以使用explain
查看插入了哪个数据库,
mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(1000000,'abc','2016-01-02',100.01,3);
+-----------+-------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------------------------------------+
| dn1 | insert into travelrecord (id,user_id,traveldate,fee,days) values(1000000,'abc','2016-01-02',100.01,3) |
+-----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(7000000,'abc','2016-01-02',100.01,3);
+-----------+-------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------------------------------------+
| dn2 | insert into travelrecord (id,user_id,traveldate,fee,days) values(7000000,'abc','2016-01-02',100.01,3) |
+-----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
_id,traveldate,fee,days) values(1000000,'abc','2016-01-02',100.01,3);
Query OK, 1 row affected, 1 warning (0.10 sec)
_id,traveldate,fee,days) values(7000000,'abc','2016-01-02',100.01,3);
Query OK, 1 row affected, 1 warning (0.06 sec)
然后还可以select,可以发现,select 不过是对每个数据库上进行,同时默认加上了limit 100
的限制。
mysql> explain select * from travelrecord;
+-----------+--------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------+
| dn1 | SELECT * FROM travelrecord LIMIT 100 |
| dn2 | SELECT * FROM travelrecord LIMIT 100 |
| dn3 | SELECT * FROM travelrecord LIMIT 100 |
+-----------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from travelrecord;
+---------+---------+------------+------+------+
| id | user_id | traveldate | fee | days |
+---------+---------+------------+------+------+
| 1000000 | abc | 2016-01-02 | 100 | 3 |
| 7000000 | abc | 2016-01-02 | 100 | 3 |
+---------+---------+------------+------+------+
2 rows in set (0.00 sec)
如果mysql是innodb存储引擎,还可以设置autocommit
,之前的操作采用默认autocommit=1
,如果设置autocommit=0
,还可以使用事务,挺好用的,如下,
mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)
_id,traveldate,fee,days) values(8000000,'abc','2016-01-02',100.01,3);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from travelrecord limit 1000;
+---------+---------+------------+------+------+
| id | user_id | traveldate | fee | days |
+---------+---------+------------+------+------+
| 1000000 | abc | 2016-01-02 | 100 | 3 |
| 7000000 | abc | 2016-01-02 | 100 | 3 |
+---------+---------+------------+------+------+
2 rows in set (0.00 sec)
在实验中还发现,如果使用autocommit=0
还可以产生锁,与使用单Mysql数据库很相似,很有意思。
此时,返回来看看实际数据库Mysql中的数据,如下,
发现一个很奇怪的事情,端口3306的数据库,也就是配置文件中的hostM1
似乎没有创建travelrecord
表,也就是说,配置文件中的hostS1
似乎覆盖了hostM1
,这与注释中的“can have multi write hosts”似乎有不符之处,不知为何。另外,在实验的过程中,travelrecord
表中插入数据过程中,似乎总是无法将数据分片到dn3
上,感觉是其分片算法auto-sharding-long
有问题,不确定这是一个bug还是算法特性。
最后,吐槽下MyCAT的示例,其默认的几张表的建表语句我实在是找了半天,才在MyCAT的doc中找到,而且呈现形式还是.docx形式的一篇安装指南,实在太不规范。比较好一点的呈现,可能是一个sql脚本,包含了所有建表语句和示例数据的insert语句;或者分为建表语句sql脚本和insert示例数据sql脚本2个文件,也是个不错的主意。
综上,可以认为,MyCAT模拟了一个虚拟Mysql数据库,并通过简单的配置文件配置,将虚拟数据库中的表映射到实际数据库中。只有那些在配置文件中配置了的表,才可以被MyCAT管理,实现分片。MyCAT还提供了很多分片算法,本文没有详述。
按取模分片分库