通过amoeba工具实现配置mysql读写分离的方法介绍

下面讲讲关于通过amoeba工具实现配置mysql读写分离的方法，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完通过amoeba工具实现配置mysql读写分离的方法这篇文章你一定会有所受益。

一，背景介绍：

Amoeba(变形虫)项目，专注 分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户端透明。具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。要想搭建Amoeba读写分离，首先需要知道MySQL的主从配置。具体的架构图如下图：

二，配置所需的环境：

Amoeba for mysql:192.168.1.28

Master : 192.168.1.247

Slave : 192.168.1.245

database:yazi passwd:root/123456

JDK1.8.0_51

三，具体的搭建和配置步骤如下：

1，配置mysql主从复制，具体参照上篇的mysql主从复制的配置

2，安装配置jdk1.8.0_51以及环境变量

（1），安装jdk命令: rpm -ivh jdk-8u51-linux-x64.rpm

[root@localhost install]# lltotal 481168-rw-r--r--. 1 root root   8850470 Nov  2 22:43 apache-tomcat-7.0.63.tar.gz-rw-r--r--. 1 root root 137808216 Jan 13 15:09 jdk-8u51-linux-x64.rpm-rw-r--r--. 1 root root  33203321 Nov 20 11:31 mysql-5.6.25.tar.gz-rw-r--r--. 1 root root 312845162 Nov 13 15:19 mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz[root@localhost install]# rpm -ivh jdk-8u51-linux-x64.rpm Preparing...                ########################################### [100%]   1:jdk1.8.0_51            ########################################### [100%]Unpacking JAR files...        rt.jar...        jsse.jar...        charsets.jar...        tools.jar...        localedata.jar...        jfxrt.jar...        plugin.jar...        javaws.jar...        deploy.jar...

（2)配置环境变量：

[root@localhost install]# vi /etc/profile

# /etc/profile# System wide environment and startup programs, for login setup# Functions and aliases go in /etc/bashrc# It's NOT a good idea to change this file unless you know what you# are doing. It's much better to create a custom.sh shell script in# /etc/profile.d/ to make custom changes to your environment, as this# will prevent the need for merging in future updates.pathmunge () {    case ":${PATH}:" in        *:"$1":*)            ;;        *)            if [ "$2" = "after" ] ; then                PATH=$PATH:$1            else                PATH=$1:$PATH            fi    esac}if [ -x /usr/bin/id ]; then    if [ -z "$EUID" ]; then        # ksh workaround        EUID=`id -u`        UID=`id -ru`    fi    USER="`id -un`"    LOGNAME=$USER    MAIL="/var/spool/mail/$USER"fi# Path manipulationif [ "$EUID" = "0" ]; then    pathmunge /sbin    pathmunge /usr/sbin    pathmunge /usr/local/sbinelse    pathmunge /usr/local/sbin after    pathmunge /usr/sbin after    pathmunge /sbin afterfiHOSTNAME=`/bin/hostname 2>/dev/null`HISTSIZE=1000if [ "$HISTCONTROL" = "ignorespace" ] ; then    export HISTCONTROL=ignorebothelse    export HISTCONTROL=ignoredupsfiexport PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL# By default, we want umask to get set. This sets it for login shell# Current threshold for system reserved uid/gids is 200# You could check uidgid reservation validity in# /usr/share/doc/setup-*/uidgid fileif [ $UID -gt 199 ] && [ "`id -gn`" = "`id -un`" ]; then    umask 002else    umask 022fifor i in /etc/profile.d/*.sh ; do    if [ -r "$i" ]; then        if [ "${-#*i}" != "$-" ]; then            . "$i"        else            . "$i" >/dev/null 2>&1        fi    fidoneunset iunset -f pathmungeexport JAVA_HOME=/usr/java/jdk1.8.0_51export JRE_HOME=/usr/java/jdk1.8.0_51/jreexport PATH=$JAVA_HOME/bin:$PATHexport CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

（3)配置生效和验证：

