mycat分库初体验

我们先来做mysql的准备工作，假装一张大表分成了3张表。

一：创建3个数据库db1、db2、db3。

二：在三个分片里分别创建travelrecord表

use db1;create table travelrecord(id varchar(64) not null,name varchar(5) not null,phone bigint(11));ENGINE=InnoDB DEFAULT CHARSET=utf8;use db2;create table travelrecord(id varchar(64) not null,name varchar(5) not null,phone bigint(11));ENGINE=InnoDB DEFAULT CHARSET=utf8;use db3;create table travelrecord(id varchar(64) not null,name varchar(5) not null,phone bigint(11));ENGINE=InnoDB DEFAULT CHARSET=utf8;

三：在三个分片里分别插入几条记录

use db1;insert into trabelrecordvalues("12345","张三封",15723456789);insert into trabelrecordvalues("12356","张无忌",15823456780);insert into trabelrecordvalues("12367","张飞",15623456780);use db2;insert into trabelrecordvalues("22345","李隆基",13523456780);insert into trabelrecordvalues("23456","李白",13623456781);insert into trabelrecordvalues("24567","李煜",13723456782);use db3;insert into trabelrecordvalues("32345","王翦",18023456780);insert into trabelrecordvalues("33456","王勃",18123456781);insert into trabelrecordvalues("34567","王维",18223456782);

mysql的准备工作暂告一段落。接下来部署mycat。

四：下载mycat1.6版本

下载地址：

https://github.com/MyCATApache/Mycat-download/blob/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-win.tar.gz

五：解压并配置

解压完成后在schema.xml里配置节点及数据库连接

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"><!-- auto sharding by id (long) --><table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /></schema><dataNode name="dn1" dataHost="localhost1" database="db1" /><dataNode name="dn2" dataHost="localhost1" database="db2" /><dataNode name="dn3" dataHost="localhost1" database="db3" /><dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  <heartbeat>select user()</heartbeat>  <writeHost host="hostM1" url="localhost:3306" user="root"   password=""></writeHost></dataHost>

六：启动mycat

点击startup_nowrap.bat，没有一闪而过，则启动成功。

Ok，mycat也部署OK了，接下来可以测试了。

我们在mysql workbench里连接mycat进去查询下分布在3个分片上的travelrecord表中的数据

Ok，分布在3个分片中的数据汇聚在一起了。