Sao lưu Incremental trong MySQL với Binary log

Sao lưu Incremental trong MySQL với Binary log

Trong công tác quản trị MySQL, mysqldump là công cụ quen thuộc giúp bạn sao lưu Full backup bằng cách dump toàn bộ database thành file .sql. Vậy trong trường hợp database có dung lượng lớn và cần sao lưu với tần suất dày để đạt yêu cầu RPO do công ty đặt ra, sao lưu Full backup bằng mysqldump liệu có ổn? Rõ ràng là không khả thi. Bởi mysqldump là thao tác không tối ưu về hiệu năng, với database dung lượng lớn sẽ mất rất nhiều thời gian. Do đó, hiệu năng database sẽ bị ảnh hưởng nghiêm trọng khi phải sao lưu liên tục. Chưa kể bạn phải tốn nhiều dung lượng hơn để lưu trữ các bản sao lưu Full backup này (trong khi lượng dữ liệu thay đổi giữa các bản Full backup này không quá nhiều).

Nếu đã từng làm việc với SQL Server hay Oracle, tình huống này khiến bạn dễ liên tưởng đến phương án giải quyết bằng cách sao lưu Transaction log trong SQL Server hay Archived log trong Oracle. Vậy với MySQL, bạn có thể tiến hành sao lưu theo cách tương tự không?

Câu trả lời là HOÀN TOÀN CÓ THỂ. Binary log trong MySQL có thể giúp bạn tiến hành thao tác này. Bài viết bên dưới sẽ giúp bạn nắm rõ khái niệm cũng như cách sử dụng Binary log để sao lưu.

1. Hiểu về Binary log trong MySQL

Binary log là gì?

Binary log là các file chứa các record lưu trữ tất cả thay đổi được cập nhật vào database. Hiểu cách khác thì mỗi thao tác INSERT, UPDATE, DELETE trước khi tiến hành cập nhật dữ liệu vào database sẽ được ghi nhận vào các file Binary log. Bên cạnh thông tin về thao tác cập nhật, Binary log còn lưu trữ thời điểm diễn ra thao tác.
Binary log trong MySQL
Muốn tìm hiểu sâu hơn về Binary log, bạn tham khảo link http://dev.mysql.com/doc/refman/5.7/en/binary-log.html.

Vì sao cần sao lưu Binary log?

Mỗi thao tác đều được ghi nhận vào Binary log nên khi sao lưu các file Binary log này, bạn lưu giữ được tất cả thao tác cập nhật dữ liệu vào database. Nhờ đó, sau này khi tiến hành phục hồi, bạn có thể thực thi lại các thao tác này để khôi phục database trở về bất kỳ thời điểm nào trong quá khứ (Point-in-Time Recovery).

Thông tin: Khi sao lưu Binary log, bạn chỉ cần sao lưu các file Binary log phát sinh sau lần sao lưu Binary log trước đó. Do đó, thao tác sao lưu Binary log trong MySQL có thể hiểu là sao lưu Incremental backup.

Binary log có dung lượng nhỏ hơn rất nhiều so với toàn bộ database nên bạn có thể dễ dàng sao lưu với tần suất dày. Khi đó, một chiến lược sao lưu kết hợp giữa Full backup bằng lệnh mysqldump và Incremental backup bằng sao lưu Binary log giúp bạn có được giải pháp toàn diện bảo vệ database đáp ứng yêu cầu về RPO (và cả RTO).

2. Sao lưu Binary log

Bật tính năng Binary logging

Ở cấu hình mặc định, MySQL không bật tính năng Binary logging nên bạn không thể sao lưu Binary log. Do đó, thao tác đầu tiên bạn cần tiến hành là bật tính năng Binary logging.

Việc bật tính năng Binary logging là tương đối đơn giản. Trong MySQL, bạn mở file my.cnf hoặc my.ini (thường nằm trong thư muc bin của MySQL) rồi thêm dòng cấu hình sau ở mục [mysqld]:

log-bin=”C:/xampp/mysql/data/binlog/bin-log”

Giá trị của tham số log-bin có dạng [Folder_Path]/[Basename]. Trong ví dụ trên, Folder_Path là thư mục “C:/xampp/mysql/data/binlog”, còn Basename là “bin-log”. Basename có thể hiểu là tên mẫu để đặt cho các file log mỗi khi được tạo ra, VD: bin-log.000001, bin-log.000002. Bạn không cần thêm phần mở rộng (extention) vào Basename này. MySQL sẽ tự động chèn phần mở rộng .000001, .000002 vào tên file khi khi tạo mới.

