溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關(guān)MyCat如何實(shí)現(xiàn)MySQL雙主一從讀寫分離的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。
1）配置server.xml
<user name="root" defaultAccount="true"> <property name="password">mysql</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>
配置用戶root，密碼為mysql，對TESTDB具有權(quán)限
2）配置schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema> <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" /> --> <dataNode name="dn1" dataHost="localhost1" database="test" /> <dataNode name="dn2" dataHost="localhost1" database="dedecms" /> <dataNode name="dn3" dataHost="localhost1" database="xcredit" /> <!--<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="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="master1" url="192.168.8.57:3306" user="root" password="mysql"> <!-- can have multi read hosts --> <readHost host="slave" url="192.168.8.59:3306" user="root" password="mysql" /> </writeHost> <writeHost host="master2" url="192.168.8.58:3306" user="root" password="mysql" />
schema標(biāo)簽：schema name屬性指定邏輯庫名，dataNode屬性指定下邊的dataNode
dataNode標(biāo)簽：dataHost指定下邊的dataHost，database指定具體的database
dataHost標(biāo)簽：balance指的負(fù)載均衡類型，switchType指的是切換的模式
balance指的負(fù)載均衡類型，目前的取值有4種：
1. balance="0", 不開啟讀寫分離機(jī)制，所有讀操作都發(fā)送到當(dāng)前可用的writeHost上。
2. balance="1"，全部的readHost與stand by writeHost參與select語句的負(fù)載均衡。
3. balance="2"，所有讀操作都隨機(jī)的在writeHost、readhost上分發(fā)。
4. balance="3"，所有讀請求隨機(jī)的分發(fā)到wiriterHost對應(yīng)的readhost執(zhí)行，writerHost不負(fù)擔(dān)讀壓力
switchType指的是切換的模式，目前的取值也有4種：
1. switchType='-1' 表示不自動切換
2. switchType='1' 默認(rèn)值，表示自動切換
3. switchType='2' 基于MySQL主從同步的狀態(tài)決定是否切換,心跳語句為 show slave status
4. switchType='3'基于MySQL galary cluster的切換機(jī)制（適合集群）（1.4.1），心跳語句為 show status like 'wsrep%'。
3）啟動mycat，并觀察日志
/usr/local/mycat/bin/mycat start
[root@oneproxy logs]# tail -f wrapper.log STATUS | wrapper | 2018/08/25 10:32:39 | --> Wrapper Started as Daemon STATUS | wrapper | 2018/08/25 10:32:40 | Launching a JVM... INFO | jvm 1 | 2018/08/25 10:32:40 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org INFO | jvm 1 | 2018/08/25 10:32:40 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved. INFO | jvm 1 | 2018/08/25 10:32:40 | INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN No appenders could be found for logger (io.mycat.memory.MyCatMemory). INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN Please initialize the log4j system properly. INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info. INFO | jvm 1 | 2018/08/25 10:32:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
4）在mycat服務(wù)器登錄mysql
mysql -uroot -pmysql -P8066 -h292.168.8.63
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
5）測試讀寫分離
mysql> use TESTDB; mysql> create table t4(hostname1 varchar(12),hostname2 varchar(12),hostname3 varchar(12)); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> select * from t4; +-----------+-----------+-----------+ | hostname1 | hostname2 | hostname3 | +-----------+-----------+-----------+ | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | | master1 | master1 | master1 | +-----------+-----------+-----------+ 64 rows in set (0.00 sec)
插入的數(shù)據(jù)都是從master1插入的，通過查詢mycat日志，發(fā)現(xiàn)所有的查詢?nèi)糠职l(fā)到了slave節(jié)點(diǎn)。
感謝各位的閱讀！關(guān)于“MyCat如何實(shí)現(xiàn)MySQL雙主一從讀寫分離”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
