Amoeba实现数据库读写分离

Amoeba变形虫(阿米巴)入门级代理服务器
Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）。

搭建步骤
1配置jdk环境
上传jdk

解压jdk

配置环境变量:vim /etc/profile

4.重写加载 source /etc/profile(手动刷新环境变量-没报任何错就是配置好了)

2安装Amoeba

[root@localhost src]# ls
amoeba-mysql-3.0.4-BETA.tar.gz  java
[root@localhost src]#  tar -xvf amoeba-mysql-3.0.4-BETA.tar.gz

[root@localhost src]# ls
amoeba-mysql-3.0.4-BETA  amoeba-mysql-3.0.4-BETA.tar.gz  java
[root@localhost src]# rm amoeba-mysql-3.0.4-BETA.tar.gz 
rm: remove regular file `amoeba-mysql-3.0.4-BETA.tar.gz'? y
[root@localhost src]# mv amoeba-mysql-3.0.4-BETA amoeba
[root@localhost src]# ls
amoeba  java
[root@localhost src]# cd amoeba/
[root@localhost amoeba]# ls
benchmark  bin  conf  jvm.properties  lib
[root@localhost amoeba]# cd conf
[root@localhost conf]# ls
access_list.conf  dbserver.dtd   functionMap.xml  rule.dtd
amoeba.dtd        dbServers.xml  log4j.dtd        ruleFunctionMap.xml
amoeba.xml        function.dtd   log4j.xml        rule.xml

更改dbServer
connectionManager连接管理器
sendBufferSize缓冲区大小
maxActive最大活跃数量
minIdle最小活跃数量

<!-- mysql port -->
            <property name="port">3306</property>

定义公共连接端口

修改如下配置

<!-- mysql user -->
            <property name="user">root</property>
            
            <property name="password">root</property>

    <!--配置主库-->
    <dbServer name="master"  parent="abstractServer">
        <factoryConfig>
            <!-- mysql ip -->
            <property name="ipAddress">192.168.161.132</property>
        </factoryConfig>
    </dbServer>
    <!--配置从库-->
    <dbServer name="slave01"  parent="abstractServer">
        <factoryConfig>
            <!-- mysql ip -->
            <property name="ipAddress">192.168.161.133</property>
        </factoryConfig>
    </dbServer>

<!--实现负载均衡-->
    <!--主读操作-->
    <dbServer name="multiPool" virtual="true">
        <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
            <!--负载均衡方式
             WEIGHTBASED 请求发往负载较少的机器-->
            <!-- 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">slave01,master,slave01</property>
        </poolConfig>
    </dbServer>

完整版

<?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="connectionManager">${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">root</property>
            
            <property name="password">root</property>
        </factoryConfig>

        <poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
            <property name="maxActive">500</property>
            <property name="maxIdle">500</property>
            <property name="minIdle">1</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.161.132</property>
        </factoryConfig>
    </dbServer>
    <!--配置从库-->
    <dbServer name="slave01"  parent="abstractServer">
        <factoryConfig>
            <!-- mysql ip -->
            <property name="ipAddress">192.168.161.133</property>
        </factoryConfig>
    </dbServer>
    <!--实现负载均衡-->
    <!--主读操作-->
    <dbServer name="multiPool" virtual="true">
        <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
            <!--负载均衡方式
             WEIGHTBASED 请求发往负载较少的机器-->
            <!-- 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">slave01,master,slave01</property>
        </poolConfig>
    </dbServer>
        
</amoeba:dbServers>

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.161.134:8066/jtdb2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
jdbc.username=root
jdbc.password=123456

amoeba.xml

        <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
                    
                    <property name="user">root</property>
                    
                    <property name="password">root</property>
                    
                    <property name="filter">
                        <bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">
                            <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                        </bean>
                    </property>
                </bean>

放开

<property name="writePool">server1</property>
        <property name="readPool">server1</property>

修改

<property name="writePool">master</property>
        <property name="readPool">multiPool</property>

完整版

<?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.mysql.server.MySQLService">
            <!-- port 服务器连接Amoeba的端口-->
            <property name="port">8066</property>
            
            <!-- bind ipAddress -->
            <!-- 
            <property name="ipAddress">127.0.0.1</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="authenticateProvider">
                <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
                    
                    <property name="user">root</property>
                    
                    <property name="password">root</property>
                    
                    <property name="filter">
                        <bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">
                            <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                        </bean>
                    </property>
                </bean>
            </property>
            
        </service>
        
        <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
            
            <!-- proxy server client process thread size -->
            <property name="executeThreadSize">128</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="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper">
            <property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</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">multiPool</property>
        
        
        <property name="writePool">master</property>
        <property name="readPool">multiPool</property>
        
        <property name="needParse">true</property>
    </queryRouter>
</amoeba:configuration>

修改jvm内存
jvm.properties

# JVM相关的参数，包括内存配置、垃圾回收策略

JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"

完整版

# app名字
APP_NAME=Amoeba-MySQL

# app版本号
APP_VERSION=3.0.0-beta

# 日志输出路径，log4j中可引用参数 ${project.output}

APP_OUTPUT_PATH=$PROJECT_HOME/logs

# 应用程序的PID文件存放路径,    默认存放在: ${project.home}/${APP_NAME}.pid

#APP_PID_PATH=/temp/logs/$APP_NAME


# 控制台输出到日志文件

APP_CONSOLE_LOG=$APP_OUTPUT_PATH/console.log


# 程序相关的配置参数

#APP_OPTIONS="-DmyParam=value1 -DmyParam2=value2"

# 启动参数  

#APP_ARGS="args0 "


# JVM相关的参数，包括内存配置、垃圾回收策略

JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"


# 应用程序忽略的信号列表，以逗号分割，程序shutdown的信号为15（可用 kill -15 pid 可让程序文明的shutdown，请不要在这儿填15）

IGNORE_SIGNALS=1,2

-Xms256m 初始内存大小256m
-Xmx1024m 最大内存 1g
-Xss196k 每个线程的大小196kb
这个要改
Amoeba对线程有要求至少需要217kb
我们取整数改成256

启动Amoeba
1关闭防火墙

      benchmark.classpath  launcher.bat        mkdirhier
benchmark.bat  launcher             launcher.classpath  shutdown
[root@localhost bin]# pwd
/usr/local/src/amoeba/bin
[root@localhost bin]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]

[root@localhost bin]# ./launcher

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.161.134:8066/jtdb2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
jdbc.username=root
jdbc.password=root

http://localhost:8091/index