雲端資料庫教學：以 Cloud SQL for MySQL 實現自動分流

Google 的雲端資料庫 Cloud SQL for MySQL 服務，不僅可讓我們透過設置兩個 Read Replica 打造讀寫分離的架構，搭配上 ProxySQL，還能以負載平衡實現自動分流。此次 Cloud Ace 架構師將從設置 Cloud SQL for MySQL 教學開始，介紹如何在 Google Cloud 上從頭建立自動分流的讀寫分離雲端資料庫，並透過 Sysbench 執行分流的壓力測試，一起來了解吧！

雲端資料庫：Cloud SQL for MySQL 教學

在《PostgreSQL 與 MySQL 適用的雲端資料庫 – Cloud SQL 介紹》中，我們提到了4個在架構面上優化 Cloud SQL 資料庫實務應用的方法，包含「以 Read Replica 分散讀取負擔」，和「以 Proxy Server 分配讀寫流量」。下面就要帶大家一起嘗試上述兩種應用方法，從建置 Cloud SQL Instance 與 Read Replica，到透過 ProxySQL 實際建置一個可自動分流的讀寫分離資料庫架構，話不多說馬上開始吧。

自動分流的讀寫分離雲端資料庫架構介紹

假設有一個後端應用程式擁有資料庫的高讀取需求且掛載在 Compute Engine 上，而資料庫使用 Cloud SQL for MySQL，並透過設置兩個 Read Replica 來滿足前述需求。在以下實作中，我們要自動分流這個應用程式對資料庫的讀寫請求，使讀取的流量能平均分配至兩台 Read Replica，且兩台 Read Replica 都出現異常時能以 Primary Instance 作為讀取資料庫的備援方案。

設置 Cloud SQL for MySQL

首先我們要來設置 Cloud SQL for MySQL，Google 提供了簡單的 UI 介面讓我們能快速設定資料庫的規格與相關的維護及連線機制，讓資料庫的建立變得非常直觀且高效。在本次範例中，我們會建立一台最小規格的 Primary Instance，並建置兩台 Read Replica。而在連線上則採用 Internal IP 來貼近真實的使用情境。

設置 Primary Instance

首先要設置 MySQL Primary Instance，這裡我們選擇 MySQL 8.0 版本。而基本的設定模板選擇 Development，並在 Machine Type 中選擇 Lightweight 1 vCPU, 3.75 GB 的最小規格主機。

接著要設定連線模式，Cloud SQL 的連線方式預設是開啟 Public IP ，但這裡我們要遵照實務上的情境使用 Private IP 來連線。要能以內網連線我們需先選定應用程式所在的 VPC 網路環境，並開啟 Private Service Connection。Private Service Connection 的建置需要指定一個  /24 以上的內網網段來供我們連線 Google 代管服務，各位可以在 Cloud SQL 建立頁面直接建置，或是於 VPC 管理分頁操作。

再來要設定備份與維護時段。我們可自行在 Cloud SQL 設定希望備份的時間段（四小時），讓代管服務於時段內進行備份。一般會建議用戶設定最少發生寫入動作的時段執行備份，降低錯誤發生的可能性。這裡可觀察到 Point-in-time recovery（PITR）功能預設開啟，且 Binary Logs（Binlog）預設保留7天，大家可依據需求自行調整。

設定完備份機制後接著要設定維護時段。Google 的代管服務一般都會要用戶在創建服務時先指定可維護時段，避免 Google SRE 執行的維護或升級作業影響到客戶的服務。這邊特別提醒大家，自動維護一定會影響到我們無間斷的服務，但可特別指定絕對不能維護的時段來將影響降到最低。

設定完上述的規格、連線機制與維運規則後，我們就完成 Primary Instance 的建置作業了。這時可透過 Instance 的 Overview 分頁查看主機的相關資訊如連線 IP、主機規格、現況資料庫和用戶資訊，也能透過 Console UI 直接進行一些簡易的設置，如修改用戶密碼。這裡要特別提醒，Cloud SQL 預設會為我們創建名為 root 的用戶，該用戶具有操作資料庫的最大權限。在本次實作中我們會全程使用這個用戶，但大家實際使用時，記得要創建依不同功能劃分權限的用戶喔。

設置 Read Replica

各位可能有發現上面我們並沒有提到創建 Read Replica 的步驟，因為在 Cloud SQL Read Replica 的建置流程上，我們必須先完成 Primary Instance 的建立，才能從 Primary Instance 的 Replicas 分頁點選 CREATE READ REPLICA 按鈕進行創建。Read Replica 的建立流程基本上比照 Primary Instance，這次我們要建立兩台以供後續分流使用。完成創建後，我們同樣可透過 Read Replica 主機的 Overview 分頁來獲取連線相關資訊。

設置 ProxySQL

