追蹤
NEIL's_學而時習之x不學無術~私藏IT技術經驗分享部落
關於部落格
NEIL. Cheng....寫作只是為了日後工作之備忘操作參考

本部落格文章撰寫前進邁向第五年(since 2009 ~至今)仍繼續推進分享IT學習!!About Windows/Linux Server,Virtualization


分享網誌: 由於,本人預先完成LAB後,才補上撰寫文章與截圖,若發現文圖對照說明有誤..本人將盡快校正,也請不吝指教! Thanks!

  • 163897

    累積人氣

  • 98

    今日人氣

    0

    追蹤人氣

Amoeba For MySQL 實現資料庫負載平衡(讀寫分離)by NEAR

 (A) 建立 MySQL Replication 架構: 

       MySQL ver: 5.6
 
      Master: 192.168.0.188   <---- Replication----->   Slave : 192.168.0.189

       #資料庫複寫建置過程略過說明,前幾篇文章已敘述多次(本LAB已預先建置好)

[驗證複寫設置是否成功]:  我們登入MySQL slave 主機下查看相關訊息

> show slave statusG;       #確認下圖標示數值為YES,確定複寫設計成功


[THEN 我們登入Master查看目前資料庫配置狀態]

本Lab 預計測試Amoeba 資料庫為Demo我們查看其目前資料表內容如下

 下圖中在資料表"UserData"中,可以看到目前存在三筆資料



[建立資料庫使用者連線帳號並授權(前端Amoeba應用程式)存取後端實體資料庫 ]

本Lab配置"amoeba" 使用者帳號,可以遠端連線存取Demo 資料庫如下

 [Master: 192.168.0.188]

> grant  all on Demo.* to amoeba@'192.168.0.%' identified by 'qyun520';

> flsuh privileges;


(B.) 配置JAVA運行環境與Amoeba組態設定

本例已預先將JAVA 配置完成,查看本人配置如下

vi   /etc/profile        #由於本人日前曾配置過Tomcat,故有Tomcat相關參數

###JAVA########
export JAVA_HOME=/usr/java/jdk
exportPATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH:
/usr/local/apache/bin:/usr/local/tomcat/bin
export CLASSPATH=$JAVA_HOME/lib

編輯完成立即生效

> source /etc/profile



[配置及安裝_Amoeba程式組態]

amoeba-mysql-binary-2.1.0-RC5.tar.gz   #免編譯,免安裝獨立程式

#建立資料夾存放amoeba程式

> mkdir -p  /usr/local/amoeba

> cd /usr/local/amoeba

> mv ~/ amoeba-mysql-binary-2.1.0-RC5.targz  /usr/local/amoeba

> tar zxvf  amoeba-mysql-binary-2.1.0-RC5

> chmod -R +x /usr/local/amoeba/bin/


#amoeba 主要組態檔位於 conf 資料夾下,本例實現讀寫分離,將使用到

  dbServer.xml 與amoeba.xml 兩個檔案組態,我的配置範例如下

#本人將此二檔案分享於本部落格右側 SKY Drive(網路硬碟) 之Linux 資料夾/amoeba

[dbServer.xml]   標示粉紅字體為本人修改部份 

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

<!-- 
Each dbServer needs to be configured into a Pool,
If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
such as 'multiPool' dbServer   
-->
<dbServer name="abstractServer" abstractive="true">
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">${defaultManager}</property>
<property name="sendBufferSize">64</property>
<property name="receiveBufferSize">128</property>
<!-- mysql port -->
<property name="port">3306</property>
<!-- mysql schema -->
<property name="schema">Demo</property>
<!-- mysql user -->
<property name="user">amoeba</property>
<!--  mysql password-->
<property name="password">qyun520</property>
</factoryConfig>

<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<property name="maxActive">500</property>
<property name="maxIdle">500</property>
<property name="minIdle">10</property>
<property name="minEvictableIdleTimeMillis">600000</property>
<property name="timeBetweenEvictionRunsMillis">600000</property>
<property name="testOnBorrow">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>

<dbServer name="Master"  parent="abstractServer">
<factoryConfig>
<property name="ipAddress">192.168.0.188</property>
</factoryConfig>
</dbServer>

        <dbServer name="Slave1" parent="abstractServer">
        <factoryConfig>
        <property name="ipAddress">192.168.0.189</property>
        </factoryConfig>
        </dbServer>
<dbServer name="NearPool" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">Master,Slave1</property>
</poolConfig>
</dbServer>
</amoeba:dbServers>
 
[amoeba.xml]:  標示粉紅字體為本人修改部份



<?xml version="1.0" encoding="gbk"?>
 
<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
 
<proxy>
<!-- service class must implements com.meidusa.amoeba.service.Service -->
<service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
<!-- port -->
<property name="port">8066</property>
<!-- bind ipAddress -->
 
