全部產品
Search
文件中心

Elastic Compute Service:通過讀寫分離提升資料吞吐效能

更新時間:Feb 28, 2024

一般情況下,對資料庫的讀和寫都在同一個資料庫伺服器中操作時,業務系統效能會降低。為了提升業務系統效能,最佳化使用者體驗,可以通過讀寫分離來減輕主要資料庫的負載。本教程主要介紹如何使用中介軟體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實現讀寫分離的操作步驟如下:

  1. 步驟一:完成準備工作

  2. 步驟二:配置讀寫分離

  3. 步驟三:授權

  4. 步驟四:驗證讀寫分離

步驟一:完成準備工作

環境說明如下:

  • 主庫IP:121.40.xx.xx

  • 從庫IP:101.37.xx.xx

  • MySQL-proxy代理IP:116.62.xx.xx

完成以下操作,做好準備工作:

  1. 建立3台ECS,並安裝MySQL。

  2. 搭建主從環境,必須保證主從資料庫資料一致。

  3. 修改主從環境的MySQL設定檔。

    • 主環境:

      vim /etc/my.cnf
      [mysqld]
      server-id=202                  #設定伺服器唯一的id,預設是1
      log-bin=mysql-bin              # 啟用二進位日誌
    • 從環境:

      [mysqld]
      server-id=203
  4. 重啟主從伺服器中的MySQL服務。

    /etc/init.d/mysqld restart
  5. 在主伺服器上建立使用者並授權slave。

    mysql -uroot -p95c758****
    grant replication slave on *.* to 'syncms'@'填寫slave-IP' identified by '123456';
    flush privileges;
  6. 查看主要資料庫狀態。

    mysql> show master status;

  7. 配置從資料庫。

    change master to master_host='填寫master-IP', master_user='syncms',      master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=602;
  8. 啟動slave同步進程並查看狀態。

    start slave;
    show slave status\G

  9. 驗證主從同步。

    1. 在主要資料庫的表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;

    2. 在從資料庫中運行以下命令,尋找表testproxy.test1的資料。

      select * from testproxy.test1;

      如果表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 -p95c758****
    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. 查看連接埠和相關進程。

    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.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;