Mysql 读写分离工具 Amoeba 的配置

最新推荐文章于 2023-05-15 20:32:48 发布

cdszdd8

最新推荐文章于 2023-05-15 20:32:48 发布

阅读量508

点赞数 1

分类专栏： SQL并发工具文章标签：工具

本文链接：https://blog.csdn.net/cdszdd8/article/details/85282265

版权

SQL并发工具专栏收录该内容

1 篇文章 0 订阅

订阅专栏

为了让MySQL的并发能力提升，需要使用第三方工具进行读写分离，这次我们使用阿里的Amoeba来进行读写控制，具体的好处就不做过多解释了。我所有写的文章都是经过验证的。我表达能力欠缺，有写的不好的地方，请大家多担待。

1 先看看Amoeba在我们程序中的位置

2 配置mysql 主从 (mysql 的安装就不写了大家写的已经很全面了)

我们现在有2台机器就先凑合着用吧，我这边安装后mysql后就随便选了一个当Master

10.211.55.13(Master)

10.211.55.14(Slave)

配置10.211.55.13

修改mysql 的配置文件

vi /etc/my.cnf
[mysqld]
#添加以下两行
server-id=1
log-bin=master-bin

需要配置好mysql 需要重启

/etc/init.d/mysql restart

配置好添加一个同步账号让从库用户这个账号进行同步数据（同步是需要时间的所以会有延迟情况，看了一篇文章貌似mysql在5.7+同步是多线程同步机制延迟问题可能会好很多）

在主库创建同步账号:

mysql -u root -p 
create user back identified by  '123456';
grant all privileges on *.* to 'back'@'%'identified by '123456' with grant option;

这样创建了一个back 账号密码是123456

看一下主库的主从配置信息

show master status;

File和Position这个要留住，在从库中需要使用到这两个信息。

修改mysql 的配置文件

[mysqld]
server-id=2
#可以指定要复制的库
replicate-do-db = test #在master端不指定binlog-do-db，在slave端用replication-do-db来过滤
replicate-ignore-db = mysql #忽略的库

需要配置好mysql 需要重启

登录mysql 后执行内容需要替换，HOST , USER , PASSWORD, FILE, POS.

mysql> stop slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.211.55.13',
-> MASTER_USER='back',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='master-bin.000001',
-> MASTER_LOG_POS=1626;
mysql> start slave;

查看一下是否配置完成 show slave status \G; 看下图，如果都是yes 就说明成功了。

3 Amoeba 的配置

数据库主从创建完成后就来配置Amoeba

下载Amoeba https://sourceforge.net/projects/amoeba/ 找到最新版本下载这边下载的是3.0.5

解压后的目录

需要修改两个问 amoeba.xml 和 dbServers.xml

dbServers.xml 用了配置Amoeba 登录mysql 的用户信息和数据库信息

<?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">txx</property>

                        <!-- mysql user -->
                        <property name="user">back</property>

                        <property name="password">back</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="masterdb"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">127.0.0.1</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slavedb"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">10.211.55.14</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slavepool" 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">slavedb</property>
                </poolConfig>
        </dbServer>

</amoeba:dbServers>

amoeba.xml 配置了 amoeba 的基本配置

<?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>

                                        <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">masterdb</property>

<property name="writePool">masterdb</property>
                <property name="readPool">slavepool</property>

                <property name="needParse">true</property>
        </queryRouter>
</amoeba:configuration>

配置 ${amoeba_home}/jvm.properties 替换为下面这个

JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"

配置成功后启动Amoeba

到${amoeba_home}/bin

./launcher

ok 到此为止，看看成果