當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

基于Ameoba实现mysql读写分离

發(fā)布時(shí)間：2023/12/20 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了基于Ameoba实现mysql读写分离小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

一、Amoeba介紹

Amoeba：基于Java研發(fā)，配置文件xml文檔。對(duì)事務(wù)支持功能很差，不支持分布式讀，尤其不支持分布式事務(wù)。默認(rèn)監(jiān)聽(tīng)在8066端口上。2.2.x開(kāi)始支持單庫(kù)事務(wù)，沒(méi)有緩存功能，所有請(qǐng)求直接向后轉(zhuǎn)發(fā)。對(duì)內(nèi)存占用少，而對(duì)CPU占用大。

二、架構(gòu)簡(jiǎn)介

Amoeba服：192.168.1.253 監(jiān)聽(tīng)端口8066改3306，作為后端mysql服務(wù)器代理，監(jiān)控后端mysql服務(wù)器健康的端口9066。

主服：station20:192.168.1.20，前端代理往主服寫(xiě)操作會(huì)自動(dòng)復(fù)制到從服。

從服：station21:192.168.1.21

如果是多從，在前端代理里設(shè)置一個(gè)負(fù)載均衡從服組(虛擬服務(wù)器)，組內(nèi)按調(diào)度算法調(diào)度，三種算法：輪詢rr,加權(quán)輪詢wrr,高可用ha.

地址：http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/

三、操作步驟

主服新建遠(yuǎn)程用戶，從服自動(dòng)復(fù)制次條目 mysql>grant?all?on?*.*?to?'root'@'%'?identified?by?'amoebapass'; mysql>flush?privileges; ------------------------------------------------------------------ 從服 mysql>show?grants?for?root@'%'; |?Grantsfor?root@%??????????????????????????????????????????????????????????????????????????????????????????? |?GRANTALL?PRIVILEGES?ON?*.*?TO?'root'@'%'?IDENTIFIED?BY?PASSWORD'*84BB5DF4823DA319BBF86C99624479A198E6EEE9'?|

Amoba服

