Amoeba安装(精品)实现Mysql负载和读写分离详解

环境介绍,Alvin.zeng
master--IP:192.168.100.206
slave---IP:192.168.100.214
Amoeba--IP:192.168.100.215

一.安装.JDK
#:wgethttp://download.oracle.com/otn-pub/java/jdk/7u40-b43/jdk-7u40-linux-x64.tar.gz?AuthParam=1380445567_a84768e3a5d3481661dc1d57002eedae
#:tar-xvfjdk-7u40-linux-x64.tar.gz
#:vim/etc/profile
JAVA_HOME=/Amoeba/jdk1.7.0_40
exportJAVA_HOME

PATH=$JAVA_HOME/bin:$PATH
exportPATH

CLASSPATH=.:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/lib/dt.jar:$CLASSPATH
exportCLASSPATH

二.安装:Amoeba
#:wgethttp://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/amoeba-mysql-1.3.1-BETA.zip
#:mkdir/usr/local/amoeba
#:unzipamoeba-mysql-1.3.1-BETA.zip-d/usr/local/amoeba
#:chmod-R+x/usr/local/amoeba/bin/

三:主从都需要执行建立用户授权
mysql-->grantallprivilegeson*.*toamoeba@'%'identifiedby'amoeba123';
mysql-->flushprivileges;

四.配置:Amoeba
配置文件位于conf目录下，执行文件位于bin目录下,解压后发现bin目录下的启动文件没有可执行权限，请执行：chmod-R+x/usr/local/amoeba/bin/

AmoebaForMySQL的使用非常简单，所有的配置文件都是标准的XML文件，总共有四个配置文件。分别为：

◆amoeba.xml：主配置文件，配置所有数据源以及Amoeba自身的参数设置；实现主从的话配置这个文件就可以了；

◆rule.xml：配置所有Query路由规则的信息；

◆functionMap.xml：配置用于解析Query中的函数所对应的Java实现类；

◆rullFunctionMap.xml：配置路由规则中需要使用到的特定函数的实现类；

下面我们就来通过更改amoeba.xml配置文件实现mysql主从读写分离，我的配置如下：
<?xmlversion="1.0"encoding="gbk"?>

<!DOCTYPEamoeba:configurationSYSTEM"amoeba.dtd">
<amoeba:configurationxmlns:amoeba="http://amoeba.meidusa.com/">

<server>
<!--proxyserver绑定的端口-->
<propertyname="port">9006</property>

<!--proxyserver绑定的IP-->
<propertyname="ipAddress">192.168.100.215</property>




<!--proxyservernetIOReadthreadsize-->
<propertyname="readThreadPoolSize">20</property>

<!--proxyserverclientprocessthreadsize-->
<propertyname="clientSideThreadPoolSize">30</property>

<!--mysqlserverdatapacketprocessthreadsize-->
<propertyname="serverSideThreadPoolSize">30</property>

<!--socketSendandreceiveBufferSize(unit:K)-->
<propertyname="netBufferSize">128</property>

