架構(gòu)圖
amoeba-mysql.png
介紹
- 分布式數(shù)據(jù)庫(kù)前端代理層
- 主要在應(yīng)用層訪問(wèn)MySQL的時(shí)候充當(dāng)query 路由功能
- 對(duì)客戶端透明
- 負(fù)載均衡
- 高可用性
- Query過(guò)濾
- 讀寫分離
- 可路由相關(guān)的query到目標(biāo)數(shù)據(jù)庫(kù)
- 可并發(fā)請(qǐng)求多臺(tái)數(shù)據(jù)庫(kù)合并結(jié)果
- 在Amoeba上面你能夠完成多數(shù)據(jù)源的高可用赖阻、負(fù)載均衡、數(shù)據(jù)切片的功能
缺陷
- 目前還不支持事務(wù)
- 暫時(shí)不支持存儲(chǔ)過(guò)程
- 不適合從amoeba導(dǎo)數(shù)據(jù)的場(chǎng)景或者對(duì)大數(shù)據(jù)量查詢的query并不合適（比如一次請(qǐng)求返回10w以上甚至更多數(shù)據(jù)的場(chǎng)合）
- 暫時(shí)不支持分庫(kù)分表，amoeba目前只做到分?jǐn)?shù)據(jù)庫(kù)實(shí)例摆碉，每個(gè)被切分的節(jié)點(diǎn)需要保持庫(kù)表結(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 測(cè)試服務(wù)器 - 172.17.0.3
1. MySQL 主從復(fù)制
基于 GTID 的主從復(fù)制
-
配置 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)建一個(gè)復(fù)制用戶
master> CREATE USER repl_user; -
給服務(wù)用戶賦予適當(dāng)權(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; -
啟動(dòng)主從復(fù)制
slave> START SLAVE;
-
2. amoeba 配置
-
環(huán)境要求
Amoeba框架是基于Java SE1.5開(kāi)發(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>有兩個(gè)部分需要說(shuō)明一下豁陆。
-
mysql節(jié)點(diǎn)公共信息設(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: 端口號(hào)
schema: 默認(rèn)連接的數(shù)據(jù)庫(kù)，若不存在需要事先創(chuàng)建吵护，否則Amoeba啟動(dòng)報(bào)錯(cuò)
user: mysql用戶名
password: 登錄密碼
-
* 真實(shí)MYSQL服務(wù)器master和slave相關(guān)信息
```
<!-- master節(jié)點(diǎn)繼承abstractServer -->
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- master數(shù)據(jù)庫(kù)主機(jī)地址 -->
<property name="ipAddress">172.17.0.4</property>
</factoryConfig>
</dbServer>
<!-- slave 節(jié)點(diǎn)繼承abstractServer -->
<dbServer name="slave" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- slave數(shù)據(jù)庫(kù)主機(jī)地址 -->
<property name="ipAddress">172.17.0.5</property>
</factoryConfig>
</dbServer>
<!--配置 讀取數(shù)據(jù)庫(kù)節(jié)點(diǎn)池 -->
<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>
```
**對(duì)dbServers.xml文件的配置盒音，主要就是對(duì)dbServer節(jié)點(diǎn)的配置表鳍。其中，readPool節(jié)點(diǎn)需要特別注意祥诽，因?yàn)锳moeba實(shí)現(xiàn)讀寫分離就是根據(jù)它來(lái)實(shí)現(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過(guò)濾信息(安全控制) --> <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ù)器端口號(hào)
user: 登錄amoeba的用戶名
password: 登錄amoeba的密碼
defaultPool: 設(shè)置默認(rèn)數(shù)據(jù)庫(kù)節(jié)點(diǎn)，一般是 master 節(jié)點(diǎn)
writePool: 設(shè)置只讀數(shù)據(jù)庫(kù)節(jié)點(diǎn)
readPool: 設(shè)置只讀數(shù)據(jù)庫(kù)節(jié)點(diǎn)
在amoeba.xml中雄坪，主要完成連接信息和SQL路由配置厘熟。在queryRouter節(jié)點(diǎn)中，通過(guò)配置writePool和readPool可以實(shí)現(xiàn)讀寫分離维哈。配置完成后绳姨，重啟Amoeba
-
驗(yàn)證讀寫分離
- 先暫時(shí)停掉主從復(fù)制
- 在 master 節(jié)點(diǎn)插入數(shù)據(jù)，然后通過(guò) amoeba 查詢
- 在 slave 節(jié)點(diǎn)插入數(shù)據(jù)阔挠，然后通過(guò) amoeba 查詢
