ProxySQL Hostgroup: Route Traffic MySQL Cho Đúng

Lần đầu mở admin interface của ProxySQL, bạn sẽ thấy hostgroup_id ở khắp nơi: trong mysql_servers, trong mysql_users, trong mysql_query_rules. Nhìn thì tưởng chỉ là một config lặt vặt. Không phải đâu. Hostgroup chính là concept cốt lõi của ProxySQL. Hiểu được nó rồi thì read/write splitting, failover, maintenance hay setup multi-DC đều tự nhiên mà thông.

Ở bài ProxySQL trước, mình dùng ProxySQL làm connection pooler. Bài này đào sâu thêm một tầng: hostgroup là gì, sinh ra để làm gì, người ta dùng nó thế nào, hay sai ở đâu, và nó chạy ra sao với Galera cluster hay với nhiều ProxySQL rải qua nhiều data center.

ProxySQL Là Gì?

ProxySQL là một SQL-aware proxy cho MySQL (và các database tương thích MySQL như Percona Server, MariaDB, các cluster dựa trên Galera). Nó nói chuyện bằng MySQL protocol, nên app connect vào ProxySQL y như connect vào MySQL, mặc định qua port 6033. Bạn quản lý nó qua một admin interface riêng ở port 6032, cũng là MySQL protocol. Config thì viết bằng SQL bình thường lên các bảng in-memory.

Khác với TCP load balancer kiểu HAProxy, ProxySQL parse từng query. Nhờ vậy nó đủ thông tin để:

• Pool và multiplex connection
• Route từng query tới backend phù hợp dựa trên rule
• Health-check backend và loại mấy con "bệnh" ra khỏi vòng quay
• Cache, rewrite, mirror hoặc block query
• Ghi statistics theo từng query digest

Config được chia làm ba tầng. Nhớ kỹ cái này vì cả bài đều dính tới nó:

Bạn sửa ở MEMORY, đẩy lên RUNTIME để có hiệu lực, rồi SAVE ... TO DISK để restart xong không bay mất.

Sao Phải Cần Tới Thứ Như ProxySQL?

Khi database không còn là một server duy nhất, app phải tự quyết query này gửi tới server nào. Không có proxy thì logic đó nằm luôn trong code:

// Service nào cũng tự code lại cái này, mỗi nơi một kiểu
$writer = new PDO('mysql:host=db-primary;...');
$reader = new PDO('mysql:host=db-replica-' . rand(1, 3) . ';...');
 
// db-replica-2 chết thì sao? Nó trễ 30 giây thì sao?
// Primary failover sang db-replica-1 thì sao?

Service nào cũng phải biết topology, tự handle failover, tự check replication lag, và phải redeploy mỗi lần thêm server. Công ty có chục service viết bằng ba ngôn ngữ là có chục phiên bản cùng một logic, mỗi bản lệch nhau một chút, bug một kiểu. Đúng là mớ bòng bong.

Proxy gom hết logic đó về một chỗ:

App chỉ connect vào một endpoint. Topology bên dưới đổi thế nào cũng được (failover, thêm replica, đem node đi maintenance) mà không ai phải redeploy gì cả. Và hostgroup chính là cách ProxySQL mô hình hóa cái topology đó.

Hostgroup Là Gì?

Hostgroup là một nhóm backend server có tên (à, đúng ra là có số) mà ProxySQL coi là "thay thế được cho nhau" khi route.

Định nghĩa chỉ có vậy thôi. Hostgroup chỉ là một con số integer. ProxySQL không gán ý nghĩa gì cho nó cả. "Hostgroup 10 là writer, 20 là reader" là quy ước do bạn tự đặt.

Bạn cho server vào hostgroup qua bảng mysql_servers:

