Những lệnh MySQL CLI cần cần dùng khi hệ thống gặp sự cố

Khi vận hành MySQL trong môi trường production, chúng ta thường quen thuộc với các hệ thống giám sát như Grafana, Prometheus (mysqld_exporter), Datadog hay các công cụ GUI như DBeaver, MySQL Workbench. Tuy nhiên, khi xảy ra sự cố hoặc database bị treo không phản hồi, công cụ nhanh nhất, nhẹ nhất và cứu cánh đầu tiên của DBA luôn là MySQL Client qua terminal.

Dưới đây là 10 nhóm câu lệnh và kỹ thuật thiết yếu nhất khi bạn phải xử lý sự cố MySQL.

1. Kiểm tra toàn bộ kết nối và luồng đang chạy (Processlist)

Khi ứng dụng báo lỗi Too many connections hoặc response time tăng đột biến, điều đầu tiên cần làm là kiểm tra xem ai đang làm gì trên database.

SHOW FULL PROCESSLIST;

Mẹo: Thêm FULL để xem trọn vẹn câu lệnh truy vấn thay vì chỉ thấy 100 ký tự đầu tiên.

Nếu muốn lọc và sắp xếp chi tiết hơn bằng SQL, hãy truy vấn bảng processlist trong information_schema hoặc performance_schema:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO 
FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' 
ORDER BY TIME DESC;

Các thông tin quan trọng cần đọc:

• Time: Thời gian (tính bằng giây) luồng đang ở trạng thái hiện tại.
• Command: Thường là Query (đang xử lý) hoặc Sleep (kết nối mở nhưng chờ việc).
• State: Cho biết nút thắt cổ chai:
  • Sending data: Đang đọc/gửi lượng dữ liệu lớn về client.
  • Locked: Đang chờ khóa bảng hoặc row lock.
  • Creating sort index / Copying to tmp table: Query thiếu index, buộc phải gom dữ liệu ra ổ đĩa/RAM tạm để sắp xếp.

2. Tìm và bắt các Long-running Queries

Các câu lệnh quét Full Table Scan trên bảng hàng chục triệu dòng có thể kéo sập CPU và làm nghẽn toàn bộ luồng xử lý khác.

Để lọc ngay những query đang chạy quá 30 giây:

SELECT 
    id, user, host, db, time, state, info 
FROM information_schema.processlist 
WHERE command = 'Query' AND time > 30 
ORDER BY time DESC;

Nếu server đã bật sys schema (mặc định từ MySQL 5.7.7+), bạn có view rất trực quan:

SQL
SELECT * FROM sys.processlist 
WHERE command != 'Sleep' 
ORDER BY exec_time DESC;

3. Kill nhanh session hoặc query gây sự cố

Khi đã xác định được ID (PID) của query chạy hàng nghìn giây làm đứng hệ thống, bạn cần giải phóng tài nguyên ngay lập tức.

Chỉ hủy câu truy vấn (Query Cancel)

(Hỗ trợ từ MySQL 8.0)

KILL QUERY 12345;

Lệnh này chỉ dừng thực thi truy vấn đó nhưng giữ lại kết nối TCP của client, hạn chế việc ứng dụng bị văng kết nối đột ngột.

Đóng hoàn toàn kết nối (Connection Kill)

KILL 12345;

-- hoặc chỉ định rõ:

KILL CONNECTION 12345;

Lệnh này sẽ rollback transaction đang dở dang và ngắt phiên làm việc của ID đó.

4. Kiểm tra Lock và Deadlock trong InnoDB

Nhiều trường hợp CPU và RAM hoàn toàn rảnh rỗi nhưng API phía backend vẫn timeout hàng loạt. Nguyên nhân kinh điển là Row Lock Contention (nhiều transaction tranh chấp nhau một tài nguyên).

Cách 1: Xem chi tiết trạng thái InnoDB engine

SHOW ENGINE INNODB STATUS\G

Lưu ý: Dùng \G để format kết quả theo chiều dọc dễ đọc. Hãy cuộn đến các mục:

• LATEST DETECTED DEADLOCK: Xem 2 transaction nào gây deadlock và câu SQL thủ phạm.
• TRANSACTIONS: Danh sách transaction đang giữ lock hoặc đang chờ lock.

Cách 2: Xem quan hệ chặn (Blocking Lock) qua sys schema

Từ MySQL 5.7+, bạn có thể xác định ngay session nào đang chặn session nào mà không cần tự parse text:

SELECT * FROM sys.innodb_lock_waits\G

Kết quả sẽ chỉ rõ:

• waiting_query: Query đang bị đứng chờ.
• blocking_query: Query đang giữ khóa mà không nhả.
• blocking_pid: PID của session cần kill ngay.

5. Tìm Transaction tồn tại quá lâu (Long-Running Transactions)

