一、Amoeba简介
Amoeba(变形虫),专注于分布式数据库代理层(Database Proxy)开发,介于应用层与DB Server(s)之间,对客户端透明,主要在应用层访问DB Server(s)的时候充当SQL路由功能。它具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。在Amoeba上面你能够完成多数据源的高可用、负载均衡、数据切片的功能。
二、Amoeba的特点
主要解决:
• 降低数据切分带来的复杂多数据库结构
• 提供切分规则并降低数据切分规则给应用带来的影响
• 降低db与客户端的连接数
• 读写分离
不能解决:
• 目前还不支持事务
• 暂时不支持存储过程
• 不适合从amoeba导数据的场景,对大数据量查询的query也不合适(比如一次请求返回10w以上甚至更多数据的场合)
• 暂时不支持分库分表,amoeba目前只做到分数据库实例,每个被切分的节点需要保持库表结构一致
三、基于Amoeba实现mysql数据库读写分离
本文主要是为了使用Amoeba实现mysql数据库主从读写分离,故需要提前配置好mysql主从复制环境,这里不再演示。
1、实验环境,Amoeba的要求,简要原理图
1)实验环境
System:CentOS release 6.8
Amoeba:172.16.1.201
Master:172.16.1.202
Slave:172.16.1.203
2)Amoeba的要求
Amoeba 是基于jdk1.5 开发的,所以如果要运行 Amoeba,必须先安装 jdk1.5或以上环境。
3)简要原理图
2、安装Java环境
先去官网下载:http://download.oracle.com/otn-pub/java/jdk/8u161-b12/2f38c3b165be4555a1fa6e98c45e0808/jdk-8u161-linux-x64.rpm
上传到Amoeba服务器然后安装:
[root@amoeba ~]# rpm -ivh jdk-8u161-linux-x64.rpm #设置java环境变量 [root@amoeba ~]# vim /etc/profile #在最后一行添加 #set java environment JAVA_HOME=/usr/java/jdk1.8.0_161 JRE_HOME=/usr/java/jdk1.8.0_161/jre CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin export JAVA_HOME JRE_HOME CLASS_PATH PATH #使profile文件生效 [root@amoeba ~]# source /etc/profile #测试java是否安装成功 [root@amoeba ~]# java -version java version "1.8.0_161" Java(TM) SE Runtime Environment (build 1.8.0_161-b12) Java HotSpot(TM) 64-Bit Server VM (build 25.161-b12, mixed mode)
3、安装Amoeba
下载地址:https://sourceforge.net/projects/amoeba/
我这里下载的是amoeba-mysql-3.0.5-RC-distribution.zip,安装非常简单,直接将Amoeba解压到/usr/local/amoeba目录下即可。
[root@amoeba ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/ [root@amoeba local]# mv amoeba-mysql-3.0.5-RC/ amoeba [root@amoeba amoeba]# ll total 28 -rw-r--r-- 1 root root 4 Apr 11 23:32 Amoeba-MySQL.pid drwxrwxrwx 2 root root 4096 Jul 5 2013 benchmark drwxrwxrwx 2 root root 4096 Apr 11 23:23 bin drwxrwxrwx 2 root root 4096 Apr 11 10:52 conf -rwxrwxrwx 1 root root 780 Apr 11 23:28 jvm.properties drwxrwxrwx 2 root root 4096 Jul 5 2013 lib drwxr-xr-x 2 root root 4096 Apr 13 00:00 logs
4、配置Amoeba
1)Amoeba的配置文件位于/usr/local/amoeba/conf目录下。总共有7个配置文件,分别如下:
• 主配置文件($AMOEBA_HOME/conf/amoeba.xml),用来配置Amoeba服务的基本参数,如Amoeba主机地址、端口、认证方式、用于连接的用户名、密码、线程数、超时时间、其他配置文件的位置等。
• 数据库服务器配置文件($AMOEBA_HOME/conf/dbServers.xml),用来存储和配置Amoeba所代理的数据库服务器的信息,如:主机IP、端口、用户名、密码等。
• 切分规则配置文件($AMOEBA_HOME/conf/rule.xml),用来配置切分规则。
• 数据库函数配置文件($AMOEBA_HOME/conf/functionMap.xml),用来配置数据库函数的处理方法,Amoeba将使用该配置文件中的方法解析数据库函数。
• 切分规则函数配置文件($AMOEBA_HOME/conf/ruleFunctionMap.xml),用来配置切分规则中使用的用户自定义函数的处理方法。
• 访问规则配置文件($AMOEBA_HOME/conf/access_list.conf),用来授权或禁止某些服务器IP访问Amoeba。
• 日志规格配置文件($AMOEBA_HOME/conf/log4j.xml),用来配置Amoeba输出日志的级别和方式。
2)配置文件比较多,但是使用读写分离功能,只需配置两个文件即可,分别是dbServers.xml和amoeba.xml。下面首先介绍dbServers.xml:
1 [root@amoeba conf]# vim dbServers.xml 2 .........................(省略) 3 <dbServer name="abstractServer" abstractive="true"> 4 <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"> 5 <property name="connectionManager">${defaultManager}</property> 6 <property name="sendBufferSize">64</property> 7 <property name="receiveBufferSize">128</property> 8 9 <!-- mysql port --> #设置 mysql 数据库的端口 10 <property name="port">3306</property> 11 #设置缺省的数据库,当连接amoeba时,操作表必须显式的指定数据库名,即采用dbname.tablename的方式,不支持 use dbname指定缺省库,因为操作会调度到各个后端dbserver 12 <!-- mysql schema --> 13 <property name="schema">testdb</property> 14 #设置amoeba连接后端数据库服务器的账号和密码,需在后端数据库器上创建该用户,并授权amoeba连接 15 <!-- mysql user --> 16 <property name="user">amoeba</property> 17 18 <property name="password">amoeba123</property> 19 </factoryConfig> 20 21 <poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool"> 22 <property name="maxActive">500</property> #最大连接数[默认] 23 <property name="maxIdle">500</property> #最大空闲连接数[默认] 24 <property name="minIdle">1</property> 25 <property name="minEvictableIdleTimeMillis">600000</property> 26 <property name="timeBetweenEvictionRunsMillis">600000</property> 27 <property name="testOnBorrow">true</property> 28 <property name="testOnReturn">true</property> 29 <property name="testWhileIdle">true</property> 30 </poolConfig> 31 </dbServer> 32 33 #设置一个后端的dbServer,名为master,这个可以随便取,但是为了明确其含义,最好给予特殊含义的单词,这里是主库 34 <dbServer name="master" parent="abstractServer"> 35 <factoryConfig> 36 <!-- mysql ip --> #设置这台dbServer的ip地址 37 <property name="ipAddress">172.16.1.202</property> 38 </factoryConfig> 39 </dbServer> 40 41 #再设置一个后端的dbServer,这里叫slave,名字需要唯一[这里可以创建N个后端数据库,只要复制下面<dbServer>...</dbServer>] 42 <dbServer name="slave" parent="abstractServer"> 43 <factoryConfig> 44 <!-- mysql ip --> #设置这台dbServer的ip地址 45 <property name="ipAddress">172.16.1.203</property> 46 </factoryConfig> 47 </dbServer> 48 49 #指定一个虚拟的dbServer,将上面定义的dbserver加入这个虚拟的dbserver,相当于组成一个组[这里我们将读的数据库组成一个组] 50 #这里需要将name="mul..."改成自己想要取的名字,如Myslave,这个名字也需要有含义,后面会用到 51 <dbServer name="Myslave" virtual="true"> 52 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"> 53 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--> 54 #选择调度算法,1是轮询,2是权重,3是HA,这里选择1轮询 55 <property name="loadbalance">1</property> 56 57 <!-- Separated by commas,such as: server1,server2,server1 --> 58 #负载均衡,slave1,slave2当成2个服务器进行调度,这模拟量加权的调度算法。 59 #注意这里使用的dbserver必须是已经定义了的,可以写多个,如slave1,slave2 60 <property name="poolNames">slave</property> #Myslave组成员 61 </poolConfig> 62 </dbServer> 63 </amoeba:dbServers>
3)下面配置amoeba.xml:
1 [root@amoeba conf]# vim amoeba.xml 2 .........................(省略) 3 <proxy> 4 <!-- service class must implements com.meidusa.amoeba.service.Service --> 5 <service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService"> 6 <!-- port --> 7 #设置amoeba监听的端口(这里如果默认,后面测试需要指定端口,就是这里的端口) 8 <property name="port">8066</property> #也可以设置为3306,模拟MySQL端口 9 <!-- bind ipAddress --> 10 <!-- 11 #设置监听的IP,如果不设置,则监听所有的IP[选择默认] 12 <property name="ipAddress">127.0.0.1</property> 13 --> 14 .........................(省略) 15 <property name="authenticator"> 16 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"> 17 #客户端连接amoeba时需要使用这里设定的账号(这里的账号密码和amoeba连接后端数据库服务器的密码无关) 18 <property name="user">root</property> 19 #客户端连接amoeba时需要使用这里设定的密码 20 <property name="password">amoeba123</property> 21 22 <property name="filter"> 23 <bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController"> 24 <property name="ipFile">${amoeba.home}/conf/access_list.conf</property> 25 </bean> 26 </property> 27 </bean> 28 </property> 29 </service> 30 .........................(省略) 31 <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"> 32 <property name="ruleLoader"> 33 <bean class="com.meidusa.amoeba.route.TableRuleFileLoader"> 34 <property name="ruleFile">${amoeba.home}/conf/rule.xml</property> 35 <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property> 36 </bean> 37 </property> 38 <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property> 39 <property name="LRUMapSize">1500</property> 40 <property name="defaultPool">master</property> #设置默认连接的组为master 41 <!-- --> 42 #把注释去掉并将writePool设置为master,将readPool设置为myslave(这个名字,我们前面在dbServer.xml里设置一个读数据库组,这里是作为只读池) 43 <property name="writePool">master</property> 44 <property name="readPool">myslave</property> 45 <property name="needParse">true</property> 46 </queryRouter>
4)在master上创建数据库testdb
mysql> create database testdb; Query OK, 1 row affected (0.08 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | testdb | +--------------------+ 4 rows in set (0.00 sec) #查看slave是否复制成功 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | testdb | +--------------------+ 4 rows in set (0.00 sec)
5)分别在master和slave上为amoeba授权
mysql> GRANT ALL ON testdb.* TO 'amoeba'@'172.16.1.201' IDENTIFIED BY 'amoeba123'; Query OK, 0 rows affected (0.05 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec)
6)启动amoeba
[root@amoeba amoeba]# /usr/local/amoeba/bin/launcher #可以为amoeba配置环境变量,不使用全路径启动 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0 The stack size specified is too small, Specify at least 228k Error: Could not create the Java Virtual Machine. Error: A fatal exception has occurred. Program will exit.
出现报错,从错误文字上看,应该是由于stack size太小,导致JVM启动失败,要如何修改呢?
其实Amoeba已经考虑到这个问题,并将JVM参数配置写在属性文件里。现在,让我们通过该属性文件修改JVM参数。
修改jvm.properties文件JVM_OPTIONS参数:
[root@amoeba amoeba]# vim jvm.properties 改成:JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k" #看上面提示,后面两个参数在8.0被移除了 原为:JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
再次启动
[root@amoeba amoeba]# /usr/local/amoeba/bin/launcher at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:329) at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:239) at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409) at org.codehaus.classworlds.Launcher.mainWithExitCode(Launcher.java:127) at org.codehaus.classworlds.Launcher.main(Launcher.java:110) Caused by: com.meidusa.toolkit.common.bean.util.InitialisationException: cannot found Pool=myslave #出现一个错误,是myslave单词写错了,改正后启动成功 at com.meidusa.amoeba.route.AbstractQueryRouter.init(AbstractQueryRouter.java:469) at com.meidusa.amoeba.context.ProxyRuntimeContext.initAllInitialisableBeans(ProxyRuntimeContext.java:337) ... 11 more 2018-04-11 10:36:12 [INFO] Project Name=Amoeba-MySQL, PID=5060 , System shutdown .... Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0 2018-04-11 10:41:35 [INFO] Project Name=Amoeba-MySQL, PID=5096 , starting... log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2018-04-11 10:41:36,873 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2018-04-11 10:41:53,394 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066. #查看端口 [root@bogon ~]# netstat -unlpt | grep java tcp 0 0 :::8066 :::* LISTEN 5096/java
7)测试
选择一台安装mysql客户端的服务器,通过amoeba配置文件中指定的用户名、密码、端口以及amoeba服务器ip地址远程连接mysql数据库
[root@db02 ~]# mysql -uroot -pamoeba123 -h172.16.1.201 -P8066
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1286393262 Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
在testdb中创建表test并插入数据
mysql> use testdb; Database changed mysql> create table test_table(id int(4) not null); Query OK, 0 rows affected (0.33 sec) mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | test_table | +------------------+ 1 row in set (0.01 sec) mysql> insert into test_table values(1); Query OK, 1 row affected (0.11 sec) mysql> select * from test_table; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
分别登陆master和slave查看数据
master: mysql> use testdb Database changed mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | test_table | +------------------+ 1 row in set (0.00 sec) mysql> select * from test_table; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
slave: mysql> use testdb Database changed mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | test_table | +------------------+ 1 row in set (0.00 sec) mysql> select * from test_table; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
停掉master,然后在客户端分别执行插入和查询功能
master: [root@master ~]# /etc/init.d/mysqld stop Shutting down MySQL..... SUCCESS! 客户端: mysql> insert into test_table values(2); ERROR 1044 (42000): Amoeba could not connect to MySQL server[172.16.1.202:3306],Connection refused mysql> select * from test_table; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.07 sec)
可以看到,关掉master之后写入报错,读正常
开启master上的msyql 关闭slave上的mysql
master: [root@master ~]# /etc/init.d/mysqld start Starting MySQL... SUCCESS! slave: [root@slave ~]# /etc/init.d/mysqld stop Shutting down MySQL...... SUCCESS!
客户端再次尝试
mysql> insert into test_table values(2); Query OK, 1 row affected (0.08 sec) mysql> select * from test_table; ERROR 1044 (42000): poolName=myslave, no valid pools
可以看到插入成功,读取失败
开启slave上的mysql,查看数据是否自动同步
slave: [root@slave ~]# /etc/init.d/mysqld start Starting MySQL..... SUCCESS! 客户端: mysql> select * from test_table; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.01 sec) mysql> insert into test_table values(3); Query OK, 1 row affected (0.20 sec) mysql> select * from test_table; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)
OK 一切正常,到此全部结束