1、环境：
|
10.96.47.195:3306 |
用于写 |
|
10.96.47.195:3307 |
用于读，暂时关闭从服务 |
|
10.96.47.195:8066 |
是amoeba代理服务的端口 |
在3306和3307中分别授权用于amoeba连接的用户信息
[root@HaiwaiTest_VM_195 conf]# mysql -S /home/mysql_3306/mysql.sock
GRANT USAGE ON *.* TO 'test_proxy'@'10.25.47.195' IDENTIFIED BY 'test_proxy' ;
GRANT ALL PRIVILEGES ON `zstest`.* TO 'test_proxy'@'10.25.47.195';
[root@HaiwaiTest_VM_195 conf]# mysql -S /home/mysql_3307/mysql.sock
GRANT USAGE ON *.* TO 'test_proxy'@'10.25.47.195' IDENTIFIED BY 'test_proxy' ;
GRANT ALL PRIVILEGES ON `zstest`.* TO 'test_proxy'@'10.25.47.195';
2、配置amoeba
[root@HaiwaiTest_VM_195 ~]# cd /usr/local/amoeba-mysql/conf
[root@HaiwaiTest_VM_195 conf]# vim amoeba.xml 修改以下内容
|
<property name="port">8066</property> |
amoeba服务的默认启动端口,可以修改为其他端口 |
|
<property name="user">amoeba_test</property> |
可以随便写，用于登陆代理使用的mysql -h -uamoeba_test -p123456 -P8066 使用的 |
|
<property name="password">123456</property> |
可以随便写，登陆密码 |
|
<property name="defaultPool">server1</property> |
|
|
<property name="writePool">server1</property> |
用于写的服务器，server1需要在dbServers.xml中配置，稍后会有详细配置内容说明 |
|
<property name="readPool">server2</property> |
用于读的服务器，server2需要在dbServers.xml中配置，稍后会有详细配置内容说明 |
[root@HaiwaiTest_VM_195 conf]# vim dbServers.xml 修改以下内容
<!-- mysql port -->
|
<property name="port">3306</property> |
？ 是用来配置写的默认端口的吗 |
<!-- mysql schema -->
|
<property name="schema">zstest</property> |
amoeba连接连接的库名，这个需要提前在库中有此授权记录 |
<!-- mysql user -->
|
<property name="user">test_proxy</property> |
amoeba连接数据库的用户名，这个需要提前在库中有此授权记录 |
|
<property name="password">test_proxy</property> |
amoeba连接数据库的密码，这个需要提前在库中有此授权记录 |
<dbServer name="server1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
|
<property name="ipAddress">10.25.47.195</property> |
配置server1的连接信息，此项为连接的IP |
|
<property name="port">3306</property> |
配置server1的连接信息，此项为连接的IP的端口号，因为server1要实现写的功能，所有为3306端口 |
</factoryConfig>
</dbServer>
<dbServer name="server2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
|
<property name="ipAddress">10.25.47.195</property> |
配置server2的连接信息，此项为连接的IP |
|
<property name="port">3307</property> |
配置server2的连接信息，此项为连接的IP的端口号，因为server2要实现读的功能，所有为3307端口 |
</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">server2,server1,server2</property>
</poolConfig>
</dbServer>
3、启动
[root@HaiwaiTest_VM_195 conf]# amoeba start
检查是否启动成功
[root@HaiwaiTest_VM_195 conf]# netstat -tulnp | grep java
tcp 0 0 :::8066 :::* LISTEN 13068/java
tcp 0 0 ::ffff:127.0.0.1:27624 :::* LISTEN 13068/java
4、测试读写分离功能
|
195：3306 |
195：3307 |
195:8066 Amoeba服务端 |
|
[root@HaiwaiTest_VM_195 conf]# tail -f /tmp/general_3306.log |
[root@HaiwaiTest_VM_195 conf]# tail -f /tmp/general_3307.log |
启动 [root@HaiwaiTest_VM_195 conf]# amoeba start log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql/conf/log4j.xml 2013-02-01 18:35:42,090 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-mysql/conf/access_list.conf 2013-02-01 18:35:42,992 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066. 2013-02-01 18:35:42,994 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:40216. |
|
|
130204 15:08:08 94 Query select @@version_comment limit 1 |
登陆 [root@HaiwaiTest_VM_195 conf]#mysql -h127.0.0.1 -uamoeba_test -p123456 -P 8066 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 792503505 Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Percona Server (GPL), Release rel21.0, Revision 158 |
|
130204 14:10:47 378 Query show databases |
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | | zstest | +--------------------+ 3 rows in set (0.01 sec)
|
|
|
130204 14:10:32 79 Query SELECT DATABASE() |
mysql> use zstest; Database changed |
|
130204 14:11:13 378 Query insert into t1 select 15,'test' |
由于没开slave的同步，所以这里看不到 |
写测试： mysql> insert into t1 select 15,'test'; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
|
130204 14:11:31 79 Query select * from t1 where a=15 |
第一次读测试：（读写分离成功） mysql> select * from t1 where a=15; +------+------+ | a | b | +------+------+ | 15 | test | | 15 | test | | 15 | test | +------+------+ 3 rows in set (0.04 sec) |
|
130204 17:13:19 460 Query select * from t1 |
|
第二次读测试：在server1端查询 （实现负载均衡，因为在这个文件中控制了负载均衡的配置，每一次select会按照server2，server1，server2的顺序进行查询 vim dbServers.xml <property name="poolNames">server2,server1,server2</property> ） mysql> select * from t1; +------+------+ | a | b | +------+------+ | 1 | test | | 2 | test | +------+------+ 2 rows in set (0.00 sec)
|
|
|
130204 17:13:16 152 Query select * from t1 |
第三次读测试：在server2端查询 mysql> select * from t1; +------+------+ | a | b | +------+------+ | 1 | test | | 2 | test | +------+------+ 2 rows in set (0.00 sec) |
|
|
2013-02-04 15:38:15,440 WARN net.ServerableConnectionManager - Amoeba for Mysql shutdown completed! 2013-02-04 15:38:15,441 WARN net.ServerableConnectionManager - Amoeba Monitor Server shutdown completed! |
关闭amoeba，结束测试 2013-02-04 15:38:15,440 WARN net.ServerableConnectionManager - Amoeba for Mysql shutdown completed! 2013-02-04 15:38:15,441 WARN net.ServerableConnectionManager - Amoeba Monitor Server shutdown completed! |
