Demand:
The user instance SERV is divided into different databases according to the org? ID
Agreement:
Beijing (ORG UU id = 101), Shanghai (ORG UU id = 102), Shenzhen (ORG UU id = 103), Chongqing (ORG UU id = 104), Sichuan (ORG UU id = 105)
Beijing bjdb, Shanghai shdb, Shenzhen szdb and Chongqing cqdb are built on dn10 and dn11
Sichuan scdb database is built on dn20 and dn21
Architecture diagram:
High availability is not covered in this article. Next step
Function Description:
1. All writes go to the main database
2. All the reading goes from the database
3. Write dn10 for non Sichuan data and read dn11 for non Sichuan data
4. Sichuan data writing dn20, Sichuan data reading dn21
Mycat profile
server.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:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="useSqlStat">0</property> <!-- 1 To turn on real-time statistics, 0 to turn off --> <property name="useGlobleTableCheck">0</property> <!-- 1 To enable full overtime consistency detection, 0 to close --> <property name="sequnceHandlerType">2</property> <!-- <property name="useCompression">1</property>--> <!--1 For opening mysql Compression protocol--> <!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--Set simulated MySQL Version number--> <!-- <property name="processorBufferChunk">40960</property> --> <!-- <property name="processors">1</property> --> <!-- <property name="processorExecutor">32</property> --> <!--Default is type 0: DirectByteBufferPool | type 1 ByteBufferArena--> <property name="processorBufferPoolType">0</property> <!--The default is 65535 64 K Be used for sql Maximum text length when parsing --> <!--<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>--> <!-- mycat Listening port---Programmers generally need to adjust it serverport:Port the program needs to connect to managerport:Port for management console --> <property name="serverPort">8077</property> <property name="managerPort">9077</property> <!-- <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> --> <!--Distributed transaction switch, 0 for unfiltered distributed transactions, 1 for filtered distributed transactions (if only global tables are involved in distributed transactions, then no filtering), 2 for unfiltered distributed transactions,But log distributed transactions--> <property name="handleDistributedTransactions">0</property> <!-- off heap for merge/order/group/limit 1 On 0 off --> <property name="useOffHeapForMerge">1</property> <!-- Unit is m --> <property name="memoryPageSize">1m</property> <!-- Unit is k --> <property name="spillsFileBufferSize">1k</property> <property name="useStreamOutput">0</property> <!-- Unit is m --> <property name="systemReserveMemorySize">384m</property> <!--Whether to adopt zookeeper Coordinated switching --> <property name="useZKSwitch">true</property> </system> <!-- Where beginners need to modify, access is defined mycat The user name and password of the mycat Database that can be connected, if there are more than one","Separate --> <user name="acct"> <property name="password">12340101</property> <property name="schemas">acctdb</property> </user> </mycat:server>
schema.xml
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- According to the amount of data, configure two primary and two secondary, but not highly available two primary and two secondary for backup. For internal label and attribute configuration, refer to the above read-write separation section--> <schema name="acctdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="acctNode"> <table name="serv" dataNode="dn1,dn2,dn3,dn4,dn5" rule="sharding-by-orgid"/> </schema> <dataNode name="dn1" dataHost="dn1host" database="bjdb" /> <dataNode name="dn2" dataHost="dn2host" database="shdb" /> <dataNode name="dn3" dataHost="dn3host" database="szdb" /> <dataNode name="dn4" dataHost="dn5host" database="cqdb" /> <dataNode name="dn5" dataHost="dn4host" database="scdb" /> <dataHost name="dn1host" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user();</heartbeat> <writeHost host="hostM1" url="192.168.245.111:3306" user="root" password="12340101"> <readHost host="hostS101" url="192.168.245.112:3306" user="root" password="12340101" /> </writeHost> </dataHost> <dataHost name="dn2host" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user();</heartbeat> <writeHost host="hostM1" url="192.168.245.111:3306" user="root" password="12340101"> <readHost host="hostS101" url="192.168.245.112:3306" user="root" password="12340101" /> </writeHost> </dataHost> <dataHost name="dn3host" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user();</heartbeat> <writeHost host="hostM1" url="192.168.245.111:3306" user="root" password="12340101"> <readHost host="hostS101" url="192.168.245.112:3306" user="root" password="12340101" /> </writeHost> </dataHost> <dataHost name="dn4host" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user();</heartbeat> <writeHost host="hostM1" url="192.168.245.111:3306" user="root" password="12340101"> <readHost host="hostS101" url="192.168.245.112:3306" user="root" password="12340101" /> </writeHost> </dataHost> <dataHost name="dn5host" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user();</heartbeat> <writeHost host="hostM1" url="192.168.245.113:3306" user="root" password="12340101"> <readHost host="hostS101" url="192.168.245.114:3306" user="root" password="12340101" /> </writeHost> </dataHost </mycat:schema>
rule.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mycat:rule SYSTEM "rule.dtd"> <mycat:rule xmlns:mycat="http://org.opencloudb/"> <tableRule name="sharding-by-hour"> <rule> <columns>org_id</columns> <algorithm>sharding-by-orgid</algorithm> </rule> </tableRule> <function name="sharding-by-orgid" class="com.jv.ShardingByOrgId"> </function> </mycat:rule >
Custom fragment rule class
import io.mycat.route.function.AbstractPartitionAlgorithm; public class ShardingByOrgId extends AbstractPartitionAlgorithm { private static final long serialVersionUID = 1L; @Override public Integer calculate(String orgId) { switch(orgId) { case "101": return 1; case "102": return 2; case "103": return 3; case "104": return 4; case "105": return 5; default: return 5; } } }
The custom fragment rule needs to inherit AbstractPartitionAlgorithm and override the calculate method. AbstractPartitionAlgorithm can be downloaded to its source code in git in Mycat-server-1.6-RELEASE.jar package
The fragmentation rules of native Mycat can be referred to MyCat segmentation algorithm learning (pure rotation)