七、mycat-ER分片

系列导航

一、实验环境

ER分片
实验环境      服务内容         dataNode
192.168.0.1 mycat mysql     dn162
192.168.0.2 mysql            dn163
192.168.0.3 mysql            dn164

二、实验内容

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

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 | 阿拉尔       |
+--------------------+---------+--------------+