Chinaunix首页 | 论坛 | 博客
  • 博客访问: 608889
  • 博文数量: 244
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 130
  • 用 户 组: 普通用户
  • 注册时间: 2016-06-27 09:53
个人简介

记录学习,记录成长

文章分类

全部博文(244)

我的朋友

分类: LINUX

2016-01-06 11:31:21

基于amoeba和GTID功能实现mysql5.6读写分离

一.amoeba简介
1.Amoeba介绍
     Amoeba是一个以MySQL为底层数据存储,并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求,依据用户事先设置的规则,将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比,作者强调的是amoeba配置的方便(基于XML的配置文件,用SQLJEP语法书写规则,比基于lua脚本的MySQL Proxy简单)。
     Amoeba相当于一个SQL请求的路由器,目的是为负载均衡、读写分离、高可用性提供机制,而不是完全实现它们。用户需要结合使用MySQL的Replication等机制来实现副本同步等功能。amoeba对底层数据库连接管理和路由实现也采用了可插拨的机制,第三方可以开发更高级的策略类来替代作者的实现。


2.amoeba主要解决的问题
                        (1).数据切分后复杂数据源整合;

                        (2).提供数据切分规则并降低数据切分规则给数据库带来的影响;

                        (3).降低数据库与客户端连接;

                        (4).读写分离路由;

3.amoeba与mysql-proxy在读写分离上的区别
在MySQLproxy6.0版本上面如果想要读写分离并且读集群、写集群机器比较多情况下,用mysqlproxy需要相当大的工作量,目前mysqlproxy没有现成的lua脚本。
mysqlproxy根本没有配置文件,lua脚本就是它的全部,当然lua是相当方便的。那么同样这种东西需要编写大量的脚本才能完成一个复杂的配置。而Amoeba只需要进行相关的配置就可以满足需求。


4.amoeba工作原理

用户访问amoeba服务器,其中读操作被amoeba服务器路由到从服务器(这里要求从服务器只负责读操作,主服务器只负责写操作),写操作被路由到主服务器,又由于主从复制的功能,主服务器将用户的写操作再复制到从服务器上,从而实现读写分离;

二.配置过程

1.配置环境
主服务器:192.168.85.144                   node1.a.com
从服务器:192.168.85.145                   node2.a.com
amoeba服务器:192.168.85.128               proxy.a.com
已配置好mysql5.6基于GTID的主从复制环境,详见:http://blog.chinaunix.net/uid-30212356-id-5580259.html

2.java环境的安装
Amoeba框架是基于Java SE1.5开发的,建议使用Java SE 1.5版本。目前Amoeba经验证在JavaTM SE1.5和Java SE1.6能正常运行
这里使用:
[root@proxy ~]# ls | grep jdk
jdk-6u45-linux-i586-rpm.bin


2.1先赋给jdk包可执行权限然后运行
[root@proxy ~]# chmod +x jdk-6u45-linux-i586-rpm.bin

[root@proxy ~]# ./jdk-6u45-linux-i586-rpm.bin


2.2配置JAVA环境
[root@proxy ~]# cat /etc/profile.d/java.sh
export JAVA_HOME=/usr/java/latest
export PATH=$JAVA_HOME/bin:$PATH

[root@proxy ~]# . /etc/profile.d/java.sh


2.3验证JAVA安装:
[root@proxy ~]# java -version
java version "1.6.0_45"
Java(TM) SE Runtime Environment (build 1.6.0_45-b06)
Java HotSpot(TM) Client VM (build 20.45-b01, mixed mode, sharing)

