Sao lưu, khôi phục SQLite: .backup, VACUUM INTO, API online

Vì sao không thể chỉ cp tệp

Cơ sở dữ liệu SQLite là một tệp duy nhất, nên rất dễ muốn sao lưu bằng cách sao chép tệp thông thường. Đôi khi cách đó được. Nhưng thường thì không.

Có hai chuyện có thể xảy ra:

• Một kết nối khác đang ghi dở khi bạn sao chép. Tệp đích chứa một transaction mới áp dụng một nửa, và bị hỏng khi mở ra.
• Cơ sở dữ liệu đang ở chế độ WAL (mặc định của phần lớn ứng dụng hiện đại). Những thay đổi gần đây nằm trong một tệp riêng là database.db-wal. Chỉ sao chép tệp chính là bạn đã âm thầm mất dữ liệu.

SQLite cung cấp các công cụ đúng nghĩa cho việc này. Chúng xử lý khóa, nội dung WAL và các tiến trình ghi đồng thời mà không gây bất ngờ. Hãy dùng chúng thay cho cp.

Lệnh .backup

Cách nhanh nhất để sao lưu cơ sở dữ liệu từ CLI là lệnh chấm .backup:

sqlite3 app.db
sqlite> .backup backup.db
sqlite> .quit

Lệnh đó ghi một bản sao đầy đủ của app.db vào backup.db. Nó vẫn chạy được kể cả khi các tiến trình khác đang đọc hoặc ghi cơ sở dữ liệu: API sao lưu lấy một chuỗi khóa nhỏ thay vì một khóa lớn, sao chép từng trang một cách tăng dần, và sao chép lại những trang bị sửa trong lúc đang sao chép.

Kết quả là một cơ sở dữ liệu SQLite dùng được hoàn toàn. Mở nó như mọi cơ sở dữ liệu khác:

sqlite3 backup.db
sqlite> .tables

Bạn cũng có thể làm tất cả trong một lệnh shell, và phần lớn cron job cuối cùng đều trông như thế này:

sqlite3 app.db ".backup '/var/backups/app-$(date +%Y%m%d).db'"

Một tệp vào, một tệp ra. Không phải dump rồi khôi phục, không phải phân tích SQL: chỉ là sao chép các trang ở tầng lưu trữ.

VACUUM INTO cho một bản sao gọn nhẹ

VACUUM INTO là một công cụ liên quan nhưng khác. Nó ghi một bản sao được dựng mới hoàn toàn của cơ sở dữ liệu vào một tệp mới:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('first'), ('second'), ('third');
DELETE FROM notes WHERE id = 2;

VACUUM INTO 'notes-backup.db';

SELECT 'wrote backup' AS status;

Kết quả vẫn là cùng một cơ sở dữ liệu về mặt logic, nhưng được ghi lại từ đầu: mọi trang được xếp chặt, không phân mảnh, không còn trang trống sót lại từ các hàng đã xóa. Nhờ vậy tệp sao lưu nhỏ nhất có thể.

Khi nào chọn cái nào:

• .backup: sao lưu thường lệ, thường xuyên. Nhanh hơn, hoạt động tốt với các tiến trình ghi đồng thời, giống hệt từng byte.
• VACUUM INTO: bản chụp định kỳ khi bạn muốn có thêm một tệp gọn gàng, kích thước tối thiểu. Chậm hơn vì phải ghi lại mọi thứ, và nó giữ khóa ghi trên nguồn trong suốt thời gian chạy.

Cả hai đều tạo ra một tệp .db hợp lệ mà bạn mở được ngay.

API sao lưu online từ code ứng dụng

Bên trong một ứng dụng, bạn không gọi ra shell để chạy sqlite3. Bạn dùng API sao lưu online mà driver cung cấp. Trong module sqlite3 của thư viện chuẩn Python, đó là Connection.backup:

import sqlite3

source = sqlite3.connect("app.db")
dest = sqlite3.connect("backup.db")

with dest:
    source.backup(dest)

source.close()
dest.close()

Phương thức backup sao chép các trang từ source sang dest trong khi các kết nối khác vẫn làm việc bình thường. Bạn cũng có thể truyền pages= để sao chép theo từng phần và progress= để nhận một callback, rất hữu ích với cơ sở dữ liệu lớn khi bạn muốn điều tiết tốc độ sao chép hoặc hiển thị tiến độ.

Phần lớn driver ở các ngôn ngữ khác cung cấp cùng API C đó (sqlite3_backup_init, _step, _finish) dưới tên tương tự. Khuôn mẫu luôn là: mở nguồn, mở đích, đi qua từng trang, kết thúc.

Sao lưu khi cơ sở dữ liệu đang được dùng

Đây là chỗ SQLite tỏa sáng một cách lặng lẽ. Cả .backup lẫn API sao lưu online đều được thiết kế cho sao lưu nóng (hot backup): cơ sở dữ liệu nguồn có thể đang mở và hoạt động trong suốt thời gian đó.

Điều thực sự diễn ra:

1. Quá trình sao lưu lấy một khóa chia sẻ và bắt đầu sao chép các trang.
2. Nếu một tiến trình ghi sửa một trang chưa được sao chép, quá trình sao lưu nhận ra và đọc lại trang đó.
3. Việc sao chép hoàn tất khi mọi trang đều nhất quán.

