Mycat垂直分庫

HelloWorld-Q發表於2020-12-29
目的垂直分庫
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>

驗證結果

Mycat垂直分庫

本作品採用《CC 協議》，轉載必須註明作者和本文連結
有夢想的人睡不著,沒有夢想的人睡不醒。

相關文章