使用amoeba配置mysql读写分离

一，背景介绍：

Amoeba(变形虫)项目，专注 分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户端透明。具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。要想搭建Amoeba读写分离，首先需要知道MySQL的主从配置。具体的架构图如下图：

二，配置所需的环境：

 Amoeba for mysql:192.168.1.28

 Master : 192.168.1.247

 Slave : 192.168.1.245

 database:yazi passwd:root/123456

 JDK1.8.0_51

三，具体的搭建和配置步骤如下：

 1，配置mysql主从复制，具体参照上篇的mysql主从复制的配置

 2，安装配置jdk1.8.0_51以及环境变量

   （1），安装jdk命令: rpm -ivh jdk-8u51-linux-x64.rpm

[root@localhost install]# ll total 481168 -rw-r--r--. 1 root root   8850470 Nov  2 22:43 apache-tomcat-7.0.63.tar.gz -rw-r--r--. 1 root root 137808216 Jan 13 15:09 jdk-8u51-linux-x64.rpm -rw-r--r--. 1 root root  33203321 Nov 20 11:31 mysql-5.6.25.tar.gz -rw-r--r--. 1 root root 312845162 Nov 13 15:19 mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz [root@localhost install]# rpm -ivh jdk-8u51-linux-x64.rpm  Preparing...                ########################################### [100%]    1:jdk1.8.0_51            ########################################### [100%] Unpacking JAR files...         rt.jar...         jsse.jar...         charsets.jar...         tools.jar...         localedata.jar...         jfxrt.jar...         plugin.jar...         javaws.jar...         deploy.jar...

   （2)配置环境变量：

[root@localhost install]# vi /etc/profile

# /etc/profile # System wide environment and startup programs, for login setup # Functions and aliases go in /etc/bashrc # It's NOT a good idea to change this file unless you know what you # are doing. It's much better to create a custom.sh shell script in # /etc/profile.d/ to make custom changes to your environment, as this # will prevent the need for merging in future updates. pathmunge () {     case ":${PATH}:" in         *:"$1":*)             ;;         *)             if [ "$2" = "after" ] ; then                 PATH=$PATH:$1             else                 PATH=$1:$PATH             fi     esac } if [ -x /usr/bin/id ]; then     if [ -z "$EUID" ]; then         # ksh workaround         EUID=`id -u`         UID=`id -ru`     fi     USER="`id -un`"     LOGNAME=$USER     MAIL="/var/spool/mail/$USER" fi # Path manipulation if [ "$EUID" = "0" ]; then     pathmunge /sbin     pathmunge /usr/sbin     pathmunge /usr/local/sbin else     pathmunge /usr/local/sbin after     pathmunge /usr/sbin after     pathmunge /sbin after fi HOSTNAME=`/bin/hostname 2>/dev/null` HISTSIZE=1000 if [ "$HISTCONTROL" = "ignorespace" ] ; then     export HISTCONTROL=ignoreboth else     export HISTCONTROL=ignoredups fi export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL # By default, we want umask to get set. This sets it for login shell # Current threshold for system reserved uid/gids is 200 # You could check uidgid reservation validity in # /usr/share/doc/setup-*/uidgid file if [ $UID -gt 199 ] && [ "`id -gn`" = "`id -un`" ]; then     umask 002 else     umask 022 fi for i in /etc/profile.d/*.sh ; do     if [ -r "$i" ]; then         if [ "${-#*i}" != "$-" ]; then             . "$i"         else             . "$i" >/dev/null 2>&1         fi     fi done unset i unset -f pathmunge export JAVA_HOME=/usr/java/jdk1.8.0_51 export JRE_HOME=/usr/java/jdk1.8.0_51/jre export PATH=$JAVA_HOME/bin:$PATH export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

    （3)配置生效和验证：

[root@FileServerA logs]# source /etc/profile [root@FileServerA logs]# java -version java version "1.8.0_51" Java(TM) SE Runtime Environment (build 1.8.0_51-b16) Java HotSpot(TM) 64-Bit Server VM (build 25.51-b03, mixed mode)

 3，安装配置amoeba

   （1）下载amoeba

