溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關MyCat如何實現MySQL雙主一從讀寫分離的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。
1）配置server.xml
<user name="root" defaultAccount="true"> <property name="password">mysql</property> <property name="schemas">TESTDB</property> <!-- 表級 DML 權限設置 --> <!-- <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具有權限
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標簽：schema name屬性指定邏輯庫名，dataNode屬性指定下邊的dataNode
dataNode標簽：dataHost指定下邊的dataHost，database指定具體的database
dataHost標簽：balance指的負載均衡類型，switchType指的是切換的模式
balance指的負載均衡類型，目前的取值有4種：
1. balance="0", 不開啟讀寫分離機制，所有讀操作都發送到當前可用的writeHost上。
2. balance="1"，全部的readHost與stand by writeHost參與select語句的負載均衡。
3. balance="2"，所有讀操作都隨機的在writeHost、readhost上分發。
4. balance="3"，所有讀請求隨機的分發到wiriterHost對應的readhost執行，writerHost不負擔讀壓力
switchType指的是切換的模式，目前的取值也有4種：
1. switchType='-1' 表示不自動切換
2. switchType='1' 默認值，表示自動切換
3. switchType='2' 基于MySQL主從同步的狀態決定是否切換,心跳語句為 show slave status
4. switchType='3'基于MySQL galary cluster的切換機制（適合集群）（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服務器登錄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)
插入的數據都是從master1插入的，通過查詢mycat日志，發現所有的查詢全部分發到了slave節點。
感謝各位的閱讀！關于“MyCat如何實現MySQL雙主一從讀寫分離”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
