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=0
500M-1000M=1
1000M-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)

分片存储成功

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