MySQL读写分离

于 2020-10-21 20:01:19 发布
阅读量58 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/longtiandao/article/details/109208553
版权

MySQL读写分离

1.定义

简单来说，主服务器写，从服务器读，

2、原理

基本的原理是让主数据库处理事务性查询，二从数据库处理Select查询
数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库

3.分类

3.1.基于程序代码内部实现

在代码中根据select、insert进行路由分类

3.2.基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接受到客户端的请求后通过判断后转发到后端数据库

4.实验

准备工作

三台安装MySQL的虚拟机（已做主从复制）IP地址master:20.0.0.5,slave1:20.0.0.6,slave2:20.0.0.7，一台客户机ip:20.0.0.8,安装MySQL数据库，一台带着amoeba软件的主机ip :20.0.0.9

卸载原有的Java环境

[root@localhost ~]# 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

安装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环境变成1.8.0_144的##
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)

安装amoeba,上传软件包到opt目录下

[root@localhost opt]# yum -y install 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 -Xms1024m -Xmx1024m -Xss256k"  //修改成这个

[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 ~] amoeba start
[root@localhost ~] netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      23093/java

在三台mysql数据库中为amoeba授权
登录20.0.0.5终端

[root@localhost ~]# mysql -u root -p              ####输入密码abc123
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT ALL  ON *.* TO test@'20.0.0.%' IDENTIFIED BY 'abc123';
mysql> FLUSH PRIVILEGES;
mysql> quit

#登录20.0.0.6终端

[root@localhost ~]# mysql -u root -p              ####输入密码abc123
mysql> GRANT ALL  ON *.* TO test@'20.0.0.%' IDENTIFIED BY 'abc123';
mysql> FLUSH PRIVILEGES;
mysql> quit

登录20.0.0.7终端

[root@localhost ~]# mysql -u root -p              ####输入密码abc123
mysql> GRANT ALL  ON *.* TO test@'20.0.0.%' IDENTIFIED BY 'abc123';
mysql> FLUSH PRIVILEGES;
mysql> quit

修改amoeba配置文件

[root@localhost opt]# cd /usr/local/amoeba

[root@localhost amoeba]# vi conf/amoeba.xml
修改如下 <!--  -->  这种注释要去掉
     28    <property name="user">amoeba</property>
     30    <property name="password">123456</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@localhost amoeba]# vi conf/dbServers.xml
--26-29行--去掉注释--修改如下：
 <property name="user">test</property>

 <property name="password">abc123</property>
 43行- <dbServer name="master"  parent="abstractServer">
 46- <property name="ipAddress">20.0.0.5</property>

--50--<dbServer name="slave1"  parent="abstractServer">
--53--<property name="ipAddress">20.0.0.6</property>



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

<!-- mysql user -->
<property name="user">test</property>      //改成数据库授权的         

<property name="password">abc123</property> 
  <dbServer name="master"  parent="abstractServer">        #####name后面改成 master
            <factoryConfig>
                    <!-- mysql ip -->
                    <property name="ipAddress">20.0.0.5</property>    
            </factoryConfig>
    </dbServer>

    <dbServer name="slave1"  parent="abstractServer">        #####name后面改成 slave1
            <factoryConfig>
                    <!-- mysql ip -->
                    <property name="ipAddress">20.0.0.6</property>    ###ipAddress加上从1mysql的IP地址
            </factoryConfig>
    </dbServer>
        <dbServer name="slave2"  parent="abstractServer">        #####name后面改成 slave2
                 <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.7</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
tcp6       0      0 :::8066                 :::*                    LISTEN      23093/java

客户机测试 20.0.0.8

在主mysql上创建数据库 20.0.0.5

[root@localhost ~]# mysql -u root -p          ###输入mysql密码 abc123
mysql> use test;
Database changed
mysql> create table zhang1 (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.05 sec）

在从1 mysql关闭同步 20.0.0.6

[root@localhost ~]# mysql -u root -p          ###输入mysql密码 abc123
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.7###

[root@localhost ~]# mysql -u root -p          ###输入mysql密码 abc123
mysql> stop slave;
Query OK, 0 rows affected (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.5

[root@localhost ~]# mysql -u root -p          ###输入mysql密码 abc123
mysql> use test;
Database changed
mysql> insert into zhang1 values('1','zhang','this_is_master');
Query OK, 0 rows affected (0.05 sec）

登录客户端 20.0.0.8

[root@localhost ~]# mysql -u root -p 
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bdqn               |
| db_test            |
| mysql              |
| performance_schema |
| sys                |
| test               |                       
+--------------------+
7 rows in set (0.01 sec)

MySQL [(none)]> use test;                    
Database changed
MySQL [test]> select * from zhang1;           //查看zhang信息 发现进入slave1 数据中的数据
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.00 sec)

MySQL [test]> select * from zhang;          //查看zhang信息 发现进入slave2 数据中的数据
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.01 sec)

结果是继续写输数据，按照正常的规则应该是slave1 和slave2是看不见的，这就是读写分离。

龙天道酬
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值