一般情況下,對資料庫的讀和寫都在同一個資料庫伺服器中操作時,業務系統效能會降低。為了提升業務系統效能,最佳化使用者體驗,可以通過讀寫分離來減輕主要資料庫的負載。本教程主要介紹如何使用中介軟體MySQL-proxy實現讀寫分離。
前提條件
使用本教程進行操作前,請確保您已經註冊了阿里雲帳號。如還未註冊,請先完成帳號註冊。
背景資訊
應用程式層中直接使用代碼實現,在進入Service之前,使用AOP來做出判斷,是使用寫庫還是讀庫,判斷依據可以根據方法名判斷,例如以query、find、get等開頭的就走讀庫,其他的走寫庫。
優點:
多資料來源切換方便,由程式自動完成。
不需要引入中介軟體。
理論上支援任何資料庫。
缺點:
由程式員完成,營運參與不到。
不能做到動態增加資料來源。
系統層的實現方法包括以下兩種:
使用Distributed Relational Database Service實現讀寫分離。
使用中介軟體MySQL-proxy實現讀寫分離。
本教程介紹如何使用中介軟體MySQL-proxy實現讀寫分離。
MySQL Proxy是一個處於Client端和MySQL server端之間的簡單程式,它可以監測、分析或改變它們的通訊。它使用靈活,沒有限制。常見的用途包括:Server Load Balancer,故障查詢分析,查詢過濾和修改等等。
MySQL Proxy是一個中介層代理,簡單地說,MySQL Proxy就是一個串連池,負責將前台應用的串連請求轉寄給背景資料庫,並且通過使用lua指令碼,可以實現複雜的串連控制和過濾,從而實現讀寫分離和Server Load Balancer。對於應用來說,MySQL Proxy是完全透明的,應用只需要串連到MySQL Proxy的監聽連接埠即可。當然,這樣proxy機器可能成為單點失效,但完全可以使用多個proxy機器做冗餘,在應用伺服器的串連池配置中配置到多個proxy的串連參數即可。
優點:
來源程式不需要做任何改動就可以實現讀寫分離。
動態添加資料來源不需要重啟程式。
缺點:
來源程式依賴於中介軟體,會導致切換資料庫變得困難。
由中介軟體做了中轉代理,效能有所下降。
操作步驟
使用中介軟體MySQL-proxy實現讀寫分離的操作步驟如下:
步驟一:完成準備工作
環境說明如下:
主庫IP:121.40.xx.xx
從庫IP:101.37.xx.xx
MySQL-proxy代理IP:116.62.xx.xx
完成以下操作,做好準備工作:
建立3台ECS,並安裝MySQL。
搭建主從環境,必須保證主從資料庫資料一致。
修改主從環境的MySQL設定檔。
主環境:
vim /etc/my.cnf [mysqld] server-id=202 #設定伺服器唯一的id,預設是1 log-bin=mysql-bin # 啟用二進位日誌
從環境:
[mysqld] server-id=203
重啟主從伺服器中的MySQL服務。
/etc/init.d/mysqld restart
在主伺服器上建立使用者並授權slave。
mysql -uroot -p95c758**** grant replication slave on *.* to 'syncms'@'填寫slave-IP' identified by '123456'; flush privileges;
查看主要資料庫狀態。
mysql> show master status;
配置從資料庫。
change master to master_host='填寫master-IP', master_user='syncms', master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=602;
啟動slave同步進程並查看狀態。
start slave; show slave status\G
驗證主從同步。
在主要資料庫的表testproxy.test1中寫入資料。
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;
如果表testproxy.test1的內容與主要資料庫的一致,則主從同步成功。
步驟二:配置讀寫分離
完成以下操作,配置讀寫分離:
安裝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
設定環境變數。
vim /etc/profile #加入以下內容 PATH=$PATH:/alidata/mysql-proxy-0.8.5/bin export PATH source /etc/profile #使變數立即生效 mysql-proxy -V
設定讀寫分離。
cd /alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/ vim rw-splitting.lua
MySQL Proxy會檢測用戶端串連,當串連沒有超過min_idle_connections預設值時,不會進行讀寫分離。預設最小4個(最大8個)以上的用戶端串連才會實現讀寫分離。現改為最小1個(最大2個),便於讀寫分離的測試。生產環境中,可以根據實際情況進行調整。
調整前:
調整後:
將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/
步驟三:授權
完成以下操作,進行授權:
在主庫中操作授權。因主從同步的原因,從庫也會執行。
mysql -uroot -p95c758**** grant all on *.* to 'mysql-proxy'@'填寫MySQL Proxy IP' identified by '123456'; flush privileges;
開啟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"
查看連接埠和相關進程。
netstat -tpln
ps -ef | grep mysql
步驟四:驗證讀寫分離
完成以下操作,驗證讀寫分離:
關閉從複製。
stop slave;
在MySQL-Proxy上操作,登入MySQL-Proxy後台管理。
mysql -u admin -padmin -P 4041 -h MySQL-Proxy-IP select * from backends; #查看狀態
第一次串連,會串連到主庫上。
mysql -umysql-proxy -p123456 -h 116.62.xx.xx -P 4040 insert into testproxy.test1 values(3,'three'); #新增一條資料,由於測試需要,關閉了從複製,因此該資料在主庫中存在,在從庫中不存在
多開幾個串連進行測試,當查詢表testproxy.test1的資料顯示是從庫的資料時,讀寫分離成功。
mysql -umysql-proxy -p123456 -h 116.62.xx.xx -P 4040 select * from testproxy.test1;