MySQL数据库的读写分离-------详细实验过程！速看！！

一、MySQL读写分离原理

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

二、解决方法

■通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力

■Amoeba:是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy，外号变形虫
●读取请求发送给从服务器时，采用轮询调度算法

■此实验涉及到的账号权限
●主从同步账号
●节点服务器开放调度账号
●Amoeba代理账号

三、主从读写分离实验

3.1 案例环境

3.2 案例实施

■所有服务器关闭Firewalld
■搭建MySQL主从复制环境，在上篇文章中有介绍主从复制的搭建，这里不做概述
■Amoeba服务器环境安装

3.2.1 Amoeba服务器上（20.0.0.13）

■若之前安装了Java，需要对其进行卸载

[root@localhost ~]# yum -y install  bash-completion   
'//tab补全功能'
[root@localhost ~]# yum -y install  net-tools                
'//ifconfig 命令'

'//如果之前安装过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并修改配置文件

'//安装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
[root@localhost opt]# service amoeba start
[root@localhost opt]# netstat -anpt | grep 8066
'//默认监听在8066端口'

3.2.2 主服务器（20.0.0.10）授权配置

[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

3.2.3 从服务器1（20.0.0.11）授权配置

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

3.2.4 从服务器2（20.0.0.12）授权配置

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

3.2.5 Amoeba服务器上修改配置文件

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

<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
'//83 84 85 去掉注释-同时把默认的server1改成master,把默认的servers改成 slaves'
'//改完后是以下内容'
     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 conf]# vi dbServers.xml


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

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

<!-- mysql user -->
<property name="user">test</property>                
'//26，去掉注释，user 后面改成test 这边是mysql数据库授权账户'

<property name="password">abc123</property>         
'//29，password 后面改成abc123 ，这是mysql数据库授权密码'
</factoryConfig>

<property name="schema">test</property>  
'//数据库中要有此处定义的数据库，否则客户端连接后会报错，默认我用test数据库，创建是test数据库不要弄错了'

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

        <dbServer name="slave1"  parent="abstractServer">        
              '//name后面改成 slave1'
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.11</property>    
                '//ipAddress加上从服务器1mysql的IP地址'
                </factoryConfig>
        </dbServer>
         '//在脚本中slave2是没有的，需要复制下'
        <dbServer name="slave2"  parent="abstractServer">        
              '//name后面改成 slave2'
                 <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.12</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
'//查看进程已经启动'

3.3 测试

3.3.1 测试客户端（20.0.0.14）

[root@localhost ~]# yum -y install mysql
[root@localhost ~]# mysql -u amoeba -p123456 -h 20.0.0.13 -P8066
'//输入密码123456登录'
MySQL [(none)]> 
'//登录成功'

3.3.2 在主服务器（20.0.0.10）上创建数据库

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

3.3.3 在从服务器1（20.0.0.11）关闭同步

[root@localhost ~]# mysql -u root -p
Enter password: 
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.01 sec)

3.3.4 在从服务器2（20.0.0.12）关闭同步

[root@localhost ~]# mysql -u root -p
Enter password: 
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_slave2');
Query OK, 1 row affected (0.01 sec)

3.3.5 在主服务器（20.0.0.10）上插入数据

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

3.3.6 登录客户端（20.0.0.14）

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| text               |
+--------------------+
6 rows in set (0.00 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)

3.3.7 在客户机上（20.0.0.14）继续写数据

■按照正常的规则应该是slave1 和slave2是看不见的

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

3.3.8 登录主服务器（20.0.0.10）

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

3.3.9 登录从服务器1（20.0.0.11）

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

3.3.10 登录从服务器2（20.0.0.12）

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

总结：在客户端（20.0.0.14）上写入数据，数据是往主服务器上写的，在两台从服务器上是看不到写的数据的，从而完成了数据库的读写分离配置。