-- Admin interface: mysql -u admin -p -h 127.0.0.1 -P 6032
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES
  (10, 'db-primary',   3306, 1000, 200),   -- writer hostgroup
  (20, 'db-replica-1', 3306, 1000, 200),   -- reader hostgroup
  (20, 'db-replica-2', 3306, 1000, 200),
  (20, 'db-primary',   3306,   10, 200);   -- primary gánh thêm chút read cho vui
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Vài điểm đáng chú ý:

• Primary key là (hostgroup_id, hostname, port). Một server có thể nằm ở nhiều hostgroup, mỗi chỗ có weight và max_connections riêng.
• weight quyết định load balancing trong một hostgroup. Ở trên, db-primary nhận khoảng 10 / 2010 ≈ 0.5% lượng read.
• Mỗi entry có status: ONLINE, SHUNNED (tạm né vì dính lỗi), OFFLINE_SOFT (không nhận traffic mới, transaction đang chạy cho chạy nốt), hoặc OFFLINE_HARD (rút ra ngay lập tức).

Traffic Đi Vào Hostgroup Bằng Cách Nào

App của bạn không bao giờ chỉ định hostgroup. ProxySQL tự quyết cho từng query theo thứ tự sau:

1. mysql_users.default_hostgroup: query của user đó đi đâu nếu không có gì khác match.
2. mysql_query_rules.destination_hostgroup: đè lên default khi có rule match (theo regex, digest, username, schema, client address, v.v.).
3. mysql_users.transaction_persistent: bật 1 thì mọi query trong một transaction sẽ ở lại hostgroup nơi transaction bắt đầu.

-- Mặc định là write
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('app', 'app_password', 10, 1);
 
-- SELECT thường đi reader; SELECT ... FOR UPDATE ở lại writer
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
  (100, 1, '^SELECT .* FOR UPDATE', 10, 1),
  (200, 1, '^SELECT',               20, 1);
 
LOAD MYSQL USERS TO RUNTIME;       SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

Hostgroup Sinh Ra Để Làm Gì?

Hostgroup tạo ra một lớp trung gian: routing rule trỏ vào vai trò, không trỏ vào máy cụ thể.

Query rule của bạn nói "SELECT đi hostgroup 20", chứ không nói "SELECT đi db-replica-1". Chính sự tách bạch này mở đường cho mọi thứ khác:

Hay hơn nữa, ProxySQL có thể tự chuyển server giữa các hostgroup giùm bạn. Với mấy bảng "hostgroup manager" đặc biệt (mysql_replication_hostgroups, mysql_galera_hostgroups, mysql_group_replication_hostgroups, mysql_aws_aurora_hostgroups), monitor của ProxySQL theo dõi trạng thái từng backend và tự phân lại. Replica được promote lên thì nó tự chuyển qua writer hostgroup, còn query rule của bạn giữ nguyên.

Với async replication kiểu truyền thống thì nó như sau:

-- ProxySQL check @@read_only trên mọi server trong hostgroup 10 và 20:
--   read_only=0 -> writer hostgroup (10)
--   read_only=1 -> reader hostgroup (20)
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'main cluster');
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

ProxySQL không thực hiện failover (việc đó của Orchestrator, MHA hay tool nhà làm). Nó chỉ đi theo failover. Khi read_only trên primary mới được lật, ProxySQL chuyển traffic qua trong vài giây.

Các Use Case Phổ Biến

1. Read/Write Splitting

Ca kinh điển, như ở trên: một writer hostgroup, một reader hostgroup, và query rule đẩy SELECT sang reader. Mẹo nhỏ: đừng vội route hết mọi SELECT sang reader. Ban đầu cứ cho tất cả về writer, rồi dùng stats_mysql_query_digest tìm mấy query read nặng nhất và chỉ route đúng mấy cái đó sang reader. Vậy là đỡ được kha khá vụ "vừa ghi xong đọc lại không thấy".

-- Tìm top read digest theo tổng thời gian
SELECT digest, SUBSTR(digest_text, 1, 80) AS query, count_star, sum_time
FROM stats_mysql_query_digest
WHERE digest_text LIKE 'SELECT%'
ORDER BY sum_time DESC
LIMIT 10;
 
