mysql讀寫分離Amoeba的部署
這裡使用Amoeba for mysql來實現mysql的讀寫分離,起到緩解主資料庫伺服器的壓力,下面是實現這一方案的架構圖:
硬傷:不支援事務~~~~生產怎麼用?
一:mysql主從複製的搭建
因為讀寫分離,所以一臺負責mysql的寫操作,另一臺負責mysql的讀操作,所以我們這裡使用mysql的主從複製再合適不過了。關於這一配置,請移步:二:java環境的配置
java環境配置
Amoeba框架是基於Java SE1.5開發的,建議使用Java SE 1.5版本。目前Amoeba經驗證在JavaTM SE 1.5和Java SE 1.6能正常執行,(可能包括其他未經驗證的版本)。
http://blog.itpub.net/29096438/viewspace-1743529/ ---另一篇部落格
三:Amoeba的安裝
Amoeba下載地址:
下面是安裝步驟:
點選(此處)摺疊或開啟
-
mkdir /usr/local/amoeba
-
wget http://softlayer.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz
-
tar xzf amoeba-mysql-binary-2.1.0-RC5.tar.gz -C /usr/local/amoeba
Amoeba for mysql配置
配置Amoeba for mysql的讀寫分離主要涉及兩個檔案:
1、/usr/local/amoeba/conf/dbServers.xml
此檔案定義由Amoeba代理的資料庫如何連線,比如最基礎的:主機IP、埠、Amoeba使用的使用者名稱和密碼等等。
2、/usr/local/amoeba/conf/amoeba.xml
此檔案定義了Amoeba代理的相關配置。
dbServers.xml檔案配置
abstractServer配置:
-
此部分定義真實mysql伺服器的埠,資料庫名稱,mysql使用者及密碼。
-
<dbServer name="abstractServer" abstractive="true">
-
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
-
<property name="manager">${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">dbname</property> -----選擇資料庫
-
-
<!-- mysql user -->
-
<property name="user">root</property> ----連線使用者名稱
-
-
<!-- mysql password -->
-
<property name="password">root</property> -----連線密碼
-
-
</factoryConfig>
-
-
<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
-
<property name="maxActive">500</property>
-
<property name="maxIdle">500</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>
此部分定義主伺服器,從伺服器,及從伺服器連線池。這裡只定義資料庫地址,它們的使用者及密碼就是上面的abstractServer裡的設定。注意用來連線真實mysql伺服器的使用者必須擁有遠端連線許可權。
-
<dbServer name="Master" parent="abstractServer"> -----主
-
<factoryConfig>
-
<!-- mysql ip -->
-
<property name="ipAddress">192.168.0.1</property>
-
</factoryConfig>
-
</dbServer>
-
<dbServer name="Slave1" parent="abstractServer"> ------從1
-
<factoryConfig>
-
<!-- mysql ip -->
-
<property name="ipAddress">192.168.0.2</property>
-
</factoryConfig>
-
</dbServer>
-
<dbServer name="Slave2" parent="abstractServer"> ----從2
-
<factoryConfig>
-
<!-- mysql ip -->
-
<property name="ipAddress">192.168.0.3</property>
-
</factoryConfig>
-
</dbServer>
-
<dbServer name="virtualSlave" 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,Slave2</property> ---salve1 slave2放入virtualslave連線池
-
</poolConfig>
-
</dbServer>
點選(此處)摺疊或開啟
-
mkdir /usr/local/amoeba
-
wget http://softlayer.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz
- tar xzf amoeba-mysql-binary-2.1.0-RC5.tar.gz -C /usr/local/amoeba
1、/usr/local/amoeba/conf/dbServers.xml
此檔案定義由Amoeba代理的資料庫如何連線,比如最基礎的:主機IP、埠、Amoeba使用的使用者名稱和密碼等等。
2、/usr/local/amoeba/conf/amoeba.xml
此檔案定義了Amoeba代理的相關配置。
-
此部分定義真實mysql伺服器的埠,資料庫名稱,mysql使用者及密碼。
-
<dbServer name="abstractServer" abstractive="true">
-
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
-
<property name="manager">${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">dbname</property> -----選擇資料庫
-
-
<!-- mysql user -->
-
<property name="user">root</property> ----連線使用者名稱
-
-
<!-- mysql password -->
-
<property name="password">root</property> -----連線密碼
-
-
</factoryConfig>
-
-
<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
-
<property name="maxActive">500</property>
-
<property name="maxIdle">500</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>
此部分定義主伺服器,從伺服器,及從伺服器連線池。這裡只定義資料庫地址,它們的使用者及密碼就是上面的abstractServer裡的設定。注意用來連線真實mysql伺服器的使用者必須擁有遠端連線許可權。
-
<dbServer name="Master" parent="abstractServer"> -----主
-
<factoryConfig>
-
<!-- mysql ip -->
-
<property name="ipAddress">192.168.0.1</property>
-
</factoryConfig>
-
</dbServer>
-
<dbServer name="Slave1" parent="abstractServer"> ------從1
-
<factoryConfig>
-
<!-- mysql ip -->
-
<property name="ipAddress">192.168.0.2</property>
-
</factoryConfig>
-
</dbServer>
-
<dbServer name="Slave2" parent="abstractServer"> ----從2
-
<factoryConfig>
-
<!-- mysql ip -->
-
<property name="ipAddress">192.168.0.3</property>
-
</factoryConfig>
-
</dbServer>
-
<dbServer name="virtualSlave" 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,Slave2</property> ---salve1 slave2放入virtualslave連線池
-
</poolConfig>
- </dbServer>
amoeba.xml配置
amoeba連線驗證配置:
-
這裡定義連線amoeba時用來驗證的使用者及密碼。
-
<property name="authenticator">
-
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
-
-
<property name="user">root</property>
-
-
<property name="password">ESBecs00</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>
讀寫分離配置:
-
<property name="defaultPool">Master</property>
-
<property name="writePool">Master</property>
- <property name="readPool">virtualSlave</property> -----連線池
writePool :配置了資料庫寫庫,通常配為Master,如這裡就配置為之前定義的Master資料庫。
readPool :配置了資料庫讀庫,通常配為Slave或者Slave組成的資料庫池,如這裡就配置之前的virtualSlave資料庫池。
四:啟動Amobe
啟動命令:
- amoeba start
此命令以前臺的方式啟動,會輸出啟動時的資訊,檢查沒有錯誤資訊後,中斷,並後臺執行:
- amoeba start &
五:執行及驗證
-
透過這個命令在test資料庫中建立了STAFF表,這裡連線的是真實的MySQL資料庫。
-
$ mysql -uroot -ppassword -h127.0.0.1 -P3306
-
Welcome to the MySQL monitor. Commands end with ; or \g.
-
Your MySQL connection id is 125
-
Server version: 5.5.9 MySQL Community Server (GPL)
-
-
Copyright (c) 2000, 2010, 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> create table `test`.`staff`( 1
-
-> `ID` int NOT NULL AUTO_INCREMENT,
-
-> `NAME` varchar(10),
-
-> PRIMARY KEY (`ID`)
-
-> );
-
Query OK, 0 rows affected (0.00 sec)
-
mysql> use test
-
Database changed
-
mysql> show tables;
-
+----------------+
-
| Tables_in_test |
-
+----------------+
-
| staff |
-
+----------------+
- 1 row in set (0.00 sec)
透過amoeba連線mysql
-
啟動Amoeba後這次透過mysql客戶端來連線Amoeba,注意埠從真實的MySQL埠3306改為Amoeba埠8066:
-
$ mysql -uroot -ppassword -h127.0.0.1 -P8066
-
Welcome to the MySQL monitor. Commands end with ; or \g.
-
Your MySQL connection id is 27778511
-
Server version: 5.1.45-mysql-amoeba-proxy-2.0.1-BETA MySQL Community Server (GPL)1 ----透過Server version可以得知連線的是Amoeba例項而不是MySQL例項
-
-
Copyright (c) 2000, 2010, 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> use test;
-
Database changed
-
mysql> INSERT INTO test.staff (ID, NAME)2
-
-> VALUES (12345, 'Daisy Li.');
-
Query OK, 1 row affected (0.03 sec)
-
-
mysql> SELECT * FROM test.staff LIMIT 0, 50;3
-
+-------+-----------+
-
| ID | NAME |
-
+-------+-----------+
-
| 12345 | Daisy Li. |
-
+-------+-----------+
-
1 row in set (0.00 sec)
-
- mysql>
原文地址:
http://www.cnblogs.com/lhj588/archive/2012/11/19/2777897.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29096438/viewspace-1819139/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL Amoeba讀寫分離MySql
- Amoeba for mysql讀寫分離MySql
- Mysql讀寫分離方案-Amoeba環境部署記錄MySql
- Amoeba for MySQL讀寫分離配置MySql
- Amoeba 實現 MySQL 讀寫分離MySql
- Amoeba+Mysql 實現讀寫分離MySql
- Amoeba實現讀寫分離
- MySQL + Atlas --- 部署讀寫分離MySql
- Mysql讀寫分離方案-MySQL Proxy環境部署記錄MySql
- ProxySQL實現Mysql讀寫分離 - 部署手冊MySql
- MySQL讀寫分離AtlasMySql
- MySQL 讀寫分離的好處MySql
- 探究MySQL MGR的讀寫分離MySql
- 【Mysql】mysql-proxy讀寫分離MySql
- mysql讀寫分離(PHP類)MySqlPHP
- mysql讀寫分離的最佳實踐MySql
- ProxySQL實現MySQL讀寫分離MySql
- mysql優化之讀寫分離MySql優化
- MySQL cetus 中介軟體 讀寫分離MySql
- MYSQL 主從 + ATLAS 讀寫分離 搭建MySql
- 配置\清除 MySQL 主從 讀寫分離MySql
- Mysql之讀寫分離架構-AtlasMySql架構
- Mysql 高可用(MHA)-讀寫分離(Atlas)MySql
- MySQL主從複製讀寫分離MySql
- MySQL 讀寫分離介紹及搭建MySql
- mysql-proxy實現讀寫分離MySql
- MySQL 官宣:支援讀寫分離了!!MySql
- Redis的讀寫分離Redis
- mongodb的讀寫分離MongoDB
- CentOS7系統下使用Mycat實現mysql讀寫分離部署CentOSMySql
- docker+atlas+mysql實現讀寫分離DockerMySql
- MySQL 中讀寫分離資料延遲MySql
- mysql讀寫分離實戰準備一MySql
- haproxy+keepalived+mycat+mysql (讀寫分離)MySql
- MySQL主從複製與讀寫分離MySql
- [Mysql]主從複製和讀寫分離MySql
- mysql-proxy+keepalived對mysql做讀寫分離MySql
- Mycat實現mysql的負載均衡讀寫分離MySql負載