SQL 8 — Quản trị Cơ sở dữ liệu (DBA)

Khi ứng dụng của bạn còn nhỏ, cơ sở dữ liệu (CSDL) thường "tự chạy". Nhưng khi dữ liệu lớn dần, người dùng đông lên và sự cố bắt đầu xuất hiện, ai đó phải đảm bảo CSDL luôn an toàn, sẵn sàng và nhanh. Người đó là DBA — Database Administrator (quản trị viên cơ sở dữ liệu).

Bài này đi từ con số 0: bạn sẽ hiểu DBA làm gì, rồi đi sâu vào năm trụ cột công việc — phân quyền, sao lưu/phục hồi, giám sát, bảo trì, sẵn sàng cao — và đóng lại bằng bảo mật cùng một checklist vận hành. Trọng tâm là PostgreSQL và MySQL, hai hệ phổ biến nhất.

Lưu ý đọc bài: Hầu hết lệnh ở đây là lệnh quản trị/DDL (tạo người dùng, cấp quyền, sao lưu, bảo trì). Chúng được đánh dấu (minh hoạ, không chạy trong sandbox). Một vài truy vấn chỉ-đọc đơn giản được đánh dấu ▶ Chạy được trong SQL Builder.

Vai trò của DBA

DBA là người chịu trách nhiệm cho "vòng đời" của CSDL trong môi trường thật (production). Có thể chia thành hai nhánh:

• DBA hệ thống (operational / infrastructure): cài đặt, cấu hình, sao lưu, phục hồi, vá lỗi, nâng cấp phiên bản, quản lý dung lượng, dựng nhân bản (replication), đảm bảo sẵn sàng cao.
• DBA ứng dụng / hiệu năng (application / performance): tối ưu truy vấn, thiết kế chỉ mục, quản lý schema migration, phối hợp với dev.

Ngày nay nhiều tổ chức dùng dịch vụ quản lý (RDS, Cloud SQL, Aurora...) nên một phần việc hạ tầng được tự động hoá. Nhưng các nguyên tắc — least privilege, test restore, giám sát chủ động — vẫn không đổi. Hiểu chúng giúp bạn dùng dịch vụ đám mây đúng cách thay vì phó mặc.

Tư duy cốt lõi của DBA gói gọn trong ba câu hỏi:

1. Nếu mất dữ liệu bây giờ, tôi phục hồi được tới thời điểm nào? (RPO)
2. Phục hồi mất bao lâu? (RTO)
3. Ai có quyền chạm vào dữ liệu, và họ chỉ được làm đúng những gì cần? (least privilege)

Quản lý người dùng & phân quyền

Mọi truy cập vào CSDL đều đi qua một danh tính (user/role) với một tập quyền (privileges). Mục tiêu là cấp đủ và chỉ đủ quyền cần thiết — nguyên tắc least privilege (đặc quyền tối thiểu).

Role và User

Trong PostgreSQL, khái niệm gốc là ROLE. Một role vừa có thể là "nhóm quyền", vừa có thể "đăng nhập" nếu có thuộc tính LOGIN (khi đó ta quen gọi là user). Cách tổ chức tốt: tạo role theo chức năng rồi gán cho từng người.

-- (minh hoạ, không chạy trong sandbox)

-- 1) Tạo role nhóm theo chức năng (không đăng nhập trực tiếp)
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;

-- 2) Cấp quyền cho từng role nhóm
GRANT CONNECT ON DATABASE shopdb TO app_readonly, app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readonly, app_readwrite;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

-- Quyền cho các bảng TẠO MỚI sau này (rất hay bị quên!)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_readonly;

-- 3) Tạo user đăng nhập và gán vào role nhóm
CREATE ROLE analyst_minh LOGIN PASSWORD 'doi_mat_khau_manh';
GRANT app_readonly TO analyst_minh;

Ở MySQL, user và quyền tách biệt rõ hơn, và user luôn gắn với host:

-- (minh hoạ, không chạy trong sandbox) -- MySQL

CREATE ROLE app_readonly;                 -- MySQL 8 hỗ trợ ROLE
GRANT SELECT ON shopdb.* TO app_readonly;