<!--Enable/disableTCP_NODELAY(disable/enableNagle'salgorithm).-->
<propertyname="tcpNoDelay">true</property>

<!--对外验证的用户名-->
<propertyname="user">root</property>

<!--对外验证的密码-->
<propertyname="password">123456</property>

<!--querytimeout(default:60second,TimeUnit:second)-->
<propertyname="queryTimeout">60</property>
</server>

<!--
每个ConnectionManager都将作为一个线程启动。
manager负责ConnectionIO读写/死亡检测
-->
<connectionManagerList>
<connectionManagername="defaultManager"class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<propertyname="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

<!--
defaultvalueisavaliableProcessors
<propertyname="processors">5</property>
-->
</connectionManager>
</connectionManagerList>

<dbServerList>
<!--
一台mysqlServer需要配置一个pool，
如果多台平等的mysql需要进行loadBalance，
平台已经提供一个具有负载均衡能力的objectPool：com.meidusa.amoeba.mysql.server.MultipleServerPool
简单的配置是属性加上virtual="true",该Pool不允许配置factoryConfig
或者自己写一个ObjectPool。
-->

<!--Master配置-->
<dbServername="server1">

<!--PoolableObjectFactory实现类-->
<factoryConfigclass="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<propertyname="manager">defaultManager</property>

<!--真实mysql(master)数据库端口-->
<propertyname="port">3306</property>

<!--真实mysql数据库IP(master)-->
<propertyname="ipAddress">192.168.100.206</property>
<!--数据库名用test,软件根据你授权的用户来决定负载的库,这可以随意填写最好是用test-->
<propertyname="schema">test</property>

<!--用于登陆mysql(master)的用户名-->
<propertyname="user">amoeba</property>

<!--用于登陆mysql(master)的密码-->

<propertyname="password">amoeba123</property>


</factoryConfig>

<!--ObjectPool实现类-->
<poolConfigclass="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<propertyname="maxActive">200</property>
<propertyname="maxIdle">200</property>
<propertyname="minIdle">10</property>
<propertyname="minEvictableIdleTimeMillis">600000</property>
<propertyname="timeBetweenEvictionRunsMillis">600000</property>
<propertyname="testOnBorrow">true</property>
<propertyname="testWhileIdle">true</property>
</poolConfig>
</dbServer>
<!--Slave配置-->
<dbServername="server2">

<!--PoolableObjectFactory实现类-->
<factoryConfigclass="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<propertyname="manager">defaultManager</property>

<!--真实mysql_slave数据库端口-->
<propertyname="port">3306</property>

<!--真实mysql数据库IP_slave-->
<propertyname="ipAddress">192.168.100.214</property>

<!--数据库名用test,软件根据你授权的用户来决定负载的库,这可以随意填写最好是用test-->
<propertyname="schema">test</property>

<!--用于登陆mysql_slave的用户名-->
<propertyname="user">amoeba</property>

<!--用于登陆mysql_slave的密码-->

<propertyname="password">amoeba123</property>


</factoryConfig>

<!--ObjectPool实现类-->
<poolConfigclass="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<propertyname="maxActive">200</property>
<propertyname="maxIdle">200</property>
<propertyname="minIdle">10</property>
<propertyname="minEvictableIdleTimeMillis">600000</property>
<propertyname="timeBetweenEvictionRunsMillis">600000</property>
<propertyname="testOnBorrow">true</property>
<propertyname="testWhileIdle">true</property>
</poolConfig>
</dbServer>


<!--Master负载配置-->

<dbServername="master"virtual="true">
<poolConfigclass="com.meidusa.amoeba.server.MultipleServerPool">
<!--负载均衡参数1=ROUNDROBIN,2=WEIGHTBASED,3=HA-->
<propertyname="loadbalance">1</property>

<!--参与该pool负载均衡的poolName列表以逗号分割-->
<propertyname="poolNames">server1</property>
</poolConfig>
</dbServer>
<!--Slave配置-->
<dbServername="slave"virtual="true">
<poolConfigclass="com.meidusa.amoeba.server.MultipleServerPool">
<!--负载均衡参数1=ROUNDROBIN,2=WEIGHTBASED,3=HA-->
<propertyname="loadbalance">1</property>

<!--参与该pool负载均衡的poolName列表以逗号分割主也可以读和从比例为1:1-->
<propertyname="poolNames">server1,server2</property>
</poolConfig>
</dbServer>

</dbServerList>

<queryRouterclass="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<propertyname="ruleConfig">${amoeba.home}/conf/rule.xml</property>
<propertyname="functionConfig">${amoeba.home}/conf/functionMap.xml</property>
<propertyname="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property>
<propertyname="LRUMapSize">1500</property>
<!--masterslave配置读写分离-->
<propertyname="defaultPool">master</property>
<propertyname="writePool">master</property>
<propertyname="readPool">slave</property>
<!--masterslave配置读写分离-->
<propertyname="needParse">true</property>
</queryRouter>
</amoeba:configuration>

五:启动Amoeba
#:cd/usr/local/amoeba/bin

这种启动方便看nohup.log日志.防止提示溢出
#:nohupbash-xamoeba&

六:测试Amobea负载
(1)在zytest里面临时新建一个alvin表.
mysql-->mysql
mysql-->createtablealvin(idint(10),namechar(10));

(2)在从上stopslave用来临时验证测试.测试成功后在startslave;
mysql-->stopslave;

(3)在主(master)操作.插入一条数据.
mysql-->insertintoalvinvalues(1,'master');

(4)在从(slave)操作,插入一条数据.
mysql-->insertintoalvinvalues(2,'slave');

(5)在安装amoeba的机器登陆验证.
mysql-uroot-p123456-h192.168.100.215-P9006

amoeba@zytest13:10>usetest;

amoeba@zytest13:10>select*fromalvin;
+------+--------+
|id|name|
+------+--------+
|1|master|
+------+--------+
1rowinset(0.02sec)

amoeba@zytest13:40>select*fromalvin;
+------+-------+
|id|name|
+------+-------+
|2|slave|
+------+-------+
1rowinset(0.00sec)
主从各读一次.OK

(6)验证写入数据.
amoeba@zytest13:40>insertintoalvinvalues(3,'masterupdate');
QueryOK,1rowaffected,1warning(0.01sec)

amoeba@zytest13:42>select*fromalvin;
+------+------------+
|id|name|
+------+------------+
|1|master|
|3|masterupda|
+------+------------+
2rowsinset(0.01sec)

amoeba@zytest13:42>select*fromalvin;
+------+-------+
|id|name|
+------+-------+
|2|slave|
+------+-------+
1rowinset(0.00sec)
OK.验证成功.

七:常见错误.
说明:如果出现协议栈大小错误.最少不小于.228k
解决如下:
#:vimamobea
DEFAULT_OPTS="-server-Xms256m-Xmx256m-Xss228k"-->将此处变量的最后一个.128k改成.228K在重新启动脚本.

八:常用配置写法.
(1)如果负载多个库,软件会根据你用户授权来决定对哪些库有权限.
grantallprivilegeson*.*toamoeba@'%'identifiedby'amoeba123';这里写的是all那么就是对所有的库有权限(会自动负载所有的库).
上面配置文件的库名test.根据这个语句来决定.如果是all.那么直接用写test来测试.方便一点.

(2)一主一从的负载写法一:
主只写.<propertyname="poolNames">server1</property>
从只读.<propertyname="poolNames">server2</property>

(3)一主一从的负载写法:
主负写:<propertyname="poolNames">server1</property>
主也负责读,从也负责读.比例1:1第一读.从.第二次读住.循环写的话,只能写主.<propertyname="poolNames">server1,server2</property>

(4)一主多从的负载写法:
比如.一台主.3台从.在最上面定义了.server1(master)server2(slave1)server3(slave2)server4(slave3)
1主只写:<propertyname="poolNames">server1</property>
3从负载读<propertyname="poolNames">server2,server3,server4</property>
权重:1:13台轮询各一次.持续循环

(5)比如我想要.slave1权重高一点.其它的.2台从.每次读一次.slave1读.2次.
<propertyname="poolNames">server2,server2,server3,server4</property>