一般情況下，對資料庫的讀和寫都在同一個資料庫伺服器中操作時，業務系統效能會降低。為了提升業務系統效能，最佳化使用者體驗，可以通過讀寫分離來減輕主要資料庫的負載。本文分別從應用程式層和系統層來介紹讀寫分離的實現方法。

應用程式層實現方法

應用程式層中直接使用代碼實現，在進入Service之前，使用AOP來做出判斷，是使用寫庫還是讀庫，判斷依據可以根據方法名判斷，比如說以query、find、get等開頭的就走讀庫，其他的走寫庫。

優點：
  • 多資料來源切換方便，由程式自動完成。
  • 不需要引入中介軟體。
  • 理論上支援任何資料庫。
缺點：
  • 由程式員完成，營運參與不到。
  • 不能做到動態增加資料來源。

系統層實現方法

系統層的實現方法包括以下兩種：
  • 使用Distributed Relational Database Service實現讀寫分離。
  • 使用中介軟體MySQL-proxy實現讀寫分離。

本教程介紹如何使用中介軟體MySQL-proxy實現讀寫分離。

MySQL proxy

MySQL Proxy是一個處於Client端和MySQL server端之間的簡單程式，它可以監測、分析或改變它們的通訊。它使用靈活，沒有限制，常見的用途包括：Server Load Balancer，故障、查詢分析，查詢過濾和修改等等。

MySQL-proxy原理

MySQL Proxy是一個中介層代理，簡單的說，MySQL Proxy就是一個串連池，負責將前台應用的串連請求轉寄給背景資料庫，並且通過使用lua指令碼，可以實現複雜的串連控制和過濾，從而實現讀寫分離和Server Load Balancer。對於應用來說，MySQL Proxy是完全透明的，應用則只需要串連到MySQL Proxy的監聽連接埠即可。當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為冗餘，在應用伺服器的串連池配置中配置到多個proxy的串連參數即可。

優點：
  • 來源程式不需要做任何改動就可以實現讀寫分離。
  • 動態添加資料來源不需要重啟程式。
缺點：
  • 序依賴於中介軟體，會導致切換資料庫變得困難。
  • 由中介軟體做了中轉代理，效能有所下降。

操作步驟

環境說明：

  • 主庫IP：121.40.18.26

  • 從庫IP：101.37.36.20

  • MySQL-proxy代理IP：116.62.101.76

前期準備：

  • 1、建立3台ECS，並安裝mysql。

  • 2、搭建主從，必須保證主從資料庫資料一致。

主環境

1. 修改mysql設定檔。 
vim /etc/my.cnf
[mysqld]
server-id=202                  #設定伺服器唯一的id，預設是1
log-bin=mysql-bin              # 啟用二進位日誌

從環境

[mysqld]
server-id=203

2. 重啟主從伺服器中的MySQL服務。

/etc/init.d/mysqld restart

3. 在主伺服器上建立帳戶並授權slave。

mysql -uroot -p95c7586783
grant replication slave on *.* to 'syncms'@'填寫slave-IP' identified by '123456';
flush privileges;

4. 查看主要資料庫狀態。

mysql> show master status;

5. 配置從資料庫。

change master to master_host='填寫master-IP', master_user='syncms',      master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=602;

6. 啟動slave同步進程並查看狀態。

start slave;
show slave status\G

7. 驗證主從同步。

mysql> create database testproxy;
mysql> create table testproxy.test1(ID int primary key,name char(10) not null);
mysql> insert into testproxy.test1 values(1,'one');
mysql> insert into testproxy.test1 values(2,'two');
mysql> select * from testproxy.test1;

從庫操作

從庫中尋找testproxy.test1表的資料，與主庫一致，主從同步成功

select * from testproxy.test1;

讀寫分離配置

1.安裝MySQL-Proxy。

wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mkdir /alidata
tar xvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz 
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/  /alidata/mysql-proxy-0.8.5

2.環境變數設定。

vim /etc/profile                     #加入以下內容
PATH=$PATH:/alidata/mysql-proxy-0.8.5/bin
export $PATH
source /etc/profile                  #使變數立即生效
mysql-proxy -V

3.讀寫分離設定。

cd /alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/
vim  rw-splitting.lua

MySQL Proxy會檢測用戶端串連, 當串連沒有超過min_idle_connections預設值時, 不會進行讀寫分離預設最小4個(最大8個)以上的用戶端串連才會實現讀寫分離, 現改為最小1個最大2個，便於讀寫分離的測試，生產環境中，可以根據實際情況進行調整。

調整前：

調整後：

4.將lua管理指令碼（admin.lua）複製到讀寫分離指令碼(rw-splitting.lua)所在目錄。

cp /alidata/mysql-proxy-0.8.5/lib/mysql-proxy/lua/admin.lua /alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/

授權

1.主庫中操作授權，因主從同步的原因，從庫也會執行。

mysql -uroot -p95c7586783
grant all on *.* to 'mysql-proxy'@'填寫MySQL Proxy IP' identified by '123456';
flush privileges;

2.開啟MySQL-Proxy。

mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins=proxy -b 填寫master-IP:3306 -r 填寫slave-IP:3306 --proxy-lua-script="/alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/admin.lua"

3.啟動MySQL-Proxy之後，查看連接埠和相關進程。

netstat -tpln

ps -ef | grep mysql

測試讀寫分離

1.關閉從複製

stop slave;

2.MySQL-Proxy上操作，登入mysql-proxy後台管理。

mysql  -u  admin -padmin -P 4041 -h MySQL-Proxy-IP
select * from backends;                #查看狀態

第一次串連，會串連到主庫上。

mysql -umysql-proxy -p123456 -h 116.62.101.76 -P 4040
insert into testproxy.test1 values(3,'three');             #新增一條資料，由於測試需要，關閉了從複製，因此該資料在主庫中存在，在從庫中不存在

多開幾個串連進行測試，當查詢testproxy.test1表的資料顯示是從庫的資料時，讀寫分離成功。

mysql -umysql-proxy -p123456 -h 116.62.101.76 -P 4040
select * from testproxy.test1;