一.MySQL读写分离
目前创新互联建站已为上1000家的企业提供了网站建设、域名、虚拟空间、绵阳服务器托管、企业网站设计、德城网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
主数据库处理事务性查询，从数据库处理select查询。数据库复制用来把事务性查询导致的变更同步到从数据库中。
二.最为常见的读写分离有两种：
1.基于程序代码实现
在代码中根据select，insert进程路由分类；
优点：性能好，因为在代码中实现，不需要额外的硬件设备；
缺点：需要开发人员来实现，对代码改动比较大，不适合大型复杂应用；
2.基于代理层实现
MySQL-Proxy：隶属于MySQL开源项目；
Amoeba：Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求；
三.实验环境
OS：CentOS 6.5 x64
Amoeba：192.168.0.128
master：192.168.0.134
slave：192.168.0.135
client：192.168.0.137
三.配置主从复制
1.MySQL读写分离是基于主从复制配置的，先配置好主从复制，再配置读写分离。
主从复制博文连接：http://guoxh.blog.51cto.com/10976315/1922643
2.添加授权账号供Amoeba访问
master和slave：
mysql> grant all on *.* to 'proxy'@'192.168.0.128' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
四.Amoeba安装配置
1. Amoeba是基于JDK开发的，所有先安装JAVA环境
[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin
[root@amoeba ~]# ./jdk-6u14-linux-x64.bin
[root@amoeba ~]# cat /etc/profile.d/java.sh
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/lib:$PATH:$HOME/bin
[root@amoeba ~]# source /etc/profile.d/java.sh
[root@amoeba ~]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
#JAVA环境配置完成
2.安装Amoeba
[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
[root@amoeba ~]# cat /etc/profile.d/amoeba.sh
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba ~]# source /etc/profile.d/amoeba.sh
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop # Amoeba安装成功
3.编辑amoeba.xml配置文件
[root@amoeba conf]# cd /usr/local/amoeba/conf/
[root@amoeba conf]# cp amoeba.xml amoeba$(date +"%Y_%m_%d").xml #修改前备份一份
[root@amoeba conf]# vim amoeba.xml
27
28
29
30 amoeba #设置client连接的用户
31
32 amoeba #设置client连接的密码
33
34
35
36 ${amoeba.home}/conf/access_list.conf
37
38
39
40
112
113 ${amoeba.home}/conf/functionMap.xml
114 1500
115 master/property> #指定默认为master
116
117 #删除注释
121 true
3.编辑dbServers.xml
25
26 proxy #填mysql授权的用户
27
28
29 123456 #填MySQL授权的用户密码
30
45
46
47
48 192.168.0.134 #指定master的IP
49
50
51
52
53
54
55 192.168.0.135 #指定slave的IP
56
57
58
59
60
61 1
62
63
64 slave
65
66
4.启动服务
[root@amoeba ~]# amoeba start &
[1] 2666
[root@amoeba ~]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2017-05-07 00:42:16,692 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
2017-05-07 00:42:17,064 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2017-05-07 00:42:17,067 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:56460.
[root@amoeba ~]# netstat -anptl | grep java
tcp 0 0 ::ffff:127.0.0.1:56460 :::* LISTEN 2666/java
tcp 0 0 :::8066 :::* LISTEN 2666/java
tcp 0 0 ::ffff:192.168.0.128:37361 ::ffff:192.168.0.135:3306 ESTABLISHED 2666/java
tcp 0 0 ::ffff:192.168.0.128:35241 ::ffff:192.168.0.134:3306 ESTABLISHED 2666/java
#Amoeba默认端口为8066
五.测试
1.客户端连接Amoeba
[root@client ~]# mysql -uamoeba -p123456 -h 192.168.0.128 -P8066
2.新建一个数据库
mysql> create database guoxh;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| guoxh |
| mysql |
| test |
+--------------------+
5 rows in set (0.01 sec)
3.master和slave查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| guoxh | #刚建的数据库已经同步
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
五.测试读写分离：
1.在master新建一个表，同步到slave上，然后关掉slave功能，在slave上插入区别语句
master：创建名为student的表
mysql> show tables;
Empty set (0.00 sec)
mysql> create table student (id int(10),name varchar(10),info varchar(50));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_guoxh |
+-----------------+
| student |
+-----------------+
1 row in set (0.00 sec)
slave：停止同步
mysql> show tables;
+-----------------+
| Tables_in_guoxh |
+-----------------+
| student |
+-----------------+
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
master：插入区别数据
mysql> insert into student values('1','zhangsan','Mysql_master');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+----------+--------------+
| id | name | info |
+------+----------+--------------+
| 1 | zhangsan | Mysql_master |
+------+----------+--------------+
1 row in set (0.00 sec)slave：插入区别数据
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student values('2','lisi','Mysql_slave');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+------+-------------+
| id | name | info |
+------+------+-------------+
| 2 | lisi | Mysql_slave |
+------+------+-------------+
1 row in set (0.00 sec)3.测试读操作：
client：
mysql> show tables;
+-----------------+
| Tables_in_guoxh |
+-----------------+
| student |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from student;
+------+------+-------------+
| id | name | info |
+------+------+-------------+
| 2 | lisi | Mysql_slave |
+------+------+-------------+
1 row in set (0.00 sec)
mysql> select * from student;
+------+------+-------------+
| id | name | info |
+------+------+-------------+
| 2 | lisi | Mysql_slave |
+------+------+-------------+
1 row in set (0.00 sec)
# 此时，读操作已经已经全部分配到slave上面。
4.测试写操作
client：插入一条数据，这里是看不到数据的，得到master查看
mysql> insert into student values('3','wangwu','Mysql_client');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+------+-------------+
| id | name | info |
+------+------+-------------+
| 2 | lisi | Mysql_slave |
+------+------+-------------+
1 row in set (0.00 sec)master：
mysql> select * from student;
+------+----------+--------------+
| id | name | info |
+------+----------+--------------+
| 1 | zhangsan | Mysql_master |
| 3 | wangwu | Mysql_client |
+------+----------+--------------+
2 rows in set (0.00 sec)
#此时，写操作全部分给了master。
到此为止，MySQL已经实现了读写分离！
分享文章：MySQL读写分离（基于Amoeba）
新闻来源：
http://cxhlcq.com/article/ghojio.html
重庆分公司
重庆分公司
