架构图
amoeba-mysql.png
介绍
- 分布式数据库前端代理层
- 主要在应用层访问MySQL的时候充当query 路由功能
- 对客户端透明
- 负载均衡
- 高可用性
- Query过滤
- 读写分离
- 可路由相关的query到目标数据库
- 可并发请求多台数据库合并结果
- 在Amoeba上面你能够完成多数据源的高可用、负载均衡、数据切片的功能
缺陷
- 目前还不支持事务
- 暂时不支持存储过程
- 不适合从amoeba导数据的场景或者对大数据量查询的query并不合适（比如一次请求返回10w以上甚至更多数据的场合）
- 暂时不支持分库分表，amoeba目前只做到分数据库实例，每个被切分的节点需要保持库表结构一致
配置方法
基于 Docker
服务器配置
- 操作系统：Ubuntu 14.04
- amoeba - 172.17.0.2
- master 写服务器 - 172.17.0.4
- slave 读服务器 - 172.17.0.5
- test_node 测试服务器 - 172.17.0.3
1. MySQL 主从复制
基于 GTID 的主从复制
-
配置 master
-
向 my.cnf 添加配置，然后重启服务
[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 上创建一个复制用户
master> CREATE USER repl_user; -
给服务用户赋予适当权限
master> GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY '123456';
-
-
配置 slave
-
向 my.cnf 添加配置，然后重启服务
[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 配置
-
环境要求
Amoeba框架是基于Java SE1.5开发的，建议使用或不低于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节点公共信息设置
<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: 默认连接的数据库，若不存在需要事先创建，否则Amoeba启动报错
user: mysql用户名
password: 登录密码
-
* 真实MYSQL服务器master和slave相关信息
```
<!-- master节点继承abstractServer -->
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- master数据库主机地址 -->
<property name="ipAddress">172.17.0.4</property>
</factoryConfig>
</dbServer>
<!-- slave 节点继承abstractServer -->
<dbServer name="slave" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- slave数据库主机地址 -->
<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>
```
**对dbServers.xml文件的配置，主要就是对dbServer节点的配置。其中，readPool节点需要特别注意，因为Amoeba实现读写分离就是根据它来实现。**
-
配置 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> <!—-设置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 服务器端口号
user: 登录amoeba的用户名
password: 登录amoeba的密码
defaultPool: 设置默认数据库节点，一般是 master 节点
writePool: 设置只读数据库节点
readPool: 设置只读数据库节点
在amoeba.xml中，主要完成连接信息和SQL路由配置。在queryRouter节点中，通过配置writePool和readPool可以实现读写分离。配置完成后，重启Amoeba
-
验证读写分离
- 先暂时停掉主从复制
- 在 master 节点插入数据，然后通过 amoeba 查询
- 在 slave 节点插入数据，然后通过 amoeba 查询
