Amoeba：读写分离

  • javenzhen

    2015-08-30 12:23:58
  • MySQL
  • 原创
1、安装jdk
  1. root@proxy:~# java -version
  2. java version "1.6.0_45"
  3. Java(TM) SE Runtime Environment (build 1.6.0_45-b06)
  4. Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01, mixed mode)
2、配置dbServer.xml
  1. root@proxy:/usr/local/amoeba/conf# cat dbServers.xml
  2. <?xml version="1.0" encoding="gbk"?>

  4. <!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
  5. <amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

  7.                 <!--
  8.                         Each dbServer needs to be configured into a Pool,
  9.                         If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
  10.                          add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
  11.                          such as 'multiPool' dbServer
  12.                 -->

  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">root</property>

  29.                         <!-- mysql password -->
  30.                         <property name="password">111</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="testOnReturn">true</property>
  42.                         <property name="testWhileIdle">true</property>
  43.                 </poolConfig>
  44.         </dbServer>

  46.         <dbServer name="master1" parent="abstractServer">
  47.                 <factoryConfig>
  48.                         <!-- mysql ip -->
  49.                         <property name="ipAddress">10.10.10.40</property>
  50.                 </factoryConfig>
  51.         </dbServer>

  53.         <dbServer name="master2" parent="abstractServer">
  54.                 <factoryConfig>
  55.                         <!-- mysql ip -->
  56.                         <property name="ipAddress">10.10.10.50</property>
  57.                 </factoryConfig>
  58.         </dbServer>

  60.         <dbServer name="slave1" parent="abstractServer">
  61.                 <factoryConfig>
  62.                         <!-- mysql ip -->
  63.                         <property name="ipAddress">10.10.10.41</property>
  64.                 </factoryConfig>
  65.         </dbServer>

  67.         <dbServer name="slave2" parent="abstractServer">
  68.                 <factoryConfig>
  69.                         <!-- mysql ip -->
  70.                         <property name="ipAddress">10.10.10.42</property>
  71.                 </factoryConfig>
  72.         </dbServer>
  73. ####rp池配置的是salve1和slave2节点
  74.         <dbServer name="rp" virtual="true">
  75.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
  76.                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
  77.                         <property name="loadbalance">1</property>

  79.                         <!-- Separated by commas,such as: server1,server2,server1 -->
  80.                         <property name="poolNames">slave1,slave2</property>
  81.                 </poolConfig>
  82.         </dbServer>
  83. ####wp池配置的是master1和master2节点
  84.         <dbServer name="wp" virtual="true">
  85.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
  86.                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
  87.                         <property name="loadbalance">1</property>

  89.                         <!-- Separated by commas,such as: server1,server2,server1 -->
  90.                         <property name="poolNames">master1,master2</property>
  91.                 </poolConfig>
  92.         </dbServer>

  94. </amoeba:dbServers>
