Getting Started with mycat

Posted by eerikk2 on Tue, 25 Feb 2020 03:48:46 +0100

First read the quick start of the official website, then build it yourself. The result is various errors and pits.Record it.

1. Environment

A virtual machine, a cloud server, a local Mac (because mysql is installed on the cloud server and locally, or you can use the virtual machine entirely, just look at yourself), mycat is deployed on the virtual machine, and two mysqls are on the cloud server and the local Mac. ps: First downloaded the Mac version of mycat, but running is a problem. Think of building nginx with docker the other day, want to play with openresty, lua module with redis is also a variety of gcc errors, docker's nginx always reported what 403, and finally it hasn't been solved. Better with brew install nginx, give it up, and have time to research later.

2. Profile

It was pasted directly.

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>
                <!-- 1 To turn on real-time statistics, 0 is off -->
        <!-- <property name="useSqlStat">0</property>   -->
        <!-- 1 To turn on full-time consistency detection, 0 is off -->
        <!-- <property name="useGlobleTableCheck">0</property>   -->

                <!-- <property name="sequnceHandlerType">2</property> -->
      <!--  <property name="useCompression">1</property>--> <!--1 To open 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 to type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
                <!-- <property name="processorBufferPoolType">0</property> -->
                <!--Default is 65535 64 K Be used for sql Maximum text length for 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>-->
                <!--
                        <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> -->
                <!--Distributed transaction switch, 0 does not filter distributed transactions, 1 does not filter distributed transactions (if only global tables are involved in distributed transactions), and 2 does not filter distributed transactions,But log distributed transactions-->
                <!-- <property name="handleDistributedTransactions">0</property> -->
                
                        <!--
                        off heap for merge/order/group/limit      1 Open 0 Close
                -->
                <!-- <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> -->
<property name="defaultSqlParser">druidparser</property>

        </system>
        
        <!-- Overall situation SQL Firewall Settings -->
        <!-- 
        <firewall> 
           <whitehost>
              <host host="127.0.0.1" user="mycat"/>
              <host host="127.0.0.2" user="mycat"/>
           </whitehost>
       <blacklist check="false">
       </blacklist>
        </firewall>
        -->
        
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">testdb</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>

        <!-- <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user> -->

</mycat:server>

This user root is the user mycat used to log in after it was started, and the password is your configured 123 456

schema.xml

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

        <schema name="testdb" checkSQLschema="true" 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" dataNode="dn1,dn2" rule="mod-long"/>
                <!-- random sharding using mod sharind rule -->
                <!-- table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
                           rule="mod-long" /> -->
                <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
                        needAddLimit="false"/> <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> -->
                <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
                        /> -->
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <dataNode name="dn1" dataHost="remoteHost" database="db1" />
        <dataNode name="dn2" dataHost="local-mac" database="db1" />
        <!-- <dataNode name="dn3" dataHost="localhost1" database="db3" /> -->
        <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
         <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="remoteHost" 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="localhost:3306" user="root"
                                   password="Ks992102!!!"> -->
                        <!-- can have multi read hosts -->
                        <!-- <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> -->
                <!-- </writeHost> -->
                <writeHost host="hostS1" url="Cloud Server ip:3306" user="root"
                                   password="Cloud Server Password" />
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>
        <dataHost name="local-mac" 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="localhost:3306" user="root"
                                   password="Ks992102!!!"> -->
                        <!-- can have multi read hosts -->
                        <!-- <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> -->
                <!-- </writeHost> -->
                <writeHost host="hostS1" url="192.168.31.228:3306" user="root"
                                   password="123456" />
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>
        <!--
                <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
                <heartbeat>             </heartbeat>
                 <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng"  password="jifeng"></writeHost>
                 </dataHost>

          <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0"   dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
                <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
                <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base"       password="123456" > </writeHost> </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="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
                <heartbeat> </heartbeat>
                 <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng"         password="jifeng"></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>