(http://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip)

   (2)压缩包无需安装，直接解压即可

[root@localhost install]# unzip amoeba-mysql-3.0.5-RC-distribution.zip  Archive:  amoeba-mysql-3.0.5-RC-distribution.zip    creating: amoeba-mysql-3.0.5-RC/    creating: amoeba-mysql-3.0.5-RC/lib/   inflating: amoeba-mysql-3.0.5-RC/lib/amoeba-core-3.0.5-RC.jar     inflating: amoeba-mysql-3.0.5-RC/lib/log4j-1.2.12.jar     inflating: amoeba-mysql-3.0.5-RC/lib/commons-lang-2.4.jar     inflating: amoeba-mysql-3.0.5-RC/lib/commons-logging-1.1.1.jar     inflating: amoeba-mysql-3.0.5-RC/lib/commons-beanutils-1.8.0.jar     inflating: amoeba-mysql-3.0.5-RC/lib/commons-collections-3.2.1.jar     inflating: amoeba-mysql-3.0.5-RC/lib/ognl-3.0.1.jar     inflating: amoeba-mysql-3.0.5-RC/lib/javassist-3.11.0.GA.jar     inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-pool-1.2.0.jar     inflating: amoeba-mysql-3.0.5-RC/lib/commons-pool-1.6.jar     inflating: amoeba-mysql-3.0.5-RC/lib/slf4j-api-1.6.1.jar     inflating: amoeba-mysql-3.0.5-RC/lib/slf4j-log4j12-1.6.2.jar     inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-bean-1.3.0.jar     inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-util-1.2.0.jar     inflating: amoeba-mysql-3.0.5-RC/lib/dom4j-1.6.1.jar     inflating: amoeba-mysql-3.0.5-RC/lib/xml-apis-1.0.b2.jar     inflating: amoeba-mysql-3.0.5-RC/lib/oro-2.0.8.jar     inflating: amoeba-mysql-3.0.5-RC/lib/commons-digester-1.8.jar     inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-net-io-2.2.0.jar     inflating: amoeba-mysql-3.0.5-RC/lib/commons-cli-1.2.jar     inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-runtime-1.3.0.jar     inflating: amoeba-mysql-3.0.5-RC/lib/spring-core-3.0.6.RELEASE.jar     inflating: amoeba-mysql-3.0.5-RC/lib/spring-asm-3.0.6.RELEASE.jar     inflating: amoeba-mysql-3.0.5-RC/lib/spring-context-3.0.6.RELEASE.jar     inflating: amoeba-mysql-3.0.5-RC/lib/spring-aop-3.0.6.RELEASE.jar     inflating: amoeba-mysql-3.0.5-RC/lib/aopalliance-1.0.jar     inflating: amoeba-mysql-3.0.5-RC/lib/spring-beans-3.0.6.RELEASE.jar     inflating: amoeba-mysql-3.0.5-RC/lib/spring-expression-3.0.6.RELEASE.jar     inflating: amoeba-mysql-3.0.5-RC/lib/plexus-classworlds-2.4.2-HEXNOVA.jar     inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-net-benchmark-1.2.0.jar     inflating: amoeba-mysql-3.0.5-RC/lib/amoeba-mysql-3.0.5-RC.jar      creating: amoeba-mysql-3.0.5-RC/bin/   inflating: amoeba-mysql-3.0.5-RC/bin/benchmark     inflating: amoeba-mysql-3.0.5-RC/bin/launcher     inflating: amoeba-mysql-3.0.5-RC/bin/mkdirhier     inflating: amoeba-mysql-3.0.5-RC/bin/shutdown     inflating: amoeba-mysql-3.0.5-RC/bin/benchmark.bat     inflating: amoeba-mysql-3.0.5-RC/bin/launcher.bat     inflating: amoeba-mysql-3.0.5-RC/bin/benchmark.classpath     inflating: amoeba-mysql-3.0.5-RC/bin/launcher.classpath      creating: amoeba-mysql-3.0.5-RC/conf/   inflating: amoeba-mysql-3.0.5-RC/conf/access_list.conf     inflating: amoeba-mysql-3.0.5-RC/conf/amoeba.dtd     inflating: amoeba-mysql-3.0.5-RC/conf/amoeba.xml     inflating: amoeba-mysql-3.0.5-RC/conf/dbserver.dtd     inflating: amoeba-mysql-3.0.5-RC/conf/dbServers.xml     inflating: amoeba-mysql-3.0.5-RC/conf/function.dtd     inflating: amoeba-mysql-3.0.5-RC/conf/functionMap.xml     inflating: amoeba-mysql-3.0.5-RC/conf/log4j.dtd     inflating: amoeba-mysql-3.0.5-RC/conf/log4j.xml     inflating: amoeba-mysql-3.0.5-RC/conf/rule.dtd     inflating: amoeba-mysql-3.0.5-RC/conf/rule.xml     inflating: amoeba-mysql-3.0.5-RC/conf/ruleFunctionMap.xml      creating: amoeba-mysql-3.0.5-RC/benchmark/   inflating: amoeba-mysql-3.0.5-RC/benchmark/context.xml     inflating: amoeba-mysql-3.0.5-RC/benchmark/objectMap.dtd     inflating: amoeba-mysql-3.0.5-RC/benchmark/query.xml     inflating: amoeba-mysql-3.0.5-RC/jvm.properties      [root@localhost install]# mv amoeba-mysql-3.0.5-RC /usr/local/

  （3）amoeba的配置文件详解：

amoeba.xml：定义前端的控制文件，包括前端的端口，用户名和密码

dbServers.xml：定义后端节点的配置文件。设置数据库，登录mysql的账号和密码

    a,配置amoeba.xml文件

[root@FileServerA conf]# vim amoeba.xml                 <!-- 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>             //***端口号***//                         <!-- 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">123456</property> "amoeba.xml" 91L, 3099C                                                                                      30,6-41        8% <?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>  //设置其他的端口                         <!-- 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">lqb</property>            //***远程访问代理的用户名和密码***//                                         <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">viplqb</property>          //***写的资源池***//                 <property name="needParse">true</property>         </queryRouter> </amoeba:configuration>

      b,配置dbServers.xml文件

[root@FileServerA conf]# vim  dbServers.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="connectionManager">${defaultManager}</property>                         <property name="sendBufferSize">64</property>                         <property name="receiveBufferSize">128</property>                         <!-- mysql port -->                         <property name="port">3306</property>     //***设置mysql访问端口号***//                                   <!-- mysql schema -->                         <property name="schema">yazi</property>  //***设置mysql访问默认库***//                         <!-- mysql user -->                         <property name="user">root</property>    //***登录mysql账号和密码***//                         <property name="password">123456</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和ip地址***//                 <factoryConfig>                         <!-- mysql ip -->                         <property name="ipAddress">192.168.1.247</property>                 </factoryConfig>         </dbServer>         <dbServer name="slave1"  parent="abstractServer">         //***设置slave1和ip地址***//                 <factoryConfig>                     //***如果有多个slave主机复制本配置到下边继续添加ip地址***//                         <!-- mysql ip -->                         <property name="ipAddress">192.168.1.245</property>                 </factoryConfig>         </dbServer>         <dbServer name="viplqb" 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">slave1</property>  //***所有从库的主机都要写上***//                 </poolConfig>         </dbServer> </amoeba:dbServers>

  （4）启动amoeba并查看是否有报错。

[root@FileServerA conf]# /usr/local/amoeba-mysql-3.0.5-RC/bin/launcher & [1] 34774 [root@FileServerA conf]# log4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf 2016-07-04 13:46:11,874 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066. Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0  2016-07-04 13:47:53 [INFO] Project Name=Amoeba-MySQL, PID=32445 , starting... log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/log4j.xml 2016-07-04 13:47:53,604 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA log4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf 2016-07-04 13:47:53,789 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.  2016-07-04 14:22:55 [INFO] ignore signal:HUP Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0  2016-07-04 17:31:08 [INFO] Project Name=Amoeba-MySQL, PID=34779 , starting... log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/log4j.xml 2016-07-04 17:31:08,868 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA log4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf 2016-07-04 17:31:09,128 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

    (5)用其他的服务器来进行登录：

[root@mysqlmaster ~]# mysql -ulqb -p -h192.168.1.28 -P8066     Enter password:  Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 965356058 Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test               | | yazi               | +--------------------+ 5 rows in set (0.01 sec) mysql>

备注：当配置这个文件时amoeba.xml ，其他的主机通过代理就可以访问，如上说明amoeba.xml配置的没有问题。

    （6）验证读写分离，这个需要配置dbServers.xml这个文件了，接下来即为验证读写分离。

   

（a)在没停掉同步之前在主库247上创建一张表；

