MySQL 读写分离 （基于Amoeba）

一.MySQL读写分离

主数据库处理事务性查询，从数据库处理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.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/lib:$PATH:$HOME/bin[root@amoeba ~]# source  /etc/profile.d/java.sh [root@amoeba ~]# java -versionjava 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/amoebaamoeba 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.xml27  28       29 30           amoeba   #设置client连接的用户31 32           amoeba #设置client连接的密码33 34              35              36                ${amoeba.home}/conf/access_list.conf37              38            39       40  112                 113                 ${amoeba.home}/conf/functionMap.xml114                 1500115                 master/property> #指定默认为master116 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的IP49                 50         51 52         53                 54                         55                         192.168.0.135 #指定slave的IP56                 57         58         59                 60                         61                         162 63                         64                         slave65                 66         

4.启动服务

[root@amoeba ~]# amoeba start &[1] 2666[root@amoeba ~]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2017-05-07 00:42:16,692 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf2017-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 javatcp        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已经实现了读写分离！