Chỉ export schema (cấu trúc) từ một SQL dump

Ba tình huống cứ liên tục lặp lại:

• Dựng một schema cho staging hoặc CI — bạn muốn cấu trúc bảng rỗng để các bài kiểm thử chạy trên cột, index và ràng buộc thật, nhưng tuyệt đối không muốn đổ hàng gigabyte dòng production vào một môi trường dùng-một-lần.
• Chỉ dữ liệu mồi — schema đã tồn tại trong cơ sở dữ liệu đích, và bạn chỉ cần các dòng INSERT để lấp đầy nó, không có lệnh CREATE gây xung đột với những gì đã có sẵn.
• Loại bỏ nhiễu — dump phình to bởi các bảng logs, sessions, audit_trail hay cache với hàng chục triệu dòng bạn chẳng bao giờ muốn import.

Vì sao mysqldump --no-data là chưa đủ

Lời khuyên kinh điển là mysqldump --no-data (chỉ cấu trúc) hoặc mysqldump --no-create-info (chỉ dữ liệu). Cả hai đều là cờ export: chúng thay đổi nội dung của một dump mới từ một cơ sở dữ liệu đang chạy. Chúng không làm được gì cho một dump bạn đã có sẵn nằm trên đĩa dưới dạng file .sql.

Trên thực tế, file thường đến từ người khác, hoặc từ một job sao lưu, hoặc từ một máy chủ bạn không còn quyền truy cập trực tiếp. Bạn không thể chạy lại mysqldump trên một cơ sở dữ liệu không kết nối được — thông tin đăng nhập đã mất, máy chủ đã ngừng hoạt động, hoặc file đơn giản là tất cả những gì bạn được trao. Đến lúc đó thì các cờ vô dụng, và bạn còn lại việc chỉnh sửa chính cái file.

Cách làm thủ công

Nước đi hiển nhiên là loại các dòng bằng grep hoặc sed. Để giữ cấu trúc và bỏ dữ liệu:

# giữ mọi thứ trừ các dòng INSERT
grep -v '^INSERT INTO' dump.sql > schema.sql

# hoặc chỉ giữ các dòng INSERT cho một file chỉ-dữ-liệu
grep '^INSERT INTO' dump.sql > data.sql

Cách này trông ổn trên một dump nhỏ, gọn gàng — rồi hỏng trên một dump thật:

• INSERT nhiều dòng. Một lệnh INSERT INTO … VALUES có thể trải dài hàng nghìn dòng. grep khớp dòng đầu tiên và bỏ lại phần giá trị còn lại mồ côi, tạo ra một file hỏng.
• Extended insert. mysqldump gộp nhiều dòng vào một lệnh khổng lồ duy nhất. Các bộ lọc theo dòng không thể biết một lệnh như vậy kết thúc ở đâu.
• Chú thích và khối. Các khối điều kiện /*! … */, LOCK TABLES, và các chú thích -- xen kẽ với dữ liệu, nên một bộ lọc ngây thơ hoặc giữ lại rác hoặc cắt mất thứ mà quá trình import cần.
• Chỉ-dữ-liệu còn tệ hơn. Một grep '^INSERT' thuần túy vứt bỏ các lệnh SET và LOCK mà các dòng phụ thuộc vào, và bất kỳ INSERT nào không bắt đầu đúng ở cột số không đều lặng lẽ biến mất.

Bạn kết cục đi gỡ lỗi cho bộ lọc của chính mình thay vì lấy được schema.

Chỉ-cấu-trúc hoặc chỉ-dữ-liệu với DumpCleaner

DumpCleaner hiểu các lệnh SQL thay vì khớp theo dòng, nên nó tách cấu trúc khỏi dữ liệu một cách đáng tin cậy — trên file kích thước bất kỳ:

1. Kéo file .sql của bạn vào DumpCleaner (MySQL, MariaDB hoặc PostgreSQL). Nó phân tích file theo luồng, nên một dump nhiều gigabyte nạp vào với bộ nhớ không đổi.
2. Bật/tắt các loại lệnh bạn muốn: CREATE TABLE, CREATE INDEX, INSERT INTO, ALTER, DROP, LOCK, SET. Bỏ tích INSERT INTO là bạn có chỉ-cấu-trúc; bỏ tích CREATE TABLE / CREATE INDEX là bạn có chỉ-dữ-liệu.
3. Hoặc chọn một preset — Chỉ cấu trúc hoặc Chỉ dữ liệu — để đặt mọi công tắc trong một bước, rồi tinh chỉnh theo từng bảng nếu bạn muốn.
4. Export. Bạn có một file schema tinh gọn cho staging hay CI, hoặc một dump chỉ-dữ-liệu sạch sẽ để mồi dữ liệu.

Vì bộ lọc nhận biết theo lệnh, một extended INSERT nhiều dòng được giữ hoặc bỏ như một khối trọn vẹn — không bao giờ bị cắt giữa chừng.

Sức mạnh thực sự là việc lọc hoạt động theo từng loại lệnh và theo từng bảng, và hai điều đó kết hợp với nhau. Bạn có thể giữ cấu trúc của mọi bảng nhưng chỉ bao gồm dữ liệu INSERT cho ba bảng bạn thực sự cần — để lại bảng logs 89 triệu dòng như một cái vỏ rỗng. Đó là điều không một cờ --no-data nào diễn tả được.

Câu hỏi thường gặp