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是看不见的，这就是读写分离。
