下面講講關于通過amoeba工具實現配置mysql讀寫分離的方法，文字的奧妙在于貼近主題相關。所以，閑話就不談了，我們直接看下文吧，相信看完通過amoeba工具實現配置mysql讀寫分離的方法這篇文章你一定會有所受益。
一，背景介紹：
Amoeba(變形蟲)項目，專注 分布式數據庫 proxy 開發。座落與Client、DB Server(s)之間。對客戶端透明。具有負載均衡、高可用性、sql過濾、讀寫分離、可路由相關的query到目標數據庫、可并發請求多臺數據庫合并結果。要想搭建Amoeba讀寫分離，首先需要知道MySQL的主從配置。具體的架構圖如下圖：
二，配置所需的環境：
Amoeba for mysql:192.168.1.28
Master : 192.168.1.247
Slave : 192.168.1.245
database:yazi passwd:root/123456
JDK1.8.0_51
三，具體的搭建和配置步驟如下：
1，配置mysql主從復制，具體參照上篇的mysql主從復制的配置
2，安裝配置jdk1.8.0_51以及環境變量
（1），安裝jdk命令: rpm -ivh jdk-8u51-linux-x64.rpm
[root@localhost install]# ll total 481168 -rw-r--r--. 1 root root 8850470 Nov 2 22:43 apache-tomcat-7.0.63.tar.gz -rw-r--r--. 1 root root 137808216 Jan 13 15:09 jdk-8u51-linux-x64.rpm -rw-r--r--. 1 root root 33203321 Nov 20 11:31 mysql-5.6.25.tar.gz -rw-r--r--. 1 root root 312845162 Nov 13 15:19 mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz [root@localhost install]# rpm -ivh jdk-8u51-linux-x64.rpm Preparing... ########################################### [100%] 1:jdk1.8.0_51 ########################################### [100%] Unpacking JAR files... rt.jar... jsse.jar... charsets.jar... tools.jar... localedata.jar... jfxrt.jar... plugin.jar... javaws.jar... deploy.jar...
（2)配置環境變量：
[root@localhost install]# vi /etc/profile
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
# It's NOT a good idea to change this file unless you know what you
# are doing. It's much better to create a custom.sh shell script in
# /etc/profile.d/ to make custom changes to your environment, as this
# will prevent the need for merging in future updates.
pathmunge () {
case ":${PATH}:" in
*:"$1":*)
;;
*)
if [ "$2" = "after" ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
esac
}
if [ -x /usr/bin/id ]; then
if [ -z "$EUID" ]; then
# ksh workaround
EUID=`id -u`
UID=`id -ru`
fi
USER="`id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"
fi
# Path manipulation
if [ "$EUID" = "0" ]; then
pathmunge /sbin
pathmunge /usr/sbin
pathmunge /usr/local/sbin
else
pathmunge /usr/local/sbin after
pathmunge /usr/sbin after
pathmunge /sbin after
fi
HOSTNAME=`/bin/hostname 2>/dev/null`
HISTSIZE=1000
if [ "$HISTCONTROL" = "ignorespace" ] ; then
export HISTCONTROL=ignoreboth
else
export HISTCONTROL=ignoredups
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL
# By default, we want umask to get set. This sets it for login shell
# Current threshold for system reserved uid/gids is 200
# You could check uidgid reservation validity in
# /usr/share/doc/setup-*/uidgid file
if [ $UID -gt 199 ] && [ "`id -gn`" = "`id -un`" ]; then
umask 002
else
umask 022
fi
for i in /etc/profile.d/*.sh ; do
if [ -r "$i" ]; then
if [ "${-#*i}" != "$-" ]; then
. "$i"
else
. "$i" >/dev/null 2>&1
fi
fi
done
unset i
unset -f pathmunge
export JAVA_HOME=/usr/java/jdk1.8.0_51
export JRE_HOME=/usr/java/jdk1.8.0_51/jre
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar （3)配置生效和驗證：
[root@FileServerA logs]# source /etc/profile [root@FileServerA logs]# java -version java version "1.8.0_51" Java(TM) SE Runtime Environment (build 1.8.0_51-b16) Java HotSpot(TM) 64-Bit Server VM (build 25.51-b03, mixed mode)
3，安裝配置amoeba
（1）下載amoeba
(http://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip)
(2)壓縮包無需安裝，直接解壓即可
[root@localhost install]# unzip amoeba-mysql-3.0.5-RC-distribution.zip Archive: amoeba-mysql-3.0.5-RC-distribution.zip creating: amoeba-mysql-3.0.5-RC/ creating: amoeba-mysql-3.0.5-RC/lib/ inflating: amoeba-mysql-3.0.5-RC/lib/amoeba-core-3.0.5-RC.jar inflating: amoeba-mysql-3.0.5-RC/lib/log4j-1.2.12.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-lang-2.4.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-logging-1.1.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-beanutils-1.8.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-collections-3.2.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/ognl-3.0.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/javassist-3.11.0.GA.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-pool-1.2.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-pool-1.6.jar inflating: amoeba-mysql-3.0.5-RC/lib/slf4j-api-1.6.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/slf4j-log4j12-1.6.2.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-bean-1.3.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-util-1.2.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/dom4j-1.6.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/xml-apis-1.0.b2.jar inflating: amoeba-mysql-3.0.5-RC/lib/oro-2.0.8.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-digester-1.8.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-net-io-2.2.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-cli-1.2.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-runtime-1.3.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-core-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-asm-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-context-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-aop-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/aopalliance-1.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-beans-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-expression-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/plexus-classworlds-2.4.2-HEXNOVA.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-net-benchmark-1.2.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/amoeba-mysql-3.0.5-RC.jar creating: amoeba-mysql-3.0.5-RC/bin/ inflating: amoeba-mysql-3.0.5-RC/bin/benchmark inflating: amoeba-mysql-3.0.5-RC/bin/launcher inflating: amoeba-mysql-3.0.5-RC/bin/mkdirhier inflating: amoeba-mysql-3.0.5-RC/bin/shutdown inflating: amoeba-mysql-3.0.5-RC/bin/benchmark.bat inflating: amoeba-mysql-3.0.5-RC/bin/launcher.bat inflating: amoeba-mysql-3.0.5-RC/bin/benchmark.classpath inflating: amoeba-mysql-3.0.5-RC/bin/launcher.classpath creating: amoeba-mysql-3.0.5-RC/conf/ inflating: amoeba-mysql-3.0.5-RC/conf/access_list.conf inflating: amoeba-mysql-3.0.5-RC/conf/amoeba.dtd inflating: amoeba-mysql-3.0.5-RC/conf/amoeba.xml inflating: amoeba-mysql-3.0.5-RC/conf/dbserver.dtd inflating: amoeba-mysql-3.0.5-RC/conf/dbServers.xml inflating: amoeba-mysql-3.0.5-RC/conf/function.dtd inflating: amoeba-mysql-3.0.5-RC/conf/functionMap.xml inflating: amoeba-mysql-3.0.5-RC/conf/log4j.dtd inflating: amoeba-mysql-3.0.5-RC/conf/log4j.xml inflating: amoeba-mysql-3.0.5-RC/conf/rule.dtd inflating: amoeba-mysql-3.0.5-RC/conf/rule.xml inflating: amoeba-mysql-3.0.5-RC/conf/ruleFunctionMap.xml creating: amoeba-mysql-3.0.5-RC/benchmark/ inflating: amoeba-mysql-3.0.5-RC/benchmark/context.xml inflating: amoeba-mysql-3.0.5-RC/benchmark/objectMap.dtd inflating: amoeba-mysql-3.0.5-RC/benchmark/query.xml inflating: amoeba-mysql-3.0.5-RC/jvm.properties [root@localhost install]# mv amoeba-mysql-3.0.5-RC /usr/local/
（3）amoeba的配置文件詳解：
amoeba.xml：定義前端的控制文件，包括前端的端口，用戶名和密碼
dbServers.xml：定義后端節點的配置文件。設置數據庫，登錄mysql的賬號和密碼
a,配置amoeba.xml文件
[root@FileServerA conf]# vim amoeba.xml
<!-- 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 -->
<!--
<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">
<property name="user">root</property>
<property name="password">123456</property>
"amoeba.xml" 91L, 3099C 30,6-41 8%
<?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 -->
<!--
<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">
<property name="user">lqb</property> //***遠程訪問代理的用戶名和密碼***//
<property name="password">123456</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">viplqb</property> //***寫的資源池***//
<property name="needParse">true</property>
</queryRouter>
</amoeba:configuration>b,配置dbServers.xml文件
[root@FileServerA conf]# vim dbServers.xml
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">
<!--
Each dbServer needs to be configured into a Pool,
If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
such as 'multiPool' dbServer
-->
<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訪問端口號***//
<!-- mysql schema -->
<property name="schema">yazi</property> //***設置mysql訪問默認庫***//
<!-- mysql user -->
<property name="user">root</property> //***登錄mysql賬號和密碼***//
<property name="password">123456</property>
</factoryConfig>
<poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
<property name="maxActive">500</property>
<property name="maxIdle">500</property>
<property name="minIdle">1</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>
<dbServer name="master" parent="abstractServer"> //***設置master和ip地址***//
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.247</property>
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer"> //***設置slave1和ip地址***//
<factoryConfig> //***如果有多個slave主機復制本配置到下邊繼續添加ip地址***//
<!-- mysql ip -->
<property name="ipAddress">192.168.1.245</property>
</factoryConfig>
</dbServer>
<dbServer name="viplqb" 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">slave1</property> //***所有從庫的主機都要寫上***//
</poolConfig>
</dbServer>
</amoeba:dbServers>（4）啟動amoeba并查看是否有報錯。
[root@FileServerA conf]# /usr/local/amoeba-mysql-3.0.5-RC/bin/launcher & [1] 34774 [root@FileServerA conf]# log4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf 2016-07-04 13:46:11,874 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066. Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0 2016-07-04 13:47:53 [INFO] Project Name=Amoeba-MySQL, PID=32445 , starting... log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/log4j.xml 2016-07-04 13:47:53,604 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA log4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf 2016-07-04 13:47:53,789 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066. 2016-07-04 14:22:55 [INFO] ignore signal:HUP Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0 2016-07-04 17:31:08 [INFO] Project Name=Amoeba-MySQL, PID=34779 , starting... log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/log4j.xml 2016-07-04 17:31:08,868 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA log4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf 2016-07-04 17:31:09,128 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.
(5)用其他的云服務器來進行登錄：
[root@mysqlmaster ~]# mysql -ulqb -p -h292.168.1.28 -P8066 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 965356058 Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | yazi | +--------------------+ 5 rows in set (0.01 sec) mysql>
備注：當配置這個文件時amoeba.xml ，其他的主機通過代理就可以訪問，如上說明amoeba.xml配置的沒有問題。
（6）驗證讀寫分離，這個需要配置dbServers.xml這個文件了，接下來即為驗證讀寫分離。
（a)在沒停掉同步之前在主庫247上創建一張表；
mysql> use yazi; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_yazi | +----------------+ | sxit | | test | +----------------+ 2 rows in set (0.00 sec) mysql> create table lqb (id int(10) ,name varchar(10),address varchar(20)); Query OK, 0 rows affected (0.02 sec)
(b)在從庫245上查看，并在slave上停止同步：
mysql> use yazi; Database changed mysql> show tables; +----------------+ | Tables_in_yazi | +----------------+ | lqb | | sxit | | test | +----------------+ 3 rows in set (0.00 sec) mysql> stop slave -> ; Query OK, 0 rows affected (0.00 sec)
（c)在主從上各插入一條不同的數據
在主庫上插入（1，'zhangsan','master');
在從庫上插入（2，’lisi','slave);
在主庫上執行以下操作：
mysql> insert into lqb values(1,'zhangsan','master'); Query OK, 1 row affected (0.00 sec) mysql> select * from lqb; +------+----------+---------+ | id | name | address | +------+----------+---------+ | 1 | zhangsan | master | +------+----------+---------+ 1 row in set (0.00 sec)
在從庫上執行以下操作：
mysql> insert into lqb values(2,'zhangsan','slave'); Query OK, 1 row affected (0.00 sec) mysql> select * from lqb; +------+----------+---------+ | id | name | address | +------+----------+---------+ | 2 | zhangsan | slave | +------+----------+---------+ 1 row in set (0.00 sec)
（d)登錄amoeba云服務器查看讀操作：顯示的是245slave云服務器
mysql> select * from lqb;
+------+----------+---------+
| id | name | address |
+------+----------+---------+
| 2 | zhangsan | slave |
+------+----------+---------+
1 row in set (0.00 sec)
（e）在amoeba云服務器上測試以下寫操作，查看還是245從庫上的數據。
mysql> insert into lqb values(3,'wanger','test_write'); Query OK, 1 row affected (0.00 sec) mysql> select * from lqb; +------+----------+---------+ | id | name | address | +------+----------+---------+ | 2 | zhangsan | slave | +------+----------+---------+ 1 row in set (0.00 sec)
在主庫247上查詢，可以看到已插入進來了
mysql> select * from lqb; +------+----------+------------+ | id | name | address | +------+----------+------------+ | 1 | zhangsan | master | | 3 | wanger | test_write | +------+----------+------------+ 2 rows in set (0.00 sec)
在從庫上245查詢，還是剛才查詢
mysql> select * from lqb; +------+----------+---------+ | id | name | address | +------+----------+---------+ | 2 | zhangsan | slave | +------+----------+---------+ 1 row in set (0.00 sec)
至此，數據庫讀寫分離已經配置完成，在正式生產環境中，需將讀寫主機的定義更改，因為數據庫用的最多的還是讀的操作。我們可以將讀或查詢交給從來處理，同樣的，我們也可以添加多個從主機。讓其自動從不同的從主機上讀取數據庫。
對于以上通過amoeba工具實現配置mysql讀寫分離的方法相關內容，大家還有什么不明白的地方嗎？或者想要了解更多相關，可以繼續關注我們的行業資訊板塊。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