[root@FileServerA logs]# source /etc/profile[root@FileServerA logs]# java -versionjava version "1.8.0_51"Java(TM) SE Runtime Environment (build 1.8.0_51-b16)Java HotSpot(TM) 64-Bit Server VM (build 25.51-b03, mixed mode)

3，安装配置amoeba

（1）下载amoeba

(http://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip)

(2)压缩包无需安装，直接解压即可

[root@localhost install]# unzip amoeba-mysql-3.0.5-RC-distribution.zip Archive:  amoeba-mysql-3.0.5-RC-distribution.zip   creating: amoeba-mysql-3.0.5-RC/   creating: amoeba-mysql-3.0.5-RC/lib/  inflating: amoeba-mysql-3.0.5-RC/lib/amoeba-core-3.0.5-RC.jar    inflating: amoeba-mysql-3.0.5-RC/lib/log4j-1.2.12.jar    inflating: amoeba-mysql-3.0.5-RC/lib/commons-lang-2.4.jar    inflating: amoeba-mysql-3.0.5-RC/lib/commons-logging-1.1.1.jar    inflating: amoeba-mysql-3.0.5-RC/lib/commons-beanutils-1.8.0.jar    inflating: amoeba-mysql-3.0.5-RC/lib/commons-collections-3.2.1.jar    inflating: amoeba-mysql-3.0.5-RC/lib/ognl-3.0.1.jar    inflating: amoeba-mysql-3.0.5-RC/lib/javassist-3.11.0.GA.jar    inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-pool-1.2.0.jar    inflating: amoeba-mysql-3.0.5-RC/lib/commons-pool-1.6.jar    inflating: amoeba-mysql-3.0.5-RC/lib/slf4j-api-1.6.1.jar    inflating: amoeba-mysql-3.0.5-RC/lib/slf4j-log4j12-1.6.2.jar    inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-bean-1.3.0.jar    inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-util-1.2.0.jar    inflating: amoeba-mysql-3.0.5-RC/lib/dom4j-1.6.1.jar    inflating: amoeba-mysql-3.0.5-RC/lib/xml-apis-1.0.b2.jar    inflating: amoeba-mysql-3.0.5-RC/lib/oro-2.0.8.jar    inflating: amoeba-mysql-3.0.5-RC/lib/commons-digester-1.8.jar    inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-net-io-2.2.0.jar    inflating: amoeba-mysql-3.0.5-RC/lib/commons-cli-1.2.jar    inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-runtime-1.3.0.jar    inflating: amoeba-mysql-3.0.5-RC/lib/spring-core-3.0.6.RELEASE.jar    inflating: amoeba-mysql-3.0.5-RC/lib/spring-asm-3.0.6.RELEASE.jar    inflating: amoeba-mysql-3.0.5-RC/lib/spring-context-3.0.6.RELEASE.jar    inflating: amoeba-mysql-3.0.5-RC/lib/spring-aop-3.0.6.RELEASE.jar    inflating: amoeba-mysql-3.0.5-RC/lib/aopalliance-1.0.jar    inflating: amoeba-mysql-3.0.5-RC/lib/spring-beans-3.0.6.RELEASE.jar    inflating: amoeba-mysql-3.0.5-RC/lib/spring-expression-3.0.6.RELEASE.jar    inflating: amoeba-mysql-3.0.5-RC/lib/plexus-classworlds-2.4.2-HEXNOVA.jar    inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-net-benchmark-1.2.0.jar    inflating: amoeba-mysql-3.0.5-RC/lib/amoeba-mysql-3.0.5-RC.jar     creating: amoeba-mysql-3.0.5-RC/bin/  inflating: amoeba-mysql-3.0.5-RC/bin/benchmark    inflating: amoeba-mysql-3.0.5-RC/bin/launcher    inflating: amoeba-mysql-3.0.5-RC/bin/mkdirhier    inflating: amoeba-mysql-3.0.5-RC/bin/shutdown    inflating: amoeba-mysql-3.0.5-RC/bin/benchmark.bat    inflating: amoeba-mysql-3.0.5-RC/bin/launcher.bat    inflating: amoeba-mysql-3.0.5-RC/bin/benchmark.classpath    inflating: amoeba-mysql-3.0.5-RC/bin/launcher.classpath     creating: amoeba-mysql-3.0.5-RC/conf/  inflating: amoeba-mysql-3.0.5-RC/conf/access_list.conf    inflating: amoeba-mysql-3.0.5-RC/conf/amoeba.dtd    inflating: amoeba-mysql-3.0.5-RC/conf/amoeba.xml    inflating: amoeba-mysql-3.0.5-RC/conf/dbserver.dtd    inflating: amoeba-mysql-3.0.5-RC/conf/dbServers.xml    inflating: amoeba-mysql-3.0.5-RC/conf/function.dtd    inflating: amoeba-mysql-3.0.5-RC/conf/functionMap.xml    inflating: amoeba-mysql-3.0.5-RC/conf/log4j.dtd    inflating: amoeba-mysql-3.0.5-RC/conf/log4j.xml    inflating: amoeba-mysql-3.0.5-RC/conf/rule.dtd    inflating: amoeba-mysql-3.0.5-RC/conf/rule.xml    inflating: amoeba-mysql-3.0.5-RC/conf/ruleFunctionMap.xml     creating: amoeba-mysql-3.0.5-RC/benchmark/  inflating: amoeba-mysql-3.0.5-RC/benchmark/context.xml    inflating: amoeba-mysql-3.0.5-RC/benchmark/objectMap.dtd    inflating: amoeba-mysql-3.0.5-RC/benchmark/query.xml    inflating: amoeba-mysql-3.0.5-RC/jvm.properties    [root@localhost install]# mv amoeba-mysql-3.0.5-RC /usr/local/

（3）amoeba的配置文件详解：

amoeba.xml：定义前端的控制文件，包括前端的端口，用户名和密码

dbServers.xml：定义后端节点的配置文件。设置数据库，登录mysql的账号和密码

a,配置amoeba.xml文件

[root@FileServerA conf]# vim amoeba.xml                                                                                8066             //***端口号***//                                                                                                                                                128                                        64                                                                                                                                                        root                                        123456"amoeba.xml" 91L, 3099C                                                                                      30,6-41        8%                                                                                        8066  //设置其他的端口                                                                                                                                                128                                        64                                                                                                                                                        lqb            //***远程访问代理的用户名和密码***//                                        123456          //***用户名和密码***//                                                                                                                                                ${amoeba.home}/conf/access_list.conf                                                                                                                                                                                                                                128                                                500                                                utf8                                                60                                                                                com.meidusa.toolkit.net.AuthingableConnectionManager                                                                ${amoeba.home}/conf/dbServers.xml                                                                                        ${amoeba.home}/conf/rule.xml                                ${amoeba.home}/conf/ruleFunctionMap.xml                                                        ${amoeba.home}/conf/functionMap.xml                1500                master                master         //***写的资源池***//                viplqb          //***写的资源池***//                true        

b,配置dbServers.xml文件

[root@FileServerA conf]# vim  dbServers.xml                                                                ${defaultManager}                        64                        128                                                3306     //***设置mysql访问端口号***//                                                          yazi  //***设置mysql访问默认库***//                                                root    //***登录mysql账号和密码***//                        123456                                                        500                        500                        1                        600000                        600000                        true                        true                        true                                          //***设置master和ip地址***//                                                                192.168.1.247                                         //***设置slave1和ip地址***//                                     //***如果有多个slave主机复制本配置到下边继续添加ip地址***//                                                192.168.1.245                                                                                                1   //***采用轮询方式***//                                                slave1  //***所有从库的主机都要写上***//                        

（4）启动amoeba并查看是否有报错。

[root@FileServerA conf]# /usr/local/amoeba-mysql-3.0.5-RC/bin/launcher &[1] 34774[root@FileServerA conf]# log4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf2016-07-04 13:46:11,874 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0 2016-07-04 13:47:53 [INFO] Project Name=Amoeba-MySQL, PID=32445 , starting...log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/log4j.xml2016-07-04 13:47:53,604 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf2016-07-04 13:47:53,789 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066. 2016-07-04 14:22:55 [INFO] ignore signal:HUPJava HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0 2016-07-04 17:31:08 [INFO] Project Name=Amoeba-MySQL, PID=34779 , starting...log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/log4j.xml2016-07-04 17:31:08,868 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf2016-07-04 17:31:09,128 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

(5)用其他的云服务器来进行登录：

[root@mysqlmaster ~]# mysql -ulqb -p -h292.168.1.28 -P8066    Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 965356058Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               || yazi               |+--------------------+5 rows in set (0.01 sec)mysql>

备注：当配置这个文件时amoeba.xml ，其他的主机通过代理就可以访问，如上说明amoeba.xml配置的没有问题。

（6）验证读写分离，这个需要配置dbServers.xml这个文件了，接下来即为验证读写分离。

（a)在没停掉同步之前在主库247上创建一张表；