mysql> use yazi; 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> show tables; +----------------+ | Tables_in_yazi | +----------------+ | sxit           | | test           | +----------------+ 2 rows in set (0.00 sec) mysql> create table lqb (id int(10) ,name varchar(10),address varchar(20));      Query OK, 0 rows affected (0.02 sec)

(b)在从库245上查看，并在slave上停止同步：

mysql> use yazi; Database changed mysql> show tables; +----------------+ | Tables_in_yazi | +----------------+ | lqb            | | sxit           | | test           | +----------------+ 3 rows in set (0.00 sec) mysql> stop slave     -> ; Query OK, 0 rows affected (0.00 sec)

（c)在主从上各插入一条不同的数据

在主库上插入（1，'zhangsan','master');

在从库上插入（2，’lisi','slave);

在主库上执行以下操作：

mysql> insert into lqb values(1,'zhangsan','master'); Query OK, 1 row affected (0.00 sec) mysql> select * from lqb; +------+----------+---------+ | id   | name     | address | +------+----------+---------+ |    1 | zhangsan | master  | +------+----------+---------+ 1 row in set (0.00 sec)

在从库上执行以下操作：

mysql> insert into lqb values(2,'zhangsan','slave');  Query OK, 1 row affected (0.00 sec) mysql> select * from lqb; +------+----------+---------+ | id   | name     | address | +------+----------+---------+ |    2 | zhangsan | slave   | +------+----------+---------+ 1 row in set (0.00 sec)

 （d)登录amoeba服务器查看读操作：显示的是245slave服务器

