2021 先定个小目标?搞清楚 MyCat 分片的两种拆分方法和分片规则!(1)
<schema name="ITCAST_DB" checkSQLschema="false" sqlMaxLimit="100"><table name="tb_areas_city" dataNode="dn1" primaryKey="id" /><table name="tb_areas_provinces" dataNode="dn1" primaryKey="id" /><table name="tb_areas_region" dataNode="dn1" primaryKey="id" /><table name="tb_user" dataNode="dn1" primaryKey="id" /><table name="tb_user_address" dataNode="dn1" primaryKey="id" />
<table name="tb_goods_base" dataNode="dn2" primaryKey="id" /><table name="tb_goods_desc" dataNode="dn2" primaryKey="goods_id" /><table name="tb_goods_item_cat" dataNode="dn2" primaryKey="id" />
<table name="tb_order_item" dataNode="dn3" primaryKey="id" /><table name="tb_order_master" dataNode="dn3" primaryKey="order_id" /><table name="tb_order_pay_log" dataNode="dn3" primaryKey="out_trade_no" /></schema>
<dataNode name="dn1" dataHost="host1" database="user_db" /><dataNode name="dn2" dataHost="host2" database="goods_db" /><dataNode name="dn3" dataHost="host3" database="order_db" />
<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.192.157:3306" user="root" password="itcast"></writeHost></dataHost>
<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="hostM2" url="192.168.192.158:3306" user="root" password="itcast"></writeHost></dataHost>
<dataHost name="host3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="hostM3" url="192.168.192.159:3306" user="root" password="itcast"></writeHost></dataHost>
</mycat:schema>
五、server.xml 的配置
<user name="root" defaultAccount="true"><property name="password">123456</property><property name="schemas">ITCAST_DB</property></user>
<user name="test"><property name="password">123456</property><property name="schemas">ITCAST_DB</property></user>
<user name="user"><property name="password">123456</property><property name="schemas">ITCAST_DB</property><property name="readOnly">true</property></user>
六、测试
1.查询数据
select * from tb_goods_base;select * from tb_user;select * from tb_order_master;
2.插入数据
insert intotb_user_address(id,user_id,province_id,city_id,town_id,mobile,address,contact,is_default,notes,create_date,alias) values(null,'java00001',NULL,NULL,NULL,'13900112222','钟楼','张三','0',NULL,NULL,NULL)
insert intotb_order_item(id,item_id,goods_id,order_id,title,price,num,total_fee,pic_path,seller_id)values (null,19,149187842867954,3,'3G 6','1.00',5,'5.00',NULL,'qiandu')
3.测试跨分片的查询
SELECT order_id , payment ,receiver, province , city , area FROM tb_order_master o ,tb_areas_provinces p , tb_areas_city c , tb_areas_region rWHERE o.receiver_province = p.provinceid
AND o.receiver_city = c.cityid ANDo.receiver_region = r.areaid ;
当运行上述的 SQL 语句时, MyCat 会报错, 原因是因为当前 SQL 语句涉及到跨域的 join 操作 ;
七、全局表配置
1.数据备份
将数据节点 user_db 中的关联的字典表 tb_areas_provinces
, tb_areas_city
, tb_areas_region
中的数据备份 ;
mysqldump -uroot -pitcast user_db tb_areas_provinces > provinces;mysqldump -uroot -pitcast user_db tb_areas_city > city;mysqldump -uroot -pitcast user_db tb_areas_region > region;
2.将备份的表结构及数据信息, 远程同步到其他两个数据节点的数据库中;
scp city root@192.168.192.158:/rootscp city root@192.168.192.159:/root
scp provinces root@192.168.192.158:/rootscp provinces root@192.168.192.159:/root
scp region root@192.168.192.158:/rootscp region root@192.168.192.159:/root
评论