<schema name="testdb" checkSQL schema="true" sqlMaxLimit="100">checkSQL schema needs to be set to true, otherwise the old newspaper# find no Route

<table name="goods" primaryKey="id" dataNode="dn1,dn2" rule="mod-long"/> This is the table you want to subdivide, according to the mod-long rule, as detailed in rule.xml remoteHost is a cloud service local-mac is the local Mac

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="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>
        <tableRule name="crc32slot">
                <rule>
                        <columns>id</columns>
                        <algorithm>crc32slot</algorithm>
                </rule>
        </tableRule>
        <tableRule name="sharding-by-month">
                <rule>
                        <columns>create_time</columns>
                        <algorithm>partbymonth</algorithm>
                </rule>
        </tableRule>
        <tableRule name="latest-month-calldate">
                <rule>
                        <columns>calldate</columns>
                        <algorithm>latestMonth</algorithm>
                </rule>
        </tableRule>
        
        <tableRule name="auto-sharding-rang-mod">
                <rule>
                        <columns>id</columns>
                        <algorithm>rang-mod</algorithm>
                </rule>
        </tableRule>
        
        <tableRule name="jch">
                <rule>
                        <columns>id</columns>
                        <algorithm>jump-consistent-hash</algorithm>
                </rule>
        </tableRule>

        <function name="murmur"
                class="io.mycat.route.function.PartitionByMurmurHash">
                <property name="seed">0</property><!-- Default is 0 -->
                <property name="count">2</property><!-- The number of database nodes to be fragmented must be specified, otherwise they cannot be fragmented -->
                <property name="virtualBucketTimes">160</property><!-- An actual database node is mapped to so many virtual nodes that the default is 160 times, that is, 160 times the number of virtual nodes than the number of physical nodes. -->
                <!-- <property name="weightMapFile">weightMapFile</property> Node weight, node without specified weight defaults to 1.with properties Fill in the file format from 0 to count-1 The integer value, that is, the node index, is key´╝îValue with node weight value.All weight values must be positive integers or 1 instead -->
                <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
                        //Used to observe the distribution of each physical node and virtual node during testing. If this property is specified, the mapping of the murmur hash value of the virtual node and the physical node will be output to this file line by line, without default value, and nothing will be output if not specified-->
        </function>

        <function name="crc32slot"
                          class="io.mycat.route.function.PartitionByCRC32PreSlot">
                <property name="count">2</property><!-- The number of database nodes to be fragmented must be specified, otherwise they cannot be fragmented -->
        </function>
        <function name="hash-int"
                class="io.mycat.route.function.PartitionByFileMap">
                <property name="mapFile">partition-hash-int.txt</property>
        </function>
        <function name="rang-long"
                class="io.mycat.route.function.AutoPartitionByLong">
                <property name="mapFile">autopartition-long.txt</property>
        </function>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>

        <function name="func1" class="io.mycat.route.function.PartitionByLong">
                <property name="partitionCount">8</property>
                <property name="partitionLength">128</property>
        </function>
        <function name="latestMonth"
                class="io.mycat.route.function.LatestMonthPartion">
                <property name="splitOneDay">24</property>
        </function>
        <function name="partbymonth"
                class="io.mycat.route.function.PartitionByMonth">
                <property name="dateFormat">yyyy-MM-dd</property>
                <property name="sBeginDate">2015-01-01</property>
        </function>
        
        <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
                <property name="mapFile">partition-range-mod.txt</property>
        </function>
        
        <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
                <property name="totalBuckets">3</property>
        </function>
</mycat:rule>

Because two are configured, so

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
</function>

Medium <property name="count">2</property>2

3. Effect

4. Attentions

  • Configuration file pays attention to the label of xml, cannot write wrong, less, miss write, otherwise start error
  • Both mysql s should have remote access enabled or they will not be able to connect. You can see mycat/logs/wrapper.log in the installation directory of the log MYCAT

Topics: Programming mycat Database JDBC xml