mysql集群-amoeba读写分离



mysql读写分离

架构图：

说明：

Mysql1 master 已存在使用中数据库实例已经开始binlog

Mysql2 slave   1的从服务器

Amoeba             代理服务器提供读写分离管理轮询

 

目的：

实现应用访问mysql服务端的时候,写主库读辅库,slave通过binlog同步master端数据供应用读操作,应用读写分离

缺点：

当主库不可用时应用端写操作失败报错,应用端只能读,slave端不可用时只写不可读

不可用情况：网络、服务器故障、mysql服务中断，服务器hang等异常情况

 

环境：

3台64位centos6.3虚拟机

2台mysql5.6.19 serverrpm安装包

1台用作amoeba代理服务器

主机：

Mysql1信息如下：

Mysql2信息如下：

Amoeba信息如下：

 

实施步骤：

1. 配置主辅同步

1. 检查主库配置文件my.cnf,找到具有唯一标示属性配置

   

   这里可以看到server_id具有唯一性，拷贝该文件到辅库并修改对应server_id改完后如下

   

    

2. Mysql1置为只读，备份mysql2上面的mysql数据目录,拷贝mysql1数据替代

   mysql1：

   flush tables with read lock;

   mysql2：

   mv mysql/ mysqlbak/

   1:

   scp -r /var/lib/mysql/192.168.137.4:/var/lib/

   2:

   /var/lib/mysql

   cp ../mysqlbak/auto.cnf.bak .

   mv auto.cnf.bak auto.cnf

   chown mysql:mysql mysql/ -R

    

   mysql1:

   授权

   grant  all privileges on *.* to 'backup'@'192.168.137.4' identified by'backup'; flush privileges;

   show master status; 记下日志和id

   

3. 启动mysql2

    servicemysql start---正常启动后检查是否有错误异常

    mysql2:

change master tomaster_host='192.168.137.3',master_user='backup',master_password='backup',master_log_file='mysql-bin.000001',master_log_pos=514;

start slave;

show slave status \G;

4、解锁mysql1

unlock tables;

------=--主从复制已经配置完成

二、配置代理

 Amoeba下载地址http://blogimg.chinaunix.net/blog/upfile2/101027160252.zip

 Java jdk下载地址http://java.sun.com/javase/downloads/index.jsp

1. 安装jdk配置环境变量如下

   

2. 解压amoeba到指定目录

   Unzip 101027160252.zip

3. 备份配置文件amoeba.xml修改配置文件

cp amoeba.xml amoeba.xml_bak

修改后配置如下：

<?xml version="1.0" encoding="GBK"?>

<!DOCTYPE configuration SYSTEM "amoeba.dtd">

-<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">


-<server>

<property name="port">8066</property>

<property name="ipAddress">192.168.137.5</property>

<!-- proxy server net IO Read thread size -->


<property name="readThreadPoolSize">20</property>

<!-- proxy server client process thread size -->


<property name="clientSideThreadPoolSize">30</property>

<!-- mysql server data packet process thread size -->


<property name="serverSideThreadPoolSize">30</property>

<!-- socket Send and receive BufferSize(unit:K) -->


<property name="netBufferSize">128</property>

<!-- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). -->


<property name="tcpNoDelay">true</property>

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

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

<!-- query timeout( default: 60 second , TimeUnit:second) -->


<property name="queryTimeout">60</property>

</server>


-<connectionManagerList>


-<connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">

<property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

<!-- default value is avaliable Processors <property name="processors">5</property> -->


</connectionManager>

</connectionManagerList>


-<dbServerList>


-<dbServer name="server1">


-<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">

<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">192.168.137.3</property>

<!-- <property name="schema">test</property> -->


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

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

</factoryConfig>


-<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">

<property name="maxActive">200</property>

<property name="maxIdle">200</property>

<property name="minIdle">10</property>

<property name="minEvictableIdleTimeMillis">600000</property>

<property name="timeBetweenEvictionRunsMillis">600000</property>

<property name="testOnBorrow">true</property>

<property name="testWhileIdle">true</property>

</poolConfig>

</dbServer>


-<dbServer name="server2">


-<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">

<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">192.168.137.4</property>

<!-- <property name="schema">test</property> -->


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

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

</factoryConfig>


-<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">

<property name="maxActive">200</property>

<property name="maxIdle">200</property>

<property name="minIdle">10</property>

<property name="minEvictableIdleTimeMillis">600000</property>

<property name="timeBetweenEvictionRunsMillis">600000</property>

<property name="testOnBorrow">true</property>

<property name="testWhileIdle">true</property>

</poolConfig>

</dbServer>


-<dbServer name="master" virtual="true">


-<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

<property name="loadbalance">1</property>

<property name="poolNames">server1</property>

</poolConfig>

</dbServer>


-<dbServer name="slave" virtual="true">


-<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

<property name="loadbalance">1</property>

<property name="poolNames">server2</property>

</poolConfig>

</dbServer>

</dbServerList>


-<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">

<property name="ruleConfig">${amoeba.home}/conf/rule.xml</property>

<property name="functionConfig">${amoeba.home}/conf/functionMap.xml</property>

<property name="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property>

<property name="LRUMapSize">1500</property>

<property name="defaultPool">master</property>

<property name="writePool">master</property>

<property name="readPool">slave</property>

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

</queryRouter>

</amoeba:configuration>

1. 授权代理与mysql服务器访问权限

   这里主从已经同步所以在主端执行就可以了

   主：

   grant  all privileges on *.* to 'amoeba'@'192.168.137.%' identified by'amoeba'; flush privileges;

2. 修改代理启动文件，启动代理

   # vim amoeba

   修改58行的Xss参数：

   DEFAULT_OPTS="-server -Xms1024m-Xmx1024m -Xss128k"

   修改为：

   DEFAULT_OPTS="-server -Xms1024m-Xmx1024m -Xss256k"

    

   环境已经配置完成启动

   ./amoeba &

   客户端连接

   

三、测试读写分离

1、验证主从同步：

主端执行mysql1：

use wangquan;

create table test(id int,name varchar(30));

insert into test values(1,'test1');

分别查询主从数据

2、验证读写分离(停止主从同步如果不同步无法知道读写使用的服务器)

Mysql2：

stop slave

查询test数据情况

客户端插入数据

 

已经可以看出实现读写分离，启动mysql2的同步

再次查询，已经同步数据一致

 

故障转移测试(读服务器网络故障或服务器无法启动)

 

负载均衡配置和测试可以轮询着读或写