1.安裝Amoeba [root@station253~]#?cp?/mnt/hgfs/Share64/jdk/jdk-6u31-linux-x64-rpm.bin?/tmp [root@station253tmp]#?chmod?+x?jdk-6u31-linux-x64-rpm.bin [root@station253tmp]#?./jdk-6u31-linux-x64-rpm.bin [root@station253tmp]#?vim?/etc/profile.d/java.sh exportJAVA_HOME=/usr/java/latest exportPATH=$JAVA_HOME/bin:$PATH [root@station253tmp]#?.?/etc/profile.d/java.sh [root@station253tmp]#?java?-version javaversion?"1.6.0_31" Java(TM)SE?Runtime?Environment?(build?1.6.0_31-b04) JavaHotSpot(TM)?64-Bit?Server?VM?(build?20.6-b01,?mixed?mode)??混合模式，支持編譯[root@station253tmp]#?cp?/mnt/hgfs/Share64/amoeba/amoeba-mysql-binary-2.2.0.tar.gz?. ameoba不會(huì)自行創(chuàng)建目錄，手工創(chuàng)建 [root@station253tmp]#?mkdir?-pv?/usr/local/amoeba-2.2.0??#保留版本信息便于后期識(shí)別 [root@station253tmp]#?tar?-zxvf?amoeba-mysql-binary-2.2.0.tar.gz?-C?/usr/local/amoeba-2.2.0 [root@station253tmp]#?cd?/usr/local/ [root@station253local]#??ln?-sv?amoeba-2.2.0/?amoeba "amoeba"->?"amoeba-2.2.0/"2.添加環(huán)境變量 [root@station253local]#?vim?/etc/profile.d/amoeba.sh exportAMOEBA_HOME=/usr/local/amoeba exportPATH=$AMOEBA_HOME/bin:$PATH [root@station253local]#?.?/etc/profile.d/amoeba.sh [root@station253conf]#?amoeba amoeba?start|stop3.配置amoeba服務(wù) [root@station253local]#?cd?amoeba [root@station253amoeba]#?cp?-r?conf/?backup [root@station253amoeba]#?ls backup??benchmark?bin??changelogs.txt??conf?lib??LICENSE.txt??README.html [root@station253amoeba]#?cd?conf 定義數(shù)據(jù)庫(kù)讀寫(xiě)分離及節(jié)點(diǎn)管理信息 [root@station253conf]#?vim?amoeba.xml <?xml?version="1.0"encoding="gbk"?> <!DOCTYPE?amoeba:configuration?SYSTEM"amoeba.dtd"> <amoeba:configurationxmlns:amoeba="http://amoeba.meidusa.com/"> <proxy>?????????????#定義代理  <service?name="Amoeba?for?Mysql"class="com.meidusa.amoeba.net.ServerableConnectionManager">???????#定義服務(wù)，由類(lèi)實(shí)現(xiàn) ?????#定義連接池 ??加外括號(hào)注釋 <property?name="port">3306</property>??????#定義監(jiān)聽(tīng)端口，將默認(rèn)改動(dòng)為3306???#定義代理服務(wù)器對(duì)外連接的監(jiān)聽(tīng)I(yíng)P<propertyname="ipAddress">0.0.0.0</property>??#定義監(jiān)聽(tīng)I(yíng)P地址，這里定義為監(jiān)聽(tīng)所有IP <propertyname="manager">${clientConnectioneManager}</property><propertyname="connectionFactory">??#連接池 <beanclass="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory"> <propertyname="sendBufferSize">128</property>??????????#定義發(fā)送緩沖大小（可根據(jù)主機(jī)內(nèi)存可調(diào)整）<propertyname="receiveBufferSize">64</property>????????#定義接受緩沖的大小（可根據(jù)主機(jī)內(nèi)存可調(diào)整）</bean></property><propertyname="authenticator">???????????#認(rèn)證器<beanclass="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"><propertyname="user">root</property>??????#客戶端連接后端時(shí)的用戶名 <property?name="password">amoebapass</property>??????#客戶端連接后端時(shí)的密碼<propertyname="filter"> <beanclass="com.meidusa.amoeba.server.IPAccessController"> <propertyname="ipFile">${amoeba.home}/conf/access_list.conf</property>?????#只允許哪些客戶端訪問(wèn)，基于文件實(shí)現(xiàn)訪問(wèn)控制 </bean> </property></bean></property></service> <servicename="Amoeba?Monitor?Server"class="com.meidusa.amoeba.monitor.MonitorServer">??#amoeba的監(jiān)控服務(wù)器，監(jiān)測(cè)每一個(gè)后端的工作狀態(tài)  <property?name="port">3306</property>???#監(jiān)控服務(wù)器監(jiān)聽(tīng)的端口，默認(rèn)注釋表示使用隨機(jī)端口  <propertyname="ipAddress">127.0.0.1</property>??#定義監(jiān)控服務(wù)器監(jiān)聽(tīng)的地址 <propertyname="daemon">true</property> <propertyname="manager">${clientConnectioneManager}</property><propertyname="connectionFactory"> <beanclass="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean> </property></service> <runtimeclass="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext"> <propertyname="readThreadPoolSize">20</property> <property?name="clientSideThreadPoolSize">30</property> <propertyname="serverSideThreadPoolSize">30</property> <propertyname="statementCacheSize">500</property> <propertyname="queryTimeout">60</property></runtime> </proxy>  <connectionManagerList>???#定義連接池的列表 <connectionManagername="clientConnectioneManager"class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper"> <propertyname="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property> </connectionManager> <connectionManager?name="defaultManager"class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper"><propertyname="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property></connectionManager></connectionManagerList>?????#連接池的列表 <dbServerLoaderclass="com.meidusa.amoeba.context.DBServerConfigFileLoader"><propertyname="configFile">${amoeba.home}/conf/dbServers.xml</property></dbServerLoader><queryRouterclass="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">????#定義查詢路由 <property?name="ruleLoader"><beanclass="com.meidusa.amoeba.route.TableRuleFileLoader"><propertyname="ruleFile">${amoeba.home}/conf/rule.xml</property><propertyname="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property> </bean> </property> <propertyname="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property> <propertyname="LRUMapSize">1500</property>????????????????????????????<propertyname="defaultPool">station20</property>???????????????????#定義讀寫(xiě)服務(wù)器，這里默認(rèn)定義到主服務(wù)器上 <propertyname="writePool">station20</property>?????????????????????#定義寫(xiě)服務(wù)器，若有多個(gè)用逗號(hào)隔開(kāi)，這些服務(wù)器名來(lái)自于dbservers.xml <propertyname="readPool">station21</property>??????????????????????#定義讀服務(wù)器<propertyname="needParse">true</property> </queryRouter> </amoeba:configuration> ###############定義連接后端Mysql服務(wù)器信息############ [root@node?conf]#?vim?dbServers.xml <?xml?version="1.0"encoding="gbk"?> <!DOCTYPE?amoeba:dbServers?SYSTEM"dbserver.dtd"> <amoeba:dbServersxmlns:amoeba="http://amoeba.meidusa.com/"><dbServername="abstractServer"?abstractive="true">?????????????????#定義服務(wù)器，名字叫抽象服務(wù)器，支持抽象功能：公共屬性的定義 <factoryConfigclass="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"> <propertyname="manager">${defaultManager}</property><propertyname="sendBufferSize">64</property>?????????????????#定義發(fā)送緩沖大小 <propertyname="receiveBufferSize">128</property>?????????????#接受緩沖的大小 <propertyname="port">3306</property>?????????????????????????#監(jiān)聽(tīng)的端口 <property?name="schema">test</property>???????????????????????#默認(rèn)連接的數(shù)據(jù)庫(kù)服務(wù)器  <property?name="user">root</property>?????????????????????????#用戶名???????????????????????????????????-->上移與<!-?配對(duì)，取消下行注釋<propertyname="password">amoebapass</property>?????????????????#后端服務(wù)器的密碼,授權(quán)用戶密碼</factoryConfig> <poolConfigclass="com.meidusa.amoeba.net.poolable.PoolableObjectPool"><propertyname="maxActive">500</property>??????????????????????#定義最大活動(dòng)連接數(shù)<propertyname="maxIdle">500</property>????????????????????????#空閑連接數(shù)<propertyname="minIdle">10</property>?????????????????????????#最少空閑連接數(shù)<propertyname="minEvictableIdleTimeMillis">600000</property><propertyname="timeBetweenEvictionRunsMillis">600000</property><propertyname="testOnBorrow">true</property><propertyname="testOnReturn">true</property> <propertyname="testWhileIdle">true</property></poolConfig> </dbServer><dbServername="station20"??parent="abstractServer">???????????#定義服務(wù)器，其父服務(wù)器就是上面的"abstractServer"繼承其屬性，若不想請(qǐng)?zhí)砑訉傩詫?shí)現(xiàn)定義<factoryConfig><propertyname="ipAddress">192.168.1.20</property>???????????#server1的IP地址 </factoryConfig></dbServer> <dbServer?name="station21"??parent="abstractServer">??????????#server1的IP地址 <factoryConfig><propertyname="ipAddress">192.168.18.21</property></factoryConfig> </dbServer> <dbServer?name="multiPool"virtual="true">??????????????????????????#定義虛擬服務(wù)器組 <poolConfigclass="com.meidusa.amoeba.server.MultipleServerPool">??????#算法：1表示輪詢，2：基于權(quán)重做輪詢，3：高可用效果<propertyname="loadbalance">1</property>??????????#還可以實(shí)現(xiàn)負(fù)載均衡，算法為1<propertyname="poolNames">station20,station21</property>?????????????#此處一定是定義的dbname</poolConfig></dbServer> </amoeba:dbServers>4.啟動(dòng)Amoeba [root@station253conf]#?amoeba?start log4j:WARNlog4j?config?load?completed?from?file:/usr/local/amoeba/conf/log4j.xml 2014-05-2505:05:40,143?INFO?context.MysqlRuntimeContext?-?Amoeba?for?Mysql?currentversoin=5.1.45-mysql-amoeba-proxy-2.2.0??#檢測(cè)后端mysql服務(wù)器兼容版本 log4j:WARNip?access?config?load?completed?fromfile:/usr/local/amoeba/conf/access_list.conf 2014-05-2505:05:40,415?INFO??net.ServerableConnectionManager-?Amoeba?for?Mysql?listening?on?/0.0.0.0:3306.?#監(jiān)聽(tīng)地址及端口 2014-05-2505:05:40,427?INFO?net.ServerableConnectionManager?-?Amoeba?Monitor?Server?listening?on/127.0.0.1:23679.??#管理地址及端口（隨機(jī)） ##########停止amoeba########### Ctrl+C #########使用后端啟動(dòng)########## [root@station253conf]#?amoeba?start?& #########后端啟動(dòng)服務(wù)時(shí)停止#### [root@station253conf]#?amoeba?stop amoebaserver?shutting?down?with?port=11422 ###########查看監(jiān)聽(tīng)端口######## [root@station253conf]#?ss?-natlp?|?grep?3306 LISTEN?????0?????128??????:::3306?????????:::*?????users:(("java",2467,50))

