Mycat垂直分库

目的垂直分库
192.168.8.51 orders库  表    orders , orders_detail, dict_order_type
192.168.8.50 orders库  表   customer

customer 用节点dn2 其他的默认dn1

备注：这里不涉及读写分离

mycat 配置文件

<?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="dn1">
    <table name="customer" dataNode="dn2"></table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="orders" />
    <dataNode name="dn2" dataHost="host2" database="orders" />

    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.8.51:3306" user="slave"
                   password="12345678">
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.8.50:3306" user="slave"
                   password="12345678">
        </writeHost>
    </dataHost>
</mycat:schema>

操作连接mycat 创建表 实现分库操作

[root@localhost ~]# mysql -umycat -p123456 -h 192.168.8.51 -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 2
Server version: 5.6.29-mycat-1.6.7.3-release-20190828215749 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2020, 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> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

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> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
+------------------+
1 row in set (0.00 sec)

mysql> select * from customer 
    -> ;
ERROR 1146 (HY000): Table 'orders.customer' doesn't exist
mysql> create table customer(
    -> id  int auto_increment,
    -> name varchar(200),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table orders(
    -> id  int auto_increment,
    -> order_type int,
    -> customer_id int,
    -> amount decimal(10,2),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table orders_detail(
    -> id  int auto_increment,
    -> detail  int,
    -> order_id int,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table dict_order_type(
    -> id  int auto_increment,
    -> order_type   varchar(200),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
| dict_order_type  |
| orders           |
| orders_detail    |
+------------------+
4 rows in set (0.00 sec)

mysql> 

验证结果

本作品采用《CC 协议》，转载必须注明作者和本文链接