首先说明一下amoeba 跟 MySQL proxy在读写分离的使用上面的区别：
在MySQL proxy 6.0版本 上面如果想要读写分离并且 读集群、写集群 机器比较多情况下，用mysql proxy 需要相当大的工作量，目前mysql proxy没有现成的 lua脚本。mysql proxy根本没有配置文件， lua脚本就是它的全部，当然lua是相当方便的。那么同样这种东西需要编写大量的脚本才能完成一 个复杂的配置。而Amoeba只需要进行相关的配置就可以满足需求。

Mysql 基于 Amoeba 的 读写分离_mysql
假设有这样的使用场景，有三个数据库节点分别命名为Master、Slave1、Slave2如下：
Amoeba: Amoeba <192.168.14.129>
Master： Master <192.168.14.131> （可读写）
Slaves：Slave1 <192.168.14.132>、Slave2<192.168.14.133> （2个平等的数据库。只读/负载均衡）
在 主从数据库 的复制的部分， 任然需要使用数据库自己的复制机制。 Amoeba 不提供复制功能。
1. 起动数据库的主从复制功能。
a. 修改配置文件
master.cnf
Cnf代码
  1. server-id = 1 #主数据库标志
  3. # 增加 日志文件， 用于验证读写分离
  4. log = /home/mysql/mysql/log/mysql.log
slave1.cnf
Cnf代码
  1. server-id = 2
  3. # 增加 日志文件， 用于验证读写分离
  4. log = /home/mysql/mysql/log/mysql.log
slave2.cnf
Cnf代码
  1. server-id = 3
  3. # 增加 日志文件， 用于验证读写分离
  4. log = /home/mysql/mysql/log/mysql.log
b. Master 中 创建两个 只读权限 的用户。 用户名均为：repl_user 密码均为：copy 分别开放给 slave1， slave2
Sql代码
  1. mysql> grant replication slave on *.* to repl_user@192.168.14.132 identified by 'copy';
  3. mysql> grant replication slave on *.* to repl_user@192.168.14.133 identified by 'copy';
c. 查看 Master 信息
Sql代码
  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000017 | 2009 | | |
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
d. Slave1 ，Slave2 中 启动 Master - Slave 复制功能。
分别执行以下命令
Sql代码
  1. mysql> slave stop;
  2. Query OK, 0 rows affected (0.02 sec)
  4. mysql> change master to
  5. -> master_host='192.168.14.131',
  6. -> master_user='repl_user',
  7. -> master_password='copy',
  8. -> master_log_file='mysql-bin.000017',
  9. -> master_log_pos=2009;
  10. Query OK, 0 rows affected (0.03 sec)
  12. mysql> start slave;
  13. Query OK, 0 rows affected (0.00 sec)
2. Amoeba 读写分离的配置
a. Master ， Slave1 ，Slave2 中开放权限给 Amoeba 访问。
在 Master ， Slave1 ， Slave2 中分别执行
Sql代码
  1. mysql> grant all on test.* to test_user@192.168.14.129 indentified by '1234';
Amoeba 访问三个数据库的账号密码相同。
b. 修改 Amoeba 的配置文件
配置文件详细说明请查看 官方文档：http://docs.hexnova.com/amoeba/rw-splitting.html
dbServer.xml
Xml代码
  1. <?xml version="1.0" encoding="gbk"?>
  3. <!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
  4. <amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">
  6. <!--
  7. Each dbServer needs to be configured into a Pool,
  8. If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
  9. add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
  10. such as 'multiPool' dbServer
  11. -->
  13. <!-- 数据库连接配置的公共部分 -->
  14. <dbServer name="abstractServer" abstractive="true">
  15. <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
  16. <property name="manager">${defaultManager}</property>
  17. <property name="sendBufferSize">64</property>
  18. <property name="receiveBufferSize">128</property>
  20. <!-- mysql port -->
  21. <property name="port">3306</property>
  23. <!-- mysql schema -->
  24. <property name="schema">test</property>
  26. <!-- mysql user -->
  27. <property name="user">test_user</property>
  29. <!-- mysql password -->
  30. <property name="password">1234</property>
  32. </factoryConfig>
  34. <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
  35. <property name="maxActive">500</property>
  36. <property name="maxIdle">500</property>
  37. <property name="minIdle">10</property>
  38. <property name="minEvictableIdleTimeMillis">600000</property>
  39. <property name="timeBetweenEvictionRunsMillis">600000</property>
  40. <property name="testOnBorrow">true</property>
  41. <property name="testWhileIdle">true</property>
  42. </poolConfig>
  43. </dbServer>
  45. <!-- Master ，Slave1， Slave2 的独立部分，也就只有 IP 了 -->
  46. <dbServer name="master" parent="abstractServer">
  47. <factoryConfig>
  48. <!-- mysql ip -->
  49. <property name="ipAddress">192.168.14.131</property>
  50. </factoryConfig>
  51. </dbServer>
  53. <dbServer name="slave1" parent="abstractServer">
  54. <factoryConfig>
  55. <!-- mysql ip -->
  56. <property name="ipAddress">192.168.14.132</property>
  57. </factoryConfig>
  58. </dbServer>
  60. <dbServer name="slave2" parent="abstractServer">
  61. <factoryConfig>
  62. <!-- mysql ip -->
  63. <property name="ipAddress">192.168.14.133</property>
  64. </factoryConfig>
  65. </dbServer>
  67. <!-- 数据库池，虚拟服务器，实现读取的负载均衡 -->
  68. <dbServer name="slaves" virtual="true">
  69. <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
  70. <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
  71. <property name="loadbalance">1</property>
  73. <!-- Separated by commas,such as: server1,server2,server1 -->
  74. <property name="poolNames">slave1,slave2</property>
  75. </poolConfig>
  76. </dbServer>
  78. </amoeba:dbServers>
