MySQL讀寫分離原理

• 讀寫分離就是只在主服務器上寫，只在從服務器上讀
• 主資料庫處理事務性查詢，而從資料庫處理select查詢
• 資料庫復制被用來把事務性查詢導致的變更同步到集群中的從資料庫
• 

應用場景

• 在企業應用中，在大量的資料請求下，單臺資料庫將無法承擔所有的讀寫操作
• 配置多臺資料庫服務器以實作讀寫分離
• 在主從復制的基礎上實作讀寫分離

一 、安裝Amoeba

1. 所有服務器關閉Firewalld或者進行規則設定并搭建Mysql主從復制環境

搭建主從復制詳細步驟鏈接

2.Amoeba服務器環境安裝

安裝jdk（因為amoeba是Java寫的，所以我們需要安裝jdk）

[root@localhost opt]# java -version   //檢查java版本
-bash: java: command not found    //沒有安裝
[root@localhost ~]# ls   //查看jdk安裝包
amoeba-mysql-3.0.5-RC-distribution.zip   jdk-8u144-linux-x64.tar.gz
[root@localhost ~]# tar xzvf jdk-8u144-linux-x64.tar.gz -C /opt
[root@localhost ~]# cd /opt
[root@localhost opt]# cp -rv jdk1.8.0_144/ /usr/local/java  //拷貝到/usr/local/目錄下便于管理
[root@localhost ~]# 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 ~]# 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)

補充

因為Amoeba并不是支持所有的Java環境，支持1.5和1.6版本和少部分的1.8版本，因此在安裝前需要檢查Java版本，若不合適，則需要卸載重新安裝，以下是卸載辦法
卸載原有的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

3.解壓Amoeba

