mycat垂直拆分

目的：不同的数据库放不同的表，不同库下的表无法跨数据库关联，需要在程序中完成
案例：test1表存放在3306库，test2存放在3308库（一主两从一备）
例如：用户库，用户评论库，订单库放在不同的数据库

server.xml,添加两个逻辑库，分别对应两个实例3306/3308上的物理库

    <user name="root">
        <property name="password">123456</property>
        <property name="schemas">TESTDB,test1,test2</property>
        <!-- 表级 DML 权限设置 -->
        <!--
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>
         -->
    </user>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--逻辑库test1/test2 下面的逻辑表test1/test2 分别放在逻辑节点node3306/node3308上-->
<!-- TESTDB虽然没用，但server.xml有记录必须写上-->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="node3306">
</schema>
<schema name="test1" checkSQLschema="false" sqlMaxLimit="100" dataNode="node3306">
        <table name="test1" dataNode="node3306"/>
</schema>

<schema name="test2" checkSQLschema="false" sqlMaxLimit="100" dataNode="node3308">
        <table name="test2" dataNode="node3308"/>
</schema>

<!--逻辑库节点node3306/node3308分别对应逻辑主机下面的真实数据库-->
    <dataNode name="node3306" dataHost="host3306" database= "test" />
    <dataNode name="node3308" dataHost="host3308" database= "test" />
<!--逻辑库节点host3306真实ip信息-->
    <dataHost name="host3306" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.247.161:3306" user="op_dbuser" password="root1234">
            <readHost host="db2" url="192.168.247.162:3306" user="op_dbuser" password="root1234" />
    </writeHost>
    <writeHost host="db3" url="192.168.247.161:3307" user="op_dbuser" password="root1234">
            <readHost host="db4" url="192.168.247.162:3307" user="op_dbuser" password="root1234" />
    </writeHost>
    </dataHost>
<!--逻辑库节点host3308真实ip信息-->
    <dataHost name="host3308" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.247.161:3308" user="op_dbuser" password="root1234">
            <readHost host="db2" url="192.168.247.162:3308" user="op_dbuser" password="root1234" />
    </writeHost>
    <writeHost host="db3" url="192.168.247.161:3309" user="op_dbuser" password="root1234">
            <readHost host="db4" url="192.168.247.162:3309" user="op_dbuser" password="root1234" />
    </writeHost>
    </dataHost>
</mycat:schema>

逻辑库对应的物理库（test1与TESTDB数据节点一致，所以对应的是同一套库）

MySQL [TESTDB]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
| test3          |
| test4          |
| tr             |
+----------------+
4 rows in set (0.00 sec)


MySQL [test1]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
| test3          |
| test4          |
| tr             |
+----------------+
4 rows in set (0.00 sec)

MySQL [test2]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test2          |
| test3          |
| test4          |
+----------------+
3 rows in set (0.01 sec)

不同的表存放在不同的库

MySQL [test1]>  insert into test1 (id,name,purchased) values(1,'aaa','2021-08-12'),(2,'bbb','2021-08-13');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test1]>  insert into test2 (id,name,purchased) values(3,'ccc','2021-08-14'),(4,'ddd','2021-08-15');
ERROR 1146 (42S02): Table 'test.test2' doesn't exist
MySQL [test1]> use test2;
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 [test2]>  insert into test2 (id,name,purchased) values(3,'ccc','2021-08-14'),(4,'ddd','2021-08-15');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0