3、配置amoeba.xml
  1. root@proxy:/usr/local/amoeba/conf# cat amoeba.xml
  2. <?xml version="1.0" encoding="gbk"?>

  4. <!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
  5. <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

  7.         <proxy>

  9.                 <!-- service class must implements com.meidusa.amoeba.service.Service -->
  10.                 <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
  11.                         <!-- port -->
  12.                         <property name="port">8066</property>

  14.                         <!-- bind ipAddress -->
  15.                         <!--
  16.                         <property name="ipAddress">127.0.0.1</property>
  17.                          -->

  19.                         <property name="manager">${clientConnectioneManager}</property>

  21.                         <property name="connectionFactory">
  22.                                 <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
  23.                                         <property name="sendBufferSize">128</property>
  24.                                         <property name="receiveBufferSize">64</property>
  25.                                 </bean>
  26.                         </property>

  28.                         <property name="authenticator">
  29.                                 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

  31.                                         <property name="user">root</property>

  33.                                         <property name="password">root</property>

  35.                                         <property name="filter">
  36.                                                 <bean class="com.meidusa.amoeba.server.IPAccessController">
  37.                                                         <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
  38.                                                 </bean>
  39.                                         </property>
  40.                                 </bean>
  41.                         </property>

  43.                 </service>

  45.                 <!-- server class must implements com.meidusa.amoeba.service.Service -->
  46.                 <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
  47.                         <!-- port -->
  48.                         <!-- default value: random number
  49.                         <property name="port">9066</property>
  50.                         -->
  51.                         <!-- bind ipAddress -->
  52.                         <property name="ipAddress">127.0.0.1</property>
  53.                         <property name="daemon">true</property>
  54.                         <property name="manager">${clientConnectioneManager}</property>
  55.                         <property name="connectionFactory">
  56.                                 <bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
  57.                         </property>

  59.                 </service>

  61.                 <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
  62.                         <!-- proxy server net IO Read thread size -->
  63.                         <property name="readThreadPoolSize">20</property>

  65.                         <!-- proxy server client process thread size -->
  66.                         <property name="clientSideThreadPoolSize">30</property>

  68.                         <!-- mysql server data packet process thread size -->
  69.                         <property name="serverSideThreadPoolSize">30</property>

  71.                         <!-- per connection cache prepared statement size -->
  72.                         <property name="statementCacheSize">500</property>

  74.                         <!-- query timeout( default: 60 second , TimeUnit:second) -->
  75.                         <property name="queryTimeout">60</property>
  76.                 </runtime>

  78.         </proxy>

  80.         <!--
  81.                 Each ConnectionManager will start as thread
  82.                 manager responsible for the Connection IO read , Death Detection
  83.         -->
  84.         <connectionManagerList>
  85.                 <connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
  86.                         <property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
  87.                         <!--
  88.                           default value is avaliable Processors
  89.                         <property name="processors">5</property>
  90.                          -->
  91.                 </connectionManager>
  92.                 <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
  93.                         <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

  95.                         <!--
  96.                           default value is avaliable Processors
  97.                         <property name="processors">5</property>
  98.                          -->
  99.                 </connectionManager>
  100.         </connectionManagerList>

  102.                 <!-- default using file loader -->
  103.         <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
  104.                 <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
  105.         </dbServerLoader>

  107.         <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. ####default池只配置了master1节点
  115.                 <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
  116.                 <property name="LRUMapSize">1500</property>
  117.                 <property name="defaultPool">master1</property>

  119.                 <property name="writePool">wp</property>
  120.                 <property name="readPool">rp</property>

  122.                 <property name="needParse">true</property>
  123.         </queryRouter>
  124. </amoeba:configuration>
4、启动amoeba
  1. root@proxy:/usr/local/amoeba# bin/amoeba start &
  2. [1] 2511
  3. root@proxy:/usr/local/amoeba#
  4. The stack size specified is too small, Specify at least 160k
  5. Could not create the Java virtual machine.

  7. [1]+ Exit 1 bin/amoeba start
更改bin/amoeba文件
  1. DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"
  2. 更改为
  3. DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"
启动正常之后，连接amoeba
  1. root@proxy:~# mysql -uroot -p -h10.10.10.39 -P8066
  2. Enter password:
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id is 307988519
  5. Server version: 5.1.45-mysql-amoeba-proxy-2.2.0

  7. Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.

  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  15. java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
  16. [root@10.10.10.39:(none)][11:46:57pm] MySQL-> at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1191)
  17.         at com.meidusa.amoeba.net.poolable.GenericObjectPool.borrowObject(GenericObjectPool.java:381)
  18.         at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:633)
  19.         at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
  20.         at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
  21.         at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
  22.         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
  23.         at java.lang.Thread.run(Thread.java:662)
连接报错，查看root.log的输出
  1. 2015-08-29 16:01:03,299 INFO net.BackendConnectionFactory - open socket channel to server[10.10.10.40:3306]
  2. 2015-08-29 16:01:03,303 ERROR net.MysqlServerConnection - handShake with /10.10.10.40:3306 error:Access denied for user 'root'@'10.10.10.39' (using password: NO),hashCode=661272757
  3. 2015-08-29 16:01:03,324 INFO net.BackendConnectionFactory - open socket channel to server[10.10.10.50:3306]
  4. 2015-08-29 16:01:03,327 ERROR net.MysqlServerConnection - handShake with /10.10.10.50:3306 error:Access denied for user 'root'@'10.10.10.39' (using password: NO),hashCode=505588567
  5. 2015-08-29 16:01:03,331 INFO net.BackendConnectionFactory - open socket channel to server[10.10.10.41:3306]
  6. 2015-08-29 16:01:03,334 ERROR net.MysqlServerConnection - handShake with /10.10.10.41:3306 error:Access denied for user 'root'@'10.10.10.39' (using password: NO),hashCode=530654357
  7. 2015-08-29 16:01:03,335 INFO net.BackendConnectionFactory - open socket channel to server[10.10.10.42:3306]
  8. 2015-08-29 16:01:03,338 ERROR net.MysqlServerConnection - handShake with /10.10.10.42:3306 error:Access denied for user 'root'@'10.10.10.39' (using password: NO),hashCode=848649429
