mycat 分库分表 枚举分片一

1、关键的xml 配置表

   server.xml

  user 标签用来指定访问数据库的账户。

<?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://io.mycat/">
    <!-- 读写都可用的用户 -->
    <user name="root" >
        <property name="password">root</property>
        <property name="schemas">testdb,test2db</property>

        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>

    <!-- 只读用户 -->
    <user name="user" >
        <property name="password">user</property>
        <property name="schemas">testdb,test2db</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>

2、schema.xml

schema  用来配置逻辑数据库名，如 testdb,test2db 是mycat 作为数据库中间件虚拟的数据库。

table 标签用来指定 表 ，dataNode  表示该表所在的分片上，即实际的物理数据库节点上。rule 指定分片的规则。

      dn$2-4  表示dn2,dn3,dn4

 dataNode 标签  name 表示分片， dataHost 表示分片的host地址，database 指定的的真实的数据库名称。

dataHost  标签 指定 heartbeat 心跳， writeHost 写数据地址，url 等。

逻辑库的意思指 test2db 对应了dn$2-4 个分片上test2 数据库。

dn$2-4 分片对应了可以多个写host地址，读host。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!--配置逻辑数据库testdb-->
    <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
	<schema name="test2db" checkSQLschema="false" sqlMaxLimit="100">
	   <table name="prv"  dataNode="dn$2-4" rule="prv" />
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"/> -->
	<dataNode name="dn1" dataHost="nginx.fandong.com" database="test" />
	<dataNode name="dn2" dataHost="dha" database="test2"/>
	<dataNode name="dn3" dataHost="dhe" database="test2"/>
	<dataNode name="dn4" dataHost="dhc" database="test2"/>
	<dataHost name="nginx.fandong.com" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="mysql_a.fandong.com:3306" user="root"
				   password="root">
			<!-- can have multi read hosts -->
			<readHost host="hostS2" url="mysql_b.fandong.com:3306" user="user" password="user" />
		</writeHost>
	</dataHost>
	<dataHost name="dha" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100" >
			  <heartbeat>select user()</heartbeat>
			  <writeHost host="hostM2" url="mysql_a.fandong.com:3306" user="root" password="root">
			  	<readHost  host="hostS2" url="mysql_a.fandong.com:3306" user="root" password="root" ></readHost>
              </writeHost>
	</dataHost>

     <dataHost name="dhe" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100" >
			  <heartbeat>select user()</heartbeat>
			  <writeHost host="hostM3" url="mysql_e.fandong.com:3306" user="root" password="root">
			     <readHost  host="hostS3" url="mysql_e.fandong.com:3306" user="root" password="root" ></readHost>
			  </writeHost>
	</dataHost>

	<dataHost name="dhc" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100" >
			  <heartbeat>select user()</heartbeat>
			  <writeHost host="hostM4" url="mysql_d.fandong.com:3306" user="root" password="root">
			     <readHost  host="hostS4" url="mysql_d.fandong.com:3306" user="root" password="root"></readHost>
			  </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://io.mycat/">
    <tableRule name="prv">
             <rule>
                <columns>name</columns>
                <algorithm>hash-int</algorithm>
                </rule>
    </tableRule>
	<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    <!--
        type: 0 表示整形， 1 非整数
    -->
		<property name="mapFile">partition-hash-int.txt</property>
		<property name="type">1</property>
		<property name="defaultNode">1</property>
	</function>
</mycat:rule>

partition-hash-int.txt

这里指定了0，1，2 表示的是dn$2-4

wuhan=0
shanghai=1
suzhou=2

文件放到配置文件中。

通过验证可以发现枚举分片OK。