使用Amoeba实现MySQL数据库主从读写分离

时间:2022-09-20 15:30:11

一、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)简要原理图

使用Amoeba实现MySQL数据库主从读写分离

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 一切正常,到此全部结束