MySQL基於amoeba的讀寫分離及負載均衡

前提條件：

• MySQL資料庫已做好主從同步配置
• 已安裝Java環境並配置好環境變數

分別在主從資料庫上為amoeba用戶授權

grant all on crm.* to amoeba@192.168.64.% identified by amoeba;nflush privileges;n

解壓安裝配置amoeba

tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -Camoeban

修改dbServers.xml

<?xml version="1.0" encoding="gbk"?>nn<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">n<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">nn <!-- n Each dbServer needs to be configured into a Pool,n If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:n add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfign such as multiPool dbServer n -->nn <dbServer name="abstractServer" abstractive="true">n <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">n <property name="manager">${defaultManager}</property>n <property name="sendBufferSize">64</property>n <property name="receiveBufferSize">128</property>n <!-- amoeba內部操作管理mysql時的埠 -->n <property name="port">3306</property>n <!-- amoeba內部操作管理的資料庫 -->n <property name="schema">crm</property>n <!-- amoeba內部連接用戶名 -->n <property name="user">amoeba</property>n <!-- amoeba內部連接密碼 -->n <property name="password">amoeba</property>n </factoryConfig>nn <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">n <property name="maxActive">500</property>n <property name="maxIdle">500</property>n <property name="minIdle">10</property>n <property name="minEvictableIdleTimeMillis">600000</property>n <property name="timeBetweenEvictionRunsMillis">600000</property>n <property name="testOnBorrow">true</property>n <property name="testOnReturn">true</property>n <property name="testWhileIdle">true</property>n </poolConfig>n </dbServer>nntt<!-- 主資料庫地址 -->n <dbServer name="master" parent="abstractServer">n <factoryConfig>n <!-- mysql ip -->n <property name="ipAddress">192.168.64.131</property>n </factoryConfig>n </dbServer>nntt<!-- 從資料庫地址 -->n <dbServer name="slave" parent="abstractServer">n <factoryConfig>n <!-- mysql ip -->n <property name="ipAddress">192.168.64.132</property>n </factoryConfig>n </dbServer>nttntt<!-- 配置在master和slave之間做負載均衡 -->n <dbServer name="MultiPool" virtual="true">n <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">n <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->n <property name="loadbalance">1</property>n <!-- Separated by commas,such as: server1,server2,server3 -->n <property name="poolNames">master,slave</property>n </poolConfig>n </dbServer>nn</amoeba:dbServers>n

修改amoeba.xml

<?xml version="1.0" encoding="gbk"?>nn<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">n<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">n <proxy>n <!-- service class must implements com.meidusa.amoeba.service.Service -->n <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">n <!-- amoeba對外連接資料庫時的埠 -->n <property name="port">8066</property>n <!-- amoeba對外連接資料庫時的IP -->n <property name="ipAddress">127.0.0.1</property>n <property name="manager">${clientConnectioneManager}</property>n <property name="connectionFactory">n <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">n <property name="sendBufferSize">128</property>n <property name="receiveBufferSize">64</property>n </bean>n </property>n <property name="authenticator">n <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">ntttttttttt<!-- amoeba對外連接資料庫時的用戶名 -->n <property name="user">root</property>ntttttttttt<!-- amoeba對外連接資料庫時的密碼 -->n <property name="password"></property>n <property name="filter">n <bean class="com.meidusa.amoeba.server.IPAccessController">n <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>n </bean>n </property>n </bean>n </property>nn </service>n <!-- server class must implements com.meidusa.amoeba.service.Service -->n <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">n <!-- port -->n <!-- default value: random numbern <property name="port">9066</property>n -->n <!-- bind ipAddress -->n <property name="ipAddress">127.0.0.1</property>n <property name="daemon">true</property>n <property name="manager">${clientConnectioneManager}</property>n <property name="connectionFactory">n <bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>n </property>nn </service>n <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">n <!-- proxy server net IO Read thread size -->n <property name="readThreadPoolSize">20</property>n <!-- proxy server client process thread size -->n <property name="clientSideThreadPoolSize">30</property>n <!-- mysql server data packet process thread size -->n <property name="serverSideThreadPoolSize">30</property>n <!-- per connection cache prepared statement size -->n <property name="statementCacheSize">500</property>n <!-- query timeout( default: 60 second , TimeUnit:second) -->n <property name="queryTimeout">60</property>n </runtime>nn </proxy>n <!-- n Each ConnectionManager will start as threadn manager responsible for the Connection IO read , Death Detectionn -->n <connectionManagerList>n <connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">n <property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>n <!-- n default value is avaliable Processors n <property name="processors">5</property>n -->n </connectionManager>n <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">n <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>nn <!-- n default value is avaliable Processors n <property name="processors">5</property>n -->n </connectionManager>n </connectionManagerList>n <!-- default using file loader -->n <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">n <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>n </dbServerLoader>nn <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">n <property name="ruleLoader">n <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">n <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>n <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>n </bean>n </property>n <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>n <property name="LRUMapSize">1500</property>n <!--amoeba默認連接的server-->n <property name="defaultPool">master</property>n <!--允許在master上寫數據-->n <property name="writePool">master</property>n <!--允許在MultiPool上讀數據-->n <property name="readPool">MultiPool</property>n <property name="needParse">true</property>n </queryRouter>n</amoeba:configuration>n

