搭建基於MySQL的讀寫分離工具Amoeba

標籤：1.0   uil   報錯   log   xmlns   write   bin   build.xml   eid   

搭建基於MySQL的讀寫分離工具Amoeba：
Amoeba工具是實現MySQL資料庫讀寫分離的一個工具，前提是基於MySQL主從複製來實現的；

實驗環境（虛擬機器）：
主機 角色
10.10.10.20 多執行個體加主從複製
10.10.10.30 Amoeba伺服器
10.10.10.40 用戶端(最後測試使用)

 

1、首先搭建MySQL的主從複製（不在多提）；
需要注意的是：在主從庫上需要建立一個使用者，在主庫上建立的使用者為amoeba，許可權是create，update，insert，delete；
在從庫上建立的使用者也是amoeba，許可權是select；

2、由於Amoeba是基於java的環境去實現的，所以要先安裝jdk外掛程式：(先通過windows主機去下載)
http://pan.baidu.com/share/link?shareid=2793927523&uk=1678158691&fid=117337971851932
下載完成後通過xshell內建的ftp軟體傳到伺服器上，然後進行安裝：

[[email protected] tools]# tar xf jdk-7u79-linux-x64.tar.gz

[[email protected] tools]# vim /etc/profile      ##編輯jdk需要的環境變數；export JAVA_HOME=/root/tools/jdk1.7.0_79export JRE_HOME=/root/tools/jdk1.7.0_79/jreexport PATH=/root/tools/jdk1.7.0_79/bin:$PATHexport CLASSAPTH=./:/root/tools/jdk1.7.0_79/lib:/root/tools/jdk1.7.0_79/lib

[[email protected] tools]# java -version        ##檢測jdk是否安裝成功，出現如下資訊算安裝成功；java version "1.7.0_79"Java(TM) SE Runtime Environment (build 1.7.0_79-b15)Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode)

3、下載並安裝Amoeba軟體：

