mycat 采用auto-sharding-long 规则

0、分别在两个红蓝分片中创建测试表t_pang;

 mysql -h192.168.1.141 -uroot -P3307 -p -D taobao -e"create table t_pang(id int not null,name varchar(20)); "

mysql -h192.168.1.141 -uroot -P3308 -p -D taobao -e"create table t_pang(id int not null,name varchar(20)); "

1、修改schema.xml

<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 
        <table name="t_pang"  dataNode="sh1,sh2" rule="auto-sharding-long" />  
</schema> 
    <dataNode name="sh1" dataHost="oldguo1" database= "taobao" /> 
    <dataNode name="sh2" dataHost="oldguo2" database= "taobao" /> 
       <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
            <heartbeat>select user()</heartbeat> 
            <writeHost host="db1" url="192.168.1.141:3307" user="root" password="123.com"> 
                <readHost host="db2" url="192.168.1.141:3309" user="root" password="123.com" /> 
            </writeHost> 
            <writeHost host="db3" url="192.168.1.145:3307" user="root" password="123.com"> 
                <readHost host="db4" url="192.168.1.145:3309" user="root" password="123.com" /> 
            </writeHost>   
       </dataHost>
       <dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
           <heartbeat>select user()</heartbeat> 
           <writeHost host="db1" url="192.168.1.141:3308" user="root" password="123.com"> 
              <readHost host="db2" url="192.168.1.141:3310" user="root" password="123.com" />
           </writeHost> 
           <writeHost host="db3" url="192.168.1.145:3308" user="root" password="123.com"> 
               <readHost host="db4" url="192.168.1.145:3310" user="root" password="123.com" /> 
           </writeHost> 
      </dataHost> 
</mycat:schema> 

 

2、在rule.xml文件中查找auto-sharding-long规则适用的算法和算法引用的文件。

[root@s10 conf]# grep -C 5 'auto-sharding-long' rule.xml 
                <rule>
                        <columns>sharding_id</columns>
                        <algorithm>hash-int</algorithm>
                </rule>
        </tableRule>
        <tableRule name="auto-sharding-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>rang-long</algorithm>
                </rule>
        </tableRule>
[root@s10 conf]# 
[root@s10 conf]# grep -C 5 'rang-long' rule.xml 
                </rule>
        </tableRule>
        <tableRule name="auto-sharding-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>rang-long</algorithm>
                </rule>
        </tableRule>
        <tableRule name="mod-long">
                <rule>
                        <columns>id</columns>
--
        </function>
        <function name="hash-int"
                class="io.mycat.route.function.PartitionByFileMap">
                <property name="mapFile">partition-hash-int.txt</property>
        </function>
        <function name="rang-long"
                class="io.mycat.route.function.AutoPartitionByLong">
                <property name="mapFile">autopartition-long.txt</property>
        </function>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->

 

3、修改autopartition-long.txt 配置文件

[root@s10 conf]# more autopartition-long.txt 
# range start-end ,data node index
# K=1000,M=10000.

#0-1000M=0

#1000M-2000M=1

#2000M-3000M=2
0-10=0
10-20=1

4、启动mycat start

5、测试

[root@s9 ~]# mysql -h192.168.1.145 -uroot -P8066 -p 

mysql> use TESTDB
mysql> insert into t_pang  values(1,'xiaoa');  
ERROR 1064 (HY000): partition table, insert must provide ColumnList

#通过mycat插入数据时需要使用完整的SQL语句。

mysql> insert into t_pang (id,name) values(1,'xiaoa'); 
Query OK, 1 row affected (0.07 sec)

mysql> insert into t_pang (id,name) values(10,'xiaob');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_pang (id,name) values(11,'xiaoc');
Query OK, 1 row affected (0.02 sec)

在后面的mysql实例上查看

[root@s9 ~]# mysql -h192.168.1.145 -uroot -p -P3308

mysql> use taobao

mysql> select * from t_pang;
+----+-------+
| id | name  |
+----+-------+
| 11 | xiaoc |
+----+-------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@s9 ~]# mysql -h192.168.1.145 -uroot -p -P3307

mysql> use taobao
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t_pang;
+----+-------+
| id | name  |
+----+-------+
|  1 | xiaoa |
| 10 | xiaob |
+----+-------+
2 rows in set (0.01 sec)