一 amoeba
Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）。
Amoeba优缺点
优点：
1、降低 数据切分带来的复杂多数据库结构
2、提高系统整体可用性
3、提供切分规则并降低 数据切分规则 给应用带来的影响
4、降低db 与客户端的连接数
5、可以直接实现读写分离及负载均衡效果，而不用修改代码
缺点：
1、不支持事务与存储过程
2、暂不支持分库分表，amoeba目前只做到分数据库实例
3、不适合从amoeba导数据的场景或者对大数据量查询的query并不合适（比如一次请求返回10w以上甚至更多数据的场合）
二 数据库读写分离配置
环境
系统 centos 6.4 x86_64
软件 mysql5.6.19 jdk1.7 amoeba2.2
amoeba服务器 192.168.200.19
master 192.168.200.17
slave 192.168.200.18
主从服务器复制见以前的博客 此处略
1 配置JDK
[root@bogon ~]# rpm -ivh jdk-7u60-linux-x64.rpm
[root@bogon ~]# vim /etc/profile.d/java.sh
export JAVA_HOME=/usr/java/latest
export PATH=$PATH:$JAVA_HOME/bin
[root@bogon ~]# . /etc/profile.d/java.sh
[root@bogon ~]# java -version
java version Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) 64-Bit Server VM (build 24.60-b09, mixed mode)
2 安装amoeba
[root@bogon ~]# mkdir /usr/local/amoeba[root@bogon ~]# tar -xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
[root@bogon ~]# cd /usr/local/amoeba/[root@bogon amoeba]# lsbenchmark bin changelogs.txt conf lib LICENSE.txt README.html[root@bogon amoeba]# ls confaccess_list.conf dbserver.dtd functionMap.xml rule.dtdamoeba.dtd dbServers.xml log4j.dtd ruleFunctionMap.xmlamoeba.xml function.dtd log4j.xml rule.xml Amoeba总共有7个配置文件，分别如下： Amoeba主配置文件($AMOEBA_HOME/conf/amoeba.xml)，用来配置Amoeba服务的基本参数，如Amoeba主机地址、端口、认证方式、用于连接的用户名、密码、线程数、超时时间、其他配置文件的位置等。 数据库服务器配置文件($AMOEBA_HOME/conf/dbServers.xml)，用来存储和配置Amoeba所代理的数据库服务器的信息，如:主机IP、端口、用户名、密码等。 切分规则配置文件($AMOEBA_HOME/conf/rule.xml)，用来配置切分规则。 数据库函数配置文件($AMOEBA_HOME/conf/functionMap.xml)，用来配置数据库函数的处理方法，Amoeba将使用该配置文件中的方法解析数据库函数。 切分规则函数配置文件($AMOEBA_HOME/conf/ruleFunctionMap.xml)，用来配置切分规则中使用的用户自定义函数的处理方法。 访问规则配置文件($AMOEBA_HOME/conf/access_list.conf)，用来授权或禁止某些服务器IP访问Amoeba。 日志规格配置文件($AMOEBA_HOME/conf/log4j.xml)，用来配置Amoeba输出日志的级别和方式。
[root@bogon conf]# vim dbServers.xml <?xml version= encoding=?><!DOCTYPE amoeba:dbServers SYSTEM ><amoeba:dbServers xmlns:amoeba=> <!-- 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 = dbServer, but the configuration does not allow the element with name factoryConfig such dbServer --> <dbServer name= abstractive=> <factoryConfig => <property name=>${defaultManager}</property> <property name=>64</property> <property name=>128</property> <!-- mysql port --> <property name=>3306</property> <!-- mysql schema --> <property name=>test</property># 设置amoeba连接后端数据库服务器的账号 密码 需在后端服务器上创建该用户，并授权amoeba连接 <!-- mysql user --> <property name=>root</property> <property name=>redhat</property> <!-- mysql password <property name=>password</property> --> </factoryConfig> <poolConfig => <property name=>500</property> <property name=>500</property> <property name=>10</property> <property name=>600000</property> <property name=>600000</property> <property name=></property> <property name=></property> <property name=></property> </poolConfig> </dbServer># 设置一个后端服务器 名字可以随便取 <dbServer name= parent=> <factoryConfig> <!-- mysql ip --> <property name=>192.168.200.17</property> </factoryConfig> </dbServer> <dbServer name= parent=> <factoryConfig> <!-- mysql ip --> <property name=>192.168.200.18</property> </factoryConfig> </dbServer>#指定一个虚拟数据库服务器，可以将上面定义的dbserver加入这个虚拟服务器，相当组成一个组 <dbServer name= => <poolConfig => <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--> <property name=>1</property> <!-- Separated by commas,such : server1,server2,server1 --> <property name=>server1,server2</property> </poolConfig> </dbServer></amoeba:dbServers>
[root@bogon conf]# vim amoeba.xml <?xml version= encoding=?><!DOCTYPE amoeba:configuration SYSTEM ><amoeba:configuration xmlns:amoeba=> <proxy> <!-- service must implements com.meidusa.amoeba.service.Service --> <service name= =>#设置amoeba监听的端口 <!-- port --> <property name=>8066</property>#设置监听的接口，如果不设置，则监听所有的IP <!-- bind ipAddress --> <!-- <property name=>127.0.0.1</property> --> <property name=>${clientConnectioneManager}</property> <property name=> <bean => <property name=>128</property> <property name=>64</property> </bean> </property> <property name=> <bean =>#提供客户端连接amoeba时需要使用的账号 密码 与 amoeba连接后端数据库服务器密码无关 <property name=>root</property> <property name=>redhat</property> <property name=> <bean => <property name=>${amoeba.home}/conf/access_list.conf</property> </bean> </property> </bean> </property> </service>................................................（省略） <dbServerLoader => <property name=>${amoeba.home}/conf/dbServers.xml</property> </dbServerLoader> <queryRouter => <property name=> <bean => <property name=>${amoeba.home}/conf/rule.xml</property> <property name=>${amoeba.home}/conf/ruleFunctionMap.xml</property> </bean> </property> <property name=>${amoeba.home}/conf/functionMap.xml</property> <property name=>1500</property> <property name=>server1</property>#设置读写分离选项 <property name=>server1</property> <property name=>server2</property> <!-- <property name=>server1</property> <property name=>server1</property> --> <property name=></property> </queryRouter></amoeba:configuration>
配置amoeba 环境变量
[root@bogon conf]# vim /etc/profile.d/amoeba.shexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin：[root@bogon conf]# . /etc/profile.d/amoeba.sh
启动amoeba
在JDK1.7下 提示会提示堆内存过小
root@bogon bin]# ./amoeba startThe stack size specified too small, Specify at least 228kError: Could not create the Java Virtual Machine.Error: A fatal exception has occurred. Program will exit.[root@bogon bin]# vim amoebaDEFAULT_OPTS=改为DEFAULT_OPTS=[root@bogon bin]# ./amoeba startlog4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2014-07-19 09:12:17,345 INFO context.MysqlRuntimeContext - Amoeba Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf2014-07-19 09:12:47,868 INFO net.ServerableConnectionManager - Amoeba Mysql listening on 0.0.0.0/0.0.0.0:8066.2014-07-19 09:12:47,870 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:19499.
测试
在主DB server上操作
mysql> create databaes db;mysql>use dbmysql>create table t1(name varchar(10);mysql>grant all privileges on *.* to @ identified by ;mysql>flush privileges;
root@bogon ~]# mysql -uroot -predhat -h192.168.200.19 --port 8066Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id 390789680Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type or help. Type to clear the current input statement.mysql> mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || db || mysql || performance_schema || test |+--------------------+5 rows set (0.01 sec)mysql> use db;Reading table information completion of table and column namesYou can turn off feature to get a quicker startup with -ADatabase changedmysql> show tables;+--------------+| Tables_in_db |+--------------+| t1 |+--------------+1 row set (0.00 sec)
注 amoeba服务器提示
java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
mysql>
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 11416420
Current database: test
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 11416420
Current database: test
ERROR 2006 (HY000): MySQL server has gone away
amoeba服务器连接mysql过慢，超时，因mysql 对客户端连接不管是host或ip 都要反解
可在/etc/my.cnf
[mysqld]
skip-name-resolve