MySQL 读写分离

最新推荐文章于 2024-03-07 11:47:52 发布

lxmy

最新推荐文章于 2024-03-07 11:47:52 发布

阅读量446

点赞数 5

分类专栏：数据库实验理论文章标签： mysql

本文链接：https://blog.csdn.net/weixin_48191211/article/details/108597258

版权

理论同时被 3 个专栏收录

57 篇文章 0 订阅

订阅专栏

实验

49 篇文章 1 订阅

订阅专栏

数据库

11 篇文章 0 订阅

订阅专栏

文章目录

一、读写分离原理
- 1.1、原因
- 1.2、原理
二、MySQL 主从复制原理及配置
三、案例实施

一、读写分离原理

1.1、原因

在企业应用中，在大量的数据请求下，单台数据库将无法承担所有的读写操作
配置多台数据库服务器以实现读写分离
读写分离建立在主从复制的基础上

1.2、原理

简单来说，主服务器写，从服务器读，基本的原理是让主数据库处理事务性查询，二从数据库处理Select查询
数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库。
目前比较常见的MySQL读写分离分为二种：
1、基于程序代码内部实现
在代码中根据select、insert进行路由分类，这类方法也是目前生产环境应用广泛的，优点是性能较好，因为
在程序代码中实现，不需要增加额外的设备作为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。
2、基于中间代理层实现
代理一般位于客户端和服务器之间，代理服务器接受到客户端的请求后通过判断后转发到后端数据库，有二个代表性程序
◆MySQL-Proxy。 ----MySQL的开源项目，通过自带的lua脚本进行SQL判断，MySQL官方不建议用这个在生产环境中
◆Amoeba（变形虫） ----由陈思儒开发，曾就职于阿里巴巴，该程序由Java语言开发，阿里将其用于生产环境
但是它不支持事物和存储过程。
在这里插入图片描述

二、MySQL 主从复制原理及配置

上篇博客有所描述，本次案例实施基于主从复制基础，详情请看：
https://blog.csdn.net/weixin_48191211/article/details/108578317

三、案例实施

3.1、环境准备

实验环境防火墙和核心防护均已关闭

systemctl stop firewalld.service 
setenforce 0

3.1.1、VMware 主机

五台centos7.6 主机
一台做客户端 ip地址为 20.0.0.24
一台做amoeba ip地址为 20.0.0.21
一台主mysql服务器 ip地址为 20.0.0.25
两台从mysql服务器 ip地址为 20.0.0.23和20.0.0.22

3.1.2、拓扑图

在这里插入图片描述

3.1.3、实验目的

实现读写分离

3.2、实验过程

3.2.1、搭建mysql主从复制环境

https://blog.csdn.net/weixin_48191211/article/details/108578317

3.2.2、安装配置Amoeba

（1）卸载原有的java环境

[root@amoeba ~]# java -version    ##确定java版本  1.8.0_181的 有点高
openjdk version "1.8.0_181"
OpenJDK Runtime Environment (build 1.8.0_181-b13)
OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode)
[root@amoeba ~]# rpm -qa |grep java
java-1.8.0-openjdk-headless-1.8.0.181-7.b13.el7.x86_64          ####卸载
tzdata-java-2018e-3.el7.noarch
python-javapackages-3.4.1-11.el7.noarch
java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64                          ####卸载
javapackages-tools-3.4.1-11.el7.noarch
java-1.7.0-openjdk-1.7.0.191-2.6.15.5.el7.x86_64                      ####卸载
java-1.7.0-openjdk-headless-1.7.0.191-2.6.15.5.el7.x86_6       ####卸载
[root@amoeba ~]#  rpm -e --nodeps java-1.8.0-openjdk-headless-1.8.0.181-7.b13.el7.x86_64
[root@amoeba ~]#  rpm -e --nodeps java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64
[root@amoeba ~]#  rpm -e --nodeps java-1.7.0-openjdk-1.7.0.191-2.6.15.5.el7.x86_64
[root@amoeba ~]#  rpm -e --nodeps java-1.7.0-openjdk-headless-1.7.0.191-2.6.15.5.el7.x86_64

（2）安装JDK
上传JDK到opt目录

[root@amoeba ~]# cd /opt
[root@amoeba opt]# tar xzvf jdk-8u144-linux-x64.tar.gz
[root@amoeba opt]# cp -rv jdk1.8.0_144/ /usr/local/java
[root@amoeba 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@amoeba opt]# source /etc/profile
[root@amoeba opt]# java -version
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)

（3）安装amoeba
上传包到opt目录