修改vim amoeba/bin/amoeba文件

修改-Xss128k為以下內容

DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"n

啟動amoeba到後台

amoeba start &n

測試讀寫分離及負載均衡

在主庫上crm資料庫中創建zhang表

create table zhang (id int(10) ,name varchar(10),address varchar(20));ninsert into zhang values(1,zhang,this_is_master);n

停掉從庫複製進程

stop slave;ninsert into zhang values(2,zhang,this_is_slave);n

連接amoeba客戶端

mysql -uroot -p -h127.0.0.1 -P8066nmysql> use crm;nDatabase changedn# 因為默認連的資料庫是主庫，第一次查詢查的是主庫的數據nmysql> select * from zhang;n+------+-------+----------------+n| id | name | address |n+------+-------+----------------+n| 1 | zhang | this_is_master |n| 2 | zhang | this_is_master |n| 3 | zhang | this_is_master |n| 4 | zhang | this_is_master |n+------+-------+----------------+n4 rows in set (0.00 sec)n# 因為開啟了負載均衡並採用輪詢模式該查詢查的是從庫的數據nmysql> select * from zhang;n+------+-------+---------------+n| id | name | address |n+------+-------+---------------+n| 2 | zhang | this_is_slave |n+------+-------+---------------+n1 row in set (0.00 sec)nn# 插入兩條數據，觀察數據插入到那個庫中nnmysql> insert into zhang values(5,zhang,this_is_master);nQuery OK, 1 row affected (0.00 sec)nnmysql> insert into zhang values(6,zhang,this_is_master);nQuery OK, 1 row affected (0.01 sec)nn# 查詢的是主庫nnmysql> select * from zhang;n+------+-------+----------------+n| id | name | address |n+------+-------+----------------+n| 1 | zhang | this_is_master |n| 2 | zhang | this_is_master |n| 3 | zhang | this_is_master |n| 4 | zhang | this_is_master |n| 5 | zhang | this_is_master |n| 6 | zhang | this_is_master |n+------+-------+----------------+n6 rows in set (0.00 sec)nn# 查詢的是從庫nnmysql> select * from zhang;n+------+-------+---------------+n| id | name | address |n+------+-------+---------------+n| 2 | zhang | this_is_slave |n+------+-------+---------------+n1 row in set (0.00 sec)n

可見數據插入到了主庫上，因為配置了主庫可讀寫、從庫為只讀，且主從同步中從庫的同步進程已被停用。

查看主庫數據

ubuntu@master:~$ mysql -uroot -h192.168.64.131 -P3306nmysql> use crm;nDatabase changednmysql> select * from zhang;n+------+-------+----------------+n| id | name | address |n+------+-------+----------------+n| 1 | zhang | this_is_master |n| 2 | zhang | this_is_master |n| 3 | zhang | this_is_master |n| 4 | zhang | this_is_master |n| 5 | zhang | this_is_master |n| 6 | zhang | this_is_master |n+------+-------+----------------+n6 rows in set (0.00 sec)n

查看從庫數據

ubuntu@slave:~$ mysql -uroot -h192.168.64.132 -P3306nmysql> show slave statusGn*************************** 1. row ***************************n Slave_IO_State: n Master_Host: 192.168.64.131n Master_User: repusern Master_Port: 3306n Connect_Retry: 60n Master_Log_File: mysql-bin.000002n Read_Master_Log_Pos: 4681n Relay_Log_File: mysqld-relay-bin.000009n Relay_Log_Pos: 4119n Relay_Master_Log_File: mysql-bin.000002n Slave_IO_Running: Non Slave_SQL_Running: Non ......n1 row in set (0.00 sec)nmysql> use crm;nDatabase changednmysql> select * from zhang;n+------+-------+---------------+n| id | name | address |n+------+-------+---------------+n| 2 | zhang | this_is_slave |n+------+-------+---------------+n1 row in set (0.00 sen

參考文檔：Amoeba使用指南n 原文鏈接： MySQL基於amoeba的讀寫分離及負載均衡

推薦閱讀：