五、mycat水平分库

系列导航

一、实验环境

垂直分库
实验环境      服务内容    dataNode
192.168.0.1 mycat         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

1、192.168.0.2 、192.168.0.3上分别创建 ship_db_01和ship_db_02数据库，在ship_db上创建shipping_info表
注：建表脚本见：数据库环境及用户名密码见mycat实验数据 
    
192.168.0.2   
mysql> create database ship_db_01;   
 
192.168.0.3  
mysql> create database ship_db_02;  
    
    
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"  />
               
              <!--这是水平分库的配置部分begin-->
              <table name="shipping_info" primaryKey="ship_id" dataNode="dn163_sp,dn164_sp"  rule="mod-long" />  
              <!--这是水平分库的配置部分end-->
         </schema>
  
        <dataNode name="dn163" dataHost="mysql163" database="order_db" />
        <dataNode name="dn164" dataHost="mysql164" database="customer_db" />
         
        <!--这是水平分库的配置部分begin-->
        <dataNode name="dn163_sp" dataHost="mysql163" database="ship_db_01" />
        <dataNode name="dn164_sp" dataHost="mysql164" database="ship_db_02" />
        <!--这是水平分库的配置部分end-->
 
        <dataHost name="mysql163" 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.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、配置rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="mod-long">
                <rule>
                        <columns>ship_id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>
 
</mycat:rule>
 
 
4、启动mycat看是否正确
   [mycat@zhu ~]$ mycat restart
     
    
   192.168.0.1上使用root和mycat用户都可以
   >mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456 
mysql> use demo_db
Database changed
mysql> show tables;
+-------------------+
| Tables in demo_db |
+-------------------+
| customer_inf      |
| order_master      |
| region_info       |
| shipping_info     |
+-------------------+
4 rows in set (0.01 sec)
 
插入数据
在162上插入数据
insert into shipping_info(ship_id,ship_name )values(0,'第1公司');
insert into shipping_info(ship_id,ship_name )values(1,'第1公司');
insert into shipping_info(ship_id,ship_name )values(2,'第2公司');
insert into shipping_info(ship_id,ship_name )values(3,'第3公司');
insert into shipping_info(ship_id,ship_name )values(4,'第4公司');
insert into shipping_info(ship_id,ship_name )values(5,'第5公司');
insert into shipping_info(ship_id,ship_name )values(6,'第6公司');
insert into shipping_info(ship_id,ship_name )values(7,'第7公司');
insert into shipping_info(ship_id,ship_name )values(8,'第8公司');
insert into shipping_info(ship_id,ship_name )values(9,'第9公司');
commit;
 
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)
 
 
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)
分片成功