Amoeba实现mysql主从读写分离2

时间:2022-09-20 14:45:40
继续上面的操作           <dbServer name="server2">

 

             <!-- PoolableObjectFactory实现类 -->

               <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">

                   <property name="manager">defaultManager</property>

 

                     <!-- 真实mysql数据库端口 -->

                       <property name="port">3306</property>

 

                      <!-- 真实mysql数据库IP -->

                        <property name="ipAddress">10.10.0.77</property>

                         <property name="schema">test</property>

 

                         <!-- 用于登陆mysql的用户名 -->

                          <property name="user">test</property>

 

                         <!-- 用于登陆mysql的密码 -->

 

                           <property name="password">123456</property>

 

             </factoryConfig>

 

            <!-- ObjectPool实现类 -->

              <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">

                   <property name="maxActive">200</property>

                   <property name="maxIdle">200</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="master" virtual="true">

             <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

                      <!-- 负载均衡参数 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->

                         <property name="loadbalance">1</property>

 

                       <!-- 参与该pool负载均衡的poolName列表以逗号分割 -->

                          <property name="poolNames">server1</property>

              </poolConfig>

       </dbServer>

       <dbServer name="slave" virtual="true">

            <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

                 <!-- 负载均衡参数 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->

                       <property name="loadbalance">1</property>

 

                  <!-- 参与该pool负载均衡的poolName列表以逗号分割 -->

                        property name="poolNames">server1,server2</property>

               </poolConfig>

        </dbServer>

 

 

        </dbServerList>

 

<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">

             <property name="ruleConfig">${amoeba.home}/conf/rule.xml</property>

              <property name="functionConfig">${amoeba.home}/conf/functionMap.xml</property>

      <property name="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property>

               <property name="LRUMapSize">1500</property>

               <property name="defaultPool">master</property>

                <property name="writePool">master</property>

                <property name="readPool">slave</property>

                <property name="needParse">true</property>

        </queryRouter>

</amoeba:configuration>

 

 # /usr/local/amoeba/bin/amoeba &   //启动amoeba服务
 # ps aux |grep  /usr/local/amoebaroot      9116  0.0  1.0 460460 43028 pts/0    Sl   12:50   0:00 /usr/java/jdk1.5.0_16/bin/java -server -Xms256m -Xmx256m -Xss128k -Damoeba.home=/usr/local/amoeba -Dclassworlds.conf=/usr/local/amoeba/bin/amoeba.classworlds -classpath /usr/local/amoeba/lib/classworlds-1.0.jar org.codehaus.classworlds.Launcher # netstat -lntpu |grep 8066tcp        0      0 10.10.0.87:8066         :::*       看到进程以及端口说明amoeba服务启动正常测试mysql的读写分离
测试之前先要保证amoeba-server有访问两个主从服务器test库的权限,在主从mysql上都执行:
mysql>  grant all on test.* to test@'10.10.0.%' identified by '123456';mysql>  flush privileges; 登录amoeba服务器使用如下命令(用户名密码和上面配置的要一致):#  mysql -u root -psxit -h10.10.0.87  -P8066  //如果可以登陆并且没有任何错误就说                                               明是OK的. 登录上去后,为了测试读和写必须先把mysql的主从复制停掉,才能更清楚地看出读写的服务器是哪台,在从上使用stop slave;登录到amoeba-mysql上,使用命令mysql -uroot -psxit -h10.10.0.87  -P8066,然后执行写和读操作,查看写的是哪台服务器,读的是哪台服务器,实验结果显示:写只在主上进行,读在主和从都进行,比率是1:1 在没有停止同步之前先在master服务器上创建一个表mysql>  create table sxit (id int(10) ,name varchar(10)); 然后在slave服务器上停止数据同步 mysql> stop slave; 在主从上各插入一条不同数据(供测试读的时候用):在主上插入:insert into sxit values('1','zhangsan');
在从上插入:insert into sxit values('2','lisi');
 登陆到amoeba服务器,进行读写分离的测试: # mysql -u root -psxit -h10.10.0.87  -P8066 mysql> use test;mysql> select * from sxit;          //第一次执行select是在master服务器上查询的+------+----------+| id   | name     |+------+----------+|    1 | zhangsan |+------+----------+1 row in set (0.00 sec) mysql> select * from sxit;           //第二次执行select是在slave服务器上查询的+------+------+| id   | name |+------+------+|    2 | lisi |+------+------+1 row in set (0.01 sec) mysql> insert into sxit values('3','wangmazi');  //插入一条数据,然后在selectQuery OK, 1 row affected (0.05 sec)  mysql> select * from sxit;      //可以看到上面新插入的语句在master上了+------+----------+| id   | name     |+------+----------+|    1 | zhangsan ||    3 | wangmazi |+------+----------+2 rows in set (0.00 sec) mysql> select * from sxit;         //slave服务器上是没有这条新的语句的,+------+------+| id   | name |+------+------+|    2 | lisi |+------+------+1 row in set (0.00 sec)权重的设置
在这里可能会想到,两台数据库服务器,一台主,一台从,按照上面的配置只能是主和从的读取比率是1:1,而写又全部在主上进行,这样主的压力就很大了,所以如果能让主和从的读设置权重,比如设置成1:3,这样就可以很好的解决主从不压力均衡的问题!通过研究确实可以!
配置就是将上面的读的池的配置更改一下:
将<property name="poolNames">server1,server2</property>更改成
<property name="poolNames">server1,server2,server2,server2</property>
 mysql> select * from sxit;+------+------+| id   | name |+------+------+|    2 | lisi |+------+------+1 row in set (0.00 sec) mysql> select * from sxit;+------+------+| id   | name |+------+------+|    2 | lisi |+------+------+1 row in set (0.01 sec) mysql> select * from sxit;+------+------+| id   | name |+------+------+|    2 | lisi |+------+------+1 row in set (0.00 sec) mysql> select * from sxit;+------+----------+| id   | name     |+------+----------+|    1 | zhangsan ||    3 | wangmazi |+------+----------+2 rows in set (0.00 sec) //从以上的查询来看,select确实是安装1:3的比例来进行查询的. 关于amoeba的其他功能大家可以到官方上去看看,里面有关于垂直和水平切割方面的知识,相关信息如下: 开发者博客链接: http://amoeba.sourceforge.net/wordpress/amoeba 中文文档下载地址:http://amoeba.meidusa.com/amoeba.pdfamoeba 未来发展方向:http://amoeba.meidusa.com/amoeba-big-picture.pdf

本文出自 “Devops” 博客,请务必保留此出处http://wiliiwin.blog.51cto.com/928128/462906