| MySQL读写分离Amoeba的实现 | ||||||||||||||||
|
1、安装amoeba 下载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=";> <!-- 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"$amp;>{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="co.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$amp;>amp;$amp;1 >/dev/null &) /usr/local/amoeba/bin/launcher log4j:WARN log4j config load completed from file:D:openSourceamoeba-mysql-1.2.0-GAconflog4j.xml log4j:WARN ip access config load completed from file:D:openSourceamoeba-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搭建和实战得以生效
|