amoeba实现MySQL读写分离

测试环境：
centos6+mysql5.1+amoeba2.2.0

测试拓扑:

#在mysql-amoeba上操作
amoeba需要java的支持，首先安装java

#下载解压安装jdk

mkdir -p /usr/local/amoeba
cd?/usr/local/amoeba
tar xf jdk-8u111-linux-x64.tar.gz

[root@webserve1 jdk]# tail -3 /etc/profile
export JAVA_HOME=/usr/local/jdk
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH:$HOME/bin

[root@webserve1 jdk]# java -version
java version “1.8.0_111”
Java(TM) SE Runtime Environment (build 1.8.0_111-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.111-b14, mixed mode)
mkdir -p /usr/local/amoeba
cd /usr/local/amoeba/
wget http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz
tar xf amoeba-mysql-binary-2.2.0.tar.gz

#amoeba的启动
nohup /usr/local/amoeba/bin/amoeba start &
#amoeba的停止
/usr/local/amoeba/bin/amoeba stop

#amoeba开机自启动
[root@webserve1 ~]# tail -2 /etc/rc.local
#amoeba的启动
nohup /usr/local/amoeba/bin/amoeba start &

amoeba的主要就是对这个2个配置文件做操作[amoeba.xml和dbServers.xml]
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">1107</property>
 
 <!-- bind ipAddress -->
 
 <property name="ipAddress">192.168.10.252</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">99web</property>
 
 <property name="password">99web</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>
 
 <!-- server class must implements com.meidusa.amoeba.service.Service -->
 <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
 <!-- port -->
 <!-- default value: random number
 <property name="port">9066</property>
 -->
 <!-- bind ipAddress -->
 <property name="ipAddress">127.0.0.1</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>
 <!-- 
 default value is avaliable Processors 
 <property name="processors">5</property>
 -->
 </connectionManager>
 <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
 <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
 
 <!-- 
 default value is avaliable Processors 
 <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">readservers</property>
 
 <property name="needParse">true</property>
 </queryRouter>
</amoeba:configuration>

dbServers.xml.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">test</property>

<!-- mysql user -->
<property name="user">99web</property>


<property name="password">99web</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="testOnReturn">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>

<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.10.230</property>
</factoryConfig>
</dbServer>

<dbServer name="slave" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.10.229</property>
</factoryConfig>
</dbServer>

<dbServer name="readservers" 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,slave</property>
</poolConfig>
</dbServer>

</amoeba:dbServers>

?

登陆mysql-amoeba:
mysql -h 192.168.10.252 -u99web -p’99web’ -P 1107

登陆master:
mysql -uroot -p’123456′

登陆slave:
mysql -uroot -p’123456′

测试之前先要保证amoeba-server有访问两个主从服务器test库的权限,在主从mysql上都执行：
grant all on test.* to 99web@’192.168.10.%’ identified by ’99web’;
flush privileges;

ps:主库和从库使用相同的用户，但授予不同的权限，这样就显得更专业
主库：
grant all on 99health.* to 99web@”192.168.1.%” identified by “!@#$mysql99″;
从库：
grant select on 99health.* to 99web@”192.168.1.%” identified by ‘!@#$mysql99’;

测试步骤：
还没有停掉从同步之前，创建一个表：
create table zhang (id int(10) ,name varchar(10),address varchar(20));

在从上执行
stop slave;

然后在主从上各插入一条不同数据（供测试读的时候用），
在主上插入：insert into zhang values(‘1′,’zhang’,’this_is_master’);
在从上插入：insert into zhang values(‘2′,’zhang’,’this_is_slave’);
接下来通过登录amoeba-mysql上来测试读写：

###第一次执行显示在主上读取的数据！
mysql> select * from zhang;
+——+——-+—————-+
| id | name | address |
+——+——-+—————-+
| 1 | zhang | this_is_master |
+——+——-+—————-+
1 row in set (0.01 sec)
####第二次执行select语句显示是在从上读取的数据
mysql> select * from zhang;
+——+——-+—————+
| id | name | address |
+——+——-+—————+
| 2 | zhang | this_is_slave |
+——+——-+—————+
1 row in set (0.00 sec)
主从库的读比例是1:1
###插入一条数据，
mysql> insert into zhang values(‘3′,’hhh’,’test_write’);
Query OK, 1 row affected (0.01 sec)

###我们可以看到插入的数据被添加到了主上！
mysql> select * from zhang;
+——+——-+—————-+
| id | name | address |
+——+——-+—————-+
| 1 | zhang | this_is_master |
| 3 | hhh | test_write |
+——+——-+—————-+
2 rows in set (0.00 sec)

###从上还是没有插入，因为执行了stop slave；
mysql> select * from zhang;
+——+——-+—————+
| id | name | address |
+——+——-+—————+
| 2 | zhang | this_is_slave |
+——+——-+—————+
1 row in set (0.00 sec

大家可能会想到，我们加入只有两台数据库服务器，一台主，一台从，按照上面的配置只能是主和从的读取比率是1:1,而写又全部在主上进行，这样主的压力就很大了，所以如果能让主和从的读设置权重，比如设置成1:3,这样就可以很好的解决主从不压力均衡的问题！通过研究确实可以！
配置就是将上面的读的池的配置更改一下：
将server1,server2更改成
server1,server2,server2,server2

参考：http://blog.chinaunix.net/uid-20639775-id-154600.html

PS.在实际的生产环境下，默认的amoeba的配置参数可能需要调整和优化一下，同时要考虑服务器的硬件配置，网站的规模和并发。以下是我的线上的参数设置：

a.设置Amoeba启动脚本来调整内存参数

在Amoeba的启动脚本中找到DEFAULT_OPTS变量所在的行
…
DEFAULT_OPTS=”-server -Xms1024m -Xmx1024m -Xss256k”
…

一般在server端的应用程序上面设置-Xms 跟 -Xmx的值一致将有效提升应用程序的性能。 -Xms小于等于-Xmx的值。
将DEFAULT_OPTS设置如上将使得JVM获得更大的内存从而提升Amoeba的性能。

b.配置amoeba.xml的多线程相关选项

 ...
 <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
 <!-- proxy server net IO Read thread size -->
 <property name="readThreadPoolSize">500</property>-1
 
 <!-- proxy server client process thread size -->
 <property name="clientSideThreadPoolSize">300</property> -2
 
 <!-- mysql server data packet process thread size -->
 <property name="serverSideThreadPoolSize">300</property> -3
 
 <!-- per connection cache prepared statement size -->
 <property name="statementCacheSize">500</property> -4
 
 <!-- query timeout( default: 60 second , TimeUnit:second) -->
 <property name="queryTimeout">90</property> -5
 </runtime>
 ...

1.readThreadPoolSize –用于处理客户端连接发送过来的数据，跟数据库服务器返回的数据的线程数 量这个可以根据客户端连接数量来调整。
2.clientSideThreadPoolSize –在读线程读完客户端的请求数据包以后，这个线程将会接手处理具体 的业务逻辑（比如：解析SQL、SQL 路由）。
3.serverSideThreadPoolSize –在server端读取数据库返回的数据包,合并多数据库返回的数据，将数 据包发送到客户端。
4.statementCacheSize –连接池缓存的Prepared Statement数量。
5.queryTimeout –语句执行超时时间设置。

c.调整读写buffersize的大小

…
<property name=”connectionFactory”>
<bean class=”com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory”>
<property name=”sendBufferSize”>1024</property>
<property name=”receiveBufferSize”>512</property>
</bean>
</property>
…