第十三課mysql8.0高可用架構(gòu)之mycat

SQL92 、 MySQLServer上(DataHost)，即datanode=database@datahost方式，因此你可以用一臺(tái)到N臺(tái)服務(wù)器來分片，分片規(guī)則為（shardingrule)典型的字符串枚舉分片規(guī)則，一個(gè)規(guī)則的定端。以select*fromOrderswhereprov=?語句為例，查到prov=wuhan，按照分片函數(shù)，yuminstall-y[root@vmware1logs]#envXDG_SESSION_ID=5HOSTNAME=vmware1[root@vmware1logs]#catlocalhostlocalhost.local 4vmware1Mycat本機(jī)預(yù)先安裝MySQL環(huán)境，但不需要啟動(dòng)MySQLtar–zxvfMycat-server-1.6-RELEASE-20161028204710-創(chuàng)建日志mkdirbin程序 下存放配置文件，server.xml是Mycat服務(wù)器參數(shù)調(diào)整和用戶 Mycat或者通過9066端口reload. 己的需要，可以調(diào)整輸出級別為debug，debug級別下，會(huì)輸出 ./mycat[root@vmware1bin]#./mycatstartStartingMycat-server...[root@vmware1bin]#ps-ef|grepmycat 1019:48? wrapper.syslog.ident=mycatwrapper.pidfile=/root/mycat/logs/mycat.pidwrapper.daemonize=TRUE 3157119:48? 00:00:04java-DMYCAT_HOME=.-server-XX:MaxPermSize=64M-XX:+AggressiveOpts-XX:MaxDirectMemorySize=2G .sun.management.jmxremote- .sun.management.jmxremote.authenticate=false-Dcom.sun.management.jmxremote.ssl=false-Xmx4G-Xms1G-Djava.library.path=lib-classpath [root@wyg003conf]#vi<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="false" <dataNodename="dn1"dataHost="localhost1"database="db1"<dataHostname="localhost1"maxCon="1000"minCon="10"balance="1" writeType="0"dbType="mysql"dbDriver="native"switchType="1"><heartbeat>select<writeHosthost="wyg001"url="31:3306"user="root"<readHosthost="wyg002"url="32:3306"user="root"password="123456"<schemaname="TESTDB"checkSQLschema="false"sqlMaxLimit="100"></schema> 用于定義MyCat實(shí)例中的邏輯庫，MyCat可以有多個(gè)邏輯庫，每個(gè)邏輯庫都有自己的相關(guān)配置?？梢允褂胹chema 如果不配置 <schemaname="TESTDB"checkSQLschema="false"sqlMaxLimit="100"><tablename="travelrecord"</table></schema>

rule="auto-sharding-<schema<table </table></schema>

checkSQLschema="false"sqlMaxLimit="100">dataNode="dn10,dn11,dn12"rule="auto-sharding-long">如上所示的配置就配置了兩個(gè)不同的邏輯庫，邏輯庫的概念和MYSQL數(shù)據(jù)庫中Database<schemaname="USERDB"checkSQLschema="false"<tablename=“tuser”那么現(xiàn)在tuser就綁定到dn1所配置的具體database上，可以直接這個(gè)database,沒有配置該屬性的值需要和dataNode中name當(dāng)該值設(shè)置為true時(shí)，如果我們執(zhí)行語句select*fromTESTDB.travelrecord;則MyCat把語句修改為select*fromtravelrecord;。即把表示schema的字符去掉，避免發(fā)送到后端數(shù)據(jù)庫執(zhí)行（ERROR1146(42S02):Table‘record’doesn’texist）。不過，即使設(shè)置該值為true，如果語句所帶的是并非是schema指定的名字，例如：select*record;那么MyCat并不會(huì)刪除db1這個(gè)字段，如果沒有定義該庫的話則會(huì)報(bào)錯(cuò)，所以在提供SQL語句的最好是不帶這個(gè)字段。當(dāng)設(shè)置為false時(shí)，則會(huì)把語句原封不動(dòng)的發(fā)往最終的MySQL當(dāng)該值設(shè)置為某個(gè)數(shù)值時(shí)。每條執(zhí)行的SQL語句，如果沒有加上limit語句，MyCat也會(huì)自動(dòng)的加上所對應(yīng)的值。例如設(shè)置值為100，執(zhí)行**select*fromTESTDB.travelrecord;**的效果為和執(zhí)行**select*fromTESTDB.travelrecordlimit100;**相同。<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="false"dataNode="dn1"></schema>