查看数据库连接权限
  1. [root@localhost:mysql][11:55:29pm] MySQL-> select host,user from user;
  2. +-----------+----------+
  3. | host | user |
  4. +-----------+----------+
  5. | % | rep_user |
  6. | % | root |
  7. | 127.0.0.1 | root |
  8. | ::1 | root |
  9. | localhost | |
  10. | localhost | root |
  11. | ubuntu | |
  12. | ubuntu | root |
  13. +-----------+----------+
  14. 8 rows in set (0.00 sec)
数据库权限正常，使用客户端连接测试
  1. root@proxy:~# mysql -uroot -p -h10.10.10.40
  2. Enter password:
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id is 417
  5. Server version: 5.6.19-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

  7. Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.

  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  15. [root@10.10.10.40:(none)][11:59:09pm] MySQL-> use test;
  16. Reading table information for completion of table and column names
  17. You can turn off this feature to get a quicker startup with -A

  19. Database changed
  20. [root@10.10.10.40:test][11:59:12pm] MySQL-> show tables;
  21. +----------------+
  22. | Tables_in_test |
  23. +----------------+
  24. | t1 |
  25. +----------------+
  26. 1 row in set (0.00 sec)
客户端连接也正常，猜想应该是amoeba配置有问题
  1.                         <!-- mysql password
  2.                         <property name="password">111</property>
  3.                         -->
发现密码被注释了，更改
  1.                         <!-- mysql password -->
  2.                         <property name="password">111</property>
启动一切正常

  1. root@proxy:~# mysql -uroot -p -h10.10.10.39 -P8066
  2.     Enter password:
  3.     Welcome to the MySQL monitor. Commands end with ; or \g.
  4.     Your MySQL connection id is 1113757163
  5.     Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MySQL Enterprise Server - Advanced Edition (Commercial)

  7.     Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

  9.     Oracle is a registered trademark of Oracle Corporation and/or its
  10.     affiliates. Other names may be trademarks of their respective
  11.     owners.

  13.     Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  15.     [root@10.10.10.39:(none)][12:06:15am] MySQL-> use test;
  16.     Database changed
  17.     [root@10.10.10.39:test][12:06:18am] MySQL->
  18.     [root@10.10.10.39:test][12:06:18am] MySQL-> show tables;
  19.     +----------------+
  20.     | Tables_in_test |
  21.     +----------------+
  22.     | t1 |
  23.     +----------------+
  24.     1 row in set (0.00 sec)
5、测试读写分离
停止master2，slave1，slave2节点的复制
  1. [root@localhost:(none)][12:27:38am] MySQL-> stop slave;
  2. Query OK, 0 rows affected (0.04 sec)

  4. [root@localhost:(none)][12:27:53am] MySQL-> show slave status \G
  5. *************************** 1. row ***************************
  6.                Slave_IO_State:
  7.                   Master_Host: 10.10.10.40
  8.                   Master_User: rep_user
  9.                   Master_Port: 3306
  10.                 Connect_Retry: 60
  11.               Master_Log_File: master1-bin.000007
  12.           Read_Master_Log_Pos: 442
  13.                Relay_Log_File: slave-relay-bin.000008
  14.                 Relay_Log_Pos: 607
  15.         Relay_Master_Log_File: master1-bin.000007
  16.              Slave_IO_Running: No
  17.             Slave_SQL_Running: No
使用amoeba连接，往测试表t1中插入测试数据
  1. root@proxy:~# mysql -uroot -p -h10.10.10.39 -P8066
  2. Enter password:
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id is 750410715
  5. Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MySQL Enterprise Server - Advanced Edition (Commercial)

  7. Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.

  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  15. [root@10.10.10.39:(none)][12:25:49am] MySQL-> use test;
  16. Database changed
  17. [root@10.10.10.39:test][12:26:51am] MySQL-> insert into t1 values (null,'aaaaa');
  18. Query OK, 1 row affected (0.05 sec)
master1查看表t1

  1. [root@localhost:mysql][11:27:16pm] MySQL-> select * from test.t1;
  2. +----+-------+
  3. | id | name |
  4. +----+-------+
  5. | 1 | aaaaa |
  6. +----+-------+
  7. 1 row in set (0.00 sec)
在master2，slave1，slave2上查看表t1

  1. [root@localhost:(none)][12:28:28am] MySQL-> use test;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A

  5. Database changed
  6. [root@localhost:test][12:29:17am] MySQL-> select * from t1;
  7. Empty set (0.00 sec)
在proxy上查看表t1表的数据

  1. [root@10.10.10.39:test][12:30:01am] MySQL-> select * from t1;
  2. Empty set (0.05 sec)
在proxy上无法查看插入的数据，说明读是在rp池里，而rp池只有slave1和slave2，数据没有同步到从节点。
请使用浏览器的分享功能分享到微信等