3.安装amoeba
3.1因为amoeba解压后不会自己创建目录,因此手动创建
[root@proxy ~]# mkdir -pv /usr/local/amoeba
mkdir: created directory `/usr/local/amoeba'


3.2解压到指定目录
[root@proxy ~]# tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/


Amoeba有哪些主要的配置文件?
(1)想象Amoeba作为数据库代理层,它一定会和很多数据库保持通信,因此它必须知道由它代理的数据库如何连接,比如最基础的:主机IP、端口、Amoeba使用的用户名和密码等等。这些信息存储在$AMOEBA_HOME/conf/dbServers.xml中;


(2)Amoeba为了完成数据切分提供了完善的切分规则配置,为了了解如何分片数据、如何将数据库返回的数据整合,它必须知道切分规则。与切分规则相关的信息存储在$AMOEBA_HOME/conf/rule.xml中;


(3)当我们书写SQL来操作数据库的时候,常常会用到很多不同的数据库函数,比如:UNIX_TIMESTAMP()、SYSDATE()等等。这些函数如何被Amoeba解析呢?$AMOEBA_HOME/conf/functionMap.xml描述了函数名和函数处理的关系;


(4)对$AMOEBA_HOME/conf/rule.xml进行配置时,会用到一些我们自己定义的函数,比如我们需要对用户ID求HASH值来切分数据,这些函数在$AMOEBA_HOME/conf/ruleFunctionMap.xml中定义;


(5)Amoeba可以制定一些可访问以及拒绝访问的主机IP地址,这部分配置在$AMOEBA_HOME/conf/access_list.conf中;


(6)Amoeba允许用户配置输出日志级别以及方式,配置方法使用log4j的文件格式,文件是$AMOEBA_HOME/conf/log4j.xml;


4.编辑amoeba配置文件
4.1 编辑dbServer.xml文件

dbServers.xml文件

  1. <?xml version="1.0" encoding="gbk"?>

  2. <!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
  3. <amoeba:dbServers xmlns:amoeba="">

  4.                 <!--
  5.                         Each dbServer needs to be configured into a Pool,
  6.                         If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
  7.                          add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
  8.                          such as 'multiPool' dbServer
  9.                 -->

  10.         <dbServer name="abstractServer" abstractive="true">
  11.                 <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
  12.                         <property name="manager">${defaultManager}</property>
  13.                         <property name="sendBufferSize">64</property>
  14.                         <property name="receiveBufferSize">128</property>

  15.                         <!-- mysql port -->
  16.                         <property name="port">3306</property>    #设置mysql数据库的端口;

  17.                         <!-- mysql schema -->
  18.                         <property name="schema">test</property>  #连接amoeba后,默认的数据库;  

  19. #设置amoeba连接后端mysql服务器的账号和密码,需在后端数据库器上创建该用户,并授权amoeba连接;                        
  20.                         <!-- mysql user -->
  21.                         <property name="user">root</property>

  22.                         <!-- mysql password
  23.                         <property name="password">password</property>
  24.                         -->
  25.                         <property name="password">amoeba</property>
  26.                 </factoryConfig>

  27.                 <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
  28.                         <property name="maxActive">500</property>
  29.                         <property name="maxIdle">500</property>
  30.                         <property name="minIdle">10</property>
  31.                         <property name="minEvictableIdleTimeMillis">600000</property>
  32.                         <property name="timeBetweenEvictionRunsMillis">600000</property>
  33.                         <property name="testOnBorrow">true</property>
  34.                         <property name="testOnReturn">true</property>
  35.                         <property name="testWhileIdle">true</property>
  36.                 </poolConfig>
  37.         </dbServer>

  38. #设置后端一个服务器的名;
  39.         <dbServer name="master" parent="abstractServer">
  40.                 <factoryConfig>
  41.                         <!-- mysql ip -->
  42.                         <property name="ipAddress">192.168.85.144</property>
  43.                 </factoryConfig>
  44.         </dbServer>

  45.         <dbServer name="slave" parent="abstractServer">
  46.                 <factoryConfig>
  47.                         <!-- mysql ip -->
  48.                         <property name="ipAddress">192.168.85.145</property>
  49.                 </factoryConfig>
  50.         </dbServer>

  51. #指定一个虚拟的dbServer,将上面定义的dbserver加入这个虚拟的dbserver,相当于组成一个dbServer组;
    #名称虽然可以任意取,但是最好不要取为readPool,这会产生不必要的错误;

  52.         <dbServer name="myreadPool" virtual="true">
  53.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
  54.                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
  55.                         <property name="loadbalance">1</property>
  56. #对该虚拟dbServer中后端服务器的调度方法,1为轮询,2为权值,3为HA;
  57.                         <!-- Separated by commas,such as: server1,server2,server1 -->
  58.                         <property name="poolNames">slave,slave,master</property>
  59. #定义该虚拟dbServer的使用顺序,即读操作先路由到slave然后slave最后master,这里可实现负载均衡功能;
  60.                 </poolConfig>
  61.         </dbServer>

  62. </amoeba:dbServers>



4.2 编辑amoeba.xml文件

amoeba.xml文件

  1. <?xml version="1.0" encoding="gbk"?>

  2. <!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
  3. <amoeba:configuration xmlns:amoeba="">

  4.         <proxy>

  5.                 <!-- service class must implements com.meidusa.amoeba.service.Service -->
  6.                 <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
  7.                         <!-- port -->
  8.                         <property name="port">3306</property>    #设置amoeba监听的端口(如果是默认端口,后面连接时需要指定);

  9.                         <!-- bind ipAddress -->
  10.                         <!--
  11.                         <property name="ipAddress">127.0.0.1</property>    #设置监听的接口,如果不设置,则监听所有的IP;
  12.                          -->

  13.                         <property name="manager">${clientConnectioneManager}</property>

  14.                         <property name="connectionFactory">
  15.                                 <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
  16.                                         <property name="sendBufferSize">128</property>
  17.                                         <property name="receiveBufferSize">64</property>
  18.                                 </bean>
  19.                         </property>

  20. #提供客户端连接amoeba时需要使用的账号 (这里的账号密码和amoeba连接后端数据库服务器的密码无关,也无需在服务器中授权)
  21.                         <property name="authenticator">
  22.                                 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

  23.                                         <property name="user">root</property>

  24.                                         <property name="password">pass</property>

  25.                                         <property name="filter">
  26.                                                 <bean class="com.meidusa.amoeba.server.IPAccessController">
  27.                                                         <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
  28.                                                 </bean>
  29.                                         </property>
  30.                                 </bean>
  31.                         </property>

  32.                 </service>

  33.                 <!-- server class must implements com.meidusa.amoeba.service.Service -->
  34.                 <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
  35.                         <!-- port -->
  36.                         <!-- default value: random number
  37.                         <property name="port">9066</property>
  38.                         -->
  39.                         <!-- bind ipAddress -->
  40.                         <property name="ipAddress">127.0.0.1</property>
  41.                         <property name="daemon">true</property>
  42.                         <property name="manager">${clientConnectioneManager}</property>
  43.                         <property name="connectionFactory">
  44.                                 <bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
  45.                         </property>

  46.                 </service>

  47.                 <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
  48.                         <!-- proxy server net IO Read thread size -->
  49.                         <property name="readThreadPoolSize">20</property>

  50.                         <!-- proxy server client process thread size -->
  51.                         <property name="clientSideThreadPoolSize">30</property>

  52.                         <!-- mysql server data packet process thread size -->
  53.                         <property name="serverSideThreadPoolSize">30</property>

  54.                         <!-- per connection cache prepared statement size -->
  55.                         <property name="statementCacheSize">500</property>

  56.                         <!-- query timeout( default: 60 second , TimeUnit:second) -->
  57.                         <property name="queryTimeout">60</property>
  58.                 </runtime>

  59.         </proxy>

  60.         <!--
  61.                 Each ConnectionManager will start as thread
  62.                 manager responsible for the Connection IO read , Death Detection
  63.         -->
  64.         <connectionManagerList>
  65.                 <connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
  66.                         <property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
  67.                         <!--
  68.                           default value is avaliable Processors
  69.                         <property name="processors">5</property>
  70.                          -->
  71.                 </connectionManager>
  72.                 <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
  73.                         <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

  74.                         <!--
  75.                           default value is avaliable Processors
  76.                         <property name="processors">5</property>
  77.                          -->
  78.                 </connectionManager>
  79.         </connectionManagerList>

  80.                 <!-- default using file loader -->
  81.         <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
  82.                 <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
  83.         </dbServerLoader>

  84.         <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
  85.                 <property name="ruleLoader">
  86.                         <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
  87.                                 <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
  88.                                 <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
  89.                         </bean>
  90.                 </property>
  91.                 <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
  92.                 <property name="LRUMapSize">1500</property>
  93.                 <property name="defaultPool">master</property>    #默认的dbServer,一般为主服务器;

  94.                 <property name="writePool">master</property>      #写操作路由到哪些dbServer上;
  95.                 <property name="readPool">myreadPool</property>   #读操作路由到哪些dbServer上;

  96.                 <property name="needParse">true</property>
  97.         </queryRouter>
  98. </amoeba:configuration>

5.此时amoeba还无法启动,需要配置其环境变量
[root@proxy conf]# cat /etc/profile.d/amoeba.sh 
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$AMOEBA_HOME/bin:$PATH


[root@proxy conf]# . /etc/profile.d/amoeba.sh 


验证能否启动:
[root@proxy conf]# amoeba
amoeba start|stop                  #出现了这些提示,就说明能正常启动amoeba了;


6.在主服务器上创建amoeba用户
这个用户是用来让amoeba连接后端数据库的,而且要赋予一定的权限:
mysql> grant all privileges on *.* to 'root'@'192.168.85.%' identified by 'amoeba';


启动amoeba:
[root@proxy conf]# amoeba start
log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2016-01-06 09:51:18,806 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
2016-01-06 09:51:22,244 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:3306.
2016-01-06 09:51:22,258 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:40759.


7.读写分离测试(通过抓包来测试)
7.1 测试能否登录
使用node1当作客户端连接amoeba:


[root@node1 ~]# mysql -u root -h192.168.85.128 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17824568
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0
mysql> 


7.1 读测试(只有主上有)
客户端登陆后创建一个数据库:
mysql> create database mydb;

主服务器上的包:
[root@node1 ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.85.144 
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
10:31:46.642361 IP 192.168.85.128.46480 > 192.168.85.144.3306: Flags [P.], seq 1820300201:1820300226, ack 4278332093, win 457, options [nop,nop,TS val 90317748 ecr 71428717], length 25
E..M..@.@.*T..U...U.....l.....*.....H......
.b#..A.m.....create database mydb              #可以看到,刚刚的写操作被路由到了主上;


从服务器上的包:
[root@node2 ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.85.145
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
而从服务器上没有相应包;

7.2 读测试(主从上都有)

继续使用刚才登陆的客户端进行select操作:
mysql> select host from mysql.user;   #连续进行了三次该操作

主服务器上的包:
[root@node1 ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.85.144 
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
10:40:26.010942 IP 192.168.85.128.46480 > 192.168.85.144.3306: Flags [P.], seq 1820300281:1820300313, ack 4278332304, win 490, options [nop,nop,TS val 90837118 ecr 71935679], length 32
E..T..@.@.*G..U...U.....l.....+.....7......
.j.~.I.......select host from mysql.user #只出现了一次


从服务器上的包:
[root@node2 ~]# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.85.145
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
10:51:53.865350 IP 192.168.85.128.57664 > 192.168.85.145.3306: Flags [P.], seq 312144217:312144249, ack 177973825, win 457, options [nop,nop,TS val 91524949 ecr 71346629], length 32
E..T&m@.@.....U...U..@.....Y
..A.....b.....
.t.U.@.......select host from mysql.user #出现第一次
10:51:53.879848 IP 192.168.85.128.57664 > 192.168.85.145.3306: Flags [.], ack 214, win 490, options [nop,nop,TS val 91524958 ecr 71373706], length 0
E..4&n@.@.....U...U..@.....y
..............
.t.^.A..
10:51:56.498157 IP 192.168.85.128.57664 > 192.168.85.145.3306: Flags [P.], seq 32:64, ack 214, win 490, options [nop,nop,TS val 91527582 ecr 71373706], length 32
E..T&o@.@.....U...U..@.....y
........>.....
.t...A.......select host from mysql.user #出现第二次

通过上述抓包的情况,可以看到,这很符合myreadPool中定义的两次slave一次master规则;




中文帮助文档:
参考资料:
http://634871.blog.51cto.com/624871/1335129?utm_source=tuicool&utm_medium=referral





阅读(918) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~