win10 spring boot + mycat + nybaties 安装和使用

mysql 5.7 安装 ：MySQL :: Download MySQL Installer

mycat 下载：

https://github.com/MyCATApache/Mycat-download

server.xml  ：修改用户名密码即可，和自己的mysql 用户名对应即可

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
	<system>
	<property name="defaultSqlParser">druidparser</property>
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
		<!--默认是65535 64K 用于sql解析时最大文本长度 -->
		<!--<property name="maxStringLiteralLength">65535</property>-->
		<!--<property name="sequnceHandlerType">0</property>-->
		<!--<property name="backSocketNoDelay">1</property>-->
		<!--<property name="frontSocketNoDelay">1</property>-->
		<!--<property name="processorExecutor">16</property>-->
		<!-- 
			<property name="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级数据排序
	    	<property name="mutiNodePatchSize">100</property> 亿级数量排序批量
			<property name="processors">32</property> <property name="processorExecutor">32</property> 
			<property name="serverPort">8066</property> <property name="managerPort">9066</property> 
			<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
			<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
	</system>
    
	<user name="root">
		<property name="password">123456</property>
		<property name="schemas">TESTDB</property>
	</user>

	<user name="user">
		<property name="password">123456</property>
		<property name="schemas">TESTDB</property>
		<property name="readOnly">true</property>
	</user>
	<!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property> 
		<property name="weight">1</property> </node> </cluster> -->
	<!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property> 
		</host> </quarantine> -->

</mycat:server>

schema.xml： 不用改动 ，但是其中的数据 db1, db2, db3 要自己手动创建

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<!-- auto sharding by id (long) -->
		<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

		<!-- global table is auto cloned to all defined data nodes ,so can join 
			with any table whose sharding node is in the same data node -->
		<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
		<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
		
		<!-- 此处为我自己加入的 -->
		<table name="tb_company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
		<table name="tb_user" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />

		<!-- random sharding using mod sharind rule -->
		<table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3"
			rule="mod-long" />
			<!-- 
		<table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3" rule="mod-long" />
 -->
		<table name="employee" primaryKey="ID" dataNode="dn1,dn2"
			rule="sharding-by-intfile" />
		<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
			rule="sharding-by-intfile">
			<childTable name="orders" primaryKey="ID" joinKey="customer_id"
				parentKey="id">
				<childTable name="order_items" joinKey="order_id"
					parentKey="id" />
			</childTable>
			<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
				parentKey="id" />
		</table>
	</schema>

	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />
	<!-- 
	<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
	<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
	<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" />
 -->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
		writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
			password="123456">
			<!-- can have multi read hosts -->
			<!-- <readHost host="hostS1" url="localhost:3306" user="root" password="123456" 
				/> -->
		</writeHost>
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<!-- 
		<dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost>
	</dataHost>	
	-->
     <!-- 
	<dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0"
		dbType="mysql" dbDriver="jdbc">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306"
			user="root" password="123456">
		</writeHost>
	</dataHost>
	 -->
</mycat:schema>

rule.xml : 完全不用修改

<?xml version="1.0" encoding="UTF-8"?>
<!--
 -  
 - Licensed under the Apache License, Version 2.0 (the "License");
 - you may not use this file except in compliance with the License.
 - You may obtain a copy of the License at
 -  
 -      http://www.apache.org/licenses/LICENSE-2.0
 -  
 - Unless required by applicable law or agreed to in writing, software
 - distributed under the License is distributed on an "AS IS" BASIS,
 - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 - See the License for the specific language governing permissions and
 - limitations under the License.
-->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
<tableRule name="rule1">
    <rule>
      <columns>id</columns>
      <algorithm>func1</algorithm>
    </rule>
  </tableRule>

  <tableRule name="rule2">
    <rule>
      <columns>user_id</columns>
      <algorithm>func1</algorithm>
    </rule>
  </tableRule>

  <tableRule name="sharding-by-intfile">
    <rule>
      <columns>sharding_id</columns>
      <algorithm>hash-int</algorithm>
    </rule>
  </tableRule>
   <tableRule name="auto-sharding-long">
    <rule>
      <columns>id</columns>
      <algorithm>rang-long</algorithm>
    </rule>
  </tableRule>
  <tableRule name="mod-long">
    <rule>
      <columns>id</columns>
      <algorithm>mod-long</algorithm>
    </rule>
  </tableRule>
  <tableRule name="sharding-by-murmur">
      <rule>
        <columns>id</columns>
        <algorithm>murmur</algorithm>
      </rule>
   </tableRule>
   
   <function name="murmur" class="org.opencloudb.route.function.PartitionByMurmurHash">
      <property name="seed">0</property><!-- 默认是0-->
      <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片-->
      <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍-->
      <!-- 
      <property name="weightMapFile">weightMapFile</property>
                     节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
      <!-- 
      <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
                      用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
  </function>
  <function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hash-int.txt</property>
  </function>
   <function name="rang-long" class="org.opencloudb.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
  </function>
  <function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">
   <!-- how many data nodes  -->
    <property name="count">3</property>
  </function>
  
  <function name="func1" class="org.opencloudb.route.function.PartitionByLong">
    <property name="partitionCount">8</property>
    <property name="partitionLength">128</property>
  </function>
</mycat:rule>

启动 mycat   

 

 

spring boot  +  mybaties + mycat  这时我找到的一个git 源码，稍作修改就可以正常运行

springboot-learn/springboot-mybatis-mycat at master · fujiangwei/springboot-learn · GitHub

1. 修改配置

#设置应用端口
server:
  port: 8081

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    # Mycat连接
    url: jdbc:mysql://localhost:8066/TESTDB?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8
    username: root
    password: 123456

# MyBatis
mybatis:
  type-aliases-package: com.example.springbootmybatismycat.domain
  mapper-locations: classpath:/mybatis/*.xml
  #sql打印配置
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

2. 修该 mysql 驱动版本

   <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
            <scope>runtime</scope>
        </dependency>

3.执行建表语句 ， 在 db1， db2， db3 中分别创建以下表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_company
-- ----------------------------
DROP TABLE IF EXISTS `tb_company`;
CREATE TABLE `tb_company` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_company
-- ----------------------------
INSERT INTO `tb_company` VALUES ('1', '\'hs\'');

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'kinson', '1');

4. schema.xml：加入 刚刚创建的表，上面的文件中已经添加了

	<!-- 此处为我自己加入的 -->
		<table name="tb_company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
		<table name="tb_user" primaryKey="ID" type="global" dataNode="dn1,dn2" />

5. 启动运行  ：