-- Route đúng một digest sang reader
INSERT INTO mysql_query_rules (rule_id, active, digest, destination_hostgroup, apply)
VALUES (300, 1, '0x3A6B9F2C1D4E5A7B', 20, 1);

2. Cách Ly Workload Nặng

Job reporting, dashboard analytics, batch export có thể làm OLTP traffic chết đói. Cho tụi nó một replica riêng trong hostgroup riêng, rồi route theo username:

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (30, 'db-reporting', 3306);
 
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('reporting', 'reporting_password', 30);

Mấy query 40 giây của team reporting giờ không còn đụng được tới replica production nữa.

3. Sharding Theo Schema Hoặc Tenant

Nếu mỗi schema nằm trên một cluster khác nhau, route theo schemaname:

INSERT INTO mysql_query_rules (rule_id, active, schemaname, destination_hostgroup, apply) VALUES
  (400, 1, 'tenant_eu', 40, 1),
  (401, 1, 'tenant_us', 50, 1);

App chỉ connect vào một endpoint ProxySQL duy nhất, việc của nó chỉ là USE đúng schema.

4. Maintenance Không Downtime

Drain server trước khi upgrade:

UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = 'db-replica-2';
LOAD MYSQL SERVERS TO RUNTIME;
 
-- Chờ ConnUsed về 0 rồi mới làm maintenance
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree
FROM stats_mysql_connection_pool
WHERE srv_host = 'db-replica-2';

5. Canary Upgrade Và Migration

Upgrade MySQL 8.0 → 8.4? Thêm server mới vào reader hostgroup với weight thấp, theo dõi error rate và latency, rồi tăng weight từ từ. Bạn cũng có thể dùng mirror_hostgroup trong query rule để gửi bản sao traffic thật sang server mới mà không trả kết quả của nó về cho client:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, mirror_hostgroup, apply)
VALUES (500, 1, '^SELECT', 20, 60, 1);   -- hostgroup 60 = server MySQL 8.4 mới

Những Cái Bẫy Hay Gặp

1. Replication Lag Và Chuyện "Ghi Xong Đọc Lại Không Thấy"

User lưu profile (writer), trang reload (reader), và data cũ hiện ra vì replica đang trễ hai giây. Đây là lời than phổ biến nhất sau khi bật read/write splitting.

Cách xử lý:

• Set max_replication_lag cho các entry reader trong mysql_servers. Replica nào trễ quá ngưỡng sẽ bị ProxySQL shun.
• Với Galera, dùng max_transactions_behind trong mysql_galera_hostgroups (nói ở phần dưới).
• Read nào cần consistency thì giữ ở writer. Route theo digest, đừng xài ^SELECT một phát cho tất cả.
• Với async replication có GTID, ProxySQL 2.x hỗ trợ causal read (gtid_from_hostgroup trong query rule cộng với ProxySQL binlog reader), nhưng nó thêm khá nhiều thứ phải vận hành.

UPDATE mysql_servers SET max_replication_lag = 5 WHERE hostgroup_id = 20;
LOAD MYSQL SERVERS TO RUNTIME;

2. Reader Không Còn Chỗ Để Đi

Nếu mọi replica đều bị shun (trễ hoặc chết) mà writer không nằm trong reader hostgroup, read không có đích và fail. Hãy quyết định có chủ đích: hoặc thêm writer vào reader hostgroup với weight thấp làm phương án dự phòng, hoặc chấp nhận để read fail thay vì đè chết writer. Miễn đừng để tới lúc incident mới biết.

3. Thứ Tự Query Rule Và Cờ apply

Rule chạy theo thứ tự rule_id. Rule có apply=1 sẽ dừng việc xét tiếp. Rule có apply=0 chỉ set attribute rồi đi tiếp. Lỡ đặt ^SELECT trước ^SELECT .* FOR UPDATE là mấy câu locking read chạy sang replica. Ở đó nó vẫn "chạy được", nhưng chẳng lock gì trên primary cả. Luôn đặt rule cụ thể trước rule chung chung, và check stats_mysql_query_rules để xem rule nào thực sự match:

SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;

4. Quên LOAD ... TO RUNTIME (Hoặc SAVE ... TO DISK)

Sửa mysql_servers xong chẳng thấy gì thay đổi, ngồi debug mất hai mươi phút. Hoặc chạy ngon rồi ProxySQL restart cái là thay đổi bay màu. Tập thói quen LOAD + SAVE thành phản xạ. Khi debug, so mysql_servers với runtime_mysql_servers.

5. Đánh Nhau Với Hostgroup Manager

Khi mysql_replication_hostgroups hay mysql_galera_hostgroups đang active, ProxySQL là người nắm quyền xếp chỗ cho server. Bạn tự tay set một node thành OFFLINE_SOFT hay chuyển nó sang hostgroup khác, monitor có thể chuyển nó về chỗ cũ ngay lần check kế tiếp. Với Galera, hãy drain node từ phía database (SET GLOBAL pxc_maint_mode = 'MAINTENANCE' trên Percona XtraDB Cluster, hoặc SET GLOBAL wsrep_desync = ON / wsrep_reject_queries) rồi để ProxySQL tự phản ứng.

6. Monitor User Thiếu Hoặc Không Đủ Quyền

Hostgroup manager sống nhờ monitor user connect được và đọc được status variable. Sai password hay thiếu quyền là check fail, server bị shun, hoặc failover đơn giản là không xảy ra. Xem log của monitor:

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 5;
SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 5;

7. Trùng ID Hostgroup

Mọi hostgroup khai báo trong một dòng hostgroup manager (writer, reader, backup writer, offline) phải là duy nhất và không được dùng lại ở dòng manager khác. Chọn một quy ước đánh số rồi ghi lại, ví dụ 10x cho cluster A, 20x cho cluster B.

Dùng Hostgroup Với Galera Cluster

Galera (và Percona XtraDB Cluster, MariaDB Galera Cluster) là multi-master: node nào cũng nhận write. Nghe thì hấp dẫn, nhưng write vào nhiều node cùng lúc sẽ dính certification conflict. Hai node cùng sửa một row cùng lúc, một transaction thua lúc commit, và app nhận về lỗi deadlock. Đa số setup production chỉ write vào một node tại một thời điểm, các node còn lại lo read và làm hot standby.

ProxySQL 2.x hỗ trợ Galera native qua mysql_galera_hostgroups, dùng tới bốn hostgroup:

Config:

-- Cho hết node vào writer hostgroup; ProxySQL sẽ tự phân lại.
-- Weight cao nhất thắng vai writer.
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
  (10, 'galera-node1', 3306, 1000),
  (10, 'galera-node2', 3306,  100),
  (10, 'galera-node3', 3306,  100);
 
INSERT INTO mysql_galera_hostgroups
  (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup,
   active, max_writers, writer_is_also_reader, max_transactions_behind, comment)
VALUES
  (10, 12, 20, 9999, 1, 1, 2, 100, 'galera main');
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Mấy setting quan trọng nghĩa là gì:

• max_writers = 1: chỉ một node trong writer hostgroup. Các ứng viên writer còn lại vào backup writer hostgroup. Đây là cách né certification conflict.
• writer_is_also_reader:
  • 0: writer không phục vụ read
  • 1: writer và backup writer đều nằm trong reader hostgroup
  • 2: chỉ backup writer làm reader (lựa chọn phổ biến: writer lo write, mấy node kia lo read)
• max_transactions_behind: nếu receive queue của một node (wsrep_local_recv_queue) vượt ngưỡng này, ProxySQL rút nó khỏi reader hostgroup cho tới khi nó bắt kịp. Đây là phiên bản Galera của max_replication_lag.

