383      阿裏雲  技術社區[雲棲]

雲服務器 ECS 配置：利用MySQL讀寫分離，提升應用數據吞吐性能

一般情況下，對數據庫的讀和寫都在同一個數據庫服務器中操作時，業務係統性能會降低。為了提升業務係統性能，優化用戶體驗，可以通過讀寫分離來減輕主數據庫的負載。本篇文章分別從應用層和係統層來介紹讀寫分離的實現方法。

應用層實現方法：

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

優點：

1、多數據源切換方便，由程序自動完成。

2、不需要引入中間件。

3、理論上支持任何數據庫。

缺點：

1、由程序員完成，運維參與不到。

2、不能做到動態增加數據源。

係統層實現方法：

方式一：使用DRDS實現

https://help.aliyun.com/document_detail/29681.html

方式二：使用中間件MySQL-proxy實現

本教程使用MySQL-proxy實現讀寫分離。

MySQL-proxy介紹：

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

MySQL-proxy原理：

MySQL Proxy是一個中間層代理，簡單的說，MySQL Proxy就是一個連接池，負責將前台應用的連接請求轉發給後台的數據庫，並且通過使用lua腳本，可以實現複雜的連接控製和過濾，從而實現讀寫分離和負載平衡。對於應用來說，MySQL Proxy是完全透明的，應用則隻需要連接到MySQL Proxy的監聽端口即可。當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為冗餘，在應用服務器的連接池配置中配置到多個proxy的連接參數即可。

優點：

1、源程序不需要做任何改動就可以實現讀寫分離。

2、動態添加數據源不需要重啟程序。

缺點：

1、程序依賴於中間件，會導致切換數據庫變得困難。

2、由中間件做了中轉代理，性能有所下降。

操作步驟

環境說明：

主庫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;

原文鏈接

最後更新：2017-08-14 14:02:31

  上一篇：  雲服務器 ECS 配置：雲服務器ECS下的FTP服務的安裝配置與使用

  下一篇：  循環遞歸RNN，序列建模套路深（深度學習入門係列之十三）