[[email protected] tools]# mkdir /usr/local/amoeba       ##建立一個目錄用來放amoeba的軟體；https://sourceforge.net/projects/amoeba/       ##windows主機登入網址到網頁中部的地方下載 Released /OldFiles/amoeba-mysql-0.19.zip，通過xshell內建FTP工具放到伺服器上；[[email protected] amoeba]# cd /usr/local/amoeba/[[email protected] amoeba]# unzip amoeba-mysql-0.19.zip[[email protected] amoeba]# lsLICENSE.txt README.html amoeba-mysql-0.19.zip bin build.xml conf doc lib logs src ##主要用到的目錄是conf這個配置目錄；[[email protected] conf]# lsaccess_list.conf amoeba.dtd amoeba.xml function.dtd functionMap.xml log4j.dtd log4j.xml rule.dtd rule.xml ruleFunctionMap.xml[[email protected] conf]# vim amoeba.xml<?xml version="1.0" encoding="gbk"?><!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd"><amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/"><server><!-- proxy server°樵???--><property name="port">3306</property>       ##配置的是Amoeba的服務連接埠，這裡用3306就可以，預設的是8806；<!-- proxy server°樵éP --><property name="ipAddress">10.10.10.30</property>      ##配置的是Amoeba伺服器的IP地址，預設的是127.0.0.1，改為物理網卡上的IP地址；<!-- proxy server net IO Read thread size --><property name="readThreadPoolSize">100</property> ##下面的四個數字可以調大一點；<!-- proxy server client process thread size --><property name="clientSideThreadPoolSize">100</property><!-- mysql server data packet process thread size --><property name="serverSideThreadPoolSize">200</property><!-- socket Send and receive BufferSize(unit:K) --><property name="netBufferSize">128</property><!-- Enable/disable TCP_NODELAY (disable/enable Nagle‘s algorithm). --><property name="tcpNoDelay">true</property><!-- ????μ??§? --><property name="user">root</property>        ##配置用戶端通過Amoeba串連資料庫的使用者，預設的是root；<property name="password">xyp123123</property>     ##配置的是用戶端通過Amoeba串連資料庫的密碼，預設是被注釋掉的；</server><dbServer name="server1"> ##定義mysql池的名字；<!-- PoolableObjectFactory?Ж` --><factoryConfig><className>com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory</className><property name="manager">defaultManager</property><!-- ??mysql??????--><property name="port">3306</property>   ##主庫的連接埠號碼；<!-- ??mysql???? --><property name="ipAddress">10.10.10.20</property> ##主庫的IP地址；<property name="schema">xyp</property>##需要進行操作的庫；<!-- ??μ??mysqlμ??§? --><property name="user">amoeba</property>  ##用於Amoeba串連資料庫的使用者；<!-- ??μ??mysqlμ??鞭-><property name="password">123123</property>   ##用於Amoeba串連資料庫的密碼；</factoryConfig><!-- ObjectPool?Ж` --><poolConfig><className>com.meidusa.amoeba.net.poolable.PoolableObjectPool</className>   ##這裡的不用動；<property name="maxActive">200</property><property name="maxIdle">200</property><property name="minIdle">10</property><property name="minEvictableIdleTimeMillis">600000</property><property name="timeBetweenEvictionRunsMillis">600000</property><property name="testOnBorrow">true</property><property name="testWhileIdle">true</property></poolConfig><dbServer name="server2">   ##定義從庫的池；<!-- PoolableObjectFactory?Ж` --><factoryConfig><className>com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory</className><property name="manager">defaultManager</property><!-- ??mysql??????--><property name="port">3307</property>   ##從庫連接埠；<!-- ??mysql???? --><property name="ipAddress">10.10.10.20</property> ##從庫ip地址；<property name="schema">xyp</property> ##從庫上需要進行操作的庫；<!-- ??μ??mysqlμ??§? --><property name="user">amoeba</property>   ##amoeba串連從庫的使用者；<!-- ??μ??mysqlμ??鞭-><property name="password">123123</property>   ##amoeba串連從庫的密碼；</factoryConfig><!-- ObjectPool?Ж` --><poolConfig><className>com.meidusa.amoeba.net.poolable.PoolableObjectPool</className>    ##下面的不用動；<property name="maxActive">200</property><property name="maxIdle">200</property><property name="minIdle">10</property><property name="minEvictableIdleTimeMillis">600000</property><property name="timeBetweenEvictionRunsMillis">600000</property><property name="testOnBorrow">true</property><property name="testWhileIdle">true</property></poolConfig></dbServer><dbServer name="master" virtual="true">    ##定義操作的池，master為主庫<poolConfig><className>com.meidusa.amoeba.server.MultipleServerPool</className><!-- ?o???? 1=ROUNDROBIN , 2=WEIGHTBASED --><property name="loadbalance">1</property><!-- 2??pool?o???poolName±?oo???-><property name="poolNames">server1</property>   ##調用上面主庫的池；</poolConfig></dbServer><dbServer name="slave" virtual="true">   ##定義從庫的池，名字為slave；<poolConfig><className>com.meidusa.amoeba.server.MultipleServerPool</className><!-- ?o???? 1=ROUNDROBIN , 2=WEIGHTBASED --><property name="loadbalance">1</property><!-- 2??pool?o???poolName±?oo???-><property name="poolNames">server2</property>    ##調用上面從庫的池；</poolConfig></dbServer></dbServerList><queryRouter><className>com.meidusa.amoeba.mysql.parser.MysqlQueryRouter</className><property name="ruleConfig">${amoeba.home}/conf/rule.xml</property><property name="functionConfig">${amoeba.home}/conf/functionMap.xml</property><property name="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property><property name="LRUMapSize">1500</property><property name="defaultPool">master</property>   ##預設的池的名字；<property name="writePool">master</property>   ##寫的池的名字；<property name="readPool">slave</property>##讀的池的名字；<property name="needParse">true</property></queryRouter>

[[email protected] conf]# chmod +x -R /usr/local/amoeba/bin/ ##給啟動命令amoeba授權；[[email protected] amoeba]# /usr/local/amoeba/bin/amoeba & ##後台啟動；log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml ##這條資訊不是報錯，屬於正常現象；[[email protected] amoeba]# jobs ##查看後台啟動的服務；[1]- Running ../bin/amoeba & (wd: /usr/local/amoeba/conf)

4、測試：

[[email protected]~]# mysql -uroot -pxyp123123 -h 10.10.10.30     ##通過用戶端去串連amoeba伺服器來登入到mysql；mysql> show databases;      ##查看資料庫，已經存在需要配置的庫xyp；+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || xyp |+--------------------+5 rows in set (0.02 sec)去從庫上停掉slave，方便測試；mysql> use xypDatabase changedmysql> create table student(id int(10) not null,name char(20) not null,age int(2) not null);      ##在庫裡面建立一個表；mysql> insert into student values(1,‘x‘,23);     ##插入一條資訊做測試；mysql> select * from xyp.student;     ##先去主庫上去查，有資料；+----+------+-----+| id | name | age |+----+------+-----+| 1 | x | 23 |+----+------+-----+1 row in set (0.02 sec)mysql> select * from xyp.student;     ##在amoeba上通過select去查結果沒東西，因為從庫的狀態是停掉的Empty set (0.01 sec)mysql> start slave; ##在從庫上把狀態開啟；Query OK, 0 rows affected (0.00 sec)mysql> select * from xyp.student;     ##在從amoeba上查看，資料已經被同步過來了；+----+------+-----+| id | name | age |+----+------+-----+| 1 | x | 23 |+----+------+-----+1 row in set (0.02 sec)

 

搭建基於MySQL的讀寫分離工具Amoeba