Window下Mycat的安装以及Mycat简单使用

原来的环境： 假设单个数据库有t_user表，t_article表，随着数据量的增加，需要使用MyCat对这两张表进行分库分表操作

表结构如下：

 

 

 

分库分表后的结构： forum_basic, forum_bj,forum_sh,forum_hz四个数据库。第一个数据库forum_basic有用户表， 后面三个数据库forum_bj,forum_sh,forum_hz是同一个表。

 

 

 一、MyCat的安装

进入下载地址  https://github.com/MyCATApache/Mycat-download

选择安装win版本 Mycat-server-1.6-RELEASE-20161028204710-win.tar.gz

1、配置server.xml

配置user节点

用户名和密码分别为root，123456

 

 

2、配置schema

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<!-- auto sharding by id (long) -->
		<table name="t_article" dataNode="dn1,dn2,dn3" rule="mod-long" />

		 <table name="t_user" primaryKey="id" dataNode="dn4" />
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="forum_bj" />
	<dataNode name="dn2" dataHost="localhost1" database="forum_sh" />
	<dataNode name="dn3" dataHost="localhost1" database="forum_hz" />
	<dataNode name="dn4" dataHost="localhost1" database="forum_basic" />
	<dataHost 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 -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
				   password="123456">
		</writeHost>
          </dataHost>
</mycat:schema>

　　

3、修改wrapper.conf

修改jdk指向

# Java Application
wrapper.java.command=C:\Program Files\Java\jdk1.8.0_131\bin\java.exe

　

配置完以后，在bin文件夹双击startup_nowrap.bat

 

4、采用sqlyog连接mycat，默认端口为8066

 

 

　

二、MyCat中操作数据库

向用户表插入3条数据

INSERT INTO t_user(id,NAME) VALUE("1","Nick");
INSERT INTO t_user(id,NAME) VALUE("2","Larry");
INSERT INTO t_user(id,NAME) VALUE("3","Tom");

　　

向文章表插入6条数据

INSERT INTO t_article(id,userId,title,content,createTime)VALUE(1,"1","title1","content1",NOW());
INSERT INTO t_article(id,userId,title,content,createTime)VALUE(2,"1","title2","content2",NOW());
INSERT INTO t_article(id,userId,title,content,createTime)VALUE(3,"1","title3","content3",NOW());
INSERT INTO t_article(id,userId,title,content,createTime)VALUE(4,"1","title4","content4",NOW());
INSERT INTO t_article(id,userId,title,content,createTime)VALUE(5,"1","title5","content5",NOW())
INSERT INTO t_article(id,userId,title,content,createTime)VALUE(6,"1","title6","content6",NOW())

　这时，应该根据不同的取模结果，存在不同的实际t_article表中

 

 

　2、两张图关联查询

/*!mycat:catlet=io.mycat.catlets.ShareJoin */
SELECT * FROM t_user u, t_article a WHERE  u.id = a.userId AND u.id = '1'

　　返回结果如下：