Amoeba for mysql实现mysql读写分离

环境描述：
数据库Master主服务器：192.168.18.157
数据库Slave从服务器：192.168.18.156
Amoeba 服务器：192.168.18.157

mysql主从复制配置

因为读写分离，所以一台负责mysql的写操作，另一台负责mysql的读操作，所以我们这里使用mysql的主从复制再合适不过了。关于这一配置，省略。。。

java环境配置

下载完成后执行./jdk-6u29-linux-x64.bin开始安装，将会安装到/usr/local/java目录。
    配置环境变量
        vi /etc/profile

         JAVA_HOME=/usr/local/java
         export JAVA_HOME
         PATH=$JAVA_HOME/bin:$PATH
    生效：source /etc/profile

Amoeba的安装

下面是安装步骤：

1. mkdir /usr/local/amoeba
2. wget http://softlayer.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz
3. tar -zxvf amoeba-mysql-binary-2.1.0-RC5.tar.gz
   

Amoeba for mysql配置

配置Amoeba for mysql的读写分离主要涉及两个文件：
1、/usr/local/amoeba/conf/dbServers.xml
此文件定义由Amoeba代理的数据库如何连接，比如最基础的：主机IP、端口、Amoeba使用的用户名和密码等等。
2、/usr/local/amoeba/conf/amoeba.xml
此文件定义了Amoeba代理的相关配置。

dbServers.xml文件配置

abstractServer配置:

     <?xml version="1.0" encoding="gbk"?>

                <!--
                        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  
                --&gt

       
               
                        ${defaultManager}
                        64
                        128

                        <!-- mysql port --&gt
                        3306

                        <!-- mysql schema --&gt
                        test

                        <!-- mysql user --&gt
                        root

                        <!--  mysql password --&gt
                        123

               

               
                        500
                        500
                        10
                        600000
                        600000
                        true
                        true
               
       

       
               
                        <!-- mysql ip --&gt
                        192.168.18.157
               
       

       
               
                        <!-- mysql ip --&gt
                        192.168.18.156
               
       

       
               
                        <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--&gt
                        1

                        <!-- Separated by commas,such as: server1,server2,server1 --&gt
                        Slave
               
       

此部分定义主服务器，从服务器，及从服务器连接池。这里只定义数据库地址，它们的用户及密码就是上面的abstractServer里的设置。注意用来连接真实mysql服务器的用户必须拥有远程连接权限。

amoeba.xml配置

amoeba连接验证配置：

     <?xml version="1.0" encoding="gbk"?>

       

                <!-- service class must implements com.meidusa.amoeba.service.Service --&gt
               
                        <!-- port --&gt
                        8066

                        <!-- bind ipAddress --&gt
                        <!--
                        192.168.18.157
                         --&gt

                        ${clientConnectioneManager}

                       
                               
                                        128
                                        64
                               
                       

                       
                               

                                        root

                                        root

                                       
                                               
                                                        ${amoeba.home}/conf/access_list.conf
                                               
                                       
                               
                       

               

                <!-- server class must implements com.meidusa.amoeba.service.Service --&gt
               
                        <!-- port --&gt
                        <!--  default value: random number
                        9066
                        --&gt
                        <!-- bind ipAddress --&gt
                        127.0.0.1
                        true
                        ${clientConnectioneManager}
                       
                               
                       

               

               
                        <!-- proxy server net IO Read thread size --&gt
                        20

                        <!-- proxy server client process thread size --&gt
                        30

                        <!-- mysql server data packet process thread size --&gt
                        30

                        <!-- per connection cache prepared statement size  --&gt
                        500

                        <!-- query timeout( default: 60 second , TimeUnit:second) --&gt
                        60
               

       

        <!--
                Each ConnectionManager will start as thread
                manager responsible for the Connection IO read , Death Detection
        --&gt
       
               
                        com.meidusa.amoeba.net.ConnectionManager
                        <!--
                          default value is avaliable Processors
                        5
                         --&gt
               
               
                        com.meidusa.amoeba.net.AuthingableConnectionManager

                        <!--
                          default value is avaliable Processors
                        5
                         --&gt
               
       

                <!-- default using file loader --&gt
       
                ${amoeba.home}/conf/dbServers.xml
       

       
               
                       
                                ${amoeba.home}/conf/rule.xml
                                ${amoeba.home}/conf/ruleFunctionMap.xml
                       
               
                ${amoeba.home}/conf/functionMap.xml
                1500
                Master

                <!-- 读写分离配置 --&gt
                Master
                Slave
                true
       

defaultPool:配置了默认的数据库节点，一些除了SELECTUPDATEINSERTDELETE的语句都会在defaultPool执行。
writePool :配置了数据库写库，通常配为Master，如这里就配置为之前定义的Master数据库。
readPool :配置了数据库读库，通常配为Slave或者Slave组成的数据库池，如这里就配置之前的virtualSlave数据库池。

amoeba启动

启动命令：

1. /usr/local/amoeba/bin/amoeba start

此命令以前台的方式启动，会输出启动时的信息，检查没有错误信息后，中断，并后台运行：

1. amoeba start &

FAQ

1、无法正常连接？
     [root@db ~]# /usr/local/mysql/bin/mysql -uroot -proot -h192.168.18.157 -P8066
     Welcome to the MySQL monitor.  Commands end with ; or \g.
     Your MySQL connection id is 924577137
     Server version: 5.1.45-mysql-amoeba-proxy-2.1.0-RC5

     Copyright (c) 2000, 2014, 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>
     mysql>
     mysql> show databaees;
     ERROR 2006 (HY000): MySQL server has gone away
     No connection. Trying to reconnect...
     Connection id:    924577137
     Current database: *** NONE ***

     ERROR 2013 (HY000): Lost connection to MySQL server during query
     mysql> show databases;
     ERROR 2006 (HY000): MySQL server has gone away
     No connection. Trying to reconnect...
     Connection id:    924577137
     Current database: *** NONE ***

                    错误原因：

                                    dbServers.xml 中
                         <!--  mysql password
                                             password
                          --&gt
                              默认是被注释掉的，设置了密码后没有把注释放开导致的。只需要把注释去掉就可以了
 2、如何配置网站数据库连接文件?
          默认的端口应该为8066,用户及密码在amoeba.xml里设置。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28881244/viewspace-1402983/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28881244/viewspace-1402983/