博客

mysql>grant all privileges on *.* to amoeba@'%' identified by 'amoeba';

在master上操作，开放一个登录名zcfz，密码zcfz的给slave登录主从复制帐号

mysql>grant all privileges on *.* to zcfz@'%' identified by 'zcfz';
mysql>show master status;

然后在slave1上操作，slave1和slave2作为从机登录到master上复制数据，实现主从复制

mysql>change master to master_host='192.168.111.142',master_user='zcfz',master_password='zcfz',master_log_file='mysql-bin.000004',master_log_pos=9003;
mysql>start slave;
mysql>show slave status\G;

vim /usr/local/amoeba/conf/amoeba.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

	<server>
		<!-- proxy server绑定的端口 -->
		<property name="port">8066</property>
		
		<!-- proxy server绑定的IP -->
		<!-- 
		<property name="ipAddress">127.0.0.1</property>
		-->

		<!-- proxy server net IO Read thread size -->
		<property name="readThreadPoolSize">20</property>
		
		<!-- proxy server client process thread size -->
		<property name="clientSideThreadPoolSize">30</property>
		
		<!-- mysql server data packet process thread size -->
		<property name="serverSideThreadPoolSize">30</property>
		
		<!-- socket Send and receive BufferSize(unit:K)  -->
		<property name="netBufferSize">128</property>
		
		<!-- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). -->
		<property name="tcpNoDelay">true</property>
		
		<!-- 对外验证的用户名 -->
		<property name="user">amoeba</property>              <!-- 改成mysql客户端连接到amoeba用的用户名 -->
		
		<!-- 对外验证的密码 -->
		
		<property name="password">amoeba</property>          <!-- 密码被注释了， 自己改密码 -->
		
		
		<!-- query timeout( default: 60 second , TimeUnit:second) -->
		<property name="queryTimeout">60</property>
	</server>
	
	<!-- 
		每个ConnectionManager都将作为一个线程启动。
		manager负责Connection IO读写/死亡检测
	-->
	<connectionManagerList>
		<connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
			<property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
			
			<!-- 
			  default value is avaliable Processors 
			<property name="processors">5</property>
			 -->
		</connectionManager>
	</connectionManagerList>
	
	<dbServerList>
		<!-- 
			一台mysqlServer 需要配置一个pool，
			如果多台 平等的mysql需要进行loadBalance， 
			平台已经提供一个具有负载均衡能力的objectPool：com.meidusa.amoeba.mysql.server.MultipleServerPool
			简单的配置是属性加上 virtual="true",该Pool 不允许配置factoryConfig
			或者自己写一个ObjectPool。
		-->
		<dbServer name="server1">
			
			<!-- 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">192.168.111.142</property>   <!-- 填master的IP地址，amoeba写操作的主机
				<property name="schema">test</property>
				
				<!-- 用于登陆mysql的用户名 -->
				<property name="user">amoeba</property>            <!-- master授权的用户和密码 -->
				
				<!-- 用于登陆mysql的密码 -->
				
				
				<property name="password">amoeba</property>       <!-- 取消注释，填master的密码     -->
				
				
			</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="server2">     <!-- 复制一份上面server1的内容粘贴到此处,修改成server2,并改成slave1的配置 -->

                        <!-- 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">192.168.111.143</property>    <!-- slave1的IP -->
                                <property name="schema">test</property>

                                <!-- 用于登陆mysql的用户名 -->
                                <property name="user">amoeba</property>                   <!-- slave1的用户名 -->

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


                                <property name="password">amoeba</property>               <!-- slave1的密码 -->


                        </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">       <!--  复制上面master内容，粘贴到此处,改名成slave    -->
                        <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                                <!-- 负载均衡参数 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                                <property name="loadbalance">1</property>

                                <!-- 参与该pool负载均衡的poolName列表以逗号分割 -->
                                <property name="poolNames">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>    <!-- 修改默认操作为master -->
		
		
		<property name="writePool">master</property>      <!-- 取消注释， 写操作用master-->
		<property name="readPool">slave</property>        <!-- 读操作用slave -->
		
		<property name="needParse">true</property>
	</queryRouter>
	
</amoeba:configuration>

修改bin/amoeba启动文件，默认配置的java虚拟机堆栈空间太小，修改大一点

[root@test1 bin]# vim amoeba

#DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"  
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

在master 上创建test库，master 会自动同步到slave1上，因为amoeba工作需要一张默认工作库

mysql>create database test;

七、测试

在amoaba主机上到/usr/local/amoeba/bin目录下启动amoaba，注意查看日志

[root@test1 bin]# ./amoeba

看到server listening on 0.0.0.0/0.0.0.0:8066信息就是启动成功了，然后mysql客户端就可以连接到amoeba的IP：192.168.111.141的端口8066测试mysql的读写分离了

要测试是否读写分离，使实验效果明显，需要停止主从同步，主从数据差异即可观察到主从分离了

在slave1上操作

mysql>stop slave;