时间：2021-07-01 10:21:17 帮助过：12人阅读
export JAVA_HOME=/usr/
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba ~]# source /etc/profile
[root@amoeba bin]# java -version
java version "1.6.0_38"
OpenJDK Runtime Environment (IcedTea6 1.13.10) (rhel-1.13.10.4.el6-i386)
OpenJDK Client VM (build 23.25-b01, mixed mode)
2 安装配置Amoeba软件
[root@amoeba ~]# mkdir -pv /usr/local/amoeba
[root@amoeba ~]# tar -zxf /usr/local/src/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amoeba ~]# chmod 755 /usr/local/amoeba
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop #显示此内容说明安装成功
3 配置Amoeba读写分离，从服务器读负载均衡
1）在Master、slave服务器中开放权限给Amoeba访问
mysql> grant all on *.* to amoeba@‘192.168.154.%‘ identified by ‘amoeba‘;
2）编辑amoeba.xml配置文件
[root@amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml
<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">amoeba</property>
<property name="password">123456</property>
<property name="filter">
<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">slaves</property>
<property name="needParse">true</property>
</queryRouter>
3）编辑dbServers.xml配置文件
[root@amoeba ~]# vim /usr/local/amoeba/conf/dbServers.xml
<!-- mysql user -->
<property name="user">amoeba</property>
<property name="password">amoeba</property>
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.154.164</property>
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.154.176</property>
</factoryConfig>
</dbServer>
<dbServer name="slave2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.154.156</property>
</factoryConfig>
</dbServer>
<dbServer name="slaves" 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>
4）启动Amoeba，默认端口为tcp:8066
[root@amoeba ~]# amoeba start&
[root@amoeba ~]# lsof -i :8066 #Amoeba默认端口为8066
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
java 3522 root 53u IPv6 21121 0t0 TCP *:8066 (LISTEN)
4 测试
1）在应用服务器上通过Amoeba代理服务器访问MySQL
[root@localhost local]# mysql -uamoeba -p123456 -P8066 -h 192.168.154.177
2）在Master服务器上创建一张表，同步到从服务器，然后停止Slave功能，再写入其它语句。
mysql> create database testdb;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testdb;
Database changed
mysql> create table zang (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.44 sec)
分别在两台从服务器上stop slave;
mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)
然后在主服务器上
mysql> insert into zang values(‘1‘,‘zhang‘,‘this_is_master‘);
Query OK, 1 row affected (0.02 sec)
3）从服务器上同步了testdb库和zang表，手动插入其它内容
slave1:
mysql> use testdb;
Database changed
mysql> insert into zang values(‘2‘,‘zhang‘,‘this_is_slave1‘);
Query OK, 1 row affected (0.00 sec)
slave2:
mysql> use testdb;
Database changed
mysql> insert into zang values(‘3‘,‘zhang‘,‘this_is_slave2‘);
Query OK, 1 row affected (0.03 sec)
4）测试读操作：
在应用服务器上第一次查询的结果如下：
mysql> select * from testdb.zang;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.00 sec)
第二次查询结果如下：
mysql> select * from testdb.zang;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.03 sec)
第三次查询结果如下：
mysql> select * from testdb.zang;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.01 sec)
由以上结果可知Amoeba实现了从服务器的SQL路由、负载均衡的功能
5）测试写操作：
在应用服务器上插入语句
mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into zang values(‘4‘,‘zhang‘,‘write_test‘);
Query OK, 1 row affected (0.02 sec)
在应用服务器上查询，看不到相关的语句
mysql> select * from zang;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.01 sec)
在Master服务器上查询，可以看到相关语句
mysql> select * from zang;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 1 | zhang | this_is_master |
| 4 | zhang | write_test |
+------+-------+----------------+
2 rows in set (0.00 sec)
由以上操作可知，写操作在Master服务器上
因此，通过上面的步骤，已经实现了MySQL主从复制和读写分离，所有写操作都在Master主服务器上，而读操作则分摊给了Slave从服务器，分担数据库的压力。
本文出自 “一万年太久，只争朝夕” 博客，请务必保留此出处http://zengwj1949.blog.51cto.com/10747365/1918983
MySQL之主从复制和读写分离（Amoeba)
标签：mysql 主从复制 读写分离