配置垂直分表
修改 schema.xml (db1)
cd /data/mycat/conf
cp schema.xml schema.xml.rwh
# 修改配置
vi 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" dataNode="sh1">
<table name="user" dataNode="sh1"/>
<table name="order_t" dataNode="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.31.205:3307" user="root" password="123">
<readHost host="db2" url="192.168.31.205:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.31.206:3307" user="root" password="123">
<readHost host="db4" url="192.168.31.206:3309" user="root" password="123" />
</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.31.205:3308" user="root" password="123">
<readHost host="db2" url="192.168.31.205:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.31.206:3308" user="root" password="123">
<readHost host="db4" url="192.168.31.206:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
创建测试数据(db1)
mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"
启动 mycat, 插入数据(db1)
mycat start
mysql -uroot -p123456 -h 127.0.0.1 -P8066
use TESTDB;
insert user values(1, 'klvchen');
insert order_t values(1, 'lucy');
测试(db1)
mysql -S /data/3307/mysql.sock -e "use taobao;select * from user;"
+------+---------+
| id | name |
+------+---------+
| 1 | klvchen |
+------+---------+
mysql -S /data/3308/mysql.sock -e "use taobao;select * from order_t;"
+------+------+
| id | name |
+------+------+
| 1 | lucy |
+------+------+
mysql -S /data/3307/mysql.sock -e "use taobao;show tables;"
+------------------+
| Tables_in_taobao |
+------------------+
| user |
+------------------+
mysql -S /data/3308/mysql.sock -e "use taobao;show tables;"
+------------------+
| Tables_in_taobao |
+------------------+
| order_t |
+------------------+
分片（水平拆分）
1.范围分片
分片：对一个"bigtable"，比如说t3表
(1)行数非常多，800w
(2)访问非常频繁
分片的目的：
（1）将大数据量进行分布存储
（2）提供均衡的访问路由
分片策略：
范围 range 800w 1-400w 400w01-800w
取模 mod 取余数
枚举
哈希 hash
时间 流水
优化关联查询
全局表
ER分片
配置
cd /data/mycat/conf/
cp schema.xml schema.xml.vertical
vi 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" dataNode="sh1">
<table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
</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.31.205:3307" user="root" password="123">
<readHost host="db2" url="192.168.31.205:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.31.206:3307" user="root" password="123">
<readHost host="db4" url="192.168.31.206:3309" user="root" password="123" />
</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.31.205:3308" user="root" password="123">
<readHost host="db2" url="192.168.31.205:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.31.206:3308" user="root" password="123">
<readHost host="db4" url="192.168.31.206:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
vi rule.xml
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
# 根据 id 来分片
vi autopartition-long.txt
0-10=0
11-20=1
# 创建测试数据库
mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
mycat restart
# 插入数据
use TESTDB；
insert into t3(id,name) values(1,'a');
insert into t3(id,name) values(2,'b');
insert into t3(id,name) values(3,'c');
insert into t3(id,name) values(4,'d');
insert into t3(id,name) values(11,'aa');
insert into t3(id,name) values(12,'bb');
insert into t3(id,name) values(13,'cc');
insert into t3(id,name) values(14,'dd');
检查
mysql -uroot -p123456 -h 127.0.0.1 -P 8066
mysql -S /data/3307/mysql.sock -e "use taobao;select * from t3;"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
mysql -S /data/3308/mysql.sock -e "use taobao;select * from t3;"
+----+------+
| id | name |
+----+------+
| 11 | aa |
| 12 | bb |
| 13 | cc |
| 14 | dd |
+----+------+