mysql> select * from lqb;

+------+----------+---------+

| id   | name     | address |

+------+----------+---------+

|    2 | zhangsan | slave   |

+------+----------+---------+

1 row in set (0.00 sec)

（e）在amoeba服务器上测试以下写操作，查看还是245从库上的数据。

mysql> insert into lqb values(3,'wanger','test_write'); Query OK, 1 row affected (0.00 sec) mysql> select * from lqb; +------+----------+---------+ | id   | name     | address | +------+----------+---------+ |    2 | zhangsan | slave   | +------+----------+---------+ 1 row in set (0.00 sec)

在主库247上查询，可以看到已插入进来了

mysql> select * from lqb; +------+----------+------------+ | id   | name     | address    | +------+----------+------------+ |    1 | zhangsan | master     | |    3 | wanger   | test_write | +------+----------+------------+ 2 rows in set (0.00 sec)

在从库上245查询，还是刚才查询

mysql> select * from lqb; +------+----------+---------+ | id   | name     | address | +------+----------+---------+ |    2 | zhangsan | slave   | +------+----------+---------+ 1 row in set (0.00 sec)

至此，数据库读写分离已经配置完成，在正式生产环境中，需将读写主机的定义更改，因为数据库用的最多的还是读的操作。我们可以将读或查询交给从来处理，同样的，我们也可以添加多个从主机。让其自动从不同的从主机上读取数据库。