Mycat学习总结

场景一：

1.一个全局表 t_test

a. 通过mycat 操作数据 insert 、delete 数据，则对应的2个datanode中的数据都会被删除

b. 2个datanode中的数据不同时，不会相互同步。

注：貌似从官方描述上来说，是会自动同步的。

摘自:Mycat 官方开发指南 V1.6.0中

2.测试分片规则

2.1 分片枚举

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

<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
        
        <table name="employee" primaryKey="ID" dataNode="dn1,dn2"
               rule="sharding-by-intfile" />
    </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:3307" user="root"
                   password="123456">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="localhost:3307" user="root" password="123456" />
        </writeHost>
    </dataHost>
 
</mycat:schema>

create table employee (id int not null primary key,name varchar(100),sharding_id int not null);

insert into employee(id,name,sharding_id) values(1,'hutuchong',10000);

insert into employee(id,name,sharding_id) values(2,'zhanghui',10010);

这两条数据会分别查到不同的分片上，因为表配置了这个rule规则

 rule="sharding-by-intfile" ，在rule.xml中配置了算法，对应的文件如下

这个意思是 当值10000时存 index为0的分片（第一个分片 dn1）

2.2 全局表

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

<mycat:schema xmlns:mycat="http://io.mycat/">
    
    <!--aps_acct start-->
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
        <table name="t_test" type="global" dataNode="aps_acct_node1,aps_acct_node2" />
    </schema>
    <dataNode name="aps_acct_node1" dataHost="aps_acct_host1" database="aps_acct" />
    <dataNode name="aps_acct_node2" dataHost="aps_acct_host2" database="aps_acct" />
    
    <dataHost name="aps_acct_host1" 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:3307" user="root" password="123456">
            <readHost host="hostS2" url="localhost:3307" user="root" password="123456" />
        </writeHost>
    </dataHost>
    <dataHost name="aps_acct_host2" 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:3308" user="root" password="123456">
            <readHost host="hostS2" url="localhost:3308" user="root" password="123456" />
        </writeHost>
    </dataHost>
    <!--aps_acct start-->
</mycat:schema>

参见1

2.3 自然月

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

<mycat:schema xmlns:mycat="http://io.mycat/">
    
    <!--aps_acct start-->
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
        <table name="t_order" dataNode="aps_acct_node1,aps_acct_node2"  rule="sharding-by-month" />
    </schema>
    <dataNode name="aps_acct_node1" dataHost="aps_acct_host1" database="aps_acct" />
    <dataNode name="aps_acct_node2" dataHost="aps_acct_host2" database="aps_acct" />
    
    <dataHost name="aps_acct_host1" 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:3307" user="root" password="123456">
            <readHost host="hostS2" url="localhost:3307" user="root" password="123456" />
        </writeHost>
    </dataHost>
    <dataHost name="aps_acct_host2" 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:3308" user="root" password="123456">
            <readHost host="hostS2" url="localhost:3308" user="root" password="123456" />
        </writeHost>
    </dataHost>
    <!--aps_acct start-->
</mycat:schema>

CREATE TABLE t_order(id INT NOT NULL PRIMARY KEY,create_time DATETIME NOT NULL);

INSERT INTO t_order(id,create_time) VALUES(1,'2015-01-01')
INSERT INTO t_order(id,create_time) VALUES(2,'2015-02-01')


请检查文件  rule.xml

需要插入2015年开始的数据

这样2条数据就分别进入到对应的datanode，由于每年有12个月，所有累计需要对应很多datanode

1,2,3,4...,24,....48....

Mycat学习总结

推荐阅读