mysql> use yazi;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_yazi |+----------------+| sxit           || test           |+----------------+2 rows in set (0.00 sec)mysql> create table lqb (id int(10) ,name varchar(10),address varchar(20));     Query OK, 0 rows affected (0.02 sec)

(b)在从库245上查看，并在slave上停止同步：

mysql> use yazi;Database changedmysql> show tables;+----------------+| Tables_in_yazi |+----------------+| lqb            || sxit           || test           |+----------------+3 rows in set (0.00 sec)mysql> stop slave    -> ;Query OK, 0 rows affected (0.00 sec)

（c)在主从上各插入一条不同的数据

在主库上插入（1，'zhangsan','master');

在从库上插入（2，'lisi','slave);

在主库上执行以下操作：

mysql> insert into lqb values(1,'zhangsan','master');Query OK, 1 row affected (0.00 sec)mysql> select * from lqb;+------+----------+---------+| id   | name     | address |+------+----------+---------+|    1 | zhangsan | master  |+------+----------+---------+1 row in set (0.00 sec)

在从库上执行以下操作：

mysql> insert into lqb values(2,'zhangsan','slave'); Query OK, 1 row affected (0.00 sec)mysql> select * from lqb;+------+----------+---------+| id   | name     | address |+------+----------+---------+|    2 | zhangsan | slave   |+------+----------+---------+1 row in set (0.00 sec)

