mysql基于Amoeba(变形虫)实现读写分离

时间:2022-03-13 03:54:03

一、实验环境

1、准备五台主机,其中三台已经实现了Mysql的主从复制(主从复制不再演示,从安装Amoeba开始),一台安装Amoeba,一台用作试验机

  • mysql-master:192.168.200.111
  • mysql-slave1:192.168.200.112
  • mysql-slave2:192.168.200.113
  • Amoeba:192.168.200.114
  • Client:192.168.200.115

2、所有主机关闭安全机制和防火墙。

[[email protected] ~]# systemctl stop firewalld
[[email protected] ~]# iptables -F
[[email protected] ~]# setenforce 0

 

二、在主机Amoeba上安装java环境(192.168.200.114)

1、上传需要的安装包

[[email protected] ~]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin
anaconda-ks.cfg

2、执行.bin文件(就相当于解压.tar包)

[[email protected] ~]# chmod  x jdk-6u14-linux-x64.bin 
[[email protected] ~]# ./jdk-6u14-linux-x64.bin 
[[email protected] ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6

3、设置开机启动项,添加内容

[[email protected] ~]# vim /etc/profile 
export JAVA_HOME=/usr/local/jdk1.6     //声明一个全局变量JAVA_HOME,安装路径为/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib   //更新类的搜索路径
export PATH=$PATH:$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$HOME/bin

export AMOEBA_HOME=/usr/local/amoeba   //amoeba的解压路径
export PATH=$PATH:$AMOEBA_HOME/bin   //amoeba的命令

4、jdk安装完成后,java的版本与jdk的版本不同,删除高版本的java程序文件,重新执行一下/etc/profile

[[email protected] ~]# java -version
openjdk version "1.8.0_222"
OpenJDK Runtime Environment (build 1.8.0_222-b10)
OpenJDK 64-Bit Server VM (build 25.222-b10, mixed mode)
[[email protected] ~]# which java
/usr/bin/java
[[email protected] ~]# rm -rf /usr/bin/java 
[[email protected] ~]# source /etc/profile
[[email protected] ~]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

 

三、安装并配置Amoeba(192.168.200.114)

1、解压安装包

[[email protected] ~]# mkdir /usr/local/amoeba
[[email protected] ~]# tar -xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[[email protected] ~]# chmod -R 755 /usr/local/amoeba/

2、配置Amoeba的读写分离,两个Slave读负载均衡

在Master、Slave1、Slave2服务器中配置Amoeba的访问授权

[[email protected] ~]# mysql -uroot -p123123    //三台主机都要执行
MariaDB [(none)]> grant all on *.* to test@192.168.200.% identified by 123123;   //对所有库下的所有表,授权的用户名为test,允许访问的网段为200网段,密码为123123.
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、编辑amoeba.xml配置文件

[[email protected] ~]# cd /usr/local/amoeba/
[[email protected] amoeba]# ls
benchmark  changelogs.txt  lib          README.html
bin        conf            LICENSE.txt
[[email protected] amoeba]# cd conf/
[[email protected] conf]# ls
access_list.conf  dbServers.xml    log4j.xml
amoeba.dtd        function.dtd     rule.dtd
amoeba.xml        functionMap.xml  ruleFunctionMap.xml
dbserver.dtd      log4j.dtd        rule.xml
[[email protected] conf]# cp amoeba.xml amoeba.xml.bak

[[email protected] conf]# vim amoeba.xml
30 <property name="user">amoeba</property> //客户端必须拿这个用户名和密码登录amoeba 32 <property name="password">123123</property> 115 <property name="defaultPool">master</property> //默认 118 <property name="writePool">master</property> //写的池,交给master 119 <property name="readPool">slaves</property> //读的池,交给slaves组

5、编辑dbServer.xml配置文件

[[email protected] conf]# cp dbServers.xml dbServers.xml.bak

[[email protected] ~]# vim /usr/local/amoeba/conf/dbServers.xml
 19                         <!-- mysql port -->
 20                         <property name="port">3306</property>
 21 
 22                         <!-- mysql schema -->
 23                         <property name="schema">test</property>
 24 
 25                         <!-- mysql user -->
 26                         <property name="user">test</property>
 27 
 28                         <!--  mysql password --> 
 29                         <property name="password">123123</property>

 

45 <dbServer name="master" parent="abstractServer"> 46 <factoryConfig> 47 <!-- mysql ip --> 48 <property name="ipAddress">192.168.200.111</property> 49 </factoryConfig> 50 </dbServer> 51 52 <dbServer name="slave1" parent="abstractServer"> 53 <factoryConfig> 54 <!-- mysql ip --> 55 <property name="ipAddress">192.168.200.112</property> 56 </factoryConfig> 57 </dbServer> 58 <dbServer name="slave2" parent="abstractServer"> 59 <factoryConfig> 60 <!-- mysql ip --> 61 <property name="ipAddress">192.168.200.113</property> 62 </factoryConfig> 63 </dbServer> 66 <dbServer name="slaves" virtual="true"> //声明组 67 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"> 68 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--> 69 <property name="loadbalance">1</property> //组内成员的效果为轮询 70 71 <!-- Separated by commas,such as: server1,server2,server1 --> 72 <property name="poolNames">slave1,slave2</property> 73 </poolConfig> 74 </dbServer>

6、配置无误后,启动Amoeba软件,默认端口为TCP协议8066

nohup:执行命令不依赖于任何终端

[[email protected] ~]# nohup /usr/local/amoeba/bin/amoeba start &   //将命令放到后台运行,不依赖于任何终端

[[email protected] ~]# netstat -lnpt   //查看端口8066
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1366/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1632/master         
tcp6       0      0 :::8066                 :::*                    LISTEN      21943/java          
tcp6       0      0 127.0.0.1:18195         :::*                    LISTEN      21943/java          
tcp6       0      0 :::22                   :::*                    LISTEN      1366/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      1632/master 

 

四、client客户机访问测试(192.168.200.115)

安装mariadb

1、连接数据库测试

[[email protected] ~]# mysql -u amoeba -p123123 -h 192.168.200.114 -P 8066
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 74675057
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type help; or h for help. Type c to clear the current input statement.

MySQL [(none)]> 

2、测试读写分离

master主机上

MariaDB [(none)]> create database db_test;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use db_test;
Database changed
MariaDB [db_test]> create table student (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.01 sec)

建完库之后在两台slave机上查看,能看到新建的库说明主从复制没有问题

3、将两台slave主机上的主从服务给关掉

salve1:
MariaDB [(none)]> stop salve;

-------------------------------------------------------------------------------

slave2:
MariaDB [(none)]> stop salve;

编写测试

master:
MariaDB [db_test]> insert into student values(1,crushlinux,this_is_master);
Query OK, 1 row affected (0.01 sec)

---------------------------------------------------------------------------
slave1:
MariaDB [db_test]> insert into student values(2,crushlinux,this_is_slave1);
Query OK, 1 row affected (0.00 sec)

--------------------------------------------------------------------------
slave2:
Database changed
MariaDB [db_test]> insert into student values(3,crushlinux,this_is_slave2);
Query OK, 1 row affected (0.01 sec)

从client客户机*问,访问到的是两台salve机的轮询

MySQL [db_test]> select * from student;
 ------ ------------ ---------------- 
| id   | name       | address        |
 ------ ------------ ---------------- 
|    2 | crushlinux | this_is_slave1 |
 ------ ------------ ---------------- 
1 row in set (0.00 sec)

MySQL [db_test]> select * from student;
 ------ ------------ ---------------- 
| id   | name       | address        |
 ------ ------------ ---------------- 
|    3 | crushlinux | this_is_slave2 |
 ------ ------------ ---------------- 
1 row in set (0.01 sec)

4、客户机上添加一个数据,访问,还是读到两台slave机上的

MySQL [db_test]> insert into student values(4,crushlinux,this_is_client);
Query OK, 1 row affected (0.01 sec)

MySQL [db_test]> select * from student;
 ------ ------------ ---------------- 
| id   | name       | address        |
 ------ ------------ ---------------- 
|    2 | crushlinux | this_is_slave1 |
 ------ ------------ ---------------- 
1 row in set (0.01 sec)

MySQL [db_test]> select * from student;
 ------ ------------ ---------------- 
| id   | name       | address        |
 ------ ------------ ---------------- 
|    3 | crushlinux | this_is_slave2 |
 ------ ------------ ---------------- 
1 row in set (0.02 sec)

在client机上写数据时,写交给master主机来处理,两台slave主机没有变化,因为此时主从服务已经停了,所以两台slave机上不会访问到master主机上写入的内容

5、开启主从复制测试

slave1:
MariaDB [db_test]> start slave;
Query OK, 0 rows affected (0.00 sec)

-----------------------------------------------------------------------
slave2:
MariaDB [db_test]> start slave;
Query OK, 0 rows affected (0.01 sec)

==========================================

client:
MySQL [db_test]> select * from student;
 ------ ------------ ---------------- 
| id   | name       | address        |
 ------ ------------ ---------------- 
|    3 | crushlinux | this_is_slave2 |
|    1 | crushlinux | this_is_master |
|    4 | crushlinux | this_is_client |
 ------ ------------ ---------------- 
3 rows in set (0.01 sec)

MySQL [db_test]> select * from student;
 ------ ------------ ---------------- 
| id   | name       | address        |
 ------ ------------ ---------------- 
|    2 | crushlinux | this_is_slave1 |
|    1 | crushlinux | this_is_master |
|    4 | crushlinux | this_is_client |
 ------ ------------ ---------------- 
3 rows in set (0.01 sec)

主从复制开启后,两台slave机上可以读取到master主机上写入的数据,client在访问测试的时候就能读到主机上的数据,因为两台slave机是轮询效果,所以能访问到1、2、4或1、3、4