mysql数据库的读写分离 amoeba

首页 > 代码库 > mysql数据库的读写分离 amoeba

mysql数据库的读写分离 amoeba

2024-07-12 23:33:34 220人阅读

一 amoeba

Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）。

Amoeba优缺点

优点：

1、降低数据切分带来的复杂多数据库结构

2、提高系统整体可用性

3、提供切分规则并降低数据切分规则给应用带来的影响

4、降低db 与客户端的连接数

5、可以直接实现读写分离及负载均衡效果，而不用修改代码

缺点：

1、不支持事务与存储过程

2、暂不支持分库分表，amoeba目前只做到分数据库实例

3、不适合从amoeba导数据的场景或者对大数据量查询的query并不合适（比如一次请求返回10w以上甚至更多数据的场合）

二数据库读写分离配置

环境

系统 centos 6.4 x86_64

软件 mysql5.6.19 jdk1.7 amoeba2.2

amoeba服务器 192.168.200.19

master 192.168.200.17

slave 192.168.200.18

主从服务器复制见以前的博客此处略

1 配置JDK

[root@bogon ~]# rpm -ivh jdk-7u60-linux-x64.rpm 
[root@bogon ~]# vim /etc/profile.d/java.sh 
export JAVA_HOME=/usr/java/latest
export PATH=$PATH:$JAVA_HOME/bin
[root@bogon ~]# .  /etc/profile.d/java.sh 
[root@bogon ~]# java -version
java version "1.7.0_60"
Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) 64-Bit Server VM (build 24.60-b09, mixed mode)

2 安装amoeba

[root@bogon ~]# mkdir /usr/local/amoeba
[root@bogon ~]# tar -xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba

[root@bogon ~]# cd /usr/local/amoeba/
[root@bogon amoeba]# ls
benchmark  bin  changelogs.txt  conf  lib  LICENSE.txt  README.html
[root@bogon amoeba]# ls conf
access_list.conf  dbserver.dtd   functionMap.xml  rule.dtd
amoeba.dtd        dbServers.xml  log4j.dtd        ruleFunctionMap.xml
amoeba.xml        function.dtd   log4j.xml        rule.xml
  Amoeba总共有7个配置文件，分别如下：

  Amoeba主配置文件($AMOEBA_HOME/conf/amoeba.xml)，用来配置Amoeba服务的基本参数，如Amoeba主机地址、端口、认证方式、用于连接的用户名、密码、线程数、超时时间、其他配置文件的位置等。

  数据库服务器配置文件($AMOEBA_HOME/conf/dbServers.xml)，用来存储和配置Amoeba所代理的数据库服务器的信息，如:主机IP、端口、用户名、密码等。

  切分规则配置文件($AMOEBA_HOME/conf/rule.xml)，用来配置切分规则。

  数据库函数配置文件($AMOEBA_HOME/conf/functionMap.xml)，用来配置数据库函数的处理方法，Amoeba将使用该配置文件中的方法解析数据库函数。

  切分规则函数配置文件($AMOEBA_HOME/conf/ruleFunctionMap.xml)，用来配置切分规则中使用的用户自定义函数的处理方法。

  访问规则配置文件($AMOEBA_HOME/conf/access_list.conf)，用来授权或禁止某些服务器IP访问Amoeba。

  日志规格配置文件($AMOEBA_HOME/conf/log4j.xml)，用来配置Amoeba输出日志的级别和方式。

[root@bogon conf]# vim 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="manager">${defaultManager}</property>
                        <property name="sendBufferSize">64</property>
                        <property name="receiveBufferSize">128</property>

                        <!-- mysql port -->
                        <property name="port">3306</property>

                        <!-- mysql schema -->
                        <property name="schema">test</property>

# 设置amoeba连接后端数据库服务器的账号 密码 需在后端服务器上创建该用户，并授权amoeba连接
                        <!-- mysql user -->
                        <property name="user">root</property>

                        <property name="password">redhat</property>
                        <!--  mysql password
                        <property name="password">password</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="testOnReturn">true</property>
                        <property name="testWhileIdle">true</property>
                </poolConfig>
        </dbServer>
# 设置一个后端服务器 名字可以随便取
        <dbServer name="server1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.200.17</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="server2"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.200.18</property>
                </factoryConfig>
        </dbServer>
#指定一个虚拟数据库服务器，可以将上面定义的dbserver加入这个虚拟服务器，相当组成一个组
 <dbServer name="multiPool" 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">server1,server2</property>
                </poolConfig>
        </dbServer>

</amoeba:dbServers>

[root@bogon conf]# vim 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.net.ServerableConnectionManager">
#设置amoeba监听的端口
                        <!-- port -->
                        <property name="port">8066</property>
#设置监听的接口，如果不设置，则监听所有的IP
                        <!-- bind ipAddress -->
                        <!-- 
                        <property name="ipAddress">127.0.0.1</property>
                         -->

                        <property name="manager">${clientConnectioneManager}</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="authenticator">
                                <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
#提供客户端连接amoeba时需要使用的账号 密码 与 amoeba连接后端数据库服务器密码无关
                                        <property name="user">root</property>

                                        <property name="password">redhat</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>

                </service>
................................................（省略）

 <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">server1</property>
#设置读写分离选项
                <property name="writePool">server1</property>
                <property name="readPool">server2</property>
                <!--
                <property name="writePool">server1</property>
                <property name="readPool">server1</property>
                -->
                <property name="needParse">true</property>
        </queryRouter>
</amoeba:configuration>

配置amoeba 环境变量

[root@bogon conf]# vim /etc/profile.d/amoeba.sh
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin：
[root@bogon conf]# .  /etc/profile.d/amoeba.sh

启动amoeba

在JDK1.7下提示会提示堆内存过小

root@bogon bin]# ./amoeba start

The stack size specified is too small, Specify at least 228k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

[root@bogon bin]# vim amoeba
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"
改为
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

[root@bogon bin]# ./amoeba start
log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2014-07-19 09:12:17,345 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2014-07-19 09:12:47,868 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2014-07-19 09:12:47,870 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:19499.

测试

在主DB server上操作

mysql> create databaes db;
mysql>use db
mysql>create table t1(name varchar(10);
mysql>grant all privileges on *.* to ‘root‘@‘192.168.200.%‘ identified by ‘redhat‘;
mysql>flush privileges;

root@bogon ~]# mysql -uroot -predhat -h192.168.200.19  --port 8066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 390789680
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)
mysql> use db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)

注 amoeba服务器提示

java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed

mysql>

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 11416420
Current database: test
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 11416420
Current database: test
ERROR 2006 (HY000): MySQL server has gone away

amoeba服务器连接mysql过慢，超时，因mysql 对客户端连接不管是host或ip 都要反解

可在/etc/my.cnf

[mysqld]

skip-name-resolve

mysql数据库的读写分离 amoeba

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > mysql数据库的读写分离 amoeba

mysql数据库的读写分离 amoeba

看完仍有疑问？有类似问题直接问程序猿