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="sh1"> #没有明确指定的表存sh1分片
        <table name="user" dataNode="sh1"/>  #user表存储在sh1
        <table name="order_t" dataNode="sh2"/> #order_t 表存储在sh2
</schema> 
    <dataNode name="sh1" dataHost="oldguo1" database= "taobao" /> 
    <dataNode name="sh2" dataHost="oldguo2" database= "taobao" /> 
    <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
            <heartbeat>select user()</heartbeat> 
            <writeHost host="db1" url="192.168.1.141:3307" user="root" password="123.com"> 
                <readHost host="db2" url="192.168.1.141:3309" user="root" password="123.com" /> 
            </writeHost> 
            <writeHost host="db3" url="192.168.1.145:3307" user="root" password="123.com"> 
                <readHost host="db4" url="192.168.1.145:3309" user="root" password="123.com" /> 
            </writeHost>   
       </dataHost>
       <dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
           <heartbeat>select user()</heartbeat> 
           <writeHost host="db1" url="192.168.1.141:3308" user="root" password="123.com"> 
              <readHost host="db2" url="192.168.1.141:3310" user="root" password="123.com" />
           </writeHost> 
           <writeHost host="db3" url="192.168.1.145:3308" user="root" password="123.com"> 
               <readHost host="db4" url="192.168.1.145:3310" user="root" password="123.com" /> 
           </writeHost> 
      </dataHost> 
</mycat:schema> 

 

注意：上面的库和表在mysql实例上必须提前创建好。

mysql -h192.168.1.141 -uroot -p -P3307 -e 'create database taobao charset utf8mb4;'

mysql -h192.168.1.145 -uroot -p -P3307 -e 'create database taobao charset utf8mb4;'

建表：

 

mysql -h192.168.1.141 -uroot -p -P3307 -e 'create table user (id int not null auto_increment primary key,name varchar(20) not null);'

mysql -h192.168.1.145 -uroot -p -P3307 -e 'create table order_t(id int not null auto_increment primary key, order_id varchar(20)   not null);'

 

mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
| user             |
+------------------+
2 rows in set (0.00 sec)

mysql> insert into user(111,'xiaoq');
ERROR 1064 (HY000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '111,'xiaoq')' at line 1
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into user(id,name) values(111,'xiaoaa');
Query OK, 1 row affected (0.03 sec)

mysql> desc order_t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

mysql> select * from order_t;
+------+------+
| id   | name |
+------+------+
|   12 | ys   |
|   13 | ws   |
|   11 | rs   |
+------+------+
3 rows in set (0.14 sec)

mysql> insert into order_t(id,name) values(111,'compuler');
Query OK, 1 row affected (0.02 sec)

mysql> select * from order_t;
+------+----------+
| id   | name     |
+------+----------+
|   12 | ys       |
|   13 | ws       |
|   11 | rs       |
|  111 | compuler |
+------+----------+
4 rows in set (0.00 sec)