配置垂直分表
修改 schema.xml (db1)
1cd /data/mycat/conf 2 3cp schema.xml schema.xml.rwh 4 5# 修改配置 6vi schema.xml 7<?xml version="1.0"?> 8<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 9<mycat:schema xmlns:mycat="http://io.mycat/"> 10<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 11 <table name="user" dataNode="sh1"/> 12 <table name="order_t" dataNode="sh2"/> 13</schema> 14 <dataNode name="sh1" dataHost="oldguo1" database= "taobao" /> 15 <dataNode name="sh2" dataHost="oldguo2" database= "taobao" /> 16 <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> 17 <heartbeat>select user()</heartbeat> 18 <writeHost host="db1" url="192.168.31.205:3307" user="root" password="123"> 19 <readHost host="db2" url="192.168.31.205:3309" user="root" password="123" /> 20 </writeHost> 21 <writeHost host="db3" url="192.168.31.206:3307" user="root" password="123"> 22 <readHost host="db4" url="192.168.31.206:3309" user="root" password="123" /> 23 </writeHost> 24 </dataHost> 25 <dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> 26 <heartbeat>select user()</heartbeat> 27 <writeHost host="db1" url="192.168.31.205:3308" user="root" password="123"> 28 <readHost host="db2" url="192.168.31.205:3310" user="root" password="123" /> 29 </writeHost> 30 <writeHost host="db3" url="192.168.31.206:3308" user="root" password="123"> 31 <readHost host="db4" url="192.168.31.206:3310" user="root" password="123" /> 32 </writeHost> 33 </dataHost> 34</mycat:schema>
创建测试数据(db1)
1mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;" 2mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;" 3mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))"; 4mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"
启动 mycat, 插入数据(db1)
1mycat start 2 3mysql -uroot -p123456 -h 127.0.0.1 -P8066 4 5use TESTDB; 6insert user values(1, 'klvchen'); 7insert order_t values(1, 'lucy');
测试(db1)
1mysql -S /data/3307/mysql.sock -e "use taobao;select * from user;" 2+------+---------+ 3| id | name | 4+------+---------+ 5| 1 | klvchen | 6+------+---------+ 7 8mysql -S /data/3308/mysql.sock -e "use taobao;select * from order_t;" 9+------+------+ 10| id | name | 11+------+------+ 12| 1 | lucy | 13+------+------+ 14 15mysql -S /data/3307/mysql.sock -e "use taobao;show tables;" 16+------------------+ 17| Tables_in_taobao | 18+------------------+ 19| user | 20+------------------+ 21 22mysql -S /data/3308/mysql.sock -e "use taobao;show tables;" 23+------------------+ 24| Tables_in_taobao | 25+------------------+ 26| order_t | 27+------------------+
分片（水平拆分）
1.范围分片
1分片：对一个"bigtable"，比如说t3表 2 3(1)行数非常多，800w 4(2)访问非常频繁 5 6分片的目的： 7（1）将大数据量进行分布存储 8（2）提供均衡的访问路由 9 10分片策略： 11范围 range 800w 1-400w 400w01-800w 12取模 mod 取余数 13枚举 14哈希 hash 15时间 流水 16 17优化关联查询 18全局表 19ER分片
配置
1cd /data/mycat/conf/ 2 3cp schema.xml schema.xml.vertical 4vi schema.xml 5 6<?xml version="1.0"?> 7<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 8<mycat:schema xmlns:mycat="http://io.mycat/"> 9<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> 10 <table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" /> 11</schema> 12 <dataNode name="sh1" dataHost="oldguo1" database= "taobao" /> 13 <dataNode name="sh2" dataHost="oldguo2" database= "taobao" /> 14 <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> 15 <heartbeat>select user()</heartbeat> 16 <writeHost host="db1" url="192.168.31.205:3307" user="root" password="123"> 17 <readHost host="db2" url="192.168.31.205:3309" user="root" password="123" /> 18 </writeHost> 19 <writeHost host="db3" url="192.168.31.206:3307" user="root" password="123"> 20 <readHost host="db4" url="192.168.31.206:3309" user="root" password="123" /> 21 </writeHost> 22 </dataHost> 23 <dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> 24 <heartbeat>select user()</heartbeat> 25 <writeHost host="db1" url="192.168.31.205:3308" user="root" password="123"> 26 <readHost host="db2" url="192.168.31.205:3310" user="root" password="123" /> 27 </writeHost> 28 <writeHost host="db3" url="192.168.31.206:3308" user="root" password="123"> 29 <readHost host="db4" url="192.168.31.206:3310" user="root" password="123" /> 30 </writeHost> 31 </dataHost> 32</mycat:schema> 33
1vi rule.xml 2 <tableRule name="auto-sharding-long"> 3 <rule> 4 <columns>id</columns> 5 <algorithm>rang-long</algorithm> 6 </rule> 7 </tableRule> 8 9# 根据 id 来分片 10vi autopartition-long.txt 110-10=0 1211-20=1 13 14# 创建测试数据库 15mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);" 16mysql -S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);" 17mycat restart 18 19# 插入数据 20use TESTDB； 21insert into t3(id,name) values(1,'a'); 22insert into t3(id,name) values(2,'b'); 23insert into t3(id,name) values(3,'c'); 24insert into t3(id,name) values(4,'d'); 25insert into t3(id,name) values(11,'aa'); 26insert into t3(id,name) values(12,'bb'); 27insert into t3(id,name) values(13,'cc'); 28insert into t3(id,name) values(14,'dd');
检查
1mysql -uroot -p123456 -h 127.0.0.1 -P 8066 2mysql -S /data/3307/mysql.sock -e "use taobao;select * from t3;" 3+----+------+ 4| id | name | 5+----+------+ 6| 1 | a | 7| 2 | b | 8| 3 | c | 9| 4 | d | 10+----+------+ 11 12mysql -S /data/3308/mysql.sock -e "use taobao;select * from t3;" 13+----+------+ 14| id | name | 15+----+------+ 16| 11 | aa | 17| 12 | bb | 18| 13 | cc | 19| 14 | dd | 20+----+------+