<dataNodename="dn1"dataHost="node1"database="test"/><dataHostname="node1"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"><heartbeat>select<writeHosthost="master1"user="root"</writeHost></dataHost></mycat:schema>重新啟動(dòng)Mycat mysql_native_passwordBY”方式創(chuàng)建，否則會(huì)報(bào)錯(cuò)CREATEUSER‘root’@‘192.168.194.%’IDENTIFIEDWITHmysql_native_passwordBY [root@localhost2019-04]#mysql-uroot-p-P8066-hEnterpassword:ERROR1045(HY000):Accessdeniedforuser'root',becausepasswordis[root@localhost2019-04]#mysql-uroot-p-P8066-h--default-EnteretotheMySQL Commandsendwith;orYourMySQLconnectionidisServerversion:5.6.29-mycat--release-20190213150257MyCatServerCopyright(c)2000,2018,Oracleand/oritsaffiliates.All OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.OthernamesmaybetrademarksoftheirrespectiveType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinput[mysql@vmware1bin]$./mysql-uroot-p-P8066-h--mysql>show |DATABASE |TESTDB mysql>useTESTDB;mysql> |Tables_in_test

| | |employees_by_lname| | | | | | | | | | | mysql>select*fromtemp2;+---- |id|name+---- ||b|||||b|||c|||d|mysql>insertintotemp2mysql>updatetemp2setname='bcd'whereid=4;mysql>deletefromtemp2whereid=3;mysql>createtabletemp3(idint,namemysql>select*fromERROR1146(42S02):Table'TESTDB.temp2'doesn'tmysql>select*+---- |id|name+---- |1|a

mysql>select*+---- |id|name+---- |1|amysql>select*+---- |id|name+---- |1|a

<?xml<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="false"sqlMaxLimit="100"dataNode="dn1"><tablename="temp2" dataNode="dn1,dn2"/></schema><dataNodename="dn1"dataHost="node1"database="test"/><dataNodename="dn2"dataHost="node1"database="test2"/><dataHostname="node1"maxCon="10"minCon="5"balance="1"dbDriver="native"switchType="1"tempReadHostAvailable="1"><heartbeat>select

writeType="0"<writeHosthost="master1"url="28:3308"user="root"</writeHost></dataHost>100rowsinset(0.01<tablename="travelrecord"dataNode="dn1,dn2,dn3"rule="auto-sharding-long"</table> 定義了MyCat中的邏輯表，所有需要拆分的表都需要在這 標(biāo)簽中name屬性屬性值一一對應(yīng)。該屬性定義了邏輯表的類型，目前邏輯表只有“全局表”和”普通表”兩種類型。對應(yīng)的配置：dataNodedataNode 定義了MyCat中的數(shù)據(jù)節(jié)點(diǎn)，用于綁定邏輯庫到某個(gè)具體的database<dataNodename="dn1"dataHost="lch3307"database="db1"></dataNode>dataNode 定義了MyCat中的數(shù)據(jù)節(jié)點(diǎn)，也就是我們通常說所的數(shù)據(jù)分片。一個(gè)dataNode 建立表與分片對應(yīng)的關(guān)系。dataHost database<mycat:schema<?xml<!DOCTYPEmycat:schemaSYSTEM<schemaname="TESTDB"dataNode="dn1"></schema>

<schemaname="TESTDB2"checkSQLschema="true"dataNode="dn2"></schema><dataNodename="dn1"dataHost="node1"database="test"/><dataNodename="dn2"dataHost="node1"database="test2"/><dataHostname="node1"maxCon="1000"minCon="10"writeType="0"dbType="mysql"dbDriver="native"<heartbeat>select<writeHosthost="master1"url="28:3308"user="root"password="mysql"></writeHost></dataHost></mycat:schema>[mysql@vmware1bin]$./mysql-uroot-p-P8066-mysql>show |DATABASE |TESTDB|TESTDB2 mysql>useReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables; | <?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="true"</schema>

dataNode="dn1"><schemaname="TESTDB2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><dataNodename="dn1"<dataNodename="dn2"

database="test"/>database="test2"/><dataHostname="node1"maxCon="10"minCon="5"balance="1" dbType="mysql"dbDriver="native"switchType="1"><heartbeat>selectuser()</heartbeat><writeHosthost="master1"url="28:3308" password="mysql"><readHosthost="slave1"url="30:3308"password="mysql"/></writeHost></dataHost></mycat:schema> mysql>showfull+-----+------+-----------------------+-------+---------+------ ---------------------+| |User| | |CommandTime| | |+r--2370--st--ee+--- |-8-|-ro--2.37.132:40776|test2|Sl

| |319|

|32:40777|

| |l320|

|32:40778

26

261|NULL |321|root|32:40779|test2|Query 0|starting|showfullprocesslist|+-----+------+-----------------------+-------+---------+------ ---------------------+ 內(nèi)指明用于和后端數(shù)據(jù)庫進(jìn)行心跳檢查的語句。例如,MYSQL可以使用select，Oracle可以使用select1fromdual 、readHost 用于標(biāo)識(shí)不同實(shí)例，一般writeHost我們使用*M1，readHost我們用*S1url user password weight屬性權(quán)重配置在readhostbalance="1"，全部的readHost與standbywriteHost參與select語句的負(fù)載均衡，簡單的說，當(dāng)雙主雙從模式(M1->S1，M2->S2，并且M1M2M2,S1,S2都參與select語句的負(fù)載均衡。MySQL：mysql>select*from+---- |id|name+---- |1|a|2|b|4||5|+---- 查看MySQLmysql>select*from+---- |id|name+---- |1|a|2|b|3|c|4|d+---- mysql>select* |id| |1| |2| |4|bcd|5|abc mysql>select* |id| |1| |2| |3| |4| mysql>select*from |id| |1| |2| |4|bcd|5|abc 4rowsinset(0.09mysql>select*from |id| |1| |2| |3|

|4|

writeType="0",所有寫操作發(fā)送到配置的第一個(gè)writeHostwriteHost，重新啟動(dòng)后已切換后的為準(zhǔn)，切換記錄在配置文件中:pertieswriteType=“1隨機(jī)的發(fā)送到配置的writeHost，但<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="true"</schema>

dataNode="dn1"><schemaname="TESTDB2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><dataNodename="dn1"dataHost="node1"database="test"/><dataNodename="dn2"dataHost="node1"database="test2"/><dataHostname="node1"maxCon="10"minCon="5"balance="2"dbDriver="native"switchType="1"><heartbeat>select

writeType="0"<writeHosthost="master1"url="28:3308"user="root"</writeHost><writeHosthost="salve1"url="30:3308"user="root"</writeHost></dataHost>mysql>insertintotemp2values(10,'a');mysql>insertintotemp2values(11,'a');mysql>insertintotemp2values(12,'a');mysql>select*from+---- |id|name+---- 1| 2| |10| 4|bcd 5|abc|11||12|+---- 當(dāng)master1關(guān)閉時(shí)，再執(zhí)行插入操作：[root@vmware1bin]#/etc/init.d/mysql.serverstopShuttingdownMySQL. mysql>insertinto*ql>+---- |id|name++ | 2|| 3|| 4|||13|||14|

當(dāng)writetype=1mysql>insertintotemp2QueryOK,1rowaffected(0.06mysql>insertintotemp2values(31,'a');QueryOK,1rowaffected(0.00sec)mysql>insertintotemp2mysql>select*fromtemp2where+---- |id|name+---- |30|a||31|

idmysql>select*fromtemp2whereidin+---- |id|name+---- |32|

英switchType1表示不自動(dòng)切換基于MySQL主從同步的狀態(tài)決定是否切換showslave<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn1"></schema><schemaname="TESTDB2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><dataNodename="dn1"<dataNodename="dn2"

database="test"/>database="test2"/><dataHostname="node1"maxCon="10"minCon="5"balance="2" dbType="mysql"dbDriver="native"switchType="-1"><heartbeat>selectuser()</heartbeat><writeHosthost="master1"url="28:3308"user="root"password="mysql"></writeHost><writeHosthost="salve1"url="30:3308"</writeHost></dataHost></mycat:schema>

password="mysql">switchType=-1時(shí)mysql>insertintotemp2values(61,'a');mysql>insertintotemp2values(62,'a');mysql>insertintotemp2values(63,'a');ERROR1184(HY000):Connectionrefusedmysql>insertintotemp2values(64,'a');ERROR1184(HY000):Connectionrefused當(dāng)switchType=1<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="true"</schema><dataNodename="dn1"dataHost="node1"database="test"/><dataHostname="node1"maxCon="10"minCon="5"balance="1"dbDriver="native"switchType="1"><heartbeat>select

dataNode="dn1">writeType="0"<writeHosthost="master1"url="28:3308"user="root"<readHosthost="salve1"url="30:3308"user="root"password="mysql"/></writeHost></dataHost>mysql>insertintotemp2values(3,'c');ERROR1184(HY000):Connectionrefusedmysql>select*fromtemp2;ERROR1184(HY000):Connection如果配置了這個(gè)屬性writeHostreadHost仍舊可用，默認(rèn)0,可配置（0、<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="true"dataNode="dn1"></schema>

<dataNodename="dn1"dataHost="node1"database="test"/><dataHostname="node1"maxCon="10"minCon="5"balance="1" dbType="mysql"dbDriver="native"switchType="1"tempReadHostAvailable="1"><heartbeat>select<writeHosthost="master1"user="root"<readHosthost="salve1"user="root"password="mysql"/></writeHost></dataHost></mycat:schema>mysql>select*+---- |id|name+---- |2|b+----

1rowinset(0.00mysql>insertintotemp2values(3,'c');ERROR1184(HY000):Connection <username="test"><propertyname="password">test</property><property<propertyname="readOnly">true</property><property ark">11111</property><propertyname="usingDecrypt">1</property></user> 其他ERROR1044(HY000):Accessdeniedforuser'test'todatabase 修改 修改readOnly為true或false 多個(gè)schema的話使用逗號(hào)隔例如:<property<username="mycat"><property<propertyname="schemas">order</property><propertyname="readOnly">true</property></user><user<property<propertyname="schemas">order</property></user>namemycat中password Mysql初始化mysql前后端連接所涉及到的一些屬packetHeaderSizeMysql協(xié)議中的報(bào)文頭長度。默認(rèn)4。maxPacketSizeMysql協(xié)議可以攜帶的數(shù)據(jù)最大長度。默認(rèn)16MidleTimeout過了空閑時(shí)間，那么這個(gè)連接會(huì)被回收，就是被直接的關(guān)閉掉。默認(rèn)30分鐘，單位毫秒。charset:連接的初始化字符集。默認(rèn)為utf8。txIsolation:前端連接的初始化事 MITTED=MITTED=REPEATED_READ=SERIALIZABLE=若超過這個(gè)時(shí)間則會(huì)直接關(guān)閉這連接。默認(rèn)時(shí)間為300秒，單位秒。bindIp:mycat服務(wù) serverPort:定義mycat的使用端口，默認(rèn)值為8066managerPort:定義mycat的管理端口，默認(rèn)值為9066。 <tableRulename="rule1"><rule><columns>id</columns><algorithm>hash-int</algorithm></rule></tableRule>name columns內(nèi)指定要拆分的列名字。algorithm使用function 則和具體路由算法。當(dāng)然，多個(gè)表規(guī)則可以連接到同一個(gè)路由算法上。table <functionname="hash-int"><propertyname="mapFile">partition-hash-</function>nameclasspropertyMycat表適合于Mycat全局表，無須對數(shù)據(jù)進(jìn)行切分，只要在所有的分片上保存一份數(shù)據(jù)即可，Mycat在Join操作中，業(yè)務(wù)表與全局表進(jìn)行Join聚合會(huì)優(yōu)先選擇相同分片內(nèi)的全局表join， <tablename="t_area"primaryKey="id"type="global"dataNode="dn1,dn2"/><mycat:schema <schemaname="TESTDB"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn1"><tablename="temp2"primaryKey="id"type="global"dataNode="dn1,dn2"/></schema><schemaname="TESTDB2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><dataNode<dataNode

dataHost="node1"database="test"/>dataHost="node1"database="test2"/><dataHostname="node1"maxCon="10"minCon="5"balance="2" dbType="mysql"dbDriver="native"switchType="-1"><heartbeat>select<writeHosthost="master1"url="28:3308"user="root"password="mysql"></writeHost><writeHosthost="salve1"url="30:3308"user="root"password="mysql"></writeHost></dataHost><?xml<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM

ms 表的主從關(guān)系，這類似業(yè)務(wù)的切分可以抽象出合適的切分規(guī)則，比如根據(jù)用戶ID切分,其他相關(guān)的表都依賴于用戶ID，再或者根據(jù)訂單ID切分，總之部分業(yè)務(wù)總會(huì)可以抽象ER分片表，子表的記錄與所關(guān)聯(lián)的父表記錄存放在同一個(gè)數(shù)據(jù)分片上，避免數(shù)據(jù)Join跨庫操作。<tablename=“orders”dataNode=“dn1,dn2"rule="mod-long"><childTablename="order_detail"primaryKey="id"joinKey="order_id"parentKey="order_id"/></table> 相同order_id的數(shù)據(jù)分到同一個(gè)分片上，在進(jìn)行數(shù)據(jù)插入操作時(shí)，Mycat會(huì)獲取order<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn1"><tablename="orders"dataNode="dn1,dn2"rule="mod-<childTablename="order_detail"primaryKey="id"joinKey="order_id"parentKey="id"/></table></schema><schemaname="TESTDB2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><dataNodename="dn1"dataHost="node1"database="test"/><dataNodename="dn2"dataHost="node2"database="test"/><dataHostname="node1"maxCon="10"minCon="5"balance="2" writeType="0"dbType="mysql"dbDriver="native"switchType="-1"><heartbeat>select<writeHosthost="master1"url="28:3308"user="root"</writeHost></dataHost><dataHostname="node2"maxCon="10"minCon="5"balance="2" writeType="0"dbType="mysql"dbDriver="native"switchType="-1"><heartbeat>select<writeHosthost="salve1"url="30:3308"user="root"</writeHost></dataHost>mysql>createtableorders(idintprimarykey,namemysql>createtableorder_detail(idintprimarykey,nameint,foreignkey(order_id)referencesMycatmysql>insertintoordersERROR1064(HY000):partitiontable,insertmustprovideColumnListmysql>insertintoorders(id,name)values(1,’a’),(2,’b’),(3,’c’),(4,’c’),(5,’c’);mysql>insertintovalues(1,’a’,1),(2,’b’,2),(3,’c’,3),(4,’c’,4),(5,’c’,5);：mysql>select*from |id|name |2| |4| 2rowsinset(0.00mysql>select*from |id|name|order_id |2| |4| mysql>select*from |id|name |1| |3| |5| 3rowsinset(0.00mysql>select*from |id|name|order_id |3| |5| 句的第一次執(zhí)行結(jié)果進(jìn)行分析，確定該Table的某個(gè)主鍵在什么分片上，并進(jìn)行ID的緩存。第二次或后續(xù)查詢mycat會(huì)優(yōu)先從緩存中查詢是否有id–>node即主鍵到分片的 些<tableRulename="sharding-by-<rule><columns>user_id</columns><algorithm>hash-int</algorithm></rule></tableRule><functionname="hash-int"><propertyname="mapFile">partition-hash-<propertyname="type">0</property><propertyname="defaultNode">0</property></function>partition-hash-int.txt配 10000=010010=1DEFAULT_NODE=1columns，algorithm，mapFile此分片適用于，提前規(guī)劃好分片字段某個(gè)范圍start<=range<=end.rangestart-end,datanodeindexK=1000,M=10000.<tableRulename="auto-sharding-<rule><columns>user_id</columns><algorithm>rang-long</algorithm></tableRule><functionname="rang-

</rule><propertyname="mapFile">autopartition-<propertyname="defaultNode">0</property></function>上面columns，algorithmrang-long函數(shù)中mapFile代表配置文件路徑defaultNode超過范圍后的默認(rèn)節(jié)點(diǎn)。<?xml<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn1"><table</table></schema>

dataNode="dn1,dn2"rule="auto-sharding-<schemaname="TESTDB2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><dataNodename="dn1"dataHost="node1"database="test"/><dataNodename="dn2"dataHost="node2"database="test"/><dataHostname="node1"maxCon="10"minCon="5"balance="2"dbDriver="native"switchType="-1"><heartbeat>select

writeType="0"<writeHosthost="master1"url="28:3308"user="root"</writeHost></dataHost><dataHostname="node2"maxCon="10"minCon="5"balance="2"dbDriver="native"switchType="-1"><heartbeat>select

writeType="0"<writeHosthost="salve1"url="30:3308"user="root"</writeHost></dataHost><?xml<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema <schemaname="TESTDB"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn1"><table</table></schema>

dataNode="dn1,dn2"rule="auto-sharding-<schemaname="TESTDB2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><dataNodename="dn1"dataHost="node1"database="test"/><dataNodename="dn2"dataHost="node2"database="test"/><dataHostname="node1"maxCon="10"minCon="5"balance="2"dbDriver="native"switchType="-1"><heartbeat>select

writeType="0"<writeHosthost="master1"url="28:3308"user="root"</writeHost></dataHost><dataHostname="node2"maxCon="10"minCon="5"balance="2"dbDriver="native"switchType="-1"><heartbeat>select

writeType="0"<writeHosthost="salve1"url="30:3308"user="root"</writeHost></dataHost>[root@vmware1conf]#viautopartition-long.txt#rangestart-end,datanodeindex#K=1000,M=10000.0-1000=01000-2000=1在Mycatmysql>insertintoorders(id,name)values(10,'a');QueryOK,1rowaffected(0.02sec)mysql>insertintoorders(id,name)QueryOK,1rowaffected(0.00mysql>insertintoorders(id,name) QueryOK,1rowaffected(0.01sec)mysql>insertintoorders(id,name) QueryOK,1rowaffected(0.01sec)mysql>insertintoorders(id,name) QueryOK,1rowaffected(0.00sec)mysql>insertintoorders(id,name)values(1005'a');

mysql>select*from+------- |id|name+------- 2|b 4|c|10|a|100|a|1000|a|10000|a+------- ：mysql>select*from+------ |id|name|1|a||3|c|||1|a||3|c||5|c||1001||1005|

<tableRulename="mod-long"><rule><columns>user_id</columns><algorithm>mod-long</algorithm></rule></tableRule><functionname="mod-long"--<propertyname="count">3</property></function>columns，algorithm明確即根據(jù)id進(jìn)行十進(jìn)制求模預(yù)算

<!--howmanydatabetween<tableRulename="sharding-by-<rule><algorithm>sharding-by-month</algorithm></rule></tableRule><functionname="sharding-by-month"<propertyname="dateFormat">yyyy-MM-dd</property><propertyname="sBeginDate">2014-01-01</property></function>columns：dateFormat式sBeginDate：開始日期原則一：能不分就不分，1000萬以內(nèi)的表，不建議分片，通過合適的索引，讀寫分離等片，一致性Hash分片，這幾種分片都有利于擴(kuò)容 原則五：查詢條件盡量優(yōu)化，盡量避免Select*量帶寬和CPU資源，查詢盡量避免返回大量結(jié)果集，并且盡量為頻繁使用的查詢語句建立索引。詢SQL，會(huì)便利所有的分片，性能相對，因此這種SQL越多，對系統(tǒng)的影響越大，所以通過debug04-2922:06:10.420DEBUG[$_NIOREACTOR-3-RW](NonBlockingSession.java:118)ServerConnection[id=1,schema=mycat,host=, mit=true,schema=mycat]select*fromt_usert,route={1->dn1{SELECT*FROMt_usertLIMIT100}2->dn2{SELECT*FROMt_usertLIMIT100}}rrs04-2922:06:10.420DEBUG[$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:78) executemutinodequeryselect*fromt該部分描述了該條sql被分配到到了分片dn1、dn2<asyncRootlevel="debug"重啟Mycatmysql>select*fromINFO|jvm1|2017/06/1521:13:12|2017-06-1521:13:12,621[DEBUG][$_NIOREACTOR-0-ServerConnection[id=1,schema=TESTDB,host=,user=root,txIsolation=3,mit=true,schema=TESTDB]select*fromtemp(.FrontendConnection:FrontendConnection.java:288)INFO|jvm1|2017/06/1521:13:12|2017-06-1521:13:12,621[DEBUG][$_NIOREACTOR-0-RW]ServerConnection[id=1,schema=TESTDB,host=,user=root,txIsolation=3,schema=TESTDB]select*from(io.mycat.server.ServerQueryHandler:ServerQueryHandler.java:57)INFO|jvm1|2017/06/1521:13:12|2017-06-1521:13:12,622[DEBUG][$_NIOREACTOR-0-RW] misscache,key:TESTDBselect*from |jvm1|2017/06/1521:13:12|2017-06-1521:13:12,622[DEBUG][$_NIOREACTOR-0-ServerConnection[id=1,schema=TESTDB,host=,user=root,txIsolation=3, schema=TESTDB]select*fromtemp,route={ |jvm1|2017/06/1521:13:12 1->dn1{select*from數(shù)據(jù)庫讀寫分離對于大型系統(tǒng)或者量很高的互聯(lián)網(wǎng)應(yīng)用來說，是必不可少的一個(gè)重要功能。從數(shù)據(jù)庫的角度來說，對于大多數(shù)應(yīng)用來說，從集中到分布，最基本的一個(gè)需求不是數(shù)據(jù)的瓶頸，而是在于計(jì)算的瓶頸，即SQL查詢的瓶頸，我們知道，正常情況下，SQL就是幾十個(gè)毫秒的時(shí)間內(nèi)寫入完成，而系統(tǒng)中的大多數(shù)SelectSQL則要幾秒到幾分鐘才 對于MySQL來說，標(biāo)準(zhǔn)的讀寫分離是主從模式，一個(gè)寫節(jié)點(diǎn)Master后面跟著多個(gè)讀節(jié)點(diǎn)，讀節(jié)點(diǎn)的數(shù)量取決于系統(tǒng)的壓力，通常是1-3個(gè)讀節(jié)點(diǎn)的配置。MySQL支持 MySQLwriteNode，從節(jié)點(diǎn)配置為readNode，同時(shí)Mycat內(nèi)部定期對一個(gè)dataHost里的所有正常情況下，Mycat會(huì)將第一個(gè)writeHost作為寫節(jié)點(diǎn)，所有的DMLSQL則Mycat會(huì)在默認(rèn)的3次心跳檢查失敗后，自動(dòng)切換到下一個(gè)可用的writeHost執(zhí)行DML<dataHostname="localhost1"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"><heartbeat>select<!--canhavemultiwritehosts--<writeHosthost="hostM1"url="localhost:3306"user="root"password="123456"><!--canhavemultireadhosts--<readHosthost="hostS1"weight="1"/></writeHost></dataHost>

url="localhost2:3306"user="root"<dataHostname="localhost1"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"><heartbeat>select<!--canhavemultiwritehosts--<writeHosthost="hostM1"url="localhost:3306"user="root"password="123456"></writeHost><writeHost</writeHost></dataHost>

url="localhost:3307"user="root"password="123456">當(dāng)你是1主3從的模式的時(shí)候，可以把第一個(gè)從節(jié)點(diǎn)配置為writeHost2，第2個(gè)和第三個(gè)從節(jié)點(diǎn)則配置為writeHost1的readHost，如下所示：<dataHostname="localhost1"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"><heartbeat>select<writeHosthost="hostM1"url="localhost:3306"user="root"password="123456"<readHost/><readHost/></writeHost>

user="root" user="root"<writeHosthost="hostS1"url="localhost2:3306"user="root"/></dataHost>/*!mycat:db_type=slave*/select*from/*#mycat:db_type=slave*/select*fromtravelrecord強(qiáng)制走寫：/*#mycat:db_type=master*/select*from/*!mycat:db_type=master*/select*frommysql>select*from+---- |id|name+---- |1|a+---- 1rowinset(0.00mysql>select*from+---- |id|name+---- |2|bmysql>/*!mycat:db_type=slave*/select*from+---- |id|name+---- |2|b+---- 1rowinset(0.00mysql>/*!mycat:db_type=slave*/select*from+---- |id|name |2|b 根據(jù)主從延時(shí)切換：讓1.4開始支持MySQL主從 MyCAT心跳檢查語句配置為showslavestatus，dataHost上定義兩個(gè)新屬性：switchType="2"與slaveThreshold="100"，此時(shí)意味著開啟MySQL主從 讀寫分離與切換機(jī)制，Mycat心跳機(jī)制通過檢測showslavestatus中的"Seconds_Behind_Master","Slave_IO_Running","Slave_SQL_Running"三個(gè)字段來確定 時(shí)延，當(dāng)<dataHostname="localhost1"maxCon="1000"minCon="10"writeType="0"dbType="mysql"dbDriver="native"slaveThreshold="100"><heartbeat>showslavestatus</heartbeat><!--canhavemultiwritehosts--<writeHosthost="hostM1"password="123456"></writeHost><writeHosthost="hostS1"</dataHost>password="123456"/> ，HA相比LVS的使用 MyCAT自身有類似其他數(shù)據(jù)庫的管 方式，可以通過Mysql命令行，登錄管理端（9066）執(zhí)行相應(yīng)的SQL登錄：目前mycat有兩個(gè)端口，8066數(shù)據(jù)端口，90669066mysqlmysql-h-utest-ptest-P9066[--hmycat按照的主機(jī)地址，本地可用-uMycatserver.xml-pMycatserver.xml-P后面是端口默認(rèn)9066，注意P-dMycatserver.xml

通過show@@help;mysql>show@@help;+--------------------------------------+----------------------------------+STATEMENT|DESCRIPTION+--------------------------------------+----------------- |||||clear@@slowwheredatanode=?|Clearslowsqlbyclear@@slowwhereschema=?|Clearslowsqlbyschema|kill@@connectionid1,id2,...|Killthespecifiedconnectionsoffline|ChangeMyCatstatustoOFF|online|ChangeMyCatstatustoON||||reload@@config|Reloadallconfigfromfile|reload@@route|Reloadrout