lSky's blog

Amoeba实现mysql读写分离

发表于 | 分类于 |

Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。
Amoeba相当于一个SQL请求的路由器，目的是为负载均衡、读写分离、高可用性提供机制，而不是完全实现它们。用户需要结合使用MySQL的 Replication等机制来实现副本同步等功能。amoeba对底层数据库连接管理和路由实现也采用了可插拨的机制，第三方可以开发更高级的策略类来替代作者的实现。

这里写图片描述
Tips:slave上涉及到复制的数据库不可以写,避免master和slave数据不一致

环境

两台centos6.5服务器
master ip： 192.168.44.137
slave ip：192.168.44.139
Amoeba置于master服务器上，也可单独放置

一．安装Amoeba

http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip
首先需要java环境

tar -xzvf amoeba-mysql-3.0.5-RC-distribution.zip

mv amoeba-mysql-3.0.5-RC /opt/amoeba

vim /etc/profile

1
2
export AMOEBA_HOME=/opt/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

若启动报错：The stack size specified is too small, Specify at least 228k
通过属性文件修改JVM参数

vim jvm.properties

1
2
3
JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
修改为：
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"

二．配置Amoeba

按照http://blog.csdn.net/tshangshi/article/details/52799934，先完成mysql的主从备份部署

1.首先数据库需要拥有一个可远程登陆的用户

1
2
mysql> grant all privileges on *.* to amoeba@'%' identified by '1234';
mysql> flush privlieges;

2.修改dbServers.xml配置文件

vim conf/dbServers.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">
    <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">ylyg</property>
            <!-- mysql user  远程用户和密码-->
            <property name="user">amoeba</property>
            <property name="password">1234</property>
        </factoryConfig>
        <poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
            <property name="maxActive">200</property>
            <property name="maxIdle">200</property>
            <property name="minIdle">10</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>
<!-- 主库master-->
    <dbServer name="master"  parent="abstractServer">
        <factoryConfig>
            <!-- mysql ip -->
            <property name="ipAddress">127.0.0.1</property>
        </factoryConfig>
    </dbServer>
<!-- 从库slave-->
    <dbServer name="slave"  parent="abstractServer">
        <factoryConfig>
            <!-- mysql ip -->
            <property name="ipAddress">192.168.44.139</property>
        </factoryConfig>
    </dbServer>
 <!--slave从库池.multiPool,如果多个slave则在poolNames里增加，以，隔开-->
    <dbServer name="multiPool" 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">slave</property>
        </poolConfig>
    </dbServer>
</amoeba:dbServers>

3.配置amoeba.xml主配置文件

vim conf/amoeba.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
<?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 对外提供程序访问的IP地址，也就是amoeba机器对外访问的IP地址 -->
            <!-- 
            <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">
<!--这里的用户和密码为登入amoeba的用户密码，而不是主从数据库的 -->
                    <property name="user">test</property>
                    <property name="password">redhat</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">multiPool</property>
        <property name="needParse">true</property>
    </queryRouter>
</amoeba:configuration>

4.启动amoeba

launcher &

登入amoeba

mysql -utest -predhat -h127.0.0.1 -P8066

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2139479565
Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution
Copyright (c) 2000, 2013, 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 tables;
mysql> create table user(id int(5),name char(10));
在主从数据库均正常打开的情况下
mysql> insert into user values (1,'zhanglong');
mysql> select * from user;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhanglong |
+------+-----------+
1 row in set (0.01 sec)
在从服务器关闭（stop slave）的情况下
mysql> insert into user values (2,'zhangfei');
mysql> select * from user；
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhanglong |
+------+-----------+
1 row in set (0.02 sec)
可以发现没有更新，再在主从数据库分别查看