1 简介

Amoeba(变形虫)项目是一个开源框架,于2008年开始发布一款 Amoeba for Mysql软件;这个软件致力于MySQL的分布式数据库前端代理层,它主要在应用层访问MySQL的时候充当SQL路由功能,专注于分布式数据库代理层(Database Proxy)开发;位于 Client、DB Server(s)之间,对客户端透明;具有负载均衡、高可用性、SQL 过滤、读写分离、可路由相关的请求到目标数据库、可并发请求多台数据库并合并结果;通过Amoeba你能够完成多数据源的高可用、负载均衡、数据切片的功能,目前Amoeba已在很多企业的生产线上面使用;


2 环境准备

mysql使用原有一主两从环境的四台机器,主从环境保持不变

mysql主 192.168.0.201
mysql从 192.168.0.202
mysql从 192.168.0.203
amoeba      192.168.0.204

mysql具体环境安装配置以及主从设置可以参考上一篇文章https://blog.51cto.com/superleedo/2419648

保持原有的一主两从环境正常使用,下面内容主要是amoeba的部署配置及使用


3 amoeba的安装配置


3.1 先安装java
    yum install java-1.8.0-openjdk* -y

[root@localhost ~]# java -version
openjdk version "1.8.0_212"
OpenJDK Runtime Environment (build 1.8.0_212-b04)
OpenJDK 64-Bit Server VM (build 25.212-b04, mixed mode)

  设置环境变量

export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.212.b04-0.el7_6.x86_64
正式环境请在/etc/profile 中声明

3.2 安装amoeba

    下载安装包

两个地址选一个
wget https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip
wget https://jaist.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip

     解压amoeba包:

  unzip amoeba-mysql-3.0.5-RC-distribution.zip
 cd amoeba-mysql-3.0.5-RC

    创建数据库授权用户:

    在master和salve数据库中给amoeba授权一个mysql远程账户(一主两从都要创建账户)

 mysql> GRANT ALL PRIVILEGES ON *.* TO 'amoeba'@'192.168.0.%' IDENTIFIED BY 'amoeba' WITH GRANT OPTION;
 mysql> flush privileges;
 
 并在主库上创建一个test数据库,供默认连接使用
 mysql> create database test;
 Query OK, 1 row affected (0.00 sec)

  3.3 修改配置文件dbServers.xml和amoeba.xml

cd amoeba-mysql-3.0.5-RC/
 
 修改 jvm.properties  根据自己机器内存大小调整  (调整-Xss为512k,默认的太小了)
 JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss512k -XX:PermSize=16m -XX:MaxPermSize=96m"
 
 cd conf/
 修改dbServers.xml
    vim dbServers.xml
        <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>   ## master和slave的数据库端口

                        <!-- mysql schema -->                         <property name="schema">test</property>   ##默认连接的数据库,必须保持之前创建的有

                        <!-- mysql user -->                         <property name="user">amoeba</property>    ##授权的数据库用户

                        <property name="password">amoeba</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">   ##设置定义主机master                 <factoryConfig>                         <!-- mysql ip -->                         <property name="ipAddress">192.168.0.201</property>  ##master对应ip                 </factoryConfig>         </dbServer>

        <dbServer name="slave1"  parent="abstractServer">        ##设置定义主机slave1                 <factoryConfig>                         <!-- mysql ip -->                         <property name="ipAddress">192.168.0.202</property> ##slave1对应ip                 </factoryConfig>         </dbServer>

        <dbServer name="slave2"  parent="abstractServer">        ##设置对应主机slave2                 <factoryConfig>                         <!-- mysql ip -->                         <property name="ipAddress">192.168.0.203</property>  ##slave2对应ip                 </factoryConfig>         </dbServer>

        <dbServer name="readservers" virtual="true">    ##设置只读连接池readservers                 <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">slave1,slave2</property>   ##添加只读主机                 </poolConfig>         </dbServer>     

vim 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.mysql.server.MySQLService">                         <!-- port -->                         <property name="port">8066</property>   ##默认amoeba的连接端口

                        <!-- bind ipAddress -->                         <!--                          <property name="ipAddress">127.0.0.1</property> ##amoeba 的本机ip                          -->

                        <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> ##设置连接8066端口的账户

                                        <property name="password">123456</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>

                        <!-- default charset -->                         <property name="serverCharset">utf8</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">master</property>  ##设置默认连接服务器                 

                <property name="writePool">master</property>   ##设置写服务器                 <property name="readPool">readservers</property> #设置读服务器,连接池

                <property name="needParse">true</property>         </queryRouter> </amoeba:configuration>

----------------------------------

   3.4 启动amoeba

/home/amoeba-mysql-3.0.5-RC/bin/launcher > /var/log/amoeba.log 2>&1 &


    3.5 连接amoeba 验证能否创建查询数据库

[root@localhost amoeba-mysql-3.0.5-RC]# mysql -uroot -p123456 -h127.0.0.1 -P 8066
MySQL [(none)]> create database newtest;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | lili               | | masu               | | mysql              | | newtest            | | performance_schema | | sys                | | test               | | yan                | +--------------------+ 9 rows in set (0.00 sec)

创建成功

MySQL [(none)]> use yan Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed MySQL [yan]> select * from tom; +----+-----------+------+ | id | name      | age  | +----+-----------+------+ |  1 | zhangshan |   20 | |  2 | wangwu    |    7 | |  3 | lisi      |   23 | +----+-----------+------+ 3 rows in set (0.01 sec)

-------------------------------------

    3.6 从机器验证读的负载

先查看从机器Value 值
mysql202> show  global  status like 'Question%'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 56    |
+---------------+-------+
1 row in set (0.01 sec)

mysql203> show  global  status like 'Question%';  +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Questions     | 56    | +---------------+-------+ 1 row in set (0.01 sec)

ameoba执行一次select * from tom;

查看slave机器查询状态 mysql202> show  global  status like 'Question%';  +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Questions     | 57    | +---------------+-------+ 1 row in set (0.00 sec)

mysql203> show  global  status like 'Question%';  +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Questions     | 58    | +---------------+-------+ 1 row in set (0.01 sec)

显然访问的是203,步长为2的即为访问的机器)

经验证,ameoba再执行一次select * from tom;  202的 Value 值变为59,203为59,202的机器是查询访问机器。 由上面可知 读的负载均衡也是成功的。 且主机器上没有步长增加2,说明查询命令都在slave机器上分发执行了,由此推测读写已经分离。

如果想验证写是否真正分离,可以将salve的同步停掉--slave stop 然后在ameoba上执行创建数据库命令,回到三个数据库机器验证,查询下是否只有主机器上有新数据库。