<property name="ipAddress">192.168.0.171</property>
<property name="manager">${clientConnectioneManager}</property>
<property name="connectionFactory">
<bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
<property name="sendBufferSize">128</property>
<property name="receiveBufferSize">64</property>
</bean>
</property>
<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">root</property>
<property name="password">qyun520</property>
<property name="filter">
<bean class="com.meidusa.amoeba.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>
</service>
<service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
<property name="port">9066</property>
<!-- bind ipAddress -->
<property name="ipAddress">192.168.0.171</property>
<property name="daemon">true</property>
<property name="manager">${clientConnectioneManager}</property>
<property name="connectionFactory">
<bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
</property>
</service>
<runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
<!-- proxy server net IO Read thread size -->
<property name="readThreadPoolSize">20</property>
<!-- proxy server client process thread size -->
<property name="clientSideThreadPoolSize">30</property>
<!-- mysql server data packet process thread size -->
<property name="serverSideThreadPoolSize">30</property>
<!-- per connection cache prepared statement size  -->
<property name="statementCacheSize">500</property>
<!-- query timeout( default: 60 second , TimeUnit:second) -->
<property name="queryTimeout">60</property>
</runtime>
</proxy>
<!-- 
Each ConnectionManager will start as thread
manager responsible for the Connection IO read , Death Detection
-->
<connectionManagerList>
<connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
 
<property name="processors">5</property>
 
</connectionManager>
<connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
 
<property name="processors">5</property>
</connectionManager>
</connectionManagerList>
<!-- default using file loader -->
<dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
</dbServerLoader>
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleLoader">
<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">Master</property>
<property name="writePool">Master</property>
<property name="readPool">NearPool</property>
<property name="needParse">true</property>
</queryRouter>
</amoeba:configuration>


#在上述配置中,我們配置了 

   amoeba 對外連線存取帳戶與密碼: root/qyun520 

   amoeba 與實體資料庫連線存取帳號與密碼: amoeba/qyun520

   負載平衡機制:  配置1 即 Round Robin   ,讀取池(NearPool)配置為 master 1: 1  slave 


[啟動Amoeba程式]

> /usr/local/amoeba/bin/amoeba start &     #背景執行Amoeba

<!-- Separated by commas,such as: server1,server2,server1 -->


[測試資料庫讀取(Read)輪詢]

[測試前置作業] : 登入MySQL SLAVE 主機(.189), 於Demo資料庫中UserData 表

插入一筆新Record 如下,離開
  #為了辨識等會測試時此台主機為SLAVE

Slave_MySQL   2222   id2


在前幾步配置,我們將NearPoot(讀取池)輪詢順利配置為 1. Master 2. Slave,我們來驗證

我們使用.199 電腦連線登入 Amoeba  中界程式 來查看

> mysql -u root -pqyun520 -P8066 -h 192.168.0.171  

#依序進行第一次,第二次......N次查詢Demo資料庫底下UserData 資料表內容

下圖中,我們依序執行了兩次Search資料動作

 第一次執行:  為Master主機

 第二次執行:  為Slave 主機


 若您MySQL配置為1:3  (Master*1  Slave*3),可以將讀取池(NearPool)配置成
  
  <property name="poolNames"> Slave1,Slave2,Slave3</property>

  進而實現資料讀取與寫入動作分離,達到MySQL資料庫之負載平衡目的
[測試資料庫寫入(Write)]

#寫入測試您可以考慮再多建一個Master to Master Replication 結構,進行測試,本人無

  多此設計

#欲測試Amoeba寫入資料庫是否ok,請先停止Slave 主機的Slave服務(暫停主從複寫)

我們使用.199 電腦連線登入 Amoeba  中界程式 來查看

> mysql -u root -pqyun520 -P8066 -h 192.168.0.171

#於Demo底下之UserData,寫入一筆資料如下

Master_id  1111 Near


THEN 最後我們切換回Master 實體主機(.188) 登入來查看資料是否已成功寫進Master

  Master_id  1111 Near   #此筆Record 已成功寫入Master主機

Lab 操作時參考資料:

Amoeba 官方文件: http://amoeba.meidusa.com/wordpress/

Amoeba For MySQL讀寫分離:  

http://translate.googleusercontent.com/translate_c?hl=zh-TW&prev=/search%3Fq%3D%2522amoeba%2Bmysql%2522%26hl%3Dzh-TW%26lr%3D%26sa%3DG%26tbs%3Dqdr:y%26prmd%3Divns&rurl=translate.google.com&sl=zh-CN&twu=1&u=http://hi.baidu.com/shenqi116/blog/item/d28ce33ecd8cf1db7d1e7152.html&usg=ALkJrhiI2dHujDQ1Rqhtr3GoSITryZsQAg
  
      
相簿設定
標籤設定
相簿狀態