MySQL là lựa chọn thực tế cho cơ sở dữ liệu quan hệ nguồn mở và bạn nên tìm hiểu cách sử dụng nhật ký cơ sở dữ liệu MySQL để cải thiện hiệu quả và bảo mật. Là một sản phẩm nguồn mở, nó miễn phí sử dụng và có một cộng đồng nhà phát triển lớn và tích cực. Điều quan trọng là phải hiểu cách chẩn đoán và theo dõi hiệu suất của một phiên bản MySQL trong thời gian dài
Đăng nhập để giải cứu
Tại sao ghi nhật ký lại quan trọng?
Cơ sở dữ liệu có hiệu suất cao và khả dụng cao là điều cần thiết cho hiệu suất của ứng dụng. Trong khi sử dụng phiên bản MySQL trong sản xuất, bạn sẽ gặp phải các sự cố như truy vấn chậm, bế tắc và kết nối bị hủy bỏ. Ghi nhật ký là điều cần thiết để chẩn đoán những vấn đề này. Hiểu rõ về nhật ký cơ sở dữ liệu MySQL của bạn sẽ giúp bạn cải thiện hoạt động bằng cách giảm thời gian trung bình để khôi phục và thời gian trung bình giữa các lần thất bại
Nhật ký cũng là chìa khóa để phát hiện và chẩn đoán các sự cố bảo mật trong phiên bản MySQL của bạn. Trong trường hợp có sự thỏa hiệp, nhật ký sẽ theo dõi chi tiết của một cuộc tấn công và các hành động mà kẻ tấn công đã thực hiện. Thông tin này cung cấp ngữ cảnh cho dữ liệu của bạn và giúp bạn thực hiện hành động khắc phục
Nhật ký giám sát có phức tạp không?
Ghi nhật ký thường bị bỏ qua vì phân tích nhật ký được coi là một hoạt động phức tạp. Tuy nhiên, việc theo dõi nhật ký từ một phiên bản MySQL không phải là một nhiệm vụ phức tạp, miễn là bạn biết những biến nào cần xem và tìm chúng ở đâu
Nếu phiên bản MySQL của bạn đang tạo ra một lượng lớn dữ liệu nhật ký mỗi ngày, thì việc xem xét tất cả chúng theo cách thủ công có thể không khả thi. Bạn có thể tự động hóa quy trình xem xét bằng cách sử dụng phần mềm giám sát nhật ký có thể xác định chính xác các sự kiện có vấn đề. Một số phần mềm giám sát thậm chí có thể được định cấu hình để gửi thông báo qua email khi phát hiện thấy điều gì đó đáng ngờ
Trong bài đăng này, chúng ta sẽ thảo luận về năm nhật ký quan trọng và các cách cụ thể mà chúng có thể giúp bạn theo dõi phiên bản MySQL của mình
- Nhật ký truy vấn chung
- Nhật ký truy vấn chậm
- Nhật ký lỗi
- Nhật ký nhị phân
- Nhật ký chuyển tiếp
Bật đăng nhập trên MySQL
Trước khi tiếp tục, điều quan trọng cần lưu ý là việc ghi nhật ký bị tắt theo mặc định trên MySQL ngoại trừ nhật ký lỗi. Hãy xem nhanh cách bật nhật ký truy vấn chung và chậm
Để bắt đầu sử dụng các lệnh MySQL, hãy mở dấu nhắc lệnh của bạn và đăng nhập vào phiên bản MySQL của bạn bằng lệnh sau
mysql -u root -p
Đầu tiên, kiểm tra trạng thái hiện tại của các biến hệ thống bằng cách sử dụng lệnh
mysql> show variables;
Nếu các biến như general_log và slow_query_log TẮT, chúng ta cần bật chúng lên
Bạn có thể bật nhật ký truy vấn chung bằng lệnh sau. Tên mặc định của tệp nhật ký là host_name. log nhưng bạn có thể thay đổi tên và đường dẫn theo yêu cầu
mysql>SET GLOBAL general_log = ‘ON’; mysql>SET GLOBAL general_log_file = ‘path_on_your_system’;
Nhật ký truy vấn chậm có thể được bật bằng các lệnh bên dưới
mysql>SET GLOBAL slow_query_log = ‘ON’; mysql>SET GLOBAL slow_query_log_file = ‘path_on_your_system’;
Bạn cũng có thể kiểm soát đích của nhật ký bằng cách đặt giá trị của biến log_output thành FILE TABLE hoặc FILE,TABLE. FILE chọn ghi nhật ký để ghi tệp trong khi TABLE chọn ghi nhật ký vào lược đồ hệ thống MySQL
Bây giờ, hãy xem 5 nhật ký nào bạn muốn theo dõi trong phiên bản MySQL của mình
1. Nhật ký truy vấn chung
Như tên ngụ ý, nhật ký truy vấn chung là một bản ghi chung về những gì MySQL đang làm. Thông tin được ghi vào nhật ký này khi máy khách kết nối hoặc ngắt kết nối với máy chủ. Máy chủ cũng ghi lại từng câu lệnh SQL mà nó nhận được từ máy khách. Nếu bạn nghi ngờ có lỗi trong máy khách, bạn có thể biết chính xác những gì máy khách đã gửi đến phiên bản MySQL bằng cách xem nhật ký truy vấn chung
Bạn nên lưu ý rằng MySQL ghi các câu lệnh vào nhật ký truy vấn chung theo thứ tự mà nó nhận được chúng. Thứ tự có thể khác với thứ tự mà các truy vấn được thực hiện bởi vì, không giống như các định dạng nhật ký khác, truy vấn được ghi vào tệp nhật ký này trước khi MySQL cố gắng thực hiện truy vấn. Do đó, nhật ký cơ sở dữ liệu MySQL là hoàn hảo để gỡ lỗi sự cố MySQL
Vì nhật ký truy vấn chung là bản ghi của mọi truy vấn mà máy chủ nhận được nên nó có thể lớn lên khá nhanh. Nếu bạn chỉ muốn có một bản ghi các truy vấn làm thay đổi dữ liệu, thì tốt hơn là sử dụng nhật ký nhị phân [sẽ nói thêm về điều đó sau]
Tác động đến hiệu suất
Về hiệu suất, việc bật nhật ký truy vấn chung không có tác động rõ rệt đến hiệu suất trong hầu hết các trường hợp. Tuy nhiên, người ta nhận thấy rằng ghi nhật ký vào tệp nhanh hơn ghi chúng vào bảng. Nếu bạn muốn phân tích chi tiết về tác động hiệu suất của truy vấn chung, bạn có thể xem qua bài viết này để tìm hiểu sâu hơn về vấn đề này
Xem Nhật ký cơ sở dữ liệu MySQL trên bàn làm việc
Để xem nhật ký trên bàn làm việc của MySQL, hãy chuyển đến menu điều hướng 'Máy chủ', sau đó chọn 'Nhật ký máy chủ'. Hình ảnh sau đây cho thấy một ví dụ về các mục trong tệp nhật ký chung
2. Nhật ký truy vấn chậm
Khi các ứng dụng mở rộng quy mô, các truy vấn từng cực kỳ nhanh có thể trở nên khá chậm. Khi bạn đang gỡ lỗi một phiên bản MySQL cho các vấn đề về hiệu suất, nhật ký truy vấn chậm là nơi bắt đầu tốt để xem truy vấn nào chậm nhất và tần suất chúng chậm
Nhật ký truy vấn chậm là các truy vấn nhật ký cơ sở dữ liệu MySQL vượt quá ngưỡng thời gian thực hiện nhất định. Theo mặc định, tất cả các truy vấn mất hơn 10 giây đều được ghi lại.
Tùy chọn cấu hình
Bạn có thể thay đổi ngưỡng thời gian thực hiện truy vấn bằng cách đặt giá trị của biến hệ thống long_query_time. Nó sử dụng đơn vị giây, với thành phần mili giây tùy chọn
SET GLOBAL long_query_time = 5.0;
Để xác minh xem nhật ký truy vấn chậm có hoạt động bình thường hay không, bạn có thể thực hiện truy vấn sau với thời gian lớn hơn giá trị của long_query_time
SELECT SLEEP[7];
Các truy vấn không sử dụng chỉ mục thường là những ứng cử viên tốt để tối ưu hóa. Có thể bật biến hệ thống log_queries_not_using_indexes để MySQL ghi nhật ký tất cả các truy vấn không sử dụng chỉ mục để giới hạn số lượng hàng được quét. Trong trường hợp này, việc ghi nhật ký xảy ra bất kể thời gian thực hiện truy vấn
Phân tích nhật ký truy vấn chậm
Đối với các ứng dụng lớn, nhật ký truy vấn chậm có thể trở nên khó điều tra. May mắn thay, MySQL có một công cụ gọi là mysqldumpslow phân tích các tệp nhật ký truy vấn chậm và in kết quả tóm tắt với các truy vấn tương tự được nhóm lại. Thông thường, mysqldumpslow nhóm các truy vấn giống nhau ngoại trừ các giá trị cụ thể của giá trị dữ liệu số và chuỗi
Cuối cùng, bạn nên hiểu rằng không phải mọi truy vấn được ghi vào nhật ký truy vấn chậm đều cần được tối ưu hóa. Truy vấn mất nhiều thời gian để chạy nhưng chỉ được chạy mỗi tháng một lần có thể không phải là vấn đề đáng lo ngại. Mặt khác, một truy vấn có thời gian thực hiện ngắn hơn nhưng chạy hàng nghìn lần một giờ có thể là ứng cử viên sáng giá để tối ưu hóa
Hình ảnh sau đây cho thấy ví dụ về các mục nhập trong tệp nhật ký truy vấn chậm
3. Nhật ký lỗi
MySQL sử dụng nhật ký lỗi để ghi lại các thông báo chẩn đoán, cảnh báo và ghi chú xảy ra trong quá trình khởi động và tắt máy chủ cũng như trong khi máy chủ đang chạy. Nhật ký lỗi cũng ghi lại thời gian khởi động và tắt máy của MySQL
Ghi nhật ký lỗi luôn được bật. Trên Linux, nếu đích không được chỉ định, máy chủ sẽ ghi nhật ký lỗi vào bảng điều khiển và đặt biến hệ thống log_error thành stderr. Trên Windows, theo mặc định, máy chủ ghi nhật ký lỗi vào host_name. tập tin err trong thư mục dữ liệu. Bạn có thể tùy chỉnh đường dẫn và tên tệp của nhật ký lỗi bằng cách đặt giá trị của biến hệ thống log_error
Các lỗi thường được ghi lại
Một số lỗi phổ biến mà MySQL ghi vào error log như sau
- Lỗi quyền
- Lỗi cấu hình
- Lỗi hết bộ nhớ
- Lỗi khởi tạo hoặc tắt plugin và InnoDB
Lọc nhật ký lỗi
Nhật ký cơ sở dữ liệu MySQL có tùy chọn lọc nhật ký lỗi, nếu bạn muốn tập trung vào các lỗi nghiêm trọng. Bạn có thể đặt mức độ chi tiết của nhật ký lỗi bằng cách thay đổi giá trị của biến hệ thống log_error_verbosity. Các giá trị được phép là 1 [chỉ lỗi], 2 [lỗi và cảnh báo], 3 [lỗi, cảnh báo và ghi chú], với giá trị mặc định là 3
mysql 8. 0 cũng cung cấp tính năng lọc lỗi dựa trên các quy tắc do người dùng xác định bằng cách sử dụng biến hệ thống log_filter_dragnet. Bạn có thể đọc thêm về cách kích hoạt tính năng lọc này trên tài liệu chính thức của MySQL
Hình ảnh bên dưới hiển thị một đoạn nhật ký lỗi
4. Nhật ký nhị phân
Nhật ký nhị phân được MySQL sử dụng để ghi lại các sự kiện làm thay đổi dữ liệu trong các bảng hoặc thay đổi chính lược đồ bảng. Ví dụ: nhật ký nhị phân ghi lại các câu lệnh INSERT, DELETE và UPDATE nhưng không ghi lại các câu lệnh SELECT hoặc SHOW không sửa đổi dữ liệu. Nhật ký nhị phân cũng chứa thông tin về thời gian thực thi của mỗi câu lệnh
Thứ tự ghi của đăng nhập nhị phân trái ngược với thứ tự của nhật ký truy vấn chung. Các sự kiện chỉ được ghi lại sau khi giao dịch được thực hiện bởi máy chủ
MySQL ghi tệp nhật ký nhị phân ở định dạng nhị phân. Để đọc nội dung của chúng ở định dạng văn bản, bạn cần sử dụng tiện ích mysqlbinlog. Ví dụ: bạn có thể sử dụng mã bên dưới để chuyển đổi nội dung của tệp nhật ký nhị phân có tên binlog. 000001 để nhắn tin
mysql> mysqlbinlog binlog.0000001
Mục đích của nhật ký nhị phân
Mục đích chính của nhật ký nhị phân là theo dõi các thay đổi đối với trạng thái chung của máy chủ trong quá trình hoạt động. Do đó, các sự kiện nhật ký nhị phân có thể được sử dụng để tái tạo các thay đổi đã xảy ra trên máy chủ trước đó. Nhật ký nhị phân có hai ứng dụng quan trọng
- Nhân rộng. Nhật ký nhị phân được sử dụng trên máy chủ chính để ghi lại tất cả các sự kiện làm thay đổi cấu trúc hoặc nội dung cơ sở dữ liệu. Mỗi bản sao kết nối với máy chủ chính yêu cầu một bản sao của nhật ký nhị phân. Bản sao sau đó thực thi các sự kiện từ nhật ký nhị phân để tái tạo các thay đổi giống như chúng đã được thực hiện trên máy chủ chính
- Phục hồi dữ liệu. Các hoạt động khôi phục dữ liệu cũng sử dụng nhật ký nhị phân. Khi cơ sở dữ liệu được khôi phục từ tệp sao lưu, các sự kiện trong nhật ký nhị phân được ghi lại sau khi sao lưu được thực hiện lại. Điều này làm cho cơ sở dữ liệu được khôi phục cập nhật với bản gốc
Định dạng ghi nhật ký nhị phân
Nhật ký cơ sở dữ liệu MySQL cung cấp ba định dạng để ghi nhật ký nhị phân
- Ghi nhật ký dựa trên câu lệnh. Ở định dạng này, MySQL ghi lại các câu lệnh SQL tạo ra các thay đổi dữ liệu. Ghi nhật ký dựa trên câu lệnh rất hữu ích khi nhiều hàng bị ảnh hưởng bởi một sự kiện vì ghi nhật ký một vài câu lệnh sẽ hiệu quả hơn nhiều hàng
- Ghi nhật ký theo hàng. Ở định dạng này, các thay đổi đối với các hàng riêng lẻ được ghi lại thay vì các câu lệnh SQL. Điều này hữu ích cho các truy vấn yêu cầu nhiều thời gian thực hiện trên nguồn nhưng dẫn đến chỉ một vài hàng được sửa đổi
- ghi nhật ký hỗn hợp. Đây là định dạng ghi nhật ký được khuyến nghị. Nó sử dụng ghi nhật ký dựa trên câu lệnh theo mặc định nhưng chuyển sang ghi nhật ký dựa trên hàng khi được yêu cầu
Định dạng ghi nhật ký nhị phân có thể được thay đổi bằng mã bên dưới. Tuy nhiên, bạn nên lưu ý rằng không nên làm như vậy trong thời gian chạy hoặc trong khi quá trình sao chép đang diễn ra
SET GLOBAL binlog_format = 'STATEMENT'; SET GLOBAL binlog_format = 'ROW'; SET GLOBAL binlog_format = 'MIXED';
Kích hoạt ghi nhật ký nhị phân trên phiên bản MySQL của bạn sẽ làm giảm hiệu suất một chút. Tuy nhiên, những lợi thế được thảo luận ở trên thường vượt xa sự sụt giảm hiệu suất nhỏ này
5. Nhật ký chuyển tiếp
Nhật ký chuyển tiếp là một tập hợp các tệp nhật ký được đánh số được tạo bởi một bản sao trong quá trình sao chép từ máy chủ chính. Nhật ký chuyển tiếp cũng bao gồm một tệp chỉ mục chứa tên của tất cả các tệp nhật ký chuyển tiếp đã sử dụng
Trong quá trình sao chép, bản sao đọc các sự kiện từ nhật ký nhị phân của máy chủ chính và ghi nó vào nhật ký chuyển tiếp của nó. Sau đó, nó thực hiện tất cả các sự kiện để đồng bộ với máy chủ chính. Sau khi tất cả các sự kiện trong tệp đã được thực thi, chuỗi SQL sao chép sẽ tự động xóa các tệp nhật ký chuyển tiếp không còn cần thiết
Định dạng của nhật ký chuyển tiếp giống như định dạng của nhật ký nhị phân, vì vậy tiện ích mysqlbinlog có thể được sử dụng để hiển thị nội dung của nó
Tùy chọn cấu hình
Theo mặc định, tên nhật ký chuyển tiếp có dạng host_name-relay-bin. nnnnnn trong đó tên máy chủ là tên của máy chủ bản sao và #nnnnnn là số thứ tự. Tên tệp mặc định cho tệp chỉ mục nhật ký chuyển tiếp là host_name-relay-bin. mục lục. Cả tệp nhật ký chuyển tiếp và tệp chỉ mục nhật ký chuyển tiếp đều được lưu trữ trong thư mục dữ liệu
Tên tệp và đường dẫn của nhật ký chuyển tiếp và tệp chỉ mục nhật ký chuyển tiếp có thể được thay đổi bằng cách đặt các biến hệ thống relay_log và relay_log_index tương ứng. Điều này hữu ích nếu bạn dự đoán rằng tên máy chủ của bản sao có thể thay đổi theo thời gian
Quản lý tệp nhật ký
Theo thời gian, nhật ký cơ sở dữ liệu MySQL trở nên lớn và cồng kềnh. Cần phải quản lý các tệp nhật ký vì hai lý do quan trọng.
Trước tiên, bạn cần hạn chế dung lượng dữ liệu nhật ký để ngăn các nhật ký cũ chiếm quá nhiều dung lượng ổ đĩa của bạn. Thứ hai, chia tệp nhật ký của bạn thành các tệp có tổ chức nhỏ hơn giúp khắc phục sự cố và phân tích chúng đơn giản hơn nhiều.
Xoay vòng nhật ký là một cách đơn giản để đạt được điều này
Nhật ký luân chuyển
Xoay vòng nhật ký là quá trình trong đó tệp nhật ký hiện tại được đổi tên, thường bằng cách thêm "1" vào tên và tệp nhật ký mới được thiết lập để ghi lại các mục nhật ký mới. Mỗi khi một tệp nhật ký mới được bắt đầu, các số trong tên tệp của các tệp nhật ký cũ sẽ tăng thêm một. Dựa trên ngưỡng của các tệp được giữ lại, các tệp nhật ký cũ sau đó được nén, xóa hoặc lưu trữ riêng để tiết kiệm dung lượng
Tùy thuộc vào yêu cầu của bạn, bạn có thể quyết định kích thước tối đa của tệp nhật ký, tần suất quay vòng và số lượng tệp nhật ký cũ mà bạn muốn giữ lại
Trên Linux, tiện ích logrotate có thể được sử dụng để tự động nén xoay vòng, loại bỏ và gửi các tệp nhật ký. Logrotate có thể được chạy như một công việc theo lịch trình hàng ngày hoặc hàng tuần hoặc khi tệp nhật ký đạt đến một kích thước nhất định. Trên các hệ thống khác, bạn có thể chạy tập lệnh tương tự bằng bộ lập lịch biểu
Sự kết luận
Ghi nhật ký là một công cụ không thể thiếu để quản lý các phiên bản MySQL của bạn. Hiểu sâu hơn về năm nhật ký được đề cập ở trên sẽ giúp bạn ưu tiên, chẩn đoán và giám sát các vấn đề khi ứng dụng của bạn mở rộng quy mô.
Mặc dù ban đầu có vẻ khó khăn nhưng bạn sẽ cải thiện nhật ký khắc phục sự cố và gỡ lỗi theo thời gian. Bắt đầu sử dụng chúng ngay hôm nay