实验详解MySQL读写分离

最新推荐文章于 2021-07-02 00:11:07 发布

Mr_ChenWJ

最新推荐文章于 2021-07-02 00:11:07 发布

阅读量662

点赞数

分类专栏：实验

本文链接：https://blog.csdn.net/Mr_ChenWJ/article/details/108597229

版权

实验专栏收录该内容

73 篇文章 0 订阅

订阅专栏

一搭建MySQl主从复制环境

此内容见上章博客
说明：所有服务器关闭firewalld或者进行规则设置

二 Amoeba服务器环境安装

2.1 安装JDK

##上传JDK到opt目录下
[root@localhost ~]# cd /opt    
[root@localhost opt]# tar xzvf jdk-8u144-linux-x64.tar.gz
[root@localhost opt]# cp -rv jdk1.8.0_144/ /usr/local/java
[root@localhost opt]# vi /etc/profile 
export JAVA_HOME=/usr/local/java    ##在最后面添加四行内容
export JRE_HOME=/usr/local/java/jre
export PATH=$PATH:/usr/local/java/bin
export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib
[root@localhost opt]# source /etc/profile
[root@localhost opt]# java -version    ##查看java环境
java version "1.8.0_144"
Java(TM) SE Runtime Environment (build 1.8.0_144-b01)
Java HotSpot(TM) 64-Bit Server VM (build 25.144-b01, mixed mode)

说明：之前有java环境的要先确定版本是否匹配，如果不匹配要先卸载原有的java环境

[root@localhost ~]# rpm -qa |grep java

##查出openjdk相关的文件并且删除它
[root@localhost ~]#  rpm -e --nodeps java-1.8.0-openjdk-headless-1.8.0.181-7.b13.el7.x86_64
[root@localhost ~]#  rpm -e --nodeps java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64
[root@localhost ~]#  rpm -e --nodeps java-1.7.0-openjdk-1.7.0.191-2.6.15.5.el7.x86_64
[root@localhost ~]#  rpm -e --nodeps java-1.7.0-openjdk-headless-1.7.0.191-2.6.15.5.el7.x86_64

2.2 安装Amoeba

[root@localhost opt]# https://sourceforge.net/projects/amoeba/files
[root@localhost opt]# yum -y install unzip    ###如果unzip命令没有，必须装下，如果装了，此步骤跳过
[root@localhost opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/
[root@localhost opt]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@localhost opt]# chmod -R 755 /usr/local/amoeba/

[root@localhost opt]#vi /usr/local/amoeba/jvm.properties
JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"   ##32行修改成如下
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

2.3 制作Amoeba管理脚本

[root@localhost opt]# vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 62 62
#
export JAVA_HOME=/usr/local/java
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
NAME=Amoeba
AMOEBA_BIN=/usr/local/amoeba/bin/launcher
SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown
PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid
SCRIPTNAME=/etc/init.d/amoeba

case "$1" in
start)
echo -n "Starting $NAME... "
$AMOEBA_BIN
echo " done"
;;
stop)
echo -n "Stoping $NAME... "
$SHUTDOWN_BIN
echo " done"
;;
restart)
$SHUTDOWN_BIN
sleep 1
$AMOEBA_BIN
;;
*)
echo "Usage: $SCRIPTNAME {start|stop|restart}"
exit 1
;;
esac
[root@localhost opt]# chmod +x /etc/init.d/amoeba
[root@localhost opt]# chkconfig --add amoeba
[root@localhost opt]# service amoeba start    ##ctrl + c 退出
[root@localhost opt]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      3870/java           
tcp6       0      0 20.0.0.14:8066          20.0.0.21:45298         ESTABLISHED 3870/java

2.4 在三台mysql数据库中为Amoeba授权

master服务器

[root@ns1 ~]# mysql -uroot -p
Enter password: 
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL  ON *.* TO test@'20.0.0.%' IDENTIFIED BY 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

slave1服务器

[root@localhost ~]# mysql -uroot -p
Enter password: 
mysql> GRANT ALL  ON *.* TO test@'20.0.0.%' IDENTIFIED BY 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

slave2服务器

[root@ceshi ~]# mysql -u root -p
Enter password: 
mysql> GRANT ALL  ON *.* TO test@'20.0.0.%' IDENTIFIED BY 'abc123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.5 在Amoeba服务器上修改配置文件

[root@localhost opt]# cd /usr/local/amoeba
[root@localhost amoeba]# vi conf/amoeba.xml
......
##配置用于客户端连接用户名密码##
 <property name="user">amoeba</property>      ##28行
 <property name="password">123456</property>    ##30行
...... 
##上面修改后如下,<!--  -->  这种注释一定要去掉
 <property name="LRUMapSize">1500</property>
 <property name="defaultPool">master</property>
 <property name="writePool">master</property>
 <property name="readPool">slaves</property>
 <property name="needParse">true</property> 

 [root@localhost amoeba]# vi conf/dbServers.xml
 ##26-29行去掉注释##
 <property name="user">test</property>
   
 <property name="password">abc123</property>

 ##主服务器地址##
 <dbServer name="master"  parent="abstractServer">    ##43行
 <property name="ipAddress">20.0.0.11</property>    ##46行

 ##50行-从服务器主机名##