上面我們已完成 Cloud SQL for MySQL 的建立，接著就要來建置用於自動分流讀寫請求的負載平衡，也就是 ProxySQL 這套開源工具。一般來說，我們可將 ProxySQL 建立在應用程式所在的主機環境，並在應用程式本身是無狀態的情況下建置自動擴展，以應對突發的巨量訪問。另外如同過去文章介紹的「以 Proxy Server 分配讀寫流量」內容，我們也能透過 ProxySQL 使用其內建的 Connection Pool 機制，達到一箭雙鵰的效果。

安裝 ProxySQL

要安裝 ProxySQL 需先知道主機使用的 OS 版本，並透過 ProxySQL 的 Github 下載目錄找到對應的版本並下載。此次實作我們使用 Debian11_amd64 的映像檔，並透過下列指令執行下載與安裝作業。

wget https://github.com/sysown/proxysql/releases/download/v2.4.4/proxysql_2.4.4-debian11_amd64.deb

sudo dpkg -i proxysql_2.4.4-debian11_amd64.deb

安裝 MySQL Client 並設置後台管理

ProxySQL 的後台操作與資料庫連線都是使用 MySQL Client 當作介面進行設置。在開始建置 ProxySQL 前，我們可先以下列指令確認當前的作業環境是否可利用內網連線到 Cloud SQL Instance。

telnet <CloudSQL Private IP> 3306

確認可正常連線資料庫後，我們接著透過下列指令下載 MySQL Client 工具，並用 ProxySQL 預設的帳號 admin 與密碼 admin 以 6032 port 登入，這裡使用 ProxySQLAdmin> 作為操作介面。

sudo apt-get install default-mysql-client
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

進入操作介面後，我們首先需設定用來操作 MySQL 資料庫的用戶，這裡使用預設的 root 帳號。而後我們還需設置 ProxySQL 內建的 Monitor 操作用戶，這裡一樣使用 root 帳號，但大家可依據實際情況去賦予用戶不同的權限。最後我們使用了 LOAD TO RUNTIME 與 SAVE TO DISK 兩行指令，將前面的設定寫入磁碟中套用，後續有許多步驟都會這樣操作。

INSERT INTO mysql_users(username,password) values ('root','<PASSWORD>');
SET mysql-monitor_username = 'root';
SET mysql-monitor_password = '<PASSWORD>';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

設置完用戶權限後，我們可透過下列兩行指令分別查詢 monitor 與 stats 兩個 Database 來獲取可觀看的監控類型。在這次的測試中我們會使用 stats.stats_mysql_connection_pool 這個 Table 來查詢 Instance 的連線狀況。

SHOW TABLES FROM monitor;
SHOW TABLES FROM stats;

設置 ProxySQL 負載平衡

接著我們要設置讓 ProxySQL 進行分流所需的 Instance 群組（hostgroup）。在 ProxySQL 中預設的群組為0，一般我們如果不指定群組則會自動將 Instance 加入預設群組。這次我們透過指定群組為1的方式，讓需要接收讀取請求的 Primary Instance 與 Read Replica 加入該群組，以利後續分流使用。完成後可看到下圖中 hostgroup_id 欄位對應的 Instance 設定。

我們之所以將 Primary Instance 加入0群組，是因為0作為預設群組後面會設定接收寫入請求。而將 Primary Instance 同時加入1群組是為了在後續設定中讓 Primary Instance 作為讀取流量的備援方案。

INSERT INTO mysql_servers (hostgroup_id,hostname) values ('0','<PRIMARY IP>'),('1','<READ REPLICA1 IP>'),('1','<READ REPLICA2 IP>'),('1','<PRIMARY IP>');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

