mycat分片部署例子

参考：https://www.cnblogs.com/raphael5200/p/5884931.html

前提：已经安装好mysql

一、下载mycat

1、下载mycat：http://www.mycat.io/

2、下载到/usr/local后执行解压Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

tar -xzvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

进入mycat目录

cd /usr/local/Mycat

启动mycat

./bin/mycat start

可以查看mycat的状态

./bin/mycat status

默认情况下mycat的配置文件指向的mysql数据库的连接信息可能不符合实际情况，所以此处运行有可能会是吧

3、mycat的配置文件

./conf/schema.xml 中定义逻辑库，表、分片节点等内容.
./conf/rule.xml 中定义分片规则.
./conf/server.xml 中定义用户以及系统相关变量，如端口等

二、配置mycat

1、在mysql数据库中创建3个数据库，模拟mycat分片

CREATE database db1;CREATE database db2;CREATE database db3; -- 注意：若是LINUX版本的MYSQL，则需要设置为Mysql大小写不敏感，否则可能会发生表找不到的问题。-- 在MySQL的配置文件/etc/my.cnf 的[mysqld] 中增加一行lower_case_table_names = 1

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db1                || db2                || db3                || mysql              || performance_schema || sys                |+--------------------+7 rows in set (0.01 sec)

2、修改mycat的schema.xml文件

<!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="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />                <!-- global table is auto cloned to all defined data nodes ,so can join                        with any table whose sharding node is in the same data node -->                <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" autoIncrement="true" 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="localhost1" database="db$0-743"                /> -->        <dataNode name="dn1" dataHost="localhost1" database="db1" />        <dataNode name="dn2" dataHost="localhost1" database="db2" />        <dataNode name="dn3" dataHost="localhost1" database="db3" />        <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />         <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />        <dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" />        <dataNode name="jdbc_dn3"       dataHost="jdbchost" 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>                <!-- can have multi write hosts -->                <writeHost host="hostM1" url="localhost:3306" user="root"                                   password="123456">                        <!-- can have multi read hosts -->                        <!-- <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> -->                </writeHost>                <!--<writeHost host="hostS1" url="localhost:3316" user="root"                                   password="123456" /> -->                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->        </dataHost>        <!--                <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">                <heartbeat>             </heartbeat>                 <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng"  password="jifeng"></writeHost>                 </dataHost>          <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0"   dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>                <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>                <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base"       password="123456" > </writeHost> </dataHost>                <dataHost name="jdbchost" maxCon="1000"         minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">                <heartbeat>select       user()</heartbeat>                <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>                <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">                <heartbeat> </heartbeat>                 <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng"         password="jifeng"></writeHost> </dataHost> -->        <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"                dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"                url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>                </dataHost> --></mycat:schema>

（1）配置中schema节点，相当于定义了一个mycat的逻辑库（类似mysql中的一个数据库），这里用默认的TESTDB

（2）schema节点中的table，相当于该TESTDB里面的表

（3）dataNode节点指定数据节点，name属性表示节点名，dataHost属性表示数据主机，database属性表示数据主机中的数据库名

（4）dataHost节点配置具体的数据主机的信息，通过writeHost可以指定多个写操作的mysql数据库（前提这些数据库已经做了主从配置），同理readHost可以直接读操作的mysql数据库。

具体的节点属性配置，可以到官网查看说明文档

3、查看分片规则

这里我使用的是schema里面的

<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

这个表travelrecord，它的分片规则是auto-sharding-long

查看rule.xml找到对应的分片规则

        <tableRule name="auto-sharding-long">                <rule>                        <columns>id</columns>                        <algorithm>rang-long</algorithm>                </rule>        </tableRule>

再查找规则的方法rang-long

<function name="rang-long"class="io.mycat.route.function.AutoPartitionByLong"><property name="mapFile">autopartition-long.txt</property></function>

查看autopartition-long.txt文件

vim ./conf/autopartition-long.txt

# range start-end ,data node index# K=1000,M=10000.0-500M=0500M-1000M=11000M-1500M=2

0-500w在分片1

500-1000W在分片2

1000W-1500w在分片3

这里应该指的是主键id的取值范围

三、尝试插入数据

连接到mycat，注意是mycat，端口是8066

mysql -uroot -P8066 -h127.0.0.1 -DTESTDB -p123456

查看下物理表

mysql> show tables;+------------------+| Tables in TESTDB |+------------------+| company          || customer         || customer_addr    || employee         || goods            || hotnews          || orders           || order_items      || travelrecord     |+------------------+9 rows in set (0.00 sec)

创建表

mysql> create table travelrecord (id bigint not null auto_increment primary key,name varchar(100));Query OK, 0 rows affected (3.84 sec)

然后db1、db2、db3都会创建这个表

插入数据

下面我们分别向表中插入三条数据分别ID是 100，6000000，11000000，看是否正常分配到三个表中

insert into travelrecord(id,name) values(100,'lucy');

insert into travelrecord(id,name) values(6000000,'lily');

insert into travelrecord(id,name) values(11000000,'tom');

mysql> insert into travelrecord(id,name) values(100,'lucy');Query OK, 1 row affected (0.07 sec)mysql> insert into travelrecord(id,name) values(6000000,'lily');Query OK, 1 row affected (0.01 sec)mysql> insert into travelrecord(id,name) values(11000000,'tom');Query OK, 1 row affected (0.00 sec)

现在分别查看db1、db2、db3的数据库，是否分别插入一条数据

现在连到mysql数据库

mysql -uroot -p

mysql> select * from db1.travelrecord;+-----+------+| id  | name |+-----+------+| 100 | lucy |+-----+------+1 row in set (0.00 sec)mysql> select * from db2.travelrecord;+---------+------+| id      | name |+---------+------+| 6000000 | lily |+---------+------+1 row in set (0.00 sec)mysql> select * from db3.travelrecord;+----------+------+| id       | name |+----------+------+| 11000000 | tom  |+----------+------+1 row in set (0.00 sec)

分片存储成功

详细深入学习可以到官网下载官方文档