Một transaction mở ra (START TRANSACTION) nhưng quên COMMIT hoặc do code xử lý logic bên ngoài quá lâu sẽ sinh ra các vấn đề nghiêm trọng: giữ undo log, làm phình file ibdata1, và ngăn dọn dẹp MVCC (Multi-Version Concurrency Control).

Truy vấn tìm transaction "treo":

SELECT 
    trx_id, 
    trx_mysql_thread_id AS pid, 
    trx_state, 
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec, 
    trx_query,
    trx_rows_locked, 
    trx_rows_modified 
FROM information_schema.innodb_trx 
ORDER BY duration_sec DESC;

Nếu một transaction có duration_sec lên đến hàng nghìn giây và trx_query là NULL (nghĩa là nó đang nhàn rỗi nhưng chưa commit), hãy dùng KILL <trx_mysql_thread_id> để ép rollback.

6. Kiểm tra bảng và Index chiếm nhiều dung lượng đĩa nhất

Khi ổ cứng sắp đầy (Disk Usage 90%+), bạn cần định vị ngay bảng nào đang ngốn dung lượng để lên phương án dọn dẹp hoặc archive.

SELECT 
    table_schema AS database_name,
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_mb,
    ROUND(data_length / 1024 / 1024, 2) AS data_size_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_size_mb
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'sys', 'performance_schema', 'mysql')
ORDER BY (data_length + index_length) DESC 
LIMIT 15;

Truy vấn này bóc tách rõ: dung lượng đến từ dữ liệu thật hay do đánh quá nhiều index thừa.

7. Kiểm tra tổng dung lượng từng Database

Nếu server chứa nhiều database hoặc chia sẻ chung cho nhiều microservice:

SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb,
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'sys', 'performance_schema', 'mysql')
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

8. Kiểm tra trạng thái Replication (Replication Lag / Error)

Trong mô hình Master-Slave (hoặc Primary-Replica), sự cố phổ biến nhất là Replica bị dừng do lỗi dữ liệu hoặc bị trễ (lag) so với Primary, khiến ứng dụng đọc dữ liệu cũ.

Trên máy Replica:

-- Dành cho MySQL 8.0.22+

SHOW REPLICA STATUS\G

-- Dành cho phiên bản cũ hơn:

SHOW SLAVE STATUS\G

Những dòng sống còn cần soi kỹ:

• Replica_IO_Running: Yes: Luồng nhận binary log từ Primary có thông suốt không.
• Replica_SQL_Running: Yes: Luồng áp dụng log vào database có chạy không. Nếu là No, xem ngay dòng Last_SQL_Error để biết lý do gãy replication (ví dụ: trùng khóa chính).
• Seconds_Behind_Source (hoặc Seconds_Behind_Master): Số giây Replica đang tụt hậu so với Primary. Nếu con số này tăng dần, server replica đang bị quá tải I/O hoặc nghẽn đơn luồng.

9. Theo dõi tỉ lệ Hit Cache của InnoDB Buffer Pool

Nếu MySQL liên tục đọc trực tiếp từ ổ cứng thay vì bộ nhớ, hệ thống sẽ chậm đi cả trăm lần.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Để tính nhanh hiệu quả bộ nhớ đệm (Hit Rate):

Hit Rate (%)=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)×100

Nếu con số này xuống dưới 99%, có thể kích thước innodb_buffer_pool_size đang được cấp phát quá nhỏ so với working dataset của bạn.

10. Biến Terminal thành Dashboard Realtime với mysqladmin và watch

PostgreSQL có lệnh \watch, còn trong thế giới MySQL, công cụ đi kèm mysqladmin là giải pháp giám sát thời gian thực cực kỳ nhẹ và không cần cài thêm package.

Theo dõi lượng Query/giây và Kết nối mỗi giây:

Chạy trực tiếp từ shell (Bash/Zsh):

mysqladmin -u root -p extended-status -i 1 -r | grep -E "Questions|Queries|Threads_connected|Threads_running"

• -i 1: Lặp lại mỗi 1 giây.
• -r: Chỉ in mức chênh lệch (rate/giây) thay vì số tích lũy.

Hoặc dùng lệnh watch của Linux kết hợp MySQL CLI:

watch -n 1 'mysql -u root -p"password" -e "SHOW PROCESSLIST;"'

Màn hình terminal sẽ tự làm mới sau mỗi giây, cho phép bạn quan sát luồng xử lý như một dashboard mini khi đang triển khai hotfix hoặc chống chọi đợt traffic spike.

Kết luận: Nắm vững các lệnh MySQL CLI giúp DBA nhanh chóng xác định nguyên nhân và xử lý sự cố ngay cả khi các công cụ giám sát hoặc GUI không khả dụng. Đây là bộ công cụ quan trọng để kiểm tra query, lock, transaction, replication và tài nguyên database trực tiếp trên terminal.