mysql读写分离架构

MySQL读写分离架构

在上面主从架构之上搭建读写分离！我们实现读写分离使用的软件史amoeba，一款java开发的开源软件！

我们再准备一台虚拟机安装amoeba！

amoeba： 192.168.189.163

安装jdk和amoeba

• 上传jdk安装包jdk-6u14-linux-x64.bin和amoeba安装包到服务器

  [root@node-163 ~]# ls
  amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin 
  

• 给安装包增加执行权限并安装

  # chmod +x jdk-6u14-linux-x64.bin
  # ./jdk-6u14-linux-x64.bin
  #执行后一路按空格就行，然后输入yes回车即可
  Do you agree to the above license terms? [yes or no]
  yes
  
  ....
  Press Enter to continue.....
  
  
  Done.
  #按下回车就可以了
  

• 将解压后的目录剪切至/usr/local目录下

  # mv jdk1.6.0_14 /usr/local/
  

• 配置环境变量

  # vim /etc/profile
  # 在文件末尾增加下面两行配置
  export JAVA_HOME=/usr/local/jdk1.6.0_14
  export PATH=$JAVA_HOME/bin:$PATH
  # 保存退出后，实行source 从新读取下
  # source  /etc/profile
  [root@node-163 ~]# 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安装目录，并将amoeba解压至目录下

  # mkdir -p /usr/local/amoeba
  # tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
  

给amoeba授权登录mysql用户

• 登录mysql主（master）服务器授权

  mysql> grant all on *.* to 'amoeba'@'%' identified by '123';
  Query OK, 0 rows affected (0.00 sec)
  

• mysql的从主机无需再授权了，他会把用户授权的命令同步过去

配置amoeba

• 编辑dbServer.xml添加mysql服务器

  # cd /usr/local/amoeba/conf/
  # vim dbServers.xml
  .......
  #配置数据库授权的登录用户amoeba，以及登录数据库密码为123
  <property name="user">amoeba</property>
  <property name="password">123</property>
  ......
  #添加master主机
  <dbServer name="master"  parent="abstractServer">
                  <factoryConfig>
                          <!-- mysql ip -->
                           #master主机的ip地址
                          <property name="ipAddress">192.168.189.161</property>
                  </factoryConfig>
  </dbServer>
  #添加slave主机，主机名为slave1
  <dbServer name="slave1"  parent="abstractServer">
                  <factoryConfig>
                          <!-- mysql ip -->
                          #salve主机的ip地址
                          <property name="ipAddress">192.168.189.162</property>
                  </factoryConfig>
  </dbServer>
  #定义slave主机组，组名为slaves
  <dbServer name="slaves" virtual="true">
              <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                    <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                          <property name="loadbalance">1</property>
  
                          <!-- Separated by commas,such as: server1,server2,server1 -->
                          #组成员有slave1
                          <property name="poolNames">slave1</property>
              </poolConfig>
  </dbServer>
  
  

• 配置amoeba.xml，配置登录amoeba的用户和读写分离规则

  # vim amoeba.xml
  .......
  #配置登录amoeba的用户为root，登录的密码为123
  <property name="user">root</property>
  <property name="password">123</property>
  .......
  #配置读写分离规则
  #默认主机为master
  <property name="defaultPool">master</property>
  #写的主机为master（master主机名）
  <property name="writePool">master</property>
  #读的主机为slaves(slave的组名)
  <property name="readPool">slaves</property>
  

• 启动amoeba,amoeba默认监听端口为8066

  # cd /usr/local/amoeba/bin
  # ./amoeba start
  log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
  2020-05-21 17:02:41,967 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
  log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
  2020-05-21 17:02:42,417 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
  2020-05-21 17:02:42,425 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:47830
  

• 登录amoeba

  随便找一个虚拟机，使用mysql客户端登录amoeba即可

  [root@node-163 ~]# mysql -uroot -p123 -h192.168.189.163 -P8066
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 218571184
  Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution
  
  Copyright (c) 2000, 2013, 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>
  

• 测试读写分离

  • 登录amoeba操作

  mysql> create database wg;
  Query OK, 1 row affected (0.01 sec)
  mysql> use wg;
  Database changed
  mysql> create table stu(id int,name varchar(5));
  Query OK, 0 rows affected (0.01 sec)
  mysql> insert into stu values(1,'tom');
  Query OK, 1 row affected (0.01 sec)
  mysql> select * from stu;
  +------+------+
  | id   | name |
  +------+------+
  |    1 | tom  |
  +------+------+
  1 row in set (0.01 sec)
  

  • 登录mysql的从主机(slave),停止slave同步

    mysql> stop slave;
    Query OK, 0 rows affected (0.01 sec)
    

  • 再登录到amoeba插入一条数据，我们发现看不到新的数据jack

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

  • 我们到mysql master主上查看有新的数据，说明数据是写到了mysql主上

    mysql> select * from stu;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | tom  |
    |    2 | jack |
    +------+------+
    2 rows in set (0.00 sec)
    

  • 我们到从上查看没有新的数据，这跟在amoeba上查询的结果一致，说明数据是从mysql的从主机上读取

    mysql> select * from stu;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | tom  |
    +------+------+
    1 row in set (0.00 sec)