mycate hash求模水平切割

测试表employees.t_emp水平分片，按照emp_no分片

配置mycat schema rule

 1 [mycat@redis04 conf]$ cat schema.xml
 2 <?xml version="1.0"?>
 3 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
 4 <mycat:schema xmlns:mycat="http://io.mycat/">
 5     <schema name="employees" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
 6        <table name="t_emp" primaryKey="emp_no" dataNode="dn$1-2" rule="mod-long" />
 7     </schema>
 8  
 9     <dataNode name="dn$1-2" dataHost="dh$1-2" database="employees" />
10     <dataHost name="dh1" maxCon="1000" minCon="10" balance="1"  writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
11         <heartbeat>select user()</heartbeat>
12         <writeHost host="hostM1" url="192.168.20.201:3306" user="ht"  password="ocm123" />
13     </dataHost>
14    <dataHost name="dh2" maxCon="1000" minCon="10" balance="1"  writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
15         <heartbeat>select user()</heartbeat>
16         <writeHost host="hostM2" url="192.168.20.202:3306" user="ht"  password="ocm123" />
17     </dataHost>
18 
19 </mycat:schema>
20 
21 <tableRule name="mod-long">
22                 <rule>
23                         <columns>emp_no</columns>
24                         <algorithm>mod-long</algorithm>
25                 </rule>
26 </tableRule>
27 
28 <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
29                 <!-- how many data nodes -->
30                 <property name="count">2</property>
31 </function>

启动mycat，使用mycat explain查看路由

 1 mysql> select count(1) from t_emp;
 2 +--------+
 3 | COUNT0 |
 4 +--------+
 5 |   1000 |
 6 +--------+
 7 1 row in set (0.02 sec)
 8 
 9 mysql> explain  select count(1) from t_emp;
10 +-----------+------------------------------------------------+
11 | DATA_NODE | SQL                                            |
12 +-----------+------------------------------------------------+
13 | dn1       | SELECT COUNT(1) AS COUNT0 FROM t_emp LIMIT 100 |
14 | dn2       | SELECT COUNT(1) AS COUNT0 FROM t_emp LIMIT 100 |
15 +-----------+------------------------------------------------+
16 2 rows in set (0.00 sec)

如果后面业务量加大2台机器无法满足业务需求需要扩容，本次模拟hash水平拆分扩容扩容。

利用explain查看那些数据需要从redis01 redis02 的部分数据导入到redis03

 1 mysql> explain select * from t_emp where emp_no=1002;
 2 +-----------+---------------------------------------+
 3 | DATA_NODE | SQL                                   |
 4 +-----------+---------------------------------------+
 5 | dn1       | select * from t_emp where emp_no=1002 |
 6 +-----------+---------------------------------------+
 7 1 row in set (0.01 sec)
 8 
 9 mysql> explain select * from t_emp where emp_no=1003;
10 +-----------+---------------------------------------+
11 | DATA_NODE | SQL                                   |
12 +-----------+---------------------------------------+
13 | dn2       | select * from t_emp where emp_no=1003 |
14 +-----------+---------------------------------------+
15 1 row in set (0.00 sec)
16 
17 mysql> explain select * from t_emp where emp_no=1004;
18 +-----------+---------------------------------------+
19 | DATA_NODE | SQL                                   |
20 +-----------+---------------------------------------+
21 | dn3       | select * from t_emp where emp_no=1004 |
22 +-----------+---------------------------------------+
23 1 row in set (0.01 sec)
24 
25 mysql> select 1002%3 from dual;
26 +--------+
27 | 1002%3 |
28 +--------+
29 |      0 |
30 +--------+
31 1 row in set (0.00 sec)
32 
33 mysql> select 1003%3 from dual;
34 +--------+
35 | 1003%3 |
36 +--------+
37 |      1 |
38 +--------+
39 1 row in set (0.00 sec)
40 
41 mysql> select 1004%3 from dual;
42 +--------+
43 | 1004%3 |
44 +--------+
45 |      2 |
46 +--------+
47 1 row in set (0.00 sec)

