四、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、数据库环境及用户名密码见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"  />
         </schema>
  
        <dataNode name="dn163" dataHost="mysql163" database="order_db" />
        <dataNode name="dn164" dataHost="mysql164" database="customer_db" />
 
 
        <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、配置service.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
     <system>
        <property name="serverPort">8066</property>
            <property name="managerPort">9066</property>
            <property name="bindIp">0.0.0.0</property>
            <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
            <property name="charset">utf8</property>
            <property name="txIolation">2</property>
            <property name="processors">8</property>
            <property name="idleTimeout">300000</property>
            <property name="sqlExecuteTimeout">300</property>
            <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
            <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
            <property name="sequnceHandlerType">2</property>
            <property name="defaultMaxLimit">100</property>
           <property name="maxPacketSize">104857600</property> 
  </system>
  <user name="mycat_user" defaultAccount="true">
           <property name="password">123456</property>
           <property name="schemas">demo_db</property>
  </user>
</mycat:server>
 
 
4、启动mycat看是否正确
   [root@zhu mysql]# su mycat
   [mycat@zhu mysql]$ mycat start
   Starting Mycat-server...  
    
   192.168.0.1上使用root和mycat用户都可以
   >mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456 
   mysql> show databases;
+----------+
| DATABASE |
+----------+
| demo_db  |
+----------+
 
mysql> use demo_db
Database changed
mysql> show tables;
+-------------------+
| Tables in demo_db |
+-------------------+
| customer_inf      |
| order_master      |
+-------------------+
 
插入数据
先清空192.168.0.2上order_master，192.168.0.3上customer_inf表的数据；
在192.168.0.1上插入数据
insert into order_master(order_id,order_sn,customer_id,shipping_user)values(1,'20190845125425',  1 ,'张三');
insert into order_master(order_id,order_sn,customer_id,shipping_user)values(2,'20190845125426',  2 ,'李四');
commit;
 
insert into customer_inf(customer_inf_id ,customer_id,customer_name)values('3','5','王五');
insert into customer_inf(customer_inf_id ,customer_id,customer_name)values('4','6','杨六');
commit;
 
插入的数据在192.168.0.1上都可以看到
order_master的数据在163上可以看到
customer_inf的数据在164上可以看到
说明垂直分库成功
 
 
5、配置全局表
之前region_info表在192.168.0.2，192.168.0.3上已经建立好了
 
配置 schema.xml 
添加
<table name="region_info" primaryKey="region_id"   dataNode="dn163,dn164"  type="global" />
 
重启mycat
mycat stop
mycat start
 
192.168.0.1上登录mycat  插入数据
[mycat@zhu ~]$ 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       |
+-------------------+
3 rows in set (0.01 sec)
 
insert into region_info(region_name )values('乌鲁木齐');
insert into region_info(region_name )values('昌吉');
commit;
 
之后在192.168.0.1，192.168.0.2，192.168.0.3都能查到数据说明配置成功