MySQL：MySQL的读写分离--理论干货--精讲案例！！！！

1.1.1 理论精讲

• 读写分离就是只在主服务器上写，只在从服务器上读
• 主数据库处理事务查询，而从数据量梳理select查询
• 数据库复制倍用来把事务性查询导致的变更同步到集群中的从数据库
  

1.2.1 案例概述

• 案例拓扑图
  *

1.3.1 案例案施

• 所有服务器关闭Firewalld或者进行规则设置
• 搭建Mysql主从复制环境

2.1.1 案例

2.2.1 案例环境

主机	操作系统	IP地址	主要软件
amoeba	centos-7.6-X86_64	192.168.100.41	amoeba-mysql-3.0.5-RC-distribution.zip jdk-8u144-linux-x64.tar.gz
Master	centos-7.6-X86_64	192.168.100.42	mysql-boost-5.7.20.tar.gz
Slave1	centos-7.6-X86_64	192.168.100.43	mysql-boost-5.7.20.tar.gz
客户机	centos-7.6-X86_64	192.168.100.45	mysql-boost-5.7.20.tar.gz

2.3.1 配置MySQL读写分离. 192.168.100.41

• 卸载原有的java环境

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

• 查出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

• 安装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------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"

• 制作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


service amoeba start
ctrl + c
netstat -anpt | grep 8066 //默认监听在8066端口

• 在两台mysql数据库中为amoeba授权

[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@'192.168.100.%' IDENTIFIED BY 'abc123';
mysql> FLUSH PRIVILEGES;
mysql> quit

• 在前端服务器上 192.168.100.41 你可以把它理解成调度器

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



[root@localhost amoeba]# vi conf/amoeba.xml
---28行-----设置客户端连接amoeba前端服务器时使用的用户名和密码----
 <property name="user">amoeba</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@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">192.168.100.42</property>

--50-从服务器主机名-
<dbServer name="slave1"  parent="abstractServer">
--53-从服务器地址-
 <property name="ipAddress">192.168.100.43</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">192.168.100.42</property>    ####ipAddress加上主mysql的IP地址
                </factoryConfig>
        </dbServer>

        <dbServer name="slave1"  parent="abstractServer">        #####name后面改成 slave1
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.100.43</property>    ###ipAddress加上从1mysql的IP地址
                </factoryConfig>
        </dbServer>
###注意啦！在脚本中slave2是没有的，需要复制下#####
        <dbServer name="slave2"  parent="abstractServer">        #####name后面改成 slave2
                 <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.100.44</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> 
 
 

---------------------以上脚本做参考---注意----------------------
<!-- mysql schema -->
<property name="schema">test</property> //数据库中要有此处定义的数据库，否则客户端连接后会报如下错误：

ERROR 1044 (42000): Could not create a validated object, cause: ValidateObject failed

#######
[root@localhost amoeba]# service amoeba restart
[root@localhost amoeba]# netstat -anpt | grep java

3.1.1 测试方法

3.2.1 测试用客户端 192.168.32.15

service firewalld stop
setenforce 0

yum install -y mysql
mysql -u amoeba -p123456 -h 192.168.100.41 -P8066 ##### 登录192.168.100.45 amoeba服务端

• 在主mysql上创建数据库 192.168.100.42

[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关闭同步 192.168.100.43

[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)

• 在主mysql上插入数据 192.168.100.42

[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）

• 登录客户端 192.168.32.15

 mysql -u amoeba -p123456 -h 192.168.32.14 -P8066
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bdqn               |
| db_test            |
| mysql              |
| performance_schema |
| sys                |
| test               |                       #####发现数据库test
+--------------------+
7 rows in set (0.01 sec)

MySQL [(none)]> use test;                    ####进入数据库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)

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

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

MySQL [(none)]> use test;
Database changed
MySQL [test]> insert into zhang1 values('4','zhang','write_test');
Query OK, 1 row affected (0.01 sec)

• 登录mastar 192.168.100.42

[root@localhost ~]# mysql -u root -p        
mysql> use test; 
Database changed
mysql> select * from zhang1;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    1 | zhang | this_is_master |
|    4 | zhang | write_test     |
+------+-------+----------------+
2 rows in set (0.00 sec)

• 登录slave1 192.168.100.43

[root@localhost ~]# mysql -u root -p 
mysql> use test; 
Database changed
mysql> select * from zhang1; 
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.00 sec)

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