mysql读写分离2

Amoeba：
========================================================



Amoeba Master Slave1 Slave2
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
IP 192.168.10.137 192.168.10.6 192.168.10.37 192.168.10.158
Server_ID 6 37 158


一、MySQL主/备复制（略）
1. Amoeba代理服务器连接MySQL Server的用户：
mysql> grant ALL on *.* to admin@'192.168.10.%' identified by 'tianyun';
mysql> flush privileges;

2. 从客户端测试到主/备的连接


二、安装Amoeba
A. JAVA环境
[root@Amoeba ~]# java -version
java version "1.7.0_09-icedtea"
OpenJDK Runtime Environment (rhel-2.3.4.1.el6_3-x86_64)
OpenJDK 64-Bit Server VM (build 23.2-b09, mixed mode)

[root@Amoeba ~]# vim /etc/profile
JAVA_HOME=/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.9.x86_64/jre
export JAVA_HOME
[root@Amoeba ~]# source /etc/profile
[root@Amoeba ~]# env |grep JAVA
JAVA_HOME=/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.9.x86_64/jre

B. Amoeba
[root@Amoeba ~]# lsof -i TCP:3306
[root@Amoeba ~]# mkdir /usr/local/amoeba
[root@Amoeba ~]# tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@Amoeba ~]# ls /usr/local/amoeba/
benchmark bin changelogs.txt conf lib LICENSE.txt README.html


三、配置Amoeba实现读写分离
1. 配置数据库的连接池
[root@Amoeba ~]# vim /usr/local/amoeba/conf/dbServers.xml
<?xml version="1.0" encoding="gbk"?>

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

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

<!-- mysql password -->
<property name="password">tianyun</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="testOnReturn">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>

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

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

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

<dbServer name="VirtualSlave" 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">slave1,slave2</property>
</poolConfig>
</dbServer>

</amoeba:dbServers>


2. amoeba.xml
[root@Amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml
<service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
<!-- port -->
<property name="port">3306</property>

<!-- bind ipAddress -->
<property name="ipAddress">192.168.10.137</property>

<property name="manager">${clientConnectioneManager}</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="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

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

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

<property name="filter">
<bean class="com.meidusa.amoeba.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>

</service>


<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">master</property>
<property name="writePool">master</property>
<property name="readPool">VirtualSlave</property>

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


四、启动并测试
1. 启动
[root@Amoeba conf]# /usr/local/amoeba/bin/amoeba start
The stack size specified is too small, Specify at least 160k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

[root@Amoeba conf]# vim /usr/local/amoeba/bin/amoeba
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"

[root@Amoeba ~]# /usr/local/amoeba/bin/amoeba start
log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2014-02-14 11:20:39,161 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2014-02-14 11:20:39,505 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on /192.168.10.137:3306.
2014-02-14 11:20:39,509 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:45856.

[root@Amoeba ~]# lsof -i TCP:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
java 8210 root 49u IPv6 58456 0t0 TCP LiangPad.LiangYunye:34650->192.168.10.158:mysql (ESTABLISHED)
java 8210 root 54u IPv6 58460 0t0 TCP LiangPad.LiangYunye:47238->192.168.10.6:mysql (ESTABLISHED)
java 8210 root 58u IPv6 58462 0t0 TCP LiangPad.LiangYunye:mysql (LISTEN)

1. 主库
mysql> grant ALL on shop.* to bbs@'192.168.1.%' identified by 'tianyun';
mysql> flush privileges;

mysql> create database shop;
mysql> create table shop.t1 (name varchar(50));

2. 备库
mysql> stop slave; //暂时断掉和主库的连接

3. 从客户端测试
a. 读 ====主 or 备
b. 写 ====主

4. 备库
mysql> start slave;
========================================================

转载于:https://www.cnblogs.com/angel1/p/8669229.html