Thông báo lỗi
Bạn đang thực hiện dở dang quá trình di chuyển cơ sở dữ liệu khổng lồ 5GB hoặc đang chạy một lệnh JOIN nặng trên 10 triệu hàng thì đột nhiên, mọi thứ dừng lại. Thay vì thấy dữ liệu, bạn lại nhận được thông báo gây ức chế này:
ERROR 2013 (HY000): Lost connection to MySQL server during query
Lỗi này khác với thông báo "MySQL server has gone away" thường gặp. Trong khi lỗi kia thường xảy ra khi bạn mới bắt đầu kết nối, Error 2013 lại xuất hiện khi truy vấn đang được thực hiện. Máy chủ đã bắt đầu công việc, nhưng đường truyền bị ngắt trước khi kết quả kịp hiển thị trên màn hình của bạn.
Tại sao kết nối bị ngắt giữa chừng
Thường có một vài điểm nghẽn gây ra sự gián đoạn này. Hầu hết liên quan đến việc máy chủ mất kiên nhẫn với mạng hoặc kích thước của các gói dữ liệu:
- Nghẽn mạng: Các giới hạn
net_read_timeouthoặcnet_write_timeouthết hạn trong khi máy chủ vẫn đang cố gắng đẩy các khối dữ liệu lớn. - Gói dữ liệu quá lớn: Kết quả truy vấn hoặc hàng bạn đang chèn vượt quá giới hạn
max_allowed_packet. - Tường lửa hoạt động quá mức: Phần cứng hoặc phần mềm mạng có thể ngắt một kết nối TCP "nhàn rỗi" nếu một truy vấn phức tạp mất tới 10 phút để tính toán trước khi gửi byte đầu tiên.
- Sự mất ổn định vật lý: Một VPN chập chờn hoặc kết nối Wi-Fi có độ trễ cao có thể khiến ngăn xếp TCP bị thiết lập lại (reset).
Các bước khắc phục
Bước 1: Tăng các biến thời gian chờ (Timeout)
Nguyên nhân phổ biến nhất là máy chủ ngừng chờ đợi máy khách quá sớm. Nếu bạn đang lấy một tập kết quả khổng lồ qua một kết nối chậm, khoảng thời gian 30 giây mặc định hiếm khi là đủ. Bạn cần mở rộng các bộ đệm này.
Tìm tệp cấu hình của bạn (thường là /etc/mysql/my.cnf trên Linux hoặc my.ini trên Windows) và tìm phần [mysqld]. Thêm hoặc cập nhật các giá trị sau:
[mysqld]
# Chờ lâu hơn để máy khách gửi dữ liệu (mặc định là 30 giây)
net_read_timeout = 120
# Chờ lâu hơn để máy khách nhận dữ liệu (mặc định là 60 giây)
net_write_timeout = 300
# Cho phép các gói dữ liệu lớn hơn (hữu ích cho BLOB hoặc chuỗi dài)
max_allowed_packet = 256M
# Giữ kết nối hoạt động cho các thao tác lâu hơn
connect_timeout = 60
Sau khi lưu các thay đổi, hãy khởi động lại dịch vụ để áp dụng:
# Hệ thống Linux
sudo systemctl restart mysql
# Docker containers
docker restart <container_name>
Bước 2: Thay đổi cài đặt mà không cần khởi động lại
Nếu bạn đang làm việc trong môi trường production và không thể khởi động lại, hãy chạy các lệnh sau trong terminal MySQL. Các lệnh này có hiệu lực ngay lập tức nhưng sẽ mất đi nếu máy chủ khởi động lại.
SET GLOBAL net_read_timeout = 120;
SET GLOBAL net_write_timeout = 300;
SET GLOBAL max_allowed_packet = 268435456; -- 256MB tính bằng byte
Bước 3: Kiểm tra các công cụ máy khách
Đôi khi máy chủ vẫn ổn, nhưng công cụ GUI của bạn lại có bộ đếm thời gian nội bộ riêng. Nếu bạn sử dụng MySQL Workbench hoặc DBeaver, công cụ đó có thể đang chủ động ngắt kết nối với máy chủ.
- MySQL Workbench: Đi tới Edit -> Preferences -> SQL Editor. Tìm mục DBMS connection read timeout interval. Nếu nó đang để là 600, hãy thử gấp đôi lên thành 1200.
- Dòng lệnh (Command Line): Nếu bạn đang nhập một bản sao lưu SQL khổng lồ, hãy truyền giới hạn gói dữ liệu trực tiếp trong lệnh:
mysql --max_allowed_packet=256M -u root -p my_database < backup.sql
### Bước 4: Giữ kết nối TCP luôn hoạt động
Với các truy vấn rất dài, tường lửa có thể cho rằng kết nối đã chết vì không có dữ liệu nào di chuyển trong vài phút. Bạn có thể buộc hệ điều hành giữ kết nối hoạt động bằng cách thêm `enable-tcp-keepalive` vào cấu hình của mình. Điều này sẽ gửi các gói tin "nhịp tim" (heartbeat) nhỏ để ngăn mạng không bị hết thời gian chờ.
## Cách xác minh các thay đổi
Đừng chỉ đoán xem các thay đổi có hiệu lực hay không. Hãy đăng nhập vào shell MySQL và xác minh các biến hiện đang hoạt động bằng lệnh sau:
SHOW VARIABLES LIKE '%timeout%'; SHOW VARIABLES LIKE 'max_allowed_packet';
Xác nhận rằng `net_write_timeout` phản ánh giá trị mới của bạn (ví dụ: 300). Bây giờ, hãy thử chạy lại truy vấn của bạn. Nếu nó hoàn thành, thì vấn đề nằm ở các khoảng thời gian chờ của bạn.
## Tối ưu hóa chủ động
Mặc dù việc tăng thời gian chờ giúp ngăn chặn lỗi, nhưng đây thường chỉ là giải pháp tạm thời cho các truy vấn chậm. Để giải quyết tận gốc vấn đề, hãy thử các chiến lược sau:
- **Chia nhỏ dữ liệu:** Thay vì xuất 1.000.000 hàng cùng một lúc, hãy sử dụng `LIMIT 0, 50000` và xử lý dữ liệu theo từng đợt nhỏ hơn.
- **Chạy EXPLAIN:** Sử dụng `EXPLAIN` trước truy vấn của bạn. Nếu bạn thấy "Full Table Scan", việc thêm một index duy nhất có thể giảm thời gian thực thi từ 10 phút xuống còn 10 mili giây.
- **Sử dụng SSH Tunnel:** Nếu bạn đang kết nối với máy chủ từ xa, hãy chạy truy vấn qua SSH tunnel. Điều này thường cung cấp kết nối ổn định hơn so với cổng MySQL từ xa trực tiếp.