七、mycat-ER分片
系列导航
一、实验环境
ER分片
实验环境 服务内容 dataNode
192.168.0.1 mycat mysql dn162
192.168.0.2 mysql dn163
192.168.0.3 mysql dn164
二、实验内容
1、192.168.0.2 、192.168.0.3上分别在 ship_db_01和ship_db_02数据库上创建shipping_detail表
注：建表脚本见：数据库环境及用户名密码见mycat实验数据
2、配置 schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="demo_db" checkSQLschema="false" sqlMaxLimit="100">
<table name="order_master" primaryKey="order_id" dataNode="dn163" />
<table name="customer_inf" primaryKey="customer_inf_id" dataNode="dn164" />
<!--这是ER分片表begin-->
<table name="shipping_info" primaryKey="ship_id" dataNode="dn163_sp,dn164_sp" rule="mod-long" >
<childTable name="shipping_detail" primaryKey="shipping_detail_id" joinKey="ship_id" parentKey="ship_id" />
</table>
<!--这是ER分片表end-->
<table name="goobal_master" primaryKey="goobal_id" dataNode="dn163_sp,dn164_sp" rule="rule1" autoIncrement="true"/>
</schema>
<dataNode name="dn163" dataHost="mysql163" database="order_db" />
<dataNode name="dn164" dataHost="mysql164" database="customer_db" />
<dataNode name="dn163_sp" dataHost="mysql163" database="ship_db_01" />
<dataNode name="dn164_sp" dataHost="mysql164" database="ship_db_02" />
<dataNode name="dn162" dataHost="mysql162" database="mycat" />
<dataHost name="mysql162" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.0.1" url="192.168.0.1:3306" user="root" password="123456">
</writeHost>
</dataHost>
<dataHost name="mysql163" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.0.2" url="192.168.0.2:3306" user="im_mycat" password="123456">
</writeHost>
</dataHost>
<dataHost name="mysql164" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="192.168.0.3" url="192.168.0.3:3306" user="im_mycat" password="123456">
</writeHost>
</dataHost>
</mycat:schema>
3、192.168.0.1上用mycat用户插入数据
[root@zhu logs]# mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456
mysql> show databases;
+----------+
| DATABASE |
+----------+
| demo_db |
+----------+
1 row in set (0.01 sec)
mysql> use demo_db
mysql> select * from shipping_info;
+---------+------------+
| ship_id | ship_name |
+---------+------------+
| 0 | 第1公司 |
| 2 | 第2公司 |
| 4 | 第4公司 |
| 6 | 第6公司 |
| 8 | 第8公司 |
| 1 | 第1公司 |
| 3 | 第3公司 |
| 5 | 第5公司 |
| 7 | 第7公司 |
| 9 | 第9公司 |
+---------+------------+
插入准备的实验数据；
insert into shipping_detail(shipping_detail_id,ship_id,address )values('10','0', '乌鲁木齐');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('11','1', '昌吉');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('12','2', '石河子');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('13','3', '伊犁');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('14','4', '巴州');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('15','5', '阿克苏');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('16','6', '奎屯');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('17','7', '图木舒克');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('18','8', '五家渠');
insert into shipping_detail(shipping_detail_id,ship_id,address )values('19','9', '阿拉尔');
commit；
mysql> select * from shipping_detail;
+--------------------+---------+--------------+
| shipping_detail_id | ship_id | address |
+--------------------+---------+--------------+
| 10 | 0 | 乌鲁木齐 |
| 12 | 2 | 石河子 |
| 11 | 1 | 昌吉 |
| 13 | 3 | 伊犁 |
| 15 | 5 | 阿克苏 |
| 14 | 4 | 巴州 |
| 16 | 6 | 奎屯 |
| 18 | 8 | 五家渠 |
| 17 | 7 | 图木舒克 |
| 19 | 9 | 阿拉尔 |
+--------------------+---------+--------------+
10 rows in set (0.08 sec)
4、192.168.0.2、192.168.0.3上验证
192.168.0.2：
mysql> select * from shipping_info;
+---------+------------+
| ship_id | ship_name |
+---------+------------+
| 0 | 第1公司 |
| 2 | 第2公司 |
| 4 | 第4公司 |
| 6 | 第6公司 |
| 8 | 第8公司 |
+---------+------------+
5 rows in set (0.00 sec)
mysql> select * from shipping_detail;
+--------------------+---------+--------------+
| shipping_detail_id | ship_id | address |
+--------------------+---------+--------------+
| 10 | 0 | 乌鲁木齐 |
| 12 | 2 | 石河子 |
| 14 | 4 | 巴州 |
| 16 | 6 | 奎屯 |
| 18 | 8 | 五家渠 |
+--------------------+---------+--------------+
192.168.0.3：
mysql> select * from shipping_info;
+---------+------------+
| ship_id | ship_name |
+---------+------------+
| 1 | 第1公司 |
| 3 | 第3公司 |
| 5 | 第5公司 |
| 7 | 第7公司 |
| 9 | 第9公司 |
+---------+------------+
5 rows in set (0.00 sec)
mysql> select * from shipping_detail;
+--------------------+---------+--------------+
| shipping_detail_id | ship_id | address |
+--------------------+---------+--------------+
| 11 | 1 | 昌吉 |
| 13 | 3 | 伊犁 |
| 15 | 5 | 阿克苏 |
| 17 | 7 | 图木舒克 |
| 19 | 9 | 阿拉尔 |
+--------------------+---------+--------------+
