枚举分片

枚举分片

有一张t5表，我就想按区域进行枚举分片，

1使用rule="sharding-by-intfile"策略对t5表进行枚举分片（sh1片he sh2片）：

[root@db01 ~]# vim /application/mycat/conf/schema.xml

[root@db01 ~]# cat /application/mycat/conf/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="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />

        <table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />

</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="10.0.0.201:3307" user="root" password="123">

            <readHost host="db2" url="10.0.0.201:3309" user="root" password="123" />

    </writeHost>

    <writeHost host="db3" url="10.0.0.202:3307" user="root" password="123">

            <readHost host="db4" url="10.0.0.202:3309" user="root" password="123" />

    </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="10.0.0.201:3308" user="root" password="123">

            <readHost host="db2" url="10.0.0.201:3310" user="root" password="123" />

    </writeHost>

    <writeHost host="db3" url="10.0.0.202:3308" user="root" password="123">

            <readHost host="db4" url="10.0.0.202:3310" user="root" password="123" />

    </writeHost>

    </dataHost>  

</mycat:schema>

[root@db01 ~]#

 

2因为t5表我们用省的名字name作为列，而不是id，所以我们需要修改一下规则：

[root@db01 ~]# vim /application/mycat/conf/rule.xml

<tableRule name="sharding-by-intfile">

                <rule>

                        <columns>sharding_id</columns>

                        <algorithm>hash-int</algorithm>

                </rule>

        </tableRule>

 

替换为：

<tableRule name="sharding-by-intfile">

                <rule>

                        <columns>name</columns>

                        <algorithm>hash-int</algorithm>

                </rule>

        </tableRule>

 

然后接着往下找hash-int这个方法：

<function name="hash-int"

                class="io.mycat.route.function.PartitionByFileMap">

                <property name="mapFile">partition-hash-int.txt</property>

        </function>

 

发现负责分片策略的文件是partition-hash-int.txt

由于hash-int这个函数不支持字符串，为了让其支持，我们需要添加一行代码：

 <function name="hash-int"

                class="io.mycat.route.function.PartitionByFileMap">

                <property name="mapFile">partition-hash-int.txt</property>

                <property name="type">1</property>

                <property name="defaultNode">0</property>

        </function>

接下来我们就在partition-hash-int.txt文件中编写分片策略(bj落到0号分片，sh落到1号分片，其他的默认落到1号分片)

[root@db01 conf]# vim /application/mycat/conf/partition-hash-int.txt

 

bj=0

sh=1

DEFAULT_NODE=1

 

3准备测试表重启mycat并进行测试：

[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

[root@db01 conf]# mycat restart

Stopping Mycat-server...

Mycat-server was not running.

Starting Mycat-server...

[root@db01 conf]# mysql -uroot -p123456 -h10.0.0.201 -P8066

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB)

 

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> use TESTDB

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> insert into t5(id,name) values(1,'bj');

Query OK, 1 row affected (2.02 sec)

 

mysql> insert into t5(id,name) values(2,'sh');

Query OK, 1 row affected (0.15 sec)

 

mysql> insert into t5(id,name) values(3,'bj');

Query OK, 1 row affected (0.05 sec)

 

mysql> insert into t5(id,name) values(4,'sh');

Query OK, 1 row affected (0.11 sec)

 

mysql> insert into t5(id,name) values(5,'tj');

Query OK, 1 row affected (0.07 sec)

 

mysql> select * from t5;

+----+------+

| id | name |

+----+------+

|  1 | bj   |

|  2 | sh   |

|  3 | bj   |

|  4 | sh   |

|  5 | tj   |

+----+------+

5 rows in set (0.32 sec)

 

mysql>

 

 

4分别登录后端节点查询数据看看是否是按照我们要求进行分片的：

 

[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.t5;"

[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "select * from taobao.t5;"

报错：sh1可以获取数据，sh2死活得不到数据，

 

解决：

结论是mycat程序异常造成的，需要将程序彻底关掉后再重启，然后删除旧的测试表，再进行测试

 

[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "delete from taobao.t5;"

[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "delete from taobao.t5;"

[root@db01 conf]# mycat stop

[root@db01 conf]# ps -ef |grep mycat

[root@db01 conf]# kill -9 1178

[root@db01 conf]# ps -ef |grep mycat

[root@db01 conf]# kill -9 1180

[root@db01 conf]# ps -ef |grep mycat

[root@db01 conf]# ps -ef |grep java

[root@db01 conf]# ps -ef |grep mycat

[root@db01 conf]# mycat start

[root@db01 conf]# netstat -tulnp

[root@db01 conf]# vim ../logs/wrapper.log

[root@db01 conf]# mycat restart

[root@db01 conf]# netstat -tulnp

[root@db01 conf]# mycat stop

[root@db01 conf]# netstat -tulnp

[root@db01 conf]# mycat start

[root@db01 conf]# netstat -tulnp

[root@db01 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P8066

mysql> use TESTDB

mysql> insert into t5(id,name) values(33,'sh');

mysql> insert into t5(id,name) values(34,'bj');

[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "select *   from taobao.t5;"

[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select *   from taobao.t5;"