mycat sub database sub table enumeration sub section 1

Posted by austrainer on Sun, 02 Feb 2020 15:14:14 +0100

1. Key xml configuration tables

   server.xml

The user tab specifies the account to access the database.

<?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/">
    <!-- Users who can read and write -->
    <user name="root" >
        <property name="password">root</property>
        <property name="schemas">testdb,test2db</property>

        <!-- Table level DML Permission settings -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>

    <!-- Read-Only 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 is used to configure the logical database name, such as testdb. Test2db is the virtual database of mycat as the database middleware.

The table tag is used to specify the table. The dataNode indicates the partition where the table is located, that is, the actual physical database node. Rule specifies the rule of fragmentation.

dn -4 for dn2,dn3,dn4

The dataNode label name represents the partition, the dataHost represents the host address of the partition, and the real database name specified by the database.

The dataHost tag specifies heartbeat, writeHost writes data address, url, etc.

Logical library means test2db corresponds to the test2 database on dn -4 slices.

The dn -4 partition corresponds to multiple write host addresses and read host.

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!--Configure logical database 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 represents the rules for specifying table fragmentation

Here is the way to enumerate slices.

<?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 Represents an integer, 1 is not an integer
    -->
		<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 and 2 are specified here, which means dn-4

wuhan=0
shanghai=1
suzhou=2

The file is placed in the configuration file.

Through verification, it can be found that enumeration fragmentation is OK.

Published 64 original articles, won praise 1, visited 2389
Private letter follow

Topics: mycat Database xml Apache