[root@localhost ~]# yum -y install unzip  ##zip格式壓縮包需要unzip程式解壓
[root@localhost ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/
[root@localhost ~]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/   ##提權限
[root@localhost ~]# vi /usr/local/amoeba/jvm.properties
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k" 
//將32行JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m" 進行修改

4.配置啟動腳本，并設定為系統服務

[root@localhost ~]# vi /etc/init.d/amoeba   ##撰寫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 ~]# chmod +x /etc/init.d/amoeba
[root@localhost ~]# chkconfig --add amoeba
[root@localhost ~]# chkconfig --list amoeba  ##成功加入啟動項
Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.
      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

amoeba         	0:off	1:off	2:off	3:on	4:off	5:on	6:off
[root@localhost ~]# service amoeba start  //開啟Amoeba
Ctrl+c    ##放到后臺
[root@localhost ~]# netstat -anpt | grep 8066    //檢查監聽狀況，開啟成功
tcp6    0     0 :::8066          :::*           LISTEN      28116/java    

二、配置Amoeba讀寫分離，兩個Slave讀負載均衡

1.在master、slave1和slave2中執行以下操作開放權限給Amoeba

[root@localhost ~]# mysql -uroot -p
……省略部分
mysql> create database test;   //注意，一定要創建和授權賬號同名的庫，否則后面會出錯
Query OK, 1 row affected (0.01 sec)
mysql> GRANT ALL  ON *.* TO test@'20.0.0.%' IDENTIFIED BY 'abc123';   //授權賬號test，密碼abc123
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;    //重新加載授權
Query OK, 0 rows affected (0.01 sec)

2.修改amoeba.xml組態檔

（代碼前面的數字是對應組態檔中的行數，方便查找）

[root@localhost ~]# cd /usr/local/amoeba/conf
[root@localhost conf]# vim amoeba.xml
     28                                         <property name="user">amoeba</property>
     29 
     30                                         <property name="password">abc123</property>
     83                 <property name="defaultPool">master</property>
     84                 <property name="writePool">master</property>  //去掉這里的注釋
     85                 <property name="readPool">slaves</property>   //去掉這里的注釋

3.修改代表dbServers.xml組態檔

（代碼前面的數字是對應組態檔中的行數，方便查找）

     22                         <!-- mysql schema -->
     23                         <property name="schema">mysql</property>
     24 
     25                         <!-- mysql user -->
     26                         <property name="user">test</property>
     27 
     28                         <property name="password">abc123</property>
     43         <dbServer name="master"  parent="abstractServer">
     44                 <factoryConfig>
     45                         <!-- mysql ip -->
     46                         <property name="ipAddress">20.0.0.12</property>
     47                 </factoryConfig>
     48         </dbServer>
     49 
     50         <dbServer name="slave1"  parent="abstractServer">
     51                 <factoryConfig>
     52                         <!-- mysql ip -->
     53                         <property name="ipAddress">20.0.0.18</property>
     54                 </factoryConfig>
     55         </dbServer>
     56 
     57         <dbServer name="slave2"  parent="abstractServer">
     58                 <factoryConfig>
     59                         <!-- mysql ip -->
     60                         <property name="ipAddress">20.0.0.19</property>
     61                 </factoryConfig>
     62         </dbServer>
     64         <dbServer name="slaves" virtual="true">
     65                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
     66                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
     67                         <property name="loadbalance">1</property>
     68 
     69                         <!-- Separated by commas,such as: server1,server2,server1 -->
     70                         <property name="poolNames">slave1,slave2</property>

4.啟動Amoeba

[root@localhost ~]# service amoeba restart
[root@localhost ~]#  netstat -anpt | grep java   //埠開啟成功
tcp6       0      0 :::8066                 :::*                    LISTEN      19029/java          
……省略部分

三、驗證試驗結果

1.首先關閉主從同步，去兩臺從服務器slave1和slave2，關閉同步復制，否則會影響試驗驗證

[root@localhost ~]# mysql -uroot -p
……省略部分
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

2.驗證兩臺從服務器的“只讀”效果

驗證設計：
在關閉主從同步的前提下，我們同時在三臺服務器中寫入資料，客戶機應當只讀兩臺從服務器slave的資料，而不會讀取master中的資料

2.1在master中插入資料

mysql> use test;
mysql> create table list (name char(16),id char(16));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into list values('master',1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from list;
+-------------+------+
| name   | id   |
+--------+------+
| master | 1    |
+--------+------+
1 row in set (0.00 sec)

2.2在slave1中插入資料

mysql> use test;
mysql> create table list (name char(16),id char(16));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into list values('slave1',2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from list;
+--------+------+
| name   | id   |
+--------+------+
| slave1 | 2    |
+--------+------+
1 row in set (0.00 sec)
（2.3）在slave2中插入資料
mysql> use test;
mysql> create table list (name char(16),id char(16));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into list values('slave2',3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from list;
+--------+------+
| name   | id   |
+--------+------+
| slave2 | 3    |
+--------+------+

2.4去客戶機測驗

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

MySQL [(none)]> 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 [test]> select * from list;
+--------+------+
| name   | id   |
+--------+------+
| slave1 | 2    |
+--------+------+
1 row in set (0.01 sec)

MySQL [test]> select * from list;
+--------+------+
| name   | id   |
+--------+------+
| slave2 | 3    |
+--------+------+
1 row in set (0.01 sec)

3. 驗證master的“只寫”功能

驗證設計：
在客戶端寫入資料，應該只是在master服務器中寫入資料，在沒有主從同步的前提下，從服務器應該是沒有客戶機寫入的資料的，

3.1首先在客戶機寫入資料

MySQL [test]> create table zhang1 (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.03 sec)
MySQL [test]>  insert into zhang1 values('4','zhang','write_test');
Query OK, 1 row affected (0.04 sec)

3.2去master查看表資料

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

3.3去slave1和slave2查看表資料

mysql> select * from zhang1;
ERROR 1146 (42S02): Table 'test.zhang1' doesn't exist