MySQL 部署分布式架构 MyCAT (三)
配置垂直分表
修改 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 | +------------------+
分片(水平拆分)
范围分片
分片:对一个"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 | +----+------+
相关推荐
地平线 2020-11-02
ptmagic 2020-10-31
互联网架构之路 2020-09-17
阿义 2020-09-11
zyshappy 2020-08-16
唐亚杰 2020-07-17
middleware0 2020-06-27
魏莉的微 2020-06-07
xayddxjsjxywuhui 2020-06-02
zhangll00 2020-05-31
憧憬 2020-05-16
isHooky 2020-05-15
憧憬 2020-05-10
深圳克林斯曼 2020-05-07
枫叶上的雨露 2020-04-18
枫叶上的雨露 2020-04-15
isHooky 2020-04-10
夙梦流尘 2020-06-14