架構(gòu)圖
amoeba-mysql.png
介紹
- 分布式數(shù)據(jù)庫前端代理層
- 主要在應(yīng)用層訪問MySQL的時候充當query 路由功能
- 對客戶端透明
- 負載均衡
- 高可用性
- Query過濾
- 讀寫分離
- 可路由相關(guān)的query到目標數(shù)據(jù)庫
- 可并發(fā)請求多臺數(shù)據(jù)庫合并結(jié)果
- 在Amoeba上面你能夠完成多數(shù)據(jù)源的高可用、負載均衡、數(shù)據(jù)切片的功能
缺陷
- 目前還不支持事務(wù)
- 暫時不支持存儲過程
- 不適合從amoeba導數(shù)據(jù)的場景或者對大數(shù)據(jù)量查詢的query并不合適（比如一次請求返回10w以上甚至更多數(shù)據(jù)的場合）
- 暫時不支持分庫分表，amoeba目前只做到分數(shù)據(jù)庫實例，每個被切分的節(jié)點需要保持庫表結(jié)構(gòu)一致
配置方法
基于 Docker
服務(wù)器配置
- 操作系統(tǒng)：Ubuntu 14.04
- amoeba - 172.17.0.2
- master 寫服務(wù)器 - 172.17.0.4
- slave 讀服務(wù)器 - 172.17.0.5
- test_node 測試服務(wù)器 - 172.17.0.3
1. MySQL 主從復制
基于 GTID 的主從復制
-
配置 master
-
向 my.cnf 添加配置，然后重啟服務(wù)
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp log-bin = master-bin log-bin-index = master-bin.index server-id = 1 gtid-mode = ON log-slave-updates enforce-gtid-consistency -
在 master 上創(chuàng)建一個復制用戶
master> CREATE USER repl_user; -
給服務(wù)用戶賦予適當權(quán)限
master> GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY '123456';
-
-
配置 slave
-
向 my.cnf 添加配置，然后重啟服務(wù)
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp server-id = 2 relay-log-index = slave-relay-bin.index relay-log = slave-relay-bin gtid-mode = ON log-slave-updates enforce-gtid-consistency -
連接 master 和 slave
slave> CHANGE MASTER TO -> MASTER_HOST = '172.17.0.4', -> MASTER_PORT = 3306, -> MASTER_USER = 'repl_user', -> MASTER_PASSWORD = '123456', -> MASTER_AUTO_POSITION = 1; -
啟動主從復制
slave> START SLAVE;
-
2. amoeba 配置
-
環(huán)境要求
Amoeba框架是基于Java SE1.5開發(fā)的，建議使用或不低于Java SE 1.5版本。 -
安裝 amoeba
- 下載地址：http://sourceforge.net/projects/amoeba/files/
- 下載后解壓到指定目錄即可
-
配置 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 schema --> <property name="schema">mysql</property> <!-- mysql user --> <property name="user">root</property> <property name="password">root</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"> <factoryConfig> <!-- mysql ip --> <property name="ipAddress">172.17.0.4</property> </factoryConfig> </dbServer> <dbServer name="slave" parent="abstractServer"> <factoryConfig> <!-- mysql ip --> <property name="ipAddress">172.17.0.5</property> </factoryConfig> </dbServer> <dbServer name="readPool" 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">slave</property> </poolConfig> </dbServer> </amoeba:dbServers>有兩個部分需要說明一下。
-
mysql節(jié)點公共信息設(shè)置
<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 schema --> <property name="schema">mysql</property> <!-- mysql user --> <property name="user">root</property> <property name="password">root</property> </factoryConfig>port: 端口號
schema: 默認連接的數(shù)據(jù)庫，若不存在需要事先創(chuàng)建，否則Amoeba啟動報錯
user: mysql用戶名
password: 登錄密碼
-
* 真實MYSQL服務(wù)器master和slave相關(guān)信息
```
<!-- master節(jié)點繼承abstractServer -->
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- master數(shù)據(jù)庫主機地址 -->
<property name="ipAddress">172.17.0.4</property>
</factoryConfig>
</dbServer>
<!-- slave 節(jié)點繼承abstractServer -->
<dbServer name="slave" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- slave數(shù)據(jù)庫主機地址 -->
<property name="ipAddress">172.17.0.5</property>
</factoryConfig>
</dbServer>
<!--配置 讀取數(shù)據(jù)庫節(jié)點池 -->
<dbServer name="readPool" 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">slave</property>
</poolConfig>
</dbServer>
```
**對dbServers.xml文件的配置，主要就是對dbServer節(jié)點的配置。其中，readPool節(jié)點需要特別注意，因為Amoeba實現(xiàn)讀寫分離就是根據(jù)它來實現(xiàn)。**
-
配置 amoeba.xml
<?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">root</property> <property name="password">root</property> <!—-設(shè)置amoeba過濾信息(安全控制) --> <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">readPool</property> <property name="needParse">true</property> </queryRouter> </amoeba:configuration>port: amoeba 服務(wù)器端口號
user: 登錄amoeba的用戶名
password: 登錄amoeba的密碼
defaultPool: 設(shè)置默認數(shù)據(jù)庫節(jié)點，一般是 master 節(jié)點
writePool: 設(shè)置只讀數(shù)據(jù)庫節(jié)點
readPool: 設(shè)置只讀數(shù)據(jù)庫節(jié)點
在amoeba.xml中，主要完成連接信息和SQL路由配置。在queryRouter節(jié)點中，通過配置writePool和readPool可以實現(xiàn)讀寫分離。配置完成后，重啟Amoeba
-
驗證讀寫分離
- 先暫時停掉主從復制
- 在 master 節(jié)點插入數(shù)據(jù)，然后通過 amoeba 查詢
- 在 slave 節(jié)點插入數(shù)據(jù)，然后通過 amoeba 查詢
