mycat實(shí)現(xiàn)分庫分表

1.下載mycat的tar包

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

2.解壓

mv Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz /usr/local/mycat.tar.gz

tar -zxvf mycat.tar.gz

3.進(jìn)入mycat/conf目錄

3.1修改邏輯庫的配置文schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <table name="dm_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-rang-mod-order" />
                <table name="dm_order_link_user" dataNode="dn1,dn2,dn3" rule="auto-sharding-rang-mod-order-link" />
        </schema>

        <dataNode name="dn1" dataHost="host" database="dm_order1" />
        <dataNode name="dn2" dataHost="host" database="dm_order2" />
        <dataNode name="dn3" dataHost="host" database="dm_order3" />

        <dataHost name="host" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select 1</heartbeat>
                <writeHost host="hostM1" url="39.105.206.175:3306" user="root" password="root">
                </writeHost>
        </dataHost>

</mycat:schema>

3.2修改分表規(guī)則配置文件：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>
       <tableRule name="auto-sharding-rang-mod-order">
        <rule>
                <columns>id</columns>
                <algorithm>rang-mod-dm</algorithm>
        </rule>
        </tableRule>
        <tableRule name="auto-sharding-rang-mod-order-link">
        <rule>
                <columns>orderId</columns>
                <algorithm>rang-mod-dm</algorithm>
        </rule>
        </tableRule>
                <function name="rang-mod-dm" class="io.mycat.route.function.PartitionByMod">
                  <!-- how many data nodes -->
                  <property name="count">3</property>
        </function>

        <function name="murmur"
                class="io.mycat.route.function.PartitionByMurmurHash">
                <property name="seed">0</property><!-- 默認(rèn)是0 -->
                <property name="count">2</property><!-- 要分片的數(shù)據(jù)庫節(jié)點(diǎn)數(shù)量嚎研，必須指定功舀，否則沒法分片 -->
                <property name="virtualBucketTimes">160</property><!-- 一個實(shí)際的數(shù)據(jù)庫節(jié)點(diǎn)被映射為這么多虛擬節(jié)點(diǎn)，默認(rèn)是160倍，也就
是虛擬節(jié)點(diǎn)數(shù)是物理節(jié)點(diǎn)數(shù)的160倍 -->
                <!-- <property name="weightMapFile">weightMapFile</property> 節(jié)點(diǎn)的權(quán)重闰靴，沒有指定權(quán)重的節(jié)點(diǎn)默認(rèn)是1。以properties文件
的格式填寫蚌本，以從0開始到count-1的整數(shù)值也就是節(jié)點(diǎn)索引為key加矛，以節(jié)點(diǎn)權(quán)重值為值。所有權(quán)重值必須是正整數(shù)儡羔，否則以1代替 -->
                <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
                        用于測試時觀察各物理節(jié)點(diǎn)與虛擬節(jié)點(diǎn)的分布情況宣羊，如果指定了這個屬性，會把虛擬節(jié)點(diǎn)的murmur hash值與物理節(jié)點(diǎn)的映>射按行輸出到這個文件汰蜘，沒有默認(rèn)值仇冯，如果不指定，就不會輸出任何東西 -->
        </function>

        <function name="crc32slot"
                          class="io.mycat.route.function.PartitionByCRC32PreSlot">
                <property name="count">2</property><!-- 要分片的數(shù)據(jù)庫節(jié)點(diǎn)數(shù)量族操，必須指定苛坚，否則沒法分片 -->
        </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">3</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>

3.3 修改用戶的配置文件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為開啟實(shí)時統(tǒng)計、0為關(guān)閉 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1為開啟全加班一致性檢測色难、0為關(guān)閉 -->

                <property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1為開啟mysql壓縮協(xié)議-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--設(shè)置模擬的MySQL版本號-->
        <!-- <property name="processorBufferChunk">40960</property> -->
        <!--
        <property name="processors">1</property>
        <property name="processorExecutor">32</property>
         -->
                <!--默認(rèn)為type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
                <property name="processorBufferPoolType">0</property>
                <!--默認(rèn)是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="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> -->
                <!--分布式事務(wù)開關(guān)泼舱，0為不過濾分布式事務(wù)，1為過濾分布式事務(wù)（如果分布式事務(wù)內(nèi)只涉及全局表枷莉，則不過濾）娇昙，2為不過濾分布>式事務(wù),但是記錄分布式事務(wù)日志-->
                <property name="handleDistributedTransactions">0</property>

                        <!--
                        off heap for merge/order/group/limit      1開啟   0關(guān)閉
                -->
                <property name="useOffHeapForMerge">1</property>

                <!--
                        單位為m
                -->
                <property name="memoryPageSize">1m</property>

                <!--
                        單位為k
                -->
                <property name="spillsFileBufferSize">1k</property>

                <property name="useStreamOutput">0</property>

                <!--
                        單位為m
                -->
                <property name="systemReserveMemorySize">384m</property>


                <!--是否采用zookeeper協(xié)調(diào)切換  -->
                <property name="useZKSwitch">true</property>


        </system>

        <!-- 全局SQL防火墻設(shè)置 -->
        <!--
        <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>

                <!-- 表級 DML 權(quán)限設(shè)置 -->
                <!--
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>

