Mysql 基于 Amoeba 的 读写分离(2)

<?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.net.ServerableConnectionManager"><!-- Amoeba 端口号 --><property name="port">8066</property><!-- bind ipAddress --><!-- <property name="ipAddress">127.0.0.1</property>--><property name="manager">${clientConnectioneManager}</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="authenticator"><bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"><!-- Amoeba 账号，密码 --><property name="user">root</property><property name="password">root</property><property name="filter"><bean class="com.meidusa.amoeba.server.IPAccessController"><property name="ipFile">${amoeba.home}/conf/access_list.conf</property></bean></property></bean></property></service><!-- server class must implements com.meidusa.amoeba.service.Service --><service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer"><!-- port --><!--  default value: random number<property name="port">9066</property>--><!-- bind ipAddress --><property name="ipAddress">127.0.0.1</property><property name="daemon">true</property><property name="manager">${clientConnectioneManager}</property><property name="connectionFactory"><bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean></property></service><runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext"><!-- proxy server net IO Read thread size --><property name="readThreadPoolSize">20</property><!-- proxy server client process thread size --><property name="clientSideThreadPoolSize">30</property><!-- mysql server data packet process thread size --><property name="serverSideThreadPoolSize">30</property><!-- per connection cache prepared statement size  --><property name="statementCacheSize">500</property><!-- query timeout( default: 60 second , TimeUnit:second) --><property name="queryTimeout">60</property></runtime></proxy><!-- Each ConnectionManager will start as threadmanager responsible for the Connection IO read , Death Detection--><connectionManagerList><connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper"><property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property><!-- default value is avaliable Processors <property name="processors">5</property>--></connectionManager><connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper"><property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property><!-- default value is avaliable Processors <property name="processors">5</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><!-- 读数据库，dbServer.xml 中配置的 虚拟数据库，数据库池 --><property name="readPool">slaves</property><property name="needParse">true</property></queryRouter>
</amoeba:configuration>

<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">
<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/"><tableRule name="message" schema="test" defaultPools="server1"></tableRule>
</amoeba:rule>

tail -f ./log/mysql.log

[mysql@prx1 mysql]$ tail -f log/mysql.log370 Query     SET SESSION sql_mode=''370 Query     SET NAMES utf8370 Query     SHOW FULL TABLES370 Query     SHOW COLUMNS FROM `t_message`370 Query     SHOW COLUMNS FROM `t_user`370 Query     SHOW PROCEDURE STATUS370 Query     SHOW FUNCTION STATUS
110813 15:21:11   370 Query     SHOW VARIABLES LIKE 'character_set_server'370 Query     SHOW FULL COLUMNS FROM `test`.`t_message`
110813 15:21:12   370 Query     SHOW CREATE TABLE `test`.`t_message`
110813 15:22:40   374 Connect   test_user@192.168.14.129 on test375 Connect   test_user@192.168.14.129 on test376 Connect   test_user@192.168.14.129 on test
110813 15:23:40   370 Query     insert into t_message values(1, 'c1')
110813 15:24:07   377 Connect   test_user@192.168.14.129 on test378 Connect   test_user@192.168.14.129 on test379 Connect   test_user@192.168.14.129 on test
110813 15:24:15   370 Query     insert into t_user values(8, 'n8', 'p8')
110813 15:24:24   370 Query     SHOW FULL COLUMNS FROM `test`.`t_user`370 Query     SHOW CREATE TABLE `test`.`t_user`
110813 15:24:35   370 Query     SHOW FULL COLUMNS FROM `test`.`t_message`370 Query     SHOW CREATE TABLE `test`.`t_message`

[mysql@prx2 mysql]$ tail -f log/mysql.log317 Connect   test_user@192.168.14.129 on test318 Connect   test_user@192.168.14.129 on test
110813 15:35:30   315 Query     SELECT @@sql_mode
110813 15:35:32   315 Query     SELECT @@sql_mode
110813 15:35:44   315 Query     SELECT @@SQL_MODE
110813 15:35:46   315 Query     SELECT @@SQL_MODE
110813 15:37:18   319 Connect   test_user@192.168.14.129 on test320 Connect   test_user@192.168.14.129 on test
110813 15:37:19   321 Connect   test_user@192.168.14.129 on test
110813 15:37:26   246 Quit
110813 15:38:21   315 Query     SELECT @@SQL_MODE
110813 15:38:22    42 Query     BEGIN42 Query     insert into t_message values(1, 'c1')42 Query     COMMIT /* implicit, from Xid_log_event */
110813 15:38:50   322 Connect   test_user@192.168.14.129 on test323 Connect   test_user@192.168.14.129 on test324 Connect   test_user@192.168.14.129 on test
110813 15:38:58    42 Query     BEGIN42 Query     insert into t_user values(8, 'n8', 'p8')42 Query     COMMIT /* implicit, from Xid_log_event */
110813 15:39:08   315 Query     SELECT @@SQL_MODE
110813 15:39:19   315 Query     SELECT @@SQL_MODE
110813 15:44:08   325 Connect   test_user@192.168.14.129 on test326 Connect   test_user@192.168.14.129 on test327 Connect   test_user@192.168.14.129 on test

[mysql@prx3 mysql]$ tail -f log/mysql.log
110813 15:35:08   305 Connect   test_user@192.168.14.129 on test306 Connect   test_user@192.168.14.129 on test307 Connect   test_user@192.168.14.129 on test
110813 15:35:31   304 Query     SELECT @@sql_mode304 Query     SELECT @@sql_mode
110813 15:35:44   304 Query     SELECT @@SQL_MODE
110813 15:35:46   304 Query     SELECT * FROM t_message t
110813 15:37:18   308 Connect   test_user@192.168.14.129 on test309 Connect   test_user@192.168.14.129 on test310 Connect   test_user@192.168.14.129 on test
110813 15:38:21     8 Query     BEGIN8 Query     insert into t_message values(1, 'c1')8 Query     COMMIT /* implicit, from Xid_log_event */
110813 15:38:50   311 Connect   test_user@192.168.14.129 on test312 Connect   test_user@192.168.14.129 on test313 Connect   test_user@192.168.14.129 on test
110813 15:38:58   304 Query     SELECT @@SQL_MODE8 Query     BEGIN8 Query     insert into t_user values(8, 'n8', 'p8')8 Query     COMMIT /* implicit, from Xid_log_event */
110813 15:39:08   304 Query     select * from t_user
110813 15:39:19   304 Query     select * from t_message
110813 15:44:08   314 Connect   test_user@192.168.14.129 on test315 Connect   test_user@192.168.14.129 on test316 Connect   test_user@192.168.14.129 on test