Lỗi Replication MySQL: Cách khắc phục 'Duplicate entry' (Slave_SQL_Running: No)

Khi việc đồng bộ dừng lại: Khắc phục lỗi Duplicate Entry

Không có gì phá hỏng một buổi chiều yên tĩnh bằng một cảnh báo hệ thống. Mới phút trước replica của bạn vẫn đang hoạt động trơn tru; phút sau, nó đã dừng lại. Khi replication bị lỗi, slave sẽ bắt đầu bị trễ (lag) ngay lập tức. Một khoảng trễ chỉ 30 phút có thể khiến các dashboard báo cáo của bạn trở nên vô dụng, hoặc trong môi trường lưu lượng cao, nó có thể tạo ra một khối lượng dữ liệu chờ xử lý (backlog) lên tới 12 giờ, một cơn ác mộng để dọn dẹp.

Sau khi nhận được cảnh báo từ hệ thống giám sát, tôi đã đăng nhập vào database replica để tìm hiểu nguyên nhân tại sao SQL thread bị crash.

Xác định lỗi

Để xem chính xác điều gì đã xảy ra, hãy chạy lệnh sau trên máy chủ Slave/Replica của bạn:

SHOW SLAVE STATUS\G

Hãy tìm cụ thể các trường Slave_SQL_Running và Last_SQL_Error. Trong trường hợp của tôi, kết quả trông như thế này:

             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table my_database.users; Duplicate entry '123' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000123, end_log_pos 4567

Lỗi này rất rõ ràng: Duplicate entry '123' for key 'PRIMARY'. Quá trình replication dừng lại vì nó cố gắng ghi dữ liệu đã tồn tại sẵn ở đó.

Tại sao điều này xảy ra

Lỗi này kích hoạt vì Master đang cố gắng chèn một hàng với Primary Key (ID 123) đã tồn tại trên Slave. Điều này thường chỉ ra một trong ba vấn đề sau:

Ghi dữ liệu ngoài luồng: Ai đó đã chèn dữ liệu thủ công trực tiếp vào Slave thay vì Master.
Lỗi cục bộ: Một lần crash trước đó khiến dữ liệu được ghi xuống đĩa nhưng không được ghi nhận trong replication logs.
Logic ứng dụng: Một dịch vụ bị cấu hình sai đang kết nối tới IP của Slave để thực hiện các thao tác ghi.

Cách khắc phục lỗi

Lựa chọn cách khắc phục tùy thuộc vào việc bạn coi trọng tính nhất quán của dữ liệu hay thời gian hoạt động (uptime) hơn.

Cách 1: Bỏ qua nhanh (Không dùng GTID)

Nếu bạn chắc chắn rằng dữ liệu trên slave đã chính xác, bạn có thể yêu cầu MySQL bỏ qua transaction bị lỗi. Cảnh báo: Cách này chỉ hoạt động nếu bạn KHÔNG sử dụng GTID-based replication.

-- Dừng các slave thread
STOP SLAVE;

-- Bỏ qua chính xác một câu lệnh
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

-- Khởi động lại các slave thread
START SLAVE;

Chạy lại lệnh SHOW SLAVE STATUS\G. Nếu Slave_SQL_Running bây giờ là Yes, replica đang bắt đầu xử lý dữ liệu tồn đọng.

Cách 2: Khắc phục tính toàn vẹn dữ liệu (Xóa và đồng bộ lại)

Một cách tiếp cận sạch sẽ hơn là xóa hàng bị xung đột khỏi slave. Điều này cho phép replication thread chèn lại phiên bản "chính thức" từ master, đảm bảo cả hai máy chủ khớp nhau hoàn toàn.

-- Trên SLAVE, kiểm tra hàng bị xung đột
SELECT * FROM my_database.users WHERE id = 123;

-- Nếu dữ liệu dư thừa, hãy xóa nó
DELETE FROM my_database.users WHERE id = 123;

-- Tiếp tục replication
STOP SLAVE;
START SLAVE;

Cách 3: Xử lý với GTID Replication

Nếu thiết lập MySQL của bạn sử dụng Global Transaction Identifiers (GTID), lệnh SQL_SLAVE_SKIP_COUNTER sẽ thất bại. Thay vào đó, bạn phải inject một transaction trống để "giả lập" việc hoàn thành ID bị lỗi. Đầu tiên, hãy tìm GTID bị lỗi trong kết quả SHOW SLAVE STATUS\G (ví dụ: 5340062a-1c22-11ed-9662-0242ac120002:101).

STOP SLAVE;
-- Commit một transaction trống cho ID bị chặn
SET GTID_NEXT = '5340062a-1c22-11ed-9662-0242ac120002:101';
BEGIN; COMMIT;

-- Quay lại hoạt động bình thường
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

Xác minh phục hồi

Sau khi áp dụng bản sửa lỗi, hãy theo dõi giá trị Seconds_Behind_Master. Nó có thể tăng vọt lúc đầu khi slave xử lý hàng đợi, nhưng sau đó sẽ bắt đầu giảm dần về 0. Hãy xác nhận rằng Last_SQL_Error hiện là một chuỗi rỗng.

Bài học kinh nghiệm & Cách phòng ngừa

Khắc phục lỗi chỉ là một biện pháp tạm thời. Để ngăn chặn nó xảy ra lần nữa, hãy thắt chặt môi trường của bạn:

Đặt Slave ở chế độ Read-Only: Cập nhật file my.cnf của bạn để bao gồm read_only = 1. Điều này ngăn người dùng thông thường thực hiện các thay đổi thủ công.
Sử dụng Super Read-Only: Trên MySQL 5.7.8+, hãy sử dụng super_read_only = 1. Điều này chặn ngay cả những người dùng có quyền SUPER vô tình làm hỏng quá trình đồng bộ.
Kiểm tra ứng dụng của bạn: Đảm bảo các chuỗi kết nối ứng dụng phân biệt rõ ràng giữa các endpoint Writer (Master) và Reader (Slave).