程序员

Amoeba for MySQL

2017-11-13  本文已影响420人  mvictor

架构图

amoeba-mysql.png

介绍

  1. 分布式数据库前端代理层
  2. 主要在应用层访问MySQL的时候充当query 路由功能
  3. 对客户端透明
  4. 负载均衡
  5. 高可用性
  6. Query过滤
  7. 读写分离
  8. 可路由相关的query到目标数据库
  9. 可并发请求多台数据库合并结果
  10. 在Amoeba上面你能够完成多数据源的高可用、负载均衡、数据切片的功能

缺陷

  1. 目前还不支持事务
  2. 暂时不支持存储过程
  3. 不适合从amoeba导数据的场景或者对大数据量查询的query并不合适（比如一次请求返回10w以上甚至更多数据的场合）
  4. 暂时不支持分库分表，amoeba目前只做到分数据库实例，每个被切分的节点需要保持库表结构一致

配置方法

基于 Docker

服务器配置

  1. 操作系统：Ubuntu 14.04
  2. amoeba - 172.17.0.2
  3. master 写服务器 - 172.17.0.4
  4. slave 读服务器 - 172.17.0.5
  5. test_node 测试服务器 - 172.17.0.3

1. MySQL 主从复制

基于 GTID 的主从复制

  1. 配置 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';

  2. 配置 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 配置

  1. 环境要求

    Amoeba框架是基于Java SE1.5开发的，建议使用或不低于Java SE 1.5版本。

  2. 安装 amoeba

    1. 下载地址：http://sourceforge.net/projects/amoeba/files/
    2. 下载后解压到指定目录即可

  3. 配置 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实现读写分离就是根据它来实现。**

  1. 配置 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

  1. 验证读写分离

    1. 先暂时停掉主从复制
    2. 在 master 节点插入数据，然后通过 amoeba 查询
    3. 在 slave 节点插入数据，然后通过 amoeba 查询
上一篇 下一篇

猜你喜欢

热点阅读