[root@amoeba opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/
[root@amoeba opt]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@amoeba opt]# chmod -R 755 /usr/local/amoeba/
[root@amoeba 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"

（4）制作amoeba管理脚本

[root@amoeba 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
"/etc/rc.d/init.d/amoeba" [New] 32L, 544C written
[root@amoeba opt]# chmod +x /etc/init.d/amoeba
[root@amoeba opt]# chkconfig --add amoeba
[root@amoeba opt]# service amoeba start
[root@amoeba opt]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      20226/java

3.2.3、配置Amoeba 读写分离，两个Slave 读负载均衡

（1）三台mysql服务器添加权限和用户开放给amoeba访问
登录20.0.0.25终端

[root@master ~]# mysql -u root -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.01 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

登录20.0.0.23终端

[root@slave1 ~]# 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.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

登录20.0.0.22终端

[root@slave2 ~]#  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.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

（2）配置amoeba服务器

[root@amoeba opt]# cd /usr/local/amoeba
[root@amoeba amoeba]#  vi conf/amoeba.xml
---28行-----设置客户端连接amoeba前端服务器时使用的用户名和密码----
 <property name="user">：</property>
----30行---------
 <property name="password">123456</property>
------------------------------------以上配置用于客户端连接用户名密码-------------

---83-去掉注释-同时把默认的server1改成master,把默认的servers改成 slaves
 <property name="defaultPool">master</property>
 <property name="writePool">master</property>
 <property name="readPool">slaves</property>
######上面修改后如下####   <!--  -->  这种注释一定要去掉
     82                 <property name="LRUMapSize">1500</property>
     83                 <property name="defaultPool">master</property>
     84                 <property name="writePool">master</property>
     85                 <property name="readPool">slaves</property>
     86                 <property name="needParse">true</property> 

[root@amoeba amoeba]# vi conf/dbServers.xml
<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://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 name="abstractServer" abstractive="true">
                <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
                        <property name="connectionManager">${defaultManager}</property>
                        <property name="sendBufferSize">64</property>
                        <property name="receiveBufferSize">128</property>

                        <!-- mysql port -->
                        <property name="port">3306</property>

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

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

                        <property name="password">abc123</property>
                </factoryConfig>

                <poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
                        <property name="maxActive">500</property>
                        <property name="maxIdle">500</property>
                        <property name="minIdle">1</property>
                        <property name="minEvictableIdleTimeMillis">600000</property>
                        <property name="timeBetweenEvictionRunsMillis">600000</property>
                        <property name="testOnBorrow">true</property>
                        <property name="testOnReturn">true</property>
                        <property name="testWhileIdle">true</property>
                </poolConfig>
        </dbServer>

        <dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.25</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.23</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave2"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.22</property>
                </factoryConfig>
        </dbServer>

        <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 -->
                        <property name="poolNames">slave1,slave2</property>
                </poolConfig>
        </dbServer>

</amoeba:dbServers>

（3）启动Amoeba软件

[root@amoeba amoeba]# service amoeba restart
[root@amoeba amoeba]#  netstat -anpt | grep java
tcp6       0      0 :::8066                 :::*                    LISTEN      72410/java          
tcp6       0      0 20.0.0.21:60102         20.0.0.22:3306          ESTABLISHED 72410/java          
tcp6       0      0 20.0.0.21:52726         20.0.0.25:3306          ESTABLISHED 72410/java          
tcp6       0      0 20.0.0.21:43044         20.0.0.23:3306          ESTABLISHED 72410/java

3.2.4、验证

（1）客户端安装mysql
由于是用来验证，所以可以直接用yum安装

[root@client ~]# yum install -y mysql
[root@client ~]# mysql -u amoeba -p123456 -h 20.0.0.21 -P8066

（2）在主mysql上创建数据库 20.0.0.25

[root@master ~]# mysql -u root -p
Enter password: 

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

（3）在从1 mysql关闭同步 20.0.0.23

[root@slave1 ~]# mysql -u root -p
Enter password: 

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

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

（4）在从2 mysql关闭同步 20.0.0.22

[root@slave2 ~]# mysql -u root -p
Enter password: 


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

mysql>  use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

（5）在主mysql上插入数据 20.0.0.25

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

（6）登录客户端 20.0.0.24

[root@client ~]# mysql -u amoeba -p123456 -h 20.0.0.21 -P8066


MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lx                 |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

MySQL [(none)]> use test;
Database changed
MySQL [test]> select * from zhang1; 
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.00 sec)



MySQL [test]> select * from zhang1;  
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.00 sec)

上述实验，实现数据读取负载均衡

（7）在客户机上 20.0.0.24 继续写输数据，按照正常的规则应该是slave1 和slave2是看不见的

MySQL [test]> insert into zhang1 values('4','zhang','write_test');

登录mastar 20.0.0.25

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.23

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

登录slave2 20.0.0.22

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

上述验证结果如下：
在客户端（20.0.0.24）上操作写入数据，数据是往master上写的，在salve1和slave2上看不到写的数据

lxmy

关注

5
点赞
踩
0

收藏

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

文章目录一、读写分离原理1.1、原因1.2、原理二、MySQL 主从复制原理及配置三、案例实施3.1、环境准备3.1.1、VMware 主机3.1.2、拓扑图3.1.3、实验目的3.2、实验过程3.2.1、搭建mysql主从复制环境3.2.2、安装配置Amoeba3.2.3、配置Amoeba 读写分离，两个Slave 读负载均衡3.2.4、验证一、读写分离原理1.1、原因在企业应用中，在大量的数据请求下，单台数据库将无法承担所有的读写操作配置多台数据库服务器以实现读写分离读写分离建立在主从复制的基
复制链接

扫一扫