Mycat水平分库配置

Mycat水平分库配置

1.1修改schema.xml文件

• name="mod_rule"为自定义名称,须和rule.xml中的 < tableRule name=“mod_rule”>相一致

<?xtiveml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
                <!--配置表名为order的表自动路由到dn2的数据库中-->
                <table name = "orders" dataNode="dn2"></table>
                <table name = "orders_hor" dataNode = "dn1,dn2" rule = "mod_rule"></table>
        </schema>
                <!--表位于数据库order1,order2中-->
        <dataNode name="dn1" dataHost="host1" database="order1" />
        <dataNode name="dn2" dataHost="host2" database="order2" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="3"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.148.134:3306" user="root"
                                   password="password">
                </writeHost>
           </dataHost>
           <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="3"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.148.136:3306" user="root"
                                   password="password">
               </writeHost>
        </dataHost>
</mycat:schema>
~                          

1.2修改rule.xml文件

• 添加分表规则

  <tableRule name="mod_rule">
                <rule>
                        <columns>customer_id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>

• 根据节点个数,设置count的值

 <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>

2.两节点中创建表orders_hor

• master1和master2上在schema.xml中

  <dataNode name="dn1" dataHost="host1" database="order1" />
  <dataNode name="dn2" dataHost="host2" database="order2" />

• order1、order2分别创建表orders_hor

CREATE TABLE orders_hor(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)  
); 

3.测试

• 插入数据

INSERT INTO orders_hor(id,order_type,customer_id,amount) VALUES(1,101,100,100100);
INSERT INTO orders_hor(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders_hor(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders_hor(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders_hor(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders_hor(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

• 验证
• mycat服务器上

mysql> select * from orders_hor;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  1 |        101 |         100 | 100100.00 |
|  2 |        101 |         100 | 100300.00 |
|  6 |        102 |         100 | 100020.00 |
|  3 |        101 |         101 | 120000.00 |
|  4 |        101 |         101 | 103000.00 |
|  5 |        102 |         101 | 100400.00 |
+----+------------+-------------+-----------+
6 rows in set (0.00 sec)

• master1上

mysql> select * from orders_hor;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  1 |        101 |         100 | 100100.00 |
|  2 |        101 |         100 | 100300.00 |
|  6 |        102 |         100 | 100020.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

• master2上

mysql>  select * from orders_hor;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  3 |        101 |         101 | 120000.00 |
|  4 |        101 |         101 | 103000.00 |
|  5 |        102 |         101 | 100400.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

• 水平分表成功