Mycat入门：垂直拆分实践

使用Mycat垂直拆分，如下图所示：

使用Mycat垂直拆分步骤如下

环境准备说明

products.product表数据如下：

+----+--------------+
| id | product_name |
+----+--------------+
|  1 | iphone X     |
|  2 | Hua Wei P20  |
|  3 | xiaomi 8     |
+----+--------------+

orders.order表数据如下：

+----+--------------+-----+---------+
| id | product_name | num | price   |
+----+--------------+-----+---------+
|  1 | iphone X     |   1 | 1000000 |
|  2 | HW p20       |   1 |  400000 |
|  3 | xiaomi 8     |   1 |  200000 |
+----+--------------+-----+---------+

使用server.xml配置系统变量和用户权限

<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
</user>

使用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">
        <table name="order" primaryKey="id" dataNode="dn1" />
        <table name="product" primaryKey="id" dataNode="dn2" />
    </schema>

    <!-- 数据节点 -->
    <dataNode name="dn1" dataHost="localhost1" database="orders"/>
    <dataNode name="dn2" dataHost="localhost2" database="products"/>

    <!-- 物理主机 -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="order" url="192.168.11.133:3306" user="root"
                   password="123456" />
    </dataHost>

    <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="product" url="192.168.11.239:3306" user="root"
                   password="123456" />
    </dataHost>
</mycat:schema>

验证Mycat

# 登陆Mycat
mysql -uroot -p123456 -P8066 -h 192.168.11.131

use TESTDB;

select * from `order`;

select * from product;