Sau khi thêm vào dòng cấu hình trên, bạn restart lại MySQL. Khi tính năng Binary logging đã hoạt động, bạn truy cập vào thư mục lưu Binary log (trong ví dụ trên là “C:/xampp/mysql/data/binlog”) sẽ thấy 2 file mới được tạo là bin-log.000001 và bin-log.index. Trong đó, bin-log.000001 chính là file Binary log chứa các thao tác cập nhật vào database, còn bin-log.index chứa danh sách các file Binary log được tạo ra.
File Binary log được tạo ra sau khi bật tính năng Binary logging

Một số thuộc tính quan trọng của Binary logging

Bên cạnh thuộc tính log-bin, Binary logging còn có một số thuộc tính quan trọng sau:

  • binlog-do-db: Xác định database sẽ được logging. Mặc định, MySQL sẽ logging tất cả database.
  • expire_logs_days: Xác định số ngày file Binary log sẽ được xóa khỏi hệ thống, giúp vùng lưu trữ Binary log không tăng lên quá nhiều gây đầy ổ cứng.
  • max_binlog_size: Xác định kích thước tối đa của một file Binary log. Khi file đạt kích thước này, MySQL sẽ tạo file Binary log khác.

Một ví dụ của các thuộc tính này:

log-bin=”C:/xampp/mysql/data/binlog/bin-log”
binlog-do-db=”TestDB”
expire_logs_days=10
max_binlog_size=100M

Sao lưu Binary log

Sau khi tính năng Binary logging được bật, định kỳ bạn chỉ việc sao lưu các file Binary log sang vùng lưu trữ an toàn (Tape, External HDD, Cloud) để sau này phục hồi khi cần. Lúc này, việc sao lưu là hoàn toàn tương tự sao lưu các dữ liệu dạng file khác. Bạn có thể sao lưu bằng cách copy thông thường, hoặc sử dụng công cụ sao lưu sẵn có như Windows Server Backup trong Windows, hoặc sử dụng các dịch vụ sao lưu theo mô hình Cloud như zBackup.

Lưu ý: File Binary log chỉ lưu trữ các thao tác cập nhật vào database kể từ thời điểm file được tạo ra. Vì thế, trước đó bạn cần có bản sao lưu Full backup (bằng lệnh mysqldump). Nếu chỉ có các file Binary log, chắc chắn bạn không thể khôi phục database.

3. Khôi phục dữ liệu từ Binary log bằng mysqlbinlog

Như trình bày ở trên, bạn biết rằng Binary log chứa các record lưu trữ tất cả thao tác cập nhật vào database. Do đó, khi phục hồi, bạn có thể thực thi lại các thao tác này để khôi phục database trở về thời điểm trước đó.

Tuy nhiên, vì các file Binary log được lưu trữ theo định dạng nhị phân riêng của MySQL. Do đó, để có thể đọc được dữ liệu này, bạn cần sử dụng công cụ mysqlbinlog có sẵn trong MySQL. Với mysqlbinlog, bạn có thể chuyển đổi dữ liệu dạng nhị phân của Binary log thành các lệnh SQL. Với các lệnh SQL này, bạn có thể dễ dàng thực thi để khôi phục lại database.

Ví dụ

Giả sử bạn có database tên là WebsiteDB. Do một thao tác DELETE nhầm nên gây mất khá nhiều dữ liệu vừa mới cập nhật kể từ sau lần sao lưu Full backup. Để khôi phục lại database trở về thời điểm trước khi tiến hành thao tác DELETE, bạn sử dụng công cụ mysqlbinlog theo các bước sau:

  1. Sử dụng lệnh mysqlbinlog để trích xuất dữ liệu từ file Binary log thành các lệnh SQL.
    C:\xampp\mysql\bin>mysqlbinlog C:\xampp\mysql\data\binlog\bin-log.000001 > C:\commands.sql
  2. Sau khi có được tất cả các lệnh SQL đã cập nhật vào database, bạn lọc lấy các lệnh trước khi tiến hành thao tác DELETE. Sau đó, thực thi các lệnh SQL này để khôi phục database trở về thời điểm trước khi DELETE.
  3. Bạn có thể gộp chung 2 bước trên bằng một lệnh duy nhất như sau:
    C:\xampp\mysql\bin>mysqlbinlog C:\xampp\mysql\data\binlog\bin-log.000001 | mysql -u root -p

Trong trường hợp cần trích xuất các lệnh SQL ở một thời điểm xác định, bạn thêm tùy chọn thời gian như sau:

C:\xampp\mysql\bin>mysqlbinlog C:\xampp\mysql\data\binlog\bin-log.000001 –start-datetime=”2015-04-21 10:31:44″ > C:\commands.sql