CREATE USER 'analyst_minh'@'10.0.%' IDENTIFIED BY 'doi_mat_khau_manh';
GRANT app_readonly TO 'analyst_minh'@'10.0.%';
SET DEFAULT ROLE app_readonly TO 'analyst_minh'@'10.0.%';

GRANT và REVOKE

GRANT cấp quyền, REVOKE thu hồi. Cú pháp quyền tổng quát:

-- (minh hoạ, không chạy trong sandbox)
GRANT SELECT, INSERT ON orders TO app_readwrite;
REVOKE INSERT ON orders FROM app_readwrite;

-- Cấp quyền cấp tiếp cho người khác (cẩn trọng!)
GRANT SELECT ON orders TO bi_team WITH GRANT OPTION;

Nguyên tắc least privilege trong thực tế

• Tách tài khoản theo mục đích: tài khoản app chạy production chỉ có SELECT/INSERT/UPDATE/DELETE, không có DROP, ALTER, hay quyền tạo user.
• Không dùng superuser cho ứng dụng: postgres (Postgres) hay root (MySQL) chỉ dùng cho việc quản trị thủ công, không nhúng vào connection string của app.
• Tài khoản chỉ-đọc cho báo cáo/BI: tránh việc một câu lệnh sai làm hỏng dữ liệu.
• Xoay vòng mật khẩu / dùng IAM: trên đám mây, ưu tiên xác thực bằng IAM token thay vì mật khẩu tĩnh.
• Rà soát định kỳ: liệt kê ai có quyền gì, thu hồi quyền của người đã rời dự án.

Bạn có thể kiểm tra quyền hiện tại (ví dụ trên Postgres) bằng các view hệ thống như information_schema.role_table_grants.

Sao lưu & Phục hồi (Backup & Recovery)

Đây là phần quan trọng nhất của DBA. Một câu nói kinh điển: "Backup không quan trọng — chỉ Restore mới quan trọng." Bản sao lưu chưa từng được kiểm tra phục hồi thì coi như không tồn tại.

Hai khái niệm phải thuộc lòng:

• RPO (Recovery Point Objective): chấp nhận mất tối đa bao nhiêu dữ liệu (tính theo thời gian). RPO = 5 phút nghĩa là khi sự cố, cùng lắm mất 5 phút dữ liệu cuối.
• RTO (Recovery Time Objective): chấp nhận hệ thống ngừng tối đa bao lâu để phục hồi.

Logical backup vs Physical backup

Logical backup xuất dữ liệu thành lệnh SQL hoặc dạng có cấu trúc (CREATE TABLE + INSERT). Physical backup sao chép trực tiếp các file dữ liệu trên đĩa của CSDL.

-- (minh hoạ, không chạy trong sandbox)

-- Logical dump (PostgreSQL) - chạy ở shell, không phải trong psql
-- pg_dump -Fc -d shopdb -f shopdb.dump
-- pg_restore -d shopdb_restore shopdb.dump
-- pg_dumpall  (toàn cụm: gồm cả role, tablespace)

-- Logical dump (MySQL)
-- mysqldump --single-transaction --routines shopdb > shopdb.sql
-- mysql shopdb_restore < shopdb.sql

Quy tắc chung: DB nhỏ dùng logical là tiện; DB lớn / yêu cầu PITR dùng physical là bắt buộc.

Full vs Incremental

• Full backup: sao lưu toàn bộ tại một thời điểm. Đơn giản nhưng tốn dung lượng và thời gian nếu chạy thường xuyên.
• Incremental backup: chỉ sao lưu phần thay đổi kể từ lần backup trước. Tiết kiệm, nhưng phục hồi phải áp dụng tuần tự nhiều bản → quy trình restore phức tạp hơn.

Chiến lược điển hình: full hằng tuần + incremental hằng ngày + lưu liên tục nhật ký giao dịch để hỗ trợ PITR.

PITR — Point In Time Recovery

PITR cho phép phục hồi CSDL về một thời điểm chính xác trong quá khứ — ví dụ "ngay trước khi ai đó lỡ tay DELETE lúc 14:03". Cơ chế: lấy một base backup (physical), rồi phát lại nhật ký giao dịch tới đúng mốc thời gian:

• PostgreSQL dùng WAL (Write-Ahead Log), bật archive_mode để gom WAL liên tục; khi phục hồi đặt recovery_target_time.
• MySQL dùng binary log (binlog); phục hồi base backup rồi mysqlbinlog --stop-datetime=... áp dụng tới mốc cần.

