mycat分库初体验

港控/mmm° 2022-09-30 06:47 134阅读 0赞

我们先来做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 trabelrecord
    values("12345","张三封",15723456789);
    insert into trabelrecord
    values("12356","张无忌",15823456780);
    insert into trabelrecord
    values("12367","张飞",15623456780);
    use db2;
    insert into trabelrecord
    values("22345","李隆基",13523456780);
    insert into trabelrecord
    values("23456","李白",13623456781);
    insert into trabelrecord
    values("24567","李煜",13723456782);
    use db3;
    insert into trabelrecord
    values("32345","王翦",18023456780);
    insert into trabelrecord
    values("33456","王勃",18123456781);
    insert into trabelrecord
    values("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][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">
    		
    		<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表中的数据

![Center][]

![Center 1][]

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

[https_github.com_MyCATApache_Mycat-download_blob_master_1.6-RELEASE_Mycat-server-1.6-RELEASE-20161028204710-win.tar.gz]: https://github.com/MyCATApache/Mycat-download/blob/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-win.tar.gz
[Center]: /images/20220708/5294118185a44e989512fae74b2196bf.png
[Center 1]: /images/20220708/d4f73493c98c40668cea0455478f82ea.png