MySQL Amoeba讀寫分離

Amoeba 簡介

Amoeba for mysql可作為mysql的分散式資料庫前端代理層，它主要在應用層訪問mysql的時候充當query 路由功能，專注 分散式資料庫 proxy 開發。座落與Client、DB Server(s)之間。對客戶端透明。具有負載均衡、高可用性、query filter、讀寫分離、可路由相關的query到目標資料庫、可併發請求多臺資料庫合併結果。 在amoeba上面你能夠完成多資料來源的高可用、負載均衡、資料切片的功能。

Amoeba for mysql不足:

• 目前還不支援事務
• 暫時不支援儲存過程
• 不適合從amoeba導資料的場景或者對大資料量查詢的query並不合適（比如一次請求返回10w以上甚至更多資料的場合）
• 暫時不支援分庫分表，amoeba目前只做到分資料庫例項，每個被切分的節點需要保持庫表結構一致

安裝Amoeba

shell> tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /db/tool/amoeba
shell> vi /etc/profile
=========================================================
export JAVA_HOME=/usr/lib/jvm/jre-1.6.0-openjdk   #使用系統自帶JDK
export AMOEBA_HOME=/db/tool/amoeba
PATH=$AMOEBA_HOME/bin:$JAVA_HOME/bin:$PATH
=========================================================
shell> source /etc/profile

shell> java -version
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.4) (rhel-1.41.1.10.4.el6-i386)
OpenJDK Server VM (build 20.0-b11, mixed mode)

安裝完成後，你可以通過命令確定Amoeba是否成功安裝。
shell> amoeba
amoeba start|stop

配置Amoeba for mysql

$AMOEBA_HOME/conf/dbServers.xml：儲存代理的資料庫連線方式，如：主機IP、埠、Amoeba使用的使用者名稱和密碼等
$AMOEBA_HOME/conf/amoeba.xml：定義了Amoeba代理的相關配置
$AMOEBA_HOME/conf/functionMap.xml：資料庫函式名和函式解析處理的關係，如：UNIX_TIMESTAMP()、SYSDATE()等等
$AMOEBA_HOME/conf/rule.xml：資料切分規則配置
$AMOEBA_HOME/conf/ruleFunctionMap.xml：rule.xml中自定義的函式
$AMOEBA_HOME/conf/access_list.conf：制定可訪問以及拒絕訪問的主機IP地址
$AMOEBA_HOME/conf/log4j.xml：Amoeba允許使用者配置輸出日誌級別以及方式，配置方法使用log4j的檔案格式

shell> vi /db/tool/amoeba/conf/dbServers.xml
========================================================================================



  



  
    
      ${defaultManager}
      64
      128
      <!-- property name="port">3306db_test
      root
      password
    

    
      500
      500
      10
      600000
      600000
      true
      true
      true
    
  

  
    
      127.0.0.1
      3307
    
  

  
    
      127.0.0.1
      3308
    
  

  
    
      <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--&gt
      1
      slave,master,slave
    
  

========================================================================================


shell> vi /db/tool/amoeba/conf/amoeba.xml
========================================================================================





  
    8066
    192.168.90.128
    ${clientConnectioneManager}
    
      
        128
        64
      
    

    
      
        root
        password
        
          
            ${amoeba.home}/conf/access_list.conf
          
        
      
    
  

  
    9066
    127.0.0.1
    true
    ${clientConnectioneManager}
    
      
    
  

 
    20
    30
    30
    500
    600
  



  
    com.meidusa.amoeba.net.ConnectionManager
    5
  

  
    com.meidusa.amoeba.net.AuthingableConnectionManager
    5
  



  ${amoeba.home}/conf/dbServers.xml



  
    
      ${amoeba.home}/conf/rule.xml
      ${amoeba.home}/conf/ruleFunctionMap.xml
    
  
  ${amoeba.home}/conf/functionMap.xml
  1500
  master
  master
  virtualdb
  true


========================================================================================

驗證Amoeba

shell> amoeba start
log4j:WARN log4j config load completed from file:/db/tool/amoeba/conf/log4j.xml
2015-01-15 01:28:21,450 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/db/tool/amoeba/conf/access_list.conf
2015-01-15 01:28:21,831 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on /192.168.90.128:8066.
2015-01-15 01:28:21,838 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:9066.


shell> mysql -h 127.0.0.1 -u root -P 8066 -p
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          11 |
+-------------+
1 row in set (0.03 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          12 |
+-------------+
1 row in set (0.02 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          12 |
+-------------+
1 row in set (0.01 sec)


mysql> create table db_test.t (id int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values (1),(2),(3),(4);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from db_test.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.04 sec)

【slave】
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)

【ameoba代理端】
mysql> insert into t values(5);
Query OK, 1 row affected (0.05 sec)

mysql> select * from db_test.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.01 sec)

mysql> select * from db_test.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.01 sec)

mysql> select * from db_test.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.01 sec)

整理自網路