amoeba.xml
Xml代码
  1. <?xml version="1.0" encoding="gbk"?>
  3. <!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
  4. <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
  6. <proxy>
  8. <!-- service class must implements com.meidusa.amoeba.service.Service -->
  9. <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
  10. <!-- Amoeba 端口号 -->
  11. <property name="port">8066</property>
  13. <!-- bind ipAddress -->
  14. <!--
  15. <property name="ipAddress">127.0.0.1</property>
  16. -->
  18. <property name="manager">${clientConnectioneManager}</property>
  20. <property name="connectionFactory">
  21. <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
  22. <property name="sendBufferSize">128</property>
  23. <property name="receiveBufferSize">64</property>
  24. </bean>
  25. </property>
  27. <property name="authenticator">
  28. <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
  29. <!-- Amoeba 账号，密码 -->
  30. <property name="user">root</property>
  32. <property name="password">root</property>
  34. <property name="filter">
  35. <bean class="com.meidusa.amoeba.server.IPAccessController">
  36. <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
  37. </bean>
  38. </property>
  39. </bean>
  40. </property>
  42. </service>
  44. <!-- server class must implements com.meidusa.amoeba.service.Service -->
  45. <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
  46. <!-- port -->
  47. <!-- default value: random number
  48. <property name="port">9066</property>
  49. -->
  50. <!-- bind ipAddress -->
  51. <property name="ipAddress">127.0.0.1</property>
  52. <property name="daemon">true</property>
  53. <property name="manager">${clientConnectioneManager}</property>
  54. <property name="connectionFactory">
  55. <bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
  56. </property>
  58. </service>
  60. <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
  61. <!-- proxy server net IO Read thread size -->
  62. <property name="readThreadPoolSize">20</property>
  64. <!-- proxy server client process thread size -->
  65. <property name="clientSideThreadPoolSize">30</property>
  67. <!-- mysql server data packet process thread size -->
  68. <property name="serverSideThreadPoolSize">30</property>
  70. <!-- per connection cache prepared statement size -->
  71. <property name="statementCacheSize">500</property>
  73. <!-- query timeout( default: 60 second , TimeUnit:second) -->
  74. <property name="queryTimeout">60</property>
  75. </runtime>
  77. </proxy>
  79. <!--
  80. Each ConnectionManager will start as thread
  81. manager responsible for the Connection IO read , Death Detection
  82. -->
  83. <connectionManagerList>
  84. <connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
  85. <property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
  86. <!--
  87. default value is avaliable Processors
  88. <property name="processors">5</property>
  89. -->
  90. </connectionManager>
  91. <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
  92. <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
  94. <!--
  95. default value is avaliable Processors
  96. <property name="processors">5</property>
  97. -->
  98. </connectionManager>
  99. </connectionManagerList>
  101. <!-- default using file loader -->
  102. <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
  103. <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
  104. </dbServerLoader>
  106. <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
  108. <property name="ruleLoader">
  109. <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
  110. <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
  111. <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
  112. </bean>
  113. </property>
  114. <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
  116. <property name="LRUMapSize">1500</property>
  117. <!-- 默认数据库，主数据库 -->
  118. <property name="defaultPool">master</property>
  120. <!-- 写数据库 -->
  121. <property name="writePool">master</property>
  122. <!-- 读数据库，dbServer.xml 中配置的 虚拟数据库，数据库池 -->
  123. <property name="readPool">slaves</property>
  125. <property name="needParse">true</property>
  126. </queryRouter>
  127. </amoeba:configuration>