設定完群組關係後，再來我們要設定基於用戶查詢行為進行的分流作業。透過執行下列指令，我們可看到群組0套用了 UPDATE 的查詢規則；群組1則套用了單純 SELECT 的查詢情境。其餘的查詢情境則會預設自動分流至群組0。

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (1,1,'^SELECT.*FOR UPDATE',0,1), (2,1,'^SELECT',1,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

接著我們針對群組1內部的分流進行權重設置，如下方指令所示，我們分別給 Read Replica1、2 設置權重50，而 Primary Instance 則設置權重1。這個設置對應的意思是 Read Replica1、2 會分別接收 50/101 的請求， Primary Instance 則只會接收 1/101 的請求。透過這樣的方式確保讀取請求主要分配給兩台 Read Replica，而 Primary Instance 會在兩台 Read Replica 停止服務時接收所有的請求，延續服務的供給。

UPDATE mysql_servers SET weight = 50 WHERE hostname = '<READ REPLICA1 IP>' AND hostgroup_id = 1;
UPDATE mysql_servers SET weight = 50 WHERE hostname = '<READ REPLICA2 IP>' AND hostgroup_id = 1;
UPDATE mysql_servers SET weight = 1 WHERE hostname = '<PRIMARY IP>' AND hostgroup_id = 1;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

資料庫讀取分流測試

上面我們完成了整個 ProxySQL 的讀寫分流設置，接著馬上來進行實測！在開始測試前，我們可先透過下列指令查看每個 Instance 當前的連線狀態。由下圖可知，如果我們這時把 Read Replica 刪除，status 欄位便會即時顯示 SHUNNED，這樣一來服務的流量便不會再導到這些 Instance。確認完當前狀態後，我們一起使用 Sysbench 這套工具模擬資料庫請求流量。

SELECT * FROM stats.stats_mysql_connection_pool;

安裝並設置 Sysbench 測試工具

首先我們可透過下列指令安裝 Sysbench，並透過 MySQL Client 以 6033 Port 進入 ProxySQLClient 介面，透過這個介面我們能直接操作 Primary Instance。接著再創建一個 Database 叫 sysbench，以利後續的測試模擬作業。

sudo apt-get install sysbench

mysql -u root -p<PASSWORD> -h 127.0.0.1 -P 6033 --prompt='ProxySQLClient> '
ProxySQLClient> CREATE DATABASE sysbench;

創建完 Database 後，我們可在 /usr/share/sysbench 目錄下找到供我們使用的測試模擬檔案。這時我們要初始化測試環境，透過下列指令以 Prepare 方法完成測試環境的建置，執行該指令後可以看到 Sysbench 在為我們創建對應的 Table 數量與測試資料。

mysql -u root -p<PASSWORD> -h 127.0.0.1 -P 6033 --sysbench /usr/share/sysbench/oltp_insert.lua --mysql-db=sysbench \
--mysql-host=127.0.0.1 --mysql-port=6033 --mysql-user=root \
--mysql-password=<PASSWORD> --db-driver=mysql --threads=10 --tables=10 \
--table-size=1000 prepare

執行壓測並觀察分流狀況

透過下列指令，Sysbench 會開始模擬寫入作業。於此同時，我們回到 ProxySQLAdmin 介面檢查 Connection Pool 的當前狀態。透過下圖我們可看到所有的連線皆分流到群組0的 Primary Instance，而此次模擬結束後，這些基於模擬請求創建的連線便會被回收到 Connection Pool 供後續使用。

sysbench /usr/share/sysbench/oltp_insert.lua --mysql-db=sysbench \
--mysql-host=127.0.0.1 --mysql-port=6033 --mysql-user=root \
--mysql-password=<PASSWORD> --db-driver=mysql --threads=10 --tables=10 \
--skip-trx-true --mysql-ignore-errors=1062 \
--table-size=1000 --time=100 --report-interval=10 run

再來我們透過另一個測試檔案 oltp_read_only.lua 執行讀取請求測試。這時我們持續刷新 Connection Pool 狀態，會發現大部分的請求被分送到群組1的兩個 Read Replica，僅少數請求被分配到 Primary Instance。這是因為前面設定分流時，Primary Instance 在群組中的權重相對低。各位如果希望能再減少分配給 Primary Instance 的請求數量，可重複上面步驟來增加 Read Replica 的權重，進而稀釋 Primary Instance 的比重。

sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-db=sysbench \
--mysql-host=127.0.0.1 --mysql-port=6033 --mysql-user=root \
--mysql-password=<PASSWORD> --db-driver=mysql --threads=10 --tables=10 \
--skip-trx-true --mysql-ignore-errors=1062 \
--table-size=1000 --time=100 --report-interval=10 run

到這裡我們已完成 Cloud SQL for MySQL 的創建並建立 Read Replica，且透過 ProxySQL 實現自動讀寫分流、Connection Pooling 和備援設置等功能。除了 ProxySQL 可實現上述功能，Google 也推薦使用 HAProxy 這類開源工具，大家可依需求選擇。另外在此次實作中，我們是手動將 Read Replica 加入群組以完成分流設置，但各位也能進一步透過 Cloud SDK 獲取 Cloud SQL Read Replica 的連線資訊，再以指令重複上述流程來實現自動化擴展。

以上就是以 Cloud SQL for MySQL 搭配 ProxySQL，實現資料庫讀寫分離並自動分流的教學介紹。大家針對文中內容如有任何問題都可留言詢問，有進一步的技術疑問或想更認識 Google Cloud，也歡迎聯絡 Cloud Ace 獲得更進一步的資訊。

▋延伸閱讀：

・PostgreSQL 與 MySQL 適用的雲端資料庫 – Cloud SQL 介紹
・使用 Cloud Run 部署一個 API Server
・【K8s 是什麼】比較 Docker 容器、K8s 和 GKE 的架構與優勢
・【Google Cloud K8s 教學】第一次用 GKE 就上手
・GKE Autopilot 教學―輕鬆管理 K8s，加快軟體開發流程