Monitor check trạng thái Galera của từng node (wsrep_local_state phải là 4/Synced, cộng thêm read_only, wsrep_desync, wsrep_reject_queries, và trên PXC là pxc_maint_mode). Nếu node1 chết hoặc rớt khỏi Primary component, nó bị chuyển vào offline hostgroup, node có weight cao kế tiếp lên làm writer, và app chỉ thấy một cú khựng ngắn.

Xem ProxySQL đã quyết định gì:

SELECT hostgroup_id, hostname, status, weight
FROM runtime_mysql_servers
ORDER BY hostgroup_id, weight DESC;
 
SELECT hostname, primary_partition, read_only, wsrep_local_recv_queue, error
FROM monitor.mysql_server_galera_log
ORDER BY time_start_us DESC
LIMIT 6;

Ý tưởng này áp dụng cho các loại cluster khác luôn. Dùng mysql_group_replication_hostgroups cho MySQL Group Replication / InnoDB Cluster và mysql_aws_aurora_hostgroups cho Aurora. Mỗi bảng manager biết cách đọc health của cluster tương ứng rồi map nó vào cùng một bộ writer/reader/offline hostgroup.

Nhiều ProxySQL Trải Qua Nhiều Data Center

Một con ProxySQL là một single point of failure, nên production chạy nhiều con. Mà khi mấy con đó nằm rải qua nhiều data center thì có ba câu hỏi: chạy ở đâu, sync với nhau kiểu gì, và route theo vị trí ra sao?

Chạy ProxySQL Ở Đâu

Cả hai đều ổn. Sidecar hay gặp ở fleet nhỏ. Tier riêng hay gặp khi bạn có cả trăm app host.

Giữ Các Instance Đồng Bộ: ProxySQL Cluster

ProxySQL có clustering built-in. Các instance khai báo trong proxysql_servers canh checksum config của nhau và tự kéo thay đổi về. Sửa một con, mấy con kia làm theo:

-- Chạy trên mọi ProxySQL instance
UPDATE global_variables SET variable_value = 'cluster_admin'
  WHERE variable_name = 'admin-cluster_username';
UPDATE global_variables SET variable_value = 'cluster_secret'
  WHERE variable_name = 'admin-cluster_password';
-- Cluster user cũng phải có trong admin-admin_credentials, vd 'admin:admin_pw;cluster_admin:cluster_secret'
 
INSERT INTO proxysql_servers (hostname, port, weight, comment) VALUES
  ('proxysql-dc1-a', 6032, 0, 'dc1'),
  ('proxysql-dc1-b', 6032, 0, 'dc1'),
  ('proxysql-dc2-a', 6032, 0, 'dc2');
 
LOAD ADMIN VARIABLES TO RUNTIME;  SAVE ADMIN VARIABLES TO DISK;
LOAD PROXYSQL SERVERS TO RUNTIME; SAVE PROXYSQL SERVERS TO DISK;

ProxySQL Cluster sync mysql_servers, mysql_users, mysql_query_rules, các bảng hostgroup manager, và mysql_variables. Lưu ý nó eventually consistent và không có leader: thay đổi cuối cùng thắng, nên hãy sửa từ một chỗ duy nhất (một admin node hoặc config management), đừng chui vào từng con random mà sửa tay.

Route Theo Vị Trí

Tới đây mới thú vị nè. Hình dung một Galera cluster trải dài qua hai DC:

Bạn muốn read thì ở lại local, còn mọi write đều về đúng một node duy nhất, bất kể nó đến từ DC nào. Vài nguyên tắc thiết kế:

1. Mọi ProxySQL instance phải thống nhất writer là ai. Đây là cái quan trọng nhất. Mỗi instance chạy monitor riêng và tự ra quyết định riêng. Nếu ProxySQL DC1 chọn node1 làm writer còn ProxySQL DC2 chọn node3, thì bạn đang chạy multi-master qua WAN mà không hay biết, và certification conflict sẽ kéo tới. Hãy làm cho việc chọn writer mang tính tất định: weight trong mysql_servers giống hệt nhau trên mọi instance (ProxySQL Cluster giúp được khoản này), với một node có weight cao nhất rõ ràng ở DC chính.

