mysql读写分开Amoeba3.x的实现

  • 内容
  • 评论
  • 相关

mysql读写分离Amoeba3.x的实现

1)安装amoeba(http://sourceforge.net/projects/amoeba/files/)
下载amoeba(1.2.0-GA)后解压到本地(/usr/local/amoeba),即完成安装
2)配置amoeba
# ls
access_list.conf    amoeba.xml        dbServers.xml    functionMap.xml    log4j.xml    ruleFunctionMap.xml
amoeba.dtd                dbserver.dtd    function.dtd     log4j.dtd                rule.dtd     rule.xml
在这里我主要介绍配置  amoeba.xml、dbServers.xml、log4j.xml 三个主要的配置文件,其它文件没有特殊需要默认就可以了夺
# vi access_list.conf 修改允许访问列表
2.0)192.168.152.*:yes
# vi amoeba.xml 修改主配置文件
2.1)把默认端口8066改成3306;前提是你的数据库与amoeba安装的机器不在同一个机器上
<property name="port">3066</property>
2.2)把默认连接用户名和密码改成自己的
<property name="user">root</property>
<property name="password">123456</property>
2.3)把默认的代理服务器客户端进程线程大小数改成300
<!-- proxy server client process thread size -->
<property name="executeThreadSize">300</property>
2.4)把默认注释掉的读写分离选项,把注释去掉并readpool修改成server2
<property name="writePool">server1</property>
<property name="readPool">server2</property>

vi dbServers.xml
增加SEVER2模块,里面的连接用户名密码及地址都表示两台MYSQL的物理机器,192.168.1.11和192.168.1.13 另SERVER1是写,SERVER是读
需要手动增加SERVER2代码,最终改变成如下:
<?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>
      <!-- mysql user -->
      <property name="user">root</property>
      <!-- mysql password -->
      <property name="password">123456</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="testWhileIdle">true</property>
    </poolConfig>
  </dbServer>

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

  <dbServer name="server2"    parent="abstractServer">
    <factoryConfig>
      <!-- mysql ip -->
      <property name="ipAddress">192.168.2.13</property>
    </factoryConfig>
  </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</property>
    </poolConfig>
  </dbServer>
</amoeba:dbServers>

 

6:修改log4j.xml 取消日志文件生成(太大了,磁盘很容易满;)
暂时还不清楚如何进行修改

7:性能优化,打开bin/amoeba(window下可用)
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"
改成
DEFAULT_OPTS="-server -Xms512m -Xmx512m -Xmn100m -Xss1204k"

8.设置压力比
大家可能会想到,我们加入只有两台数据库服务器,一台主,一台从,按照上面的配置只能是主和从的读取比率是1:1,而写又全部在主上进行,这样主的压力就很大了,所以如果能让主和从的读设置权重,比如设置成1:3,这样就可以很好的解决主从不压力均衡的问题!通过研究确实可以!
配置就是将上面的读的池的配置更改一下:
将<property name="poolNames">server1,server2</property>更改成
<property name="poolNames">server1,server2,server2,server2</property>

9:启动amoeba(1.2的nohup /usr/local/amoeba/bin/amoeba start 2>&1 >/dev/null &)
/usr/local/amoeba/bin/launcher
log4j:WARN log4j config load completed from file:D:/openSource/amoeba-mysql-1.2.0-GA/conf/log4j.xml
log4j:WARN ip access config load completed from file:D:/openSource/amoeba-mysql-1.2.0-GA/conf/access_list.conf
2010-07-03 09:55:33,821 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

10.设置mysql主从服务器的允许访问的用户和地址

三.client端调用与测试
1)在装有mysql客户端的机器上使用:
    mysql -uroot -p123456 -P3306 -h192.168.152.146
    参数分别是amoeba的用户名、密码、端口号和所做主机的地址
2)调用与测试
首先插入一条数据:insert into zone_by_id(id,name) values(20003,'name_20003')
通过查看master机上的日志/var/lib/mysql/mysql_log.log:
100703 11:58:42       1 Query       set names latin1
                      1 Query       SET NAMES latin1
                      1 Query       SET character_set_results = NULL
                      1 Query       SHOW VARIABLES
                      1 Query       SHOW COLLATION
                      1 Query       SET autocommit=1
                      1 Query       SET sql_mode='STRICT_TRANS_TABLES'
                      1 Query       SHOW VARIABLES LIKE 'tx_isolation'
                      1 Query       SHOW FULL TABLES FROM amoeba_study LIKE 'PROBABLYNOT'
                      1 Prepare     [1] insert into zone_by_id(id,name) values(?,?)
                      1 Prepare     [2] insert into zone_by_id(id,name) values(?,?)           
                      1 Execute     [2] insert into zone_by_id(id,name) values(20003,'name_20003')
得知写操作发生在master机上
通过查看slave机上的日志/var/lib/mysql/mysql_log.log:
100703 11:58:42       2 Query       insert into zone_by_id(id,name) values(20003,'name_20003')
得知slave同步执行了这条语句
然后查一条数据:select t.name from zone_by_id t where t.id = 20003
通过查看slave机上的日志/var/lib/mysql/mysql_log.log:
100703 12:02:00      33 Query       set names latin1
                     33 Prepare     [1] select t.name from zone_by_id t where t.id = ?
                     33 Prepare     [2] select t.name from zone_by_id t where t.id = ?    
                     33 Execute     [2] select t.name from zone_by_id t where t.id = 20003  
得知读操作发生在slave机上
并且通过查看slave机上的日志/var/lib/mysql/mysql_log.log发现这条语句没在master上执行
通过以上验证得知简单的master-slave搭建和实战得以生效

==========================注意事项=================================================================
Amoeba不支持事务
Amoeba不支持跨库join和排序
Insert语句必须指定表的列名
Amoeba不支持分库分表。
Amoeba不支持分库分表,只能分MySQL实例。
Amoeba不支持大数据量的查询。
Amoeba需要更严格的SQL语句规范

原创文章,转载请注明: 转载自搞代码

本文链接地址: mysql读写分开Amoeba3.x的实现

微信支付二维码

微信 赏一包辣条吧~

支付宝支付二维码

支付宝 赏一听可乐吧~

评论

0条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注