Bạn không cần dừng ứng dụng, ngắt các kết nối hay lên lịch tạm ngưng dịch vụ. Trên một cơ sở dữ liệu bận rộn, quá trình sao lưu có thể mất thêm vài vòng mới hội tụ, nhưng chắc chắn sẽ hội tụ. Tệp đích bạn nhận được là một bản chụp nhất quán tại một thời điểm.

Một điều cần biết: nếu bạn dùng chế độ WAL, thỉnh thoảng hãy chạy PRAGMA wal_checkpoint(TRUNCATE); để tệp WAL không phình to vô hạn. Bản thân quá trình sao lưu xử lý WAL đúng cách; đây chỉ là thói quen giữ WAL gọn gàng nói chung.

Khôi phục từ bản sao lưu

Khôi phục một cơ sở dữ liệu SQLite nhàm chán một cách khác thường, và đó chính là điều tốt. Tệp sao lưu chính là một cơ sở dữ liệu. Để dùng nó, chỉ cần mở ra:

sqlite3 backup.db
sqlite> SELECT COUNT(*) FROM notes;

Để khôi phục đè lên một cơ sở dữ liệu đang chạy, ví dụ để phục hồi sau khi mất dữ liệu, trình tự an toàn là:

1. Dừng mọi tiến trình đang mở cơ sở dữ liệu.
2. Xóa các tệp app.db, app.db-wal và app.db-shm hiện có. Các tệp WAL/SHM còn sót lại từ cơ sở dữ liệu cũ sẽ làm SQLite rối khi đi kèm với tệp chính đã khôi phục.
3. Sao chép bản sao lưu vào đúng chỗ: cp backup.db app.db.
4. Khởi động lại ứng dụng.

Các tệp -wal và -shm rất quan trọng. Nếu bỏ qua bước 2, SQLite có thể cố áp dụng một WAL cũ lên tệp chính đã khôi phục và bạn sẽ nhận về dữ liệu bị hỏng hoặc bị trộn lẫn kỳ quặc.

Bên trong CLI còn có lệnh .restore, phiên bản ngược lại của .backup:

sqlite3 app.db
sqlite> .restore backup.db
sqlite> .quit

Lệnh này ghi đè nội dung của cơ sở dữ liệu đang kết nối bằng nội dung của backup.db. Nó dùng cùng API sao lưu online nhưng theo chiều ngược lại.

.dump là một công cụ khác

Bạn sẽ thấy .dump được nhắc đến trong các hướng dẫn cũ. Nó không phải là sao lưu theo cùng nghĩa: nó tạo ra một tệp văn bản SQL gồm các câu lệnh CREATE và INSERT:

sqlite3 app.db .dump > app.sql

Để khôi phục, bạn chạy lại đoạn SQL đó:

sqlite3 new.db < app.sql

Cách này hữu ích khi chuyển đổi giữa các phiên bản SQLite, so sánh schema trong git, hoặc chuyển dữ liệu sang một hệ quản trị cơ sở dữ liệu khác. Nó chậm hơn, tốn dung lượng hơn và dễ mất mát thông tin hơn .backup (collation tùy chỉnh, cột được sinh tự động và một số pragma có thể cần xử lý thêm). Để sao lưu thật sự một cơ sở dữ liệu đang hoạt động, hãy ưu tiên .backup hoặc VACUUM INTO.

Một quy trình sao lưu hợp lý

Với phần lớn ứng dụng, sự kết hợp sau hoạt động tốt:

• Một lần chạy .backup theo lịch: mỗi giờ, mỗi ngày, tùy mức độ chấp nhận mất dữ liệu của bạn. Rẻ, nhanh, chạy nóng được.
• Một lần VACUUM INTO mỗi tuần sang một đường dẫn riêng. Phát hiện sai lệch, cho bạn một bản chụp gọn nhẹ, và kiểm tra thêm một nhánh code khác.
• Một chính sách lưu giữ: giữ N bản sao lưu hằng ngày gần nhất, M bản hằng tuần gần nhất. Cơ sở dữ liệu SQLite nén rất tốt, nên chạy gzip backup.db sau đó là đáng làm.
• Thỉnh thoảng khôi phục thử một bản và chạy vài truy vấn trên đó. Một bản sao lưu chưa được kiểm tra chỉ là hy vọng, chưa phải bản sao lưu.

# Daily, in cron:
sqlite3 /var/lib/app/app.db ".backup '/var/backups/app-$(date +%F).db'"
gzip "/var/backups/app-$(date +%F).db"

# Weekly:
sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-weekly-$(date +%F).db'"

Cả hai lệnh đều an toàn khi chạy trong lúc ứng dụng đang phục vụ request.

Tiếp theo: thiết lập PRAGMA

Sao lưu là một mối quan tâm vận hành; tinh chỉnh hành vi lúc chạy là một mối quan tâm khác. SQLite đưa các nút điều chỉnh của nó ra qua các câu lệnh PRAGMA: chế độ journal, mức đồng bộ, kích thước bộ nhớ đệm, bắt buộc khóa ngoại. Trang tiếp theo đi qua những thiết lập đáng biết.