上面输出可得规律 emp_no%3=2数据应该放到redis03主机上。

在redis03上创建用户及授权及创建表

create user 'ht'@'192.%' identified by 'ocm123';
GRANT ALL ON employees.* TO 'ht'@'%';

修改mycat' schema rule，重启mycat

 1 [mycat@redis04 conf]$ cat schema.xml
 2 <?xml version="1.0"?>
 3 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
 4 <mycat:schema xmlns:mycat="http://io.mycat/">
 5     <schema name="employees" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
 6        <table name="t_emp" primaryKey="emp_no" dataNode="dn$1-3" rule="mod-long" />
 7     </schema>
 8  
 9     <dataNode name="dn$1-3" dataHost="dh$1-3" database="employees" />
10     <dataHost name="dh1" maxCon="1000" minCon="10" balance="1"  writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
11         <heartbeat>select user()</heartbeat>
12         <writeHost host="hostM1" url="192.168.20.201:3306" user="ht"  password="ocm123" />
13     </dataHost>
14    <dataHost name="dh2" maxCon="1000" minCon="10" balance="1"  writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
15         <heartbeat>select user()</heartbeat>
16         <writeHost host="hostM2" url="192.168.20.202:3306" user="ht"  password="ocm123" />
17     </dataHost>
18    <dataHost name="dh3" maxCon="1000" minCon="10" balance="1"  writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
19         <heartbeat>select user()</heartbeat>
20         <writeHost host="hostM2" url="192.168.20.203:3306" user="ht"  password="ocm123" />
21     </dataHost>
22 
23 </mycat:schema>
24 
25 [mycat@redis04 conf]$ cat rule.xml
26 <?xml version="1.0" encoding="UTF-8"?>
27 <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
28     - you may not use this file except in compliance with the License. - You 
29     may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
30     - - Unless required by applicable law or agreed to in writing, software - 
31     distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
32     WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
33     License for the specific language governing permissions and - limitations 
34     under the License. -->
35 <!DOCTYPE mycat:rule SYSTEM "rule.dtd">
36 <mycat:rule xmlns:mycat="http://io.mycat/">
37 <tableRule name="mod-long">
38                 <rule>
39                         <columns>emp_no</columns>
40                         <algorithm>mod-long</algorithm>
41                 </rule>
42 </tableRule>
43 
44 <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
45                 <!-- how many data nodes -->
46                 <property name="count">3</property>
47 </function>
48 </mycat:rule>

导入导出数据及清理数据

1 redis01
2 select * into outfile '/home/mysql/t1.txt' from employees.t_emp where emp_no%3=2 ; 
3 delete  from  employees.t_emp where emp_no%3=2;
4 redis02
5 select * into outfile '/home/mysql/t2.txt' from employees.t_emp where emp_no%3=2 ; 
6 delete  from  employees.t_emp where emp_no%3=2;
7 redis03
8 load data infile '/home/mysql/t1.txt' into table employees.t_emp;
9 load data infile '/home/mysql/t2.txt' into table employees.t_emp;

这样扩容的话达到最小停机时间不过在导入导出数据时，delete 、update、select语句时路由到dn3上没数据

查看扩容后的效果

1 mysql> explain select count(1) from t_emp;
2 +-----------+------------------------------------------------+
3 | DATA_NODE | SQL                                            |
4 +-----------+------------------------------------------------+
5 | dn1       | SELECT COUNT(1) AS COUNT0 FROM t_emp LIMIT 100 |
6 | dn2       | SELECT COUNT(1) AS COUNT0 FROM t_emp LIMIT 100 |
7 | dn3       | SELECT COUNT(1) AS COUNT0 FROM t_emp LIMIT 100 |
8 +-----------+------------------------------------------------+
9 3 rows in set (0.00 sec)