</mycat:server

4.進(jìn)入mycat/bin目錄

啟動命令：./mycat start 或者 ./mycat console
停止命令：./mycat stop
重啟命令：./mycat restart
查看狀態(tài)命令：./mycat status
啟動mycat 如果報錯，輸出以下錯誤信息：
$\color{red}{wrapper | Launching a JVM...}$
$\color{red}{wrapper | JVM exited while loading the application.}$
$\color{red}{jvm 1 | Invalid maximum heap size: -Xmx4G}$
$\color{red}{jvm 1 | The specified size exceeds the maximum representable size.}$
$\color{red}{jvm 1 | Error: Could not create the Java Virtual Machine.}$
$\color{red}{jvm 1 | Error: A fatal exception has occurred. Program will exit.}$
$\color{red}{wrapper | Launching a JVM...}$
$\color{red}{wrapper | JVM exited while loading the application. }$

$\color{green}{錯誤原因：mycat默認(rèn)需要的內(nèi)存要求大于機(jī)器的實(shí)際內(nèi)存笤妙，需要修改mycat的配置文件冒掌。}$
解決方法：找到.../mycat/conf/wrapper.conf，修改

wrapper.java.additional.10=-Xmx4G (大約在36行)

wrapper.java.additional.11=-Xms1G

改成：

wrapper.java.additional.10=-Xmx1G

wrapper.java.additional.11=-Xms256M

修改完成后蹲盘，重新執(zhí)行./mycat console

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末股毫，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子召衔，更是在濱河造成了極大的恐慌铃诬，老刑警劉巖，帶你破解...
沈念sama閱讀 211,348評論 6贊 491
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異氧急，居然都是意外死亡颗胡，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,122評論 2贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門吩坝，熙熙樓的掌柜王于貴愁眉苦臉地迎上來毒姨，“玉大人，你說我怎么就攤上這事钉寝』∧牛” “怎么了？”我有些...
開封第一講書人閱讀 156,936評論 0贊 347
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵嵌纲，是天一觀的道長俘枫。經(jīng)常有香客問我，道長逮走，這世上最難降的妖魔是什么鸠蚪？我笑而不...
開封第一講書人閱讀 56,427評論 1贊 283
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮师溅，結(jié)果婚禮上茅信，老公的妹妹穿的比我還像新娘。我一直安慰自己墓臭，他們只是感情好蘸鲸，可當(dāng)我...
茶點(diǎn)故事閱讀 65,467評論 6贊 385
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著窿锉，像睡著了一般酌摇。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上嗡载，一...
開封第一講書人閱讀 49,785評論 1贊 290
城市分裂傳說
那天窑多，我揣著相機(jī)與錄音，去河邊找鬼鼻疮。笑死怯伊，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的判沟。我是一名探鬼主播，決...
沈念sama閱讀 38,931評論 3贊 406
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼崭篡，長吁一口氣：“原來是場噩夢啊……” “哼挪哄！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起琉闪，我...
開封第一講書人閱讀 37,696評論 0贊 266
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤迹炼，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體斯入，經(jīng)...
沈念sama閱讀 44,141評論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡砂碉，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,483評論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了刻两。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片增蹭。...
茶點(diǎn)故事閱讀 38,625評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖磅摹，靈堂內(nèi)的尸體忽然破棺而出滋迈，到底是詐尸還是另有隱情，我是刑警寧澤户誓，帶...
沈念sama閱讀 34,291評論 4贊 329
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布饼灿，位于F島的核電站，受9級特大地震影響帝美，放射性物質(zhì)發(fā)生泄漏碍彭。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,892評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一悼潭、第九天我趴在偏房一處隱蔽的房頂上張望庇忌。院中可真熱鬧，春花似錦女责、人聲如沸漆枚。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,741評論 0贊 21
一樁弒父案抵知，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽墙基。三九已至，卻和暖如春刷喜，著一層夾襖步出監(jiān)牢的瞬間残制，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 31,977評論 1贊 265
情欲美人皮
我被黑心中介騙來泰國打工掖疮，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留初茶，地道東北人。一個月前我還...
沈念sama閱讀 46,324評論 2贊 360
代替公主和親
正文我出身青樓浊闪，卻偏偏與公主長得像恼布，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子搁宾，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,492評論 2贊 348