5.測(cè)試連接

[root@station253conf]#?mysql?-uroot?-pamoebapass?-h192.168.1.253 YourMySQL?connection?id?is?432598870??前端連接的是代理服務(wù)器amoeba Serverversion:?5.1.45-mysql-amoeba-proxy-2.2.0?MySQLCommunity?Server?(GPL) mysql>select?version();??顯示后端數(shù)據(jù)庫(kù)版本 +------------+ |version()??| +------------+ |5.6.13-log?| +------------+ 測(cè)試讀寫(xiě)分離是否到各自服務(wù)器前端?讀庫(kù) mysql>select?user,host?from?mysql.user;???在讀寫(xiě)分離后，select必須用庫(kù)名+表名，因?yàn)槟J(rèn)路由是到test庫(kù) +------+-----------------------+ |?user?|host??????????????????| +------+-----------------------+ |?root?|%?????????????????????| |?root?|127.0.0.1?????????????| |?root?|::1???????????????????| |??????|?localhost?????????????| |?root?|localhost?????????????| |??????|?station21.example.com?| |?root?|station21.example.com?| +------+-----------------------+ 后端2服務(wù)器提前啟用tcpdump抓包 [root@station21~]#?tcpdump?-i?eth0?-A?-nn?-s0?tcp?dst?port?3306?and?ip?dst?host?192.168.1.21 tcpdump:verbose?output?suppressed,?use?-v?or?-vv?for?full?protocol?decode listeningon?eth0,?link-type?EN10MB?(Ethernet),?capture?size?65535?bytes 07:20:03.771162IP?192.168.1.253.39815?>?192.168.1.21.3306:?Flags?[P.],?select?user,host?from?mysql.user 前端?寫(xiě)庫(kù) mysql>create?database?school;?? [root@station20~]#?tcpdump?-i?eth0?-A?-nn?-s0?tcp?dst?port?3306?and?ip?dst?host?192.168.1.20 tcpdump:verbose?output?suppressed,?use?-v?or?-vv?for?full?protocol?decode listeningon?eth0,?link-type?EN10MB?(Ethernet),?capture?size?65535?bytes 07:21:30.426165IP?192.168.1.253.33837?>?192.168.1.20.3306:?Flags?[P.],?create?database?school mysql>drop?database?school;??drop會(huì)先drop寫(xiě)庫(kù)后select讀庫(kù)確認(rèn)刪除 07:33:40.080159IP?192.168.1.253.33849?>?192.168.1.20.3306:?Flags?[P.],?drop?database?school 07:33:40.410321IP?192.168.1.253.39825?>?192.168.1.21.3306:?Flags?[P.],?SELECT?DATABASE()

轉(zhuǎn)載于:https://blog.51cto.com/manfred12/1416953

總結(jié)

以上是生活随笔為你收集整理的基于Ameoba实现mysql读写分离的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

MySQL
Ameoba

上一篇： weblogic 8.1 安装并配置数据
下一篇： OpenBSD如何挂载USB闪存盘