centos7.8配置Amoeba+Mysql数据库读写分离

一、Amoeba 是什么Amoeba(变形虫)项目专注 分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户端透明。具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。主要解决• 降低 数据切分带来的复杂多数据库结构• 提供切分规则并降低 数据切分规则 给应用带来的影响• 降低db 与客户端的连接数• 读写分离二、为什么要用Amoeba目前要实现mysql的主从读写分离主要有以下几种方案1、 通过程序实现网上很多现成的代码比较复杂如果添加从服务器要更改多台服务器的代码。2、 通过mysql-proxy来实现由于mysql-proxy的主从读写分离是通过lua脚本来实现目前lua的脚本的开发跟不上节奏而写没有完美的现成的脚本因此导致用于生产环境的话风险比较大据网上很多人说mysql-proxy的性能不高。3、 自己开发接口实现这种方案门槛高开发成本高不是一般的小公司能承担得起。4、 利用阿里巴巴的开源项目Amoeba来实现具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库并且安装配置非常简单。国产的开源软件应该支持目前正在使用不发表太多结论一切等测试完再发表结论吧哈哈三、Amoeba的安装先介绍下部署环境amoeba:192.168.121.10amoeba安装的这台机器上安装的是mysql5.5的版本mysql8.0不支持masterDB192.168.121.20安装的mysql8.0slaveDB192.168.121.30安装的mysql8.0以上系统全为centos7.8Amoeba框架是居于JDK1.5开发的采用了JDK1.5的特性所以还需要安装java环境建议使用javaSE1.5以上的JDK版本此处安装的jdk1.8.0_242版本1、安装java环境先去官网下载http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html安装下载完jdk压缩包只要解压即可使用,此处我解压到/amoeba/jdk目录tar -zxf jdk-8u251-linux-x64.tar.gz -C /amoeba/jdk然后设置java环境变量修改配置文件/etc/profile#set java environment JAVA_HOME/amoeba/jdk JRE_HOME/amoeba/jdk/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修改完配置文件需要重新加载[roothadoop1 amoeba]# source /etc/profile测试是否安装成功[roothadoop1 amoeba]# java -version openjdk version 1.8.0_242 OpenJDK Runtime Environment (build 1.8.0_242-b08) OpenJDK 64-Bit Server VM (build 25.242-b08, mixed mode)jdk环境配置成功。2、安装Amoeba可以从https://sourceforge.net/projects/amoeba/下载最新版本的Amoeba我这里下载的是amoeba-mysql-binary-2.1.0-RC5.tar.gz。Amoeba安装非常简单直接解压即可使用这里将Amoeba解压到/usr/local/amoeba目录下这样就安装完成了/usr/local/目录下先创建好amoeba目录[roothadoop1 opt]# tar -zxf amoeba-mysql-binary-2.1.0-RC5.tar.gz -C /usr/local/amoeba/可以看到amoeba解压后如下[roothadoop1 opt]# cd /usr/local/amoeba/ [roothadoop1 amoeba]# pwd /usr/local/amoeba [roothadoop1 amoeba]# ll 总用量 64 drwxr-xr-x. 2 root root 4096 11月 24 18:28 benchmark drwxr-xr-x. 2 root root 4096 11月 24 18:40 bin -rw-r--r--. 1 root root 3983 5月 18 2011 changelogs.txt drwxr-xr-x. 2 root root 4096 11月 25 14:49 conf drwxr-xr-x. 3 root root 4096 11月 24 18:28 lib -rw-r--r--. 1 root root 34520 5月 18 2011 LICENSE.txt drwxr-xr-x. 2 root root 4096 11月 25 14:50 logs -rw-r--r--. 1 root root 2031 5月 18 2011 README.html成功。3、配置AmoebaAmoeba的配置文件在本环境下位于/usr/local/amoeba/conf目录下。配置文件比较多但是仅仅使用读写分离功能只需配置两个文件即可分别是dbServers.xml和amoeba.xml如果需要配置ip访问控制还需要修改access_list.conf文件下面首先介绍dbServers.xml[roothadoop1 amoeba]# cd conf/ [roothadoop1 conf]# cat dbServers.xml ?xml version1.0 encodinggbk? !DOCTYPE amoeba:dbServers SYSTEM dbserver.dtd amoeba:dbServers xmlns:amoebahttp://amoeba.meidusa.com/ !-- Each dbServer needs to be configured into a Pool, If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration: add attribute with name virtual true in dbServer, but the configuration does not allow the element with name factoryConfig such as multiPool dbServer -- dbServer nameabstractServer abstractivetrue factoryConfig classcom.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory property namemanager${defaultManager}/property property namesendBufferSize64/property property namereceiveBufferSize128/property !-- mysql port -- property nameport3306/property #设置Amoeba要连接的mysql数据库的端口默认是3306 !-- mysql schema -- property nameschematestdb/property #设置缺省的数据库当连接amoeba时操作表必须显式的指定数据库名即采用dbname.tablename的方式不支持 use dbname指定缺省库因为操作会调度到各个后端dbserver。注意检查你的数据库里面一定要有这个数据库 !-- mysql user -- property nameusertest1/property #设置amoeba连接后端数据库服务器的账号和密码因此需要在所有后端数据库上创建该用户并授权amoeba服务器可连接 property namepassword111111/property #设置amoeba连接后端数据库服务器的账号的密码注意这里原来是注释的状态注意把注释去掉否则无效 /factoryConfig poolConfig classcom.meidusa.amoeba.net.poolable.PoolableObjectPool property namemaxActive500/property #最大连接数默认500 property namemaxIdle500/property #最大空闲连接数 property nameminIdle10/property #最新空闲连接数 property nameminEvictableIdleTimeMillis600000/property property nametimeBetweenEvictionRunsMillis600000/property property nametestOnBorrowtrue/property property nametestWhileIdletrue/property /poolConfig /dbServer dbServer namewritedb parentabstractServer #writedb名称可以根据自己需求改变 factoryConfig !-- mysql ip -- property nameipAddress192.168.121.20/property #这里写你预设的想写入数据的主服务器IP /factoryConfig /dbServer dbServer nameslave parentabstractServer #slave名称可以根据自己需求改变 factoryConfig !-- mysql ip -- property nameipAddress192.168.121.30/property #这里写你预设的想读取数据的从服务器IP /factoryConfig /dbServer dbServer namemyslave virtualtrue #写你的连接池myslave池名称可以改这个池提供客户端读取数据 poolConfig classcom.meidusa.amoeba.server.MultipleServerPool !-- Load balancing strategy: 1ROUNDROBIN , 2WEIGHTBASED , 3HA-- property nameloadbalance1/property !-- Separated by commas,such as: server1,server2,server1 -- property namepoolNamesslave/property #指定读取数据时从从服务器上读取。也可以指定读取数据时从主服务器和从服务器上读取则改成writedb,slave /poolConfig /dbServer /amoeba:dbServers [roothadoop1 conf]#另一个配置文件amoeba.xml[roothadoop1 conf]# cat amoeba.xml ?xml version1.0 encodinggbk? !DOCTYPE amoeba:configuration SYSTEM amoeba.dtd amoeba:configuration xmlns:amoebahttp://amoeba.meidusa.com/ proxy !-- service class must implements com.meidusa.amoeba.service.Service -- service nameAmoeba for Mysql classcom.meidusa.amoeba.net.ServerableConnectionManager !-- port -- property nameport8066/property #设置amoeba监听的端口默认是8066 !-- bind ipAddress -- property nameipAddress192.168.121.10/property #配置监听的接口此处写amoeba安装所在的服务器IP注意把原本的注释状态取消否则无效 property namemanager${clientConnectioneManager}/property property nameconnectionFactory bean classcom.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory property namesendBufferSize128/property property namereceiveBufferSize64/property /bean /property property nameauthenticator bean classcom.meidusa.amoeba.mysql.server.MysqlClientAuthenticator property nameuseramoeba/property # 提供客户端连接amoeba时需要使用这里设定的账号 (这里的账号密码和amoeba连接后端数据库服务器的密码无关) property namepassword123456/property #客户端连接amoeba时的账户的密码 property namefilter bean classcom.meidusa.amoeba.server.IPAccessController property nameipFile${amoeba.home}/conf/access_list.conf/property /bean /property /bean /property /service !-- server class must implements com.meidusa.amoeba.service.Service -- service nameAmoeba Monitor Server classcom.meidusa.amoeba.monitor.MonitorServer !-- port -- !-- default value: random number property nameport9066/property -- !-- bind ipAddress -- property nameipAddress127.0.0.1/property property namedaemontrue/property property namemanager${clientConnectioneManager}/property property nameconnectionFactory bean classcom.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory/bean /property /service runtime classcom.meidusa.amoeba.mysql.context.MysqlRuntimeContext !-- proxy server net IO Read thread size -- property namereadThreadPoolSize20/property !-- proxy server client process thread size -- property nameclientSideThreadPoolSize30/property !-- mysql server data packet process thread size -- property nameserverSideThreadPoolSize30/property !-- per connection cache prepared statement size -- property namestatementCacheSize500/property !-- query timeout( default: 60 second , TimeUnit:second) -- property namequeryTimeout60/property /runtime /proxy !-- Each ConnectionManager will start as thread manager responsible for the Connection IO read , Death Detection -- connectionManagerList connectionManager nameclientConnectioneManager classcom.meidusa.amoeba.net.MultiConnectionManagerWrapper property namesubManagerClassNamecom.meidusa.amoeba.net.ConnectionManager/property !-- default value is avaliable Processors property nameprocessors5/property -- /connectionManager connectionManager namedefaultManager classcom.meidusa.amoeba.net.MultiConnectionManagerWrapper property namesubManagerClassNamecom.meidusa.amoeba.net.AuthingableConnectionManager/property !-- default value is avaliable Processors property nameprocessors5/property -- /connectionManager /connectionManagerList !-- default using file loader -- dbServerLoader classcom.meidusa.amoeba.context.DBServerConfigFileLoader property nameconfigFile${amoeba.home}/conf/dbServers.xml/property /dbServerLoader queryRouter classcom.meidusa.amoeba.mysql.parser.MysqlQueryRouter property nameruleLoader bean classcom.meidusa.amoeba.route.TableRuleFileLoader property nameruleFile${amoeba.home}/conf/rule.xml/property property namefunctionFile${amoeba.home}/conf/ruleFunctionMap.xml/property /bean /property property namesqlFunctionFile${amoeba.home}/conf/functionMap.xml/property property nameLRUMapSize1500/property property namedefaultPoolwritedb/property #设置amoeba默认连接的池这里设置为writedb property namewritePoolwritedb/property #这两个选项默认是注销掉的需要取消注释这里用来指定前面定义好的俩个读写池。写入数据到刚才dbServer.xml中配置的write池 property namereadPoolmyslave/property #写入数据到刚才dbServer.xml中配置的write池 property nameneedParsetrue/property /queryRouter /amoeba:configuration [roothadoop1 conf]#修改amoeba脚本否则会由于stack size太小导致JVM启动失败需要做如下修改[roothadoop1 bin]# vim /usr/local/amoeba/bin/amoeba 58 DEFAULT_OPTS-server -Xms256m -Xmx256m -Xss256k4、在masterdb上创建数据库testdb验证主从同步是否正常配置主从同步此处不作阐述可参考之前做过的配置过程查看slavedb是否复制成功以上主从同步配置成功。分别在masterdb和slavedb上为amoedb授权注意因为此时配置了主从同步所在此处授权操作在主服务器上操作一遍即可从服务器自动同步mysql8.0如下方式授权 mysqlcreate user test1192.168.121.10 identified with mysql_native_password by 111111; mysqlGRANT ALL ON testdb.* TO test1192.168.121.10; mysqlflush privileges;mysql GRANT ALL ON testdb.* TO test1192.168.121.10 IDENTIFIED BY 111111; Query OK, 0 rows affected (0.05 sec) mysql flush privileges; Query OK, 0 rows affected (0.02 sec)启动amoeba[roothadoop1 bin]# pwd /usr/local/amoeba/bin [roothadoop1 bin]# [roothadoop1 bin]# ./amoeba start log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2020-11-25 16:17:49,825 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin5.1.45-mysql-amoeba-proxy-2.1.0-RC5 log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2020-11-25 16:17:50,491 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on /192.168.121.10:8066. 2020-11-25 16:17:50,506 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:28750.启动成功。查看端口看amoeba启动情况由此可知Amoeba启动正常5、测试远程登陆mysql客户端通过指定amoeba配置文件中指定的用户名、密码、和端口以及amoeba服务器ip地址链接mysql数据库注意这里-h写amoeba所在的服务器[roothadoop1 bin]# mysql -uamoeba -p -h192.168.121.10 -P8066 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1899284416 Server version: 5.1.45-mysql-amoeba-proxy-2.1.0-RC5 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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中表a并插入数据以上验证配置主服务器进行写入数据成功。验证读取数据配置是否成功我们现在从服务器插入一条数据因为配置的主从服务器所以从服务器插入数据不会同步数据到主服务器但是我们配置的客户端读取数据是从从服务器读取所以客户端会读到从服务器中新增的那条数据以上读写分离配置成功。