<dbServer name="slave1"  parent="abstractServer">
 ##53行-从服务器地址##
 <property name="ipAddress">20.0.0.12</property>
 ##57行-从服务器主机名##
<dbServer name="slave2"  parent="abstractServer">
 ##60行-从服务器地址##
 <property name="ipAddress">20.0.0.13</property>
 ......
<!-- mysql port -->
<property name="port">3306</property>

 <!-- mysql schema -->
 <property name="schema">test</property>

<!-- mysql user -->
<property name="user">test</property>    ##user 后面改成test 这边是mysql数据库授权账户

<property name="password">abc123</property>    ##password 后面改成abc123  这是mysql数据库授权密码
</factoryConfig>
 
......
<!-- mysql schema -->
<property name="schema">test</property>    ##数据库中要有此处定义的数据库，否则客户端连接后会报错，默认我用test数据库，创建是test数据库不要弄错了

        <dbServer name="master"  parent="abstractServer">    ###name后面改成 master
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.11</property>    ##ipAddress加上主mysql的IP地址
                </factoryConfig>
        </dbServer>

        <dbServer name="slave1"  parent="abstractServer">    ###name后面改成 slave1
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.12</property>    ##ipAddress加上从1mysql的IP地址
                </factoryConfig>
        </dbServer>
##注意啦！在脚本中slave2是没有的，需要复制下##
        <dbServer name="slave2"  parent="abstractServer">    ###name后面改成 slave2
                 <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.13</property>    ##ipAddress加上从2mysql的IP地址
                 </factoryConfig>
        </dbServer>
        
        <dbServer name="slaves" virtual="true">    ##name后面改成 slaves
                <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 -->
                        <property name="poolNames">slave1,slave2</property>    ##poolNames后面改成 slave1,slave2
                </poolConfig>
        </dbServer> 
[root@localhost amoeba]# service amoeba restart
[root@localhost amoeba]# netstat -anpt | grep java
[root@localhost amoeba]# netstat -anpt | grep java
tcp6       0      0 :::8066                 :::*                    LISTEN      3870/java           
tcp6       0      0 20.0.0.14:58598         20.0.0.13:3306          ESTABLISHED 3870/java           
tcp6       0      0 20.0.0.14:45770         20.0.0.12:3306          ESTABLISHED 3870/java           
tcp6       0      0 20.0.0.14:34398         20.0.0.11:3306          ESTABLISHED 3870/java

2.6 测试

2.6.1 测试实验，实现数据读取负载均衡

测试用客户端 20.0.0.21
关闭防火墙，关闭核心防护

[root@localhost ~]# yum install -y mysql
[root@localhost ~]# mysql -u amoeba -pabc123 -h 20.0.0.14 -P8066

在主mysql上创建数据库 20.0.0.11

mysql> use test;
Database changed
mysql> create table zhang1 (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.01 sec)

在从1 mysql关闭同步 20.0.0.12

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

mysql> use test;
Database changed
mysql> insert into zhang1 values('2','zhang','this_is_slave1');
Query OK, 1 row affected (0.00 sec)

在从2 mysql关闭同步 20.0.0.13

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use test;
Database changed
mysql> insert into zhang1 values('3','zhang','this_is_slave2');
Query OK, 1 row affected (0.00 sec)

在主mysql上插入数据 20.0.0.11

mysql> insert into zhang1 values('1','zhang','this_is_master');
Query OK, 1 row affected (0.01 sec)

登录客户端 20.0.0.14

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a                  |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> select * from zhang1;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.01 sec)
mysql> select * from zhang1;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    3 | zhang | this_is_slave2 |
+------+-------+----------------+
2 rows in set (0.00 sec)

2.6.2 测试实验，实现数据读取分离

在客户机上 20.0.0.21 上继续写数据

mysql> use test;
Database changed
mysql> insert into zhang1 values('4','zhang','write_test');
Query OK, 1 row affected (0.02 sec)

登录mastar 20.0.0.11

mysql> select * from zhang1;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    1 | zhang | this_is_master |
|    4 | zhang | write_test     |
+------+-------+----------------+
2 rows in set (0.00 sec)

登录slave1 20.0.0.12

mysql> select * from zhang1; 
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.00 sec)

登录slave2 20.0.0.13

mysql> select * from zhang1; 
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    3 | zhang | this_is_slave2 |
+------+-------+----------------+
2 rows in set (0.01 sec)

说明：在客户端（20.0.0.21）上操作写入数据，数据是往master上写的，在salve1和slave2上看不到写的数据

Mr_ChenWJ

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
实验详解MySQL读写分离

目录一搭建MySQl主从复制环境二 Amoeba服务器环境安装2.1 安装JDK2.2 安装Amoeba2.3 制作Amoeba管理脚本2.4 在三台mysql数据库中为Amoeba授权2.5 在Amoeba服务器上修改配置文件2.6 测试2.6.1 测试实验，实现数据读取负载均衡2.6.2 测试实验，实现数据读取分离一搭建MySQl主从复制环境此内容见上章博客说明：所有服务器关闭firewalld或者进行规则设置二 Amoeba服务器环境安装2.1 安装JDK##上传JDK到opt目录下
复制链接

扫一扫