mycat 按取模分片

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="mod-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> 

 

3、 查询rule.xml 规则中mod-long 定义算法及引用的文件

[root@s10 conf]# grep -A 4 'mod-long' rule.xml  
        <tableRule name="mod-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
        <tableRule name="sharding-by-murmur">
                <rule>
--
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property> #根据实际的分片数设置
        </function>

 

4、重启mycat

5、测试

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

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> delete from t_pang;
Query OK, 3 rows affected (0.35 sec)

mysql> select * from t_pang;
Empty set (0.17 sec)

mysql> select * from t_pang;
Empty set (0.00 sec)

mysql> select * from t_pang;
Empty set (0.01 sec)

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

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

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

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

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

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

在后端的mysql实例上分别查看：

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

mysql> use taobao

mysql> select * from t_pang;
Empty set (0.00 sec)

mysql> select * from t_pang;
+----+-------+
| id | name  |
+----+-------+
|  1 | xiaoa |
|  3 | xiaoa |
|  5 | xiaoa |
+----+-------+
3 rows in set (0.00 sec)

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

mysql> select * from t_pang;
+----+-------+
| id | name  |
+----+-------+
|  2 | xiaoa |
|  4 | xiaoa |
|  6 | xiaoa |
+----+-------+
3 rows in set (0.00 sec)