2. Read local cần config riêng cho từng DC. Vì ProxySQL Cluster sync mysql_servers, chuyện "ưu tiên node local" không thể là weight khác nhau theo DC trong cùng một cluster đã sync. Có hai cách thực tế:

• Mỗi DC một ProxySQL Cluster riêng. Cluster của mỗi DC có mysql_servers riêng, reader local weight cao, reader remote weight thấp để dự phòng. Weight của writer thì giữ giống hệt nhau giữa các DC bằng config management (Ansible, Terraform, v.v.).
• Một cluster global với hostgroup riêng cho từng DC. Định nghĩa reader hostgroup theo DC (vd 21 cho reader DC1, 22 cho reader DC2), và cho app ở mỗi DC một ProxySQL user riêng (app_dc1, app_dc2), kèm query rule match theo username để route về reader hostgroup local.

Cách thứ nhất dễ hình dung hơn. Cách thứ hai giữ được một nguồn sự thật duy nhất. Chọn một cái và ghi document lại.

3. Chấp nhận latency write cross-DC. Commit trong Galera là synchronous trên toàn cluster, nên mỗi commit tốn ít nhất một round trip qua WAN. Dùng Galera segment (gmcast.segment, mỗi DC một segment) để giảm replication traffic giữa các DC, và giữ transaction nhỏ gọn.

4. Lên kế hoạch cho network partition. Nếu DC1 và DC2 mất liên lạc, Galera chỉ giữ phía có quorum làm Primary component. Với hai DC ngang nhau, không bên nào có đa số và cả cluster ngừng nhận write. Hãy chạy số site lẻ, thường là thêm một con garbd arbitrator nhẹ nhàng ở địa điểm thứ ba. Ở phía thiểu số, node chuyển sang non-Primary, ProxySQL bên đó đẩy chúng vào offline hostgroup, và write ở DC đó fail ngay thay vì gây split-brain. Đó là hành vi bạn muốn, nhưng app phải xử lý nó cho êm.

5. Để ý monitor load. Mỗi ProxySQL instance tự health-check mọi backend. Hai mươi sidecar × sáu DB node × check mỗi giây là con số không nhỏ đâu. Tune các giá trị mysql-monitor_*_interval, và khi fleet lớn dần thì nên chuyển từ sidecar sang tier riêng.

Tổng Kết

Hostgroup nhìn như một config lặt vặt, nhưng nó là trái tim của cách ProxySQL vận hành:

✅ Hostgroup chỉ là một nhóm server được đánh số; ý nghĩa (writer, reader, reporting) do bạn quyết
✅ Traffic vào hostgroup qua default_hostgroup, query rule và transaction persistence
✅ Routing rule trỏ vào vai trò, nên failover hay maintenance không bao giờ đụng tới rule
✅ Hostgroup manager (mysql_replication_hostgroups, mysql_galera_hostgroups, ...) tự chuyển server giùm bạn
✅ Với Galera, dùng max_writers = 1, writer_is_also_reader = 2 và max_transactions_behind
✅ Cẩn thận replication lag, thứ tự rule, và việc sửa tay đánh nhau với monitor
✅ Qua nhiều DC, mọi ProxySQL phải thống nhất writer, còn read thì ở lại local

Thiết kế hostgroup cho đúng từ đầu, phần còn lại của ProxySQL (pooling, routing, failover) chỉ còn là chuyện config, chứ không phải chữa cháy.

Liên quan: ProxySQL: Giảm Kết Nối PHP-MySQL Khi Scale
Liên quan: Galera Cluster Architecture: Synchronous Multi-Master Replication
Series: MySQL High Availability: Galera, Percona & ProxySQL Roadmap