一般情況下,對資料庫的讀和寫都在同一個資料庫伺服器中操作時,業務系統效能會降低。為了提升業務系統效能,最佳化使用者體驗,可以通過讀寫分離來減輕主要資料庫的負載。本文分別從應用程式層和系統層來介紹讀寫分離的實現方法。
應用程式層實現方法
應用程式層中直接使用代碼實現,在進入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、搭建主從,必須保證主從資料庫資料一致。
主環境
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;