（d)登录amoeba云服务器查看读操作：显示的是245slave云服务器

mysql> select * from lqb;

+------+----------+---------+

| id | name | address |

+------+----------+---------+

| 2 | zhangsan | slave |

+------+----------+---------+

1 row in set (0.00 sec)

（e）在amoeba云服务器上测试以下写操作，查看还是245从库上的数据。

mysql> insert into lqb values(3,'wanger','test_write');Query OK, 1 row affected (0.00 sec)mysql> select * from lqb;+------+----------+---------+| id   | name     | address |+------+----------+---------+|    2 | zhangsan | slave   |+------+----------+---------+1 row in set (0.00 sec)

在主库247上查询，可以看到已插入进来了

mysql> select * from lqb;+------+----------+------------+| id   | name     | address    |+------+----------+------------+|    1 | zhangsan | master     ||    3 | wanger   | test_write |+------+----------+------------+2 rows in set (0.00 sec)

在从库上245查询，还是刚才查询

mysql> select * from lqb;+------+----------+---------+| id   | name     | address |+------+----------+---------+|    2 | zhangsan | slave   |+------+----------+---------+1 row in set (0.00 sec)

至此，数据库读写分离已经配置完成，在正式生产环境中，需将读写主机的定义更改，因为数据库用的最多的还是读的操作。我们可以将读或查询交给从来处理，同样的，我们也可以添加多个从主机。让其自动从不同的从主机上读取数据库。

对于以上通过amoeba工具实现配置mysql读写分离的方法相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。