Base backup (00:00) ──> phát lại WAL/binlog ──> dừng tại 14:02:59 (ngay trước sự cố)

Vì sao phải test restore

Nguyên nhân backup "trông thì có nhưng phục hồi thất bại" rất nhiều: file hỏng âm thầm, thiếu WAL, sai phiên bản, thiếu role/quyền, script restore lỗi, dung lượng đích không đủ. Chỉ có một cách biết chắc: định kỳ thực hiện restore thử vào môi trường tách biệt và đo RTO thực tế. Hãy biến "diễn tập phục hồi" thành lịch định kỳ (ví dụ hằng quý) chứ không phải lần đầu làm là lúc đang có sự cố thật.

Giám sát (Monitoring)

Giám sát giúp bạn phát hiện vấn đề trước khi người dùng phàn nàn. Những thứ cần theo dõi:

Kết nối và truy vấn đang chạy

-- (minh hoạ, không chạy trong sandbox) (PostgreSQL): xem hoạt động hiện tại
SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE state <> 'idle';

-- (minh hoạ, không chạy trong sandbox) -- MySQL
SHOW PROCESSLIST;
SELECT * FROM performance_schema.threads WHERE PROCESSLIST_STATE IS NOT NULL;

Cần cảnh báo khi số kết nối tiến sát max_connections — thường do app rò rỉ kết nối hoặc thiếu connection pool.

Truy vấn chậm (slow query)

• PostgreSQL: bật extension pg_stat_statements để tổng hợp truy vấn theo tổng thời gian, số lần gọi, thời gian trung bình. Đây là công cụ vàng để tìm truy vấn "ăn" nhiều tài nguyên nhất.
• MySQL: bật slow query log với ngưỡng long_query_time, rồi phân tích bằng pg_stat-tương đương hoặc công cụ như pt-query-digest.

-- (minh hoạ, không chạy trong sandbox) (PostgreSQL): top truy vấn tốn thời gian
-- (cần extension pg_stat_statements được cài)
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Khoá và deadlock

Khi nhiều giao dịch tranh chấp cùng dòng/bảng, chúng có thể chờ khoá lẫn nhau, thậm chí deadlock (kẹt vòng tròn). Postgres tự phát hiện deadlock và hủy một giao dịch nạn nhân. Theo dõi qua:

-- ▶ Chạy được trong SQL Builder (PostgreSQL): xem các khoá đang giữ
SELECT locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

Dung lượng và sức khoẻ

• Kích thước DB / bảng / chỉ mục: cảnh báo khi đĩa sắp đầy.
• Tỉ lệ cache hit, IO, CPU: từ pg_stat_database, hệ điều hành, hoặc dashboard đám mây.
• Replication lag: độ trễ của replica so với primary (xem phần sau) — chỉ số sống còn cho hệ có read replica.

-- ▶ Chạy được trong SQL Builder (PostgreSQL): kích thước các bảng lớn nhất
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 5;

Thực hành tốt: đẩy các số liệu trên vào hệ thống giám sát tập trung (Prometheus + Grafana, hoặc CloudWatch) và đặt ngưỡng cảnh báo thay vì kiểm tra thủ công.

Bảo trì (Maintenance)

CSDL "phình" và "lệch" theo thời gian; bảo trì giữ cho nó nhanh và gọn.

VACUUM và ANALYZE (PostgreSQL)

PostgreSQL dùng cơ chế MVCC: mỗi UPDATE/DELETE không xoá dòng cũ ngay mà đánh dấu là dead tuple. Theo thời gian, các dòng chết tích tụ gây bloat (phình dung lượng) và làm chậm quét bảng.

• VACUUM: dọn dead tuple, trả lại không gian dùng lại được trong bảng. Autovacuum chạy tự động nhưng đôi khi cần điều chỉnh ngưỡng cho bảng ghi nhiều.
• VACUUM FULL: viết lại toàn bộ bảng để trả đĩa về OS, nhưng khoá độc quyền bảng → chỉ làm trong cửa sổ bảo trì.
• ANALYZE: cập nhật thống kê (statistics) để bộ tối ưu chọn kế hoạch truy vấn tốt. Thống kê lỗi thời là nguyên nhân âm thầm khiến truy vấn đột nhiên chậm.

-- (minh hoạ, không chạy trong sandbox)
VACUUM (VERBOSE, ANALYZE) orders;   -- dọn rác + cập nhật thống kê
ANALYZE orders;                     -- chỉ cập nhật thống kê

MySQL (InnoDB) không có khái niệm VACUUM tương tự, nhưng có purge chạy nền dọn bản ghi cũ, và ANALYZE TABLE để cập nhật thống kê:

-- (minh hoạ, không chạy trong sandbox) -- MySQL
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;   -- tái tổ chức, giảm phân mảnh (khoá/viết lại bảng)

Chỉ mục: bloat và rebuild

Chỉ mục cũng phình theo thời gian và có thể trở nên kém hiệu quả. Xây lại chỉ mục giúp giảm kích thước và tăng tốc:

-- (minh hoạ, không chạy trong sandbox) -- PostgreSQL
REINDEX INDEX CONCURRENTLY idx_orders_created_at;  -- không khoá ghi lâu
-- MySQL: thường ALTER TABLE ... hoặc OPTIMIZE TABLE

Mẹo: ưu tiên các biến thể CONCURRENTLY / online để tránh khoá bảng trên production.

Cập nhật thống kê là việc dễ quên nhất

Sau khi nạp khối lượng lớn dữ liệu (bulk load) hay thay đổi phân bố dữ liệu mạnh, hãy chạy ANALYZE ngay. Rất nhiều ca "truy vấn chậm bí ẩn" được giải quyết chỉ bằng cập nhật thống kê, vì bộ tối ưu đang dựa trên ước lượng sai số dòng.

Sẵn sàng cao & Mở rộng

Replication primary-replica

Replication (nhân bản) duy trì nhiều bản sao dữ liệu. Mô hình phổ biến: một primary nhận ghi, một hay nhiều replica nhận bản sao thay đổi.

• Đồng bộ (synchronous): primary chỉ xác nhận commit sau khi ít nhất một replica đã ghi nhận. Không mất dữ liệu (RPO≈0) nhưng ghi chậm hơn vì phải chờ.
• Bất đồng bộ (asynchronous): primary commit ngay, replica nhận sau. Ghi nhanh, nhưng nếu primary chết đột ngột có thể mất vài giao dịch cuối (RPO > 0).

Chọn lựa là sự đánh đổi giữa độ bền dữ liệu và độ trễ ghi. Nhiều hệ dùng "đồng bộ với 1 replica gần, bất đồng bộ với replica xa".

Failover

Failover là quá trình thăng cấp một replica thành primary khi primary gặp sự cố. Có thể thủ công hoặc tự động (qua Patroni, repmgr cho Postgres; Orchestrator, MySQL Group Replication / InnoDB Cluster cho MySQL). Điểm cần lưu ý: tránh split-brain (hai node cùng tưởng mình là primary) bằng cơ chế quorum/fencing.

Read replica

Replica chỉ-đọc dùng để giảm tải đọc cho primary: báo cáo, BI, lưu lượng đọc lớn được định tuyến sang replica. Cần chấp nhận replication lag — replica có thể chậm hơn primary vài mili-giây tới vài giây, nên dữ liệu đọc từ replica có thể "hơi cũ".

Sharding & Partitioning (mức khái niệm)

Khi một máy không kham nổi, ta chia nhỏ dữ liệu:

• Partitioning (phân vùng): chia một bảng lớn thành nhiều phần (theo khoảng thời gian, theo danh sách giá trị...) trong cùng một CSDL. Giúp truy vấn quét ít dữ liệu hơn và bảo trì theo từng vùng (xoá cả partition cũ rất nhanh).
• Sharding: chia dữ liệu ra nhiều máy/CSDL khác nhau theo một khoá phân mảnh (shard key). Mở rộng ngang gần như vô hạn nhưng phức tạp: truy vấn liên-shard, giao dịch phân tán, cân bằng lại dữ liệu đều khó.

Thứ tự nên cân nhắc khi DB lớn: tối ưu truy vấn/chỉ mục → read replica → partitioning → cuối cùng mới sharding.

Bảo mật

Bảo mật CSDL gồm nhiều lớp, ngoài phân quyền đã nói ở trên:

• Mã hoá in-transit (TLS): bắt buộc kết nối qua TLS để tránh nghe lén trên đường truyền. Postgres dùng sslmode=require/verify-full; MySQL dùng REQUIRE SSL cho user.
• Mã hoá at-rest: mã hoá dữ liệu trên đĩa (TDE — Transparent Data Encryption, hoặc mã hoá ở tầng đĩa/volume). Bảo vệ khi đĩa/backup bị đánh cắp.
• Audit log: ghi nhận ai đã làm gì (đăng nhập, truy vấn nhạy cảm, thay đổi quyền). Postgres dùng extension pgAudit; MySQL có audit plugin. Audit log nên lưu bất biến và tách khỏi DB chính.
• Quản lý bí mật: mật khẩu/khoá nằm trong vault (Secrets Manager, Vault) chứ không hard-code trong mã nguồn.
• Tối thiểu bề mặt tấn công: chỉ mở cổng cho mạng nội bộ, dùng firewall/security group, vô hiệu hoá tài khoản mặc định không dùng.

Checklist vận hành DBA

Hằng ngày

• Kiểm tra backup đêm qua chạy thành công và có log.
• Soát cảnh báo: dung lượng đĩa, số kết nối, CPU, replication lag.
• Soát top truy vấn chậm (pg_stat_statements / slow query log).

Hằng tuần

• Rà soát chỉ mục mới cần thiết / chỉ mục thừa.
• Kiểm tra bloat bảng & chỉ mục; lên lịch VACUUM/REINDEX nếu cần.
• Soát quyền mới cấp; thu hồi quyền không còn dùng.

Hằng tháng / Quý

• Diễn tập restore vào môi trường tách biệt; đo RTO thực tế.
• Kiểm tra failover (chuyển primary có hoạt động đúng không).
• Vá bảo mật & lên kế hoạch nâng cấp phiên bản.
• Rà soát RPO/RTO so với cam kết SLA.

Luôn luôn

• Least privilege cho mọi tài khoản.
• TLS bật cho mọi kết nối; bí mật để trong vault.
• Mọi thay đổi schema/quyền đi qua quy trình review & migration.

Tóm tắt

DBA bảo vệ ba thứ: dữ liệu không mất, hệ thống luôn sẵn sàng, truy cập được kiểm soát chặt. Năm trụ cột: phân quyền theo least privilege (role + GRANT/REVOKE); sao lưu/phục hồi với phân biệt logical vs physical, full vs incremental, và PITR — quan trọng nhất là test restore định kỳ; giám sát kết nối, truy vấn chậm, khoá/deadlock, dung lượng và replication lag; bảo trì với VACUUM/ANALYZE (Postgres), rebuild index, chống bloat, cập nhật thống kê; và sẵn sàng cao qua replication primary-replica (đồng bộ vs bất đồng bộ), failover, read replica, cùng partitioning/sharding khi cần mở rộng. Phủ lên tất cả là bảo mật (TLS, mã hoá at-rest, audit log) và một checklist vận hành giúp biến công việc thủ công thành quy trình lặp lại được. Luôn nhớ tư duy RPO/RTO: hiểu rõ bạn chấp nhận mất bao nhiêu dữ liệu và bao lâu để phục hồi.

Tự kiểm tra

1. Phân biệt RPO và RTO; vì sao một chiến lược backup chỉ "đủ tốt" khi đã được kiểm chứng bằng restore thử?
2. Khi nào nên chọn logical backup, khi nào nên chọn physical backup? PITR thường gắn với loại nào và vì sao?
3. Giải thích đánh đổi giữa replication đồng bộ và bất đồng bộ qua góc nhìn RPO và độ trễ ghi.
4. VACUUM trong PostgreSQL giải quyết vấn đề gì, và vì sao ANALYZE lại ảnh hưởng tới tốc độ truy vấn dù không thay đổi dữ liệu?
5. Nêu ba cách áp dụng nguyên tắc least privilege cho tài khoản mà ứng dụng production dùng để kết nối CSDL.
6. So sánh partitioning và sharding: chúng giải quyết bài toán mở rộng khác nhau ở điểm nào, và vì sao sharding phức tạp hơn?

Đọc tiếp

SQL 9 — Các hệ quản trị CSDL phổ biến