Khám phá MySQL và MariaDB

Hướng dẫn toàn diện về mysqldump: Sao lưu & Phục hồi MySQL hiệu quả

Sao lưu và Khôi phục
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

Cách sử dụng và các phương pháp hay nhất cho mysqldump

1. Giới thiệu

Sao lưu và phục hồi cơ sở dữ liệu là nền tảng của quản lý dữ liệu và rất cần thiết cho hoạt động đáng tin cậy. “mysqldump” của MySQL được sử dụng rộng rãi như một công cụ để sao lưu cơ sở dữ liệu một cách hiệu quả và linh hoạt. Hướng dẫn này sẽ giải thích chi tiết từ cách sử dụng mysqldump cơ bản đến cách sử dụng các tùy chọn nâng cao, phương pháp phục hồi và khắc phục sự cố. Ở cuối bài viết, chúng tôi cũng sẽ giới thiệu các phương pháp hay nhất và tài nguyên tham khảo, vì vậy hãy sử dụng nó như một trợ giúp để nắm vững mysqldump.

2. mysqldump là gì

2.1 Tổng quan về mysqldump

mysqldump là một công cụ dòng lệnh để tạo bản sao lưu cơ sở dữ liệu MySQL. Nó có thể xuất toàn bộ cơ sở dữ liệu, các bảng cụ thể hoặc dữ liệu phù hợp với các điều kiện cụ thể dưới dạng tập lệnh SQL. Tệp xuất này được sử dụng để phục hồi dữ liệu hoặc di chuyển dữ liệu sang một máy chủ mới.

2.2 Trường hợp sử dụng

  • Sao lưu: Thực hiện sao lưu định kỳ để chuẩn bị cho sự cố hệ thống hoặc mất dữ liệu.
  • Di chuyển dữ liệu: Sử dụng để di chuyển cơ sở dữ liệu giữa các máy chủ hoặc sao chép dữ liệu sang môi trường phát triển.
  • Phân tích dữ liệu: Trích xuất các tập dữ liệu cụ thể để phân tích và xác minh.

3. Cách sử dụng cơ bản

3.1 Cú pháp lệnh cơ bản

Cú pháp lệnh cơ bản của mysqldump như sau:

mysqldump -u Tên người dùng -p Tên cơ sở dữ liệu > Tên tệp đầu ra.sql
  • -u Tên người dùng: Tên người dùng để truy cập cơ sở dữ liệu.
  • -p: Nhắc nhập mật khẩu.
  • Tên cơ sở dữ liệu: Tên cơ sở dữ liệu cần sao lưu.
  • > Tên tệp đầu ra.sql: Nơi lưu tệp xuất.

3.2 Tùy chọn xác thực người dùng

  • -h Tên máy chủ: Tên máy chủ cơ sở dữ liệu (mặc định là localhost).
  • -P Số cổng: Số cổng để kết nối (mặc định là 3306).

3.3 Ví dụ: Sao lưu toàn bộ cơ sở dữ liệu

mysqldump -u root -p mydatabase > backup.sql

Lệnh này sẽ sao lưu tất cả dữ liệu của mydatabase vào tệp backup.sql. Bao gồm ngày tháng trong tên tệp sao lưu để quản lý phiên bản sẽ giúp dễ dàng theo dõi lịch sử.

4. Giải thích các tùy chọn chính

4.1 --all-databases (-A)

Tùy chọn này sao lưu tất cả các cơ sở dữ liệu cùng một lúc. Rất tiện lợi khi bạn muốn sao lưu toàn bộ máy chủ.

mysqldump -u root -p --all-databases > all_databases_backup.sql

4.2 --no-data (-d)

Được sử dụng khi bạn chỉ muốn sao lưu cấu trúc bảng (schema) mà không bao gồm dữ liệu. Ví dụ, được sử dụng khi bạn muốn lấy cấu trúc bảng để thiết lập môi trường phát triển.

mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql

4.3 --where (-w)

Được sử dụng khi bạn chỉ muốn sao lưu dữ liệu phù hợp với các điều kiện cụ thể. Ví dụ, để sao lưu chỉ các bản ghi có cột is_active bằng 1:

mysqldump -u root -p mydatabase --where="is_active=1" > filtered_data_backup.sql

4.4 --ignore-table

Được sử dụng để loại trừ các bảng cụ thể khỏi bản sao lưu. Hữu ích khi có các bảng bạn không muốn sao lưu.

mysqldump -u root -p mydatabase --ignore-table=mydatabase.table1 > partial_backup.sql

5. Các ví dụ thực tế

5.1 Chỉ xuất các bảng cụ thể

Khi bạn chỉ muốn sao lưu các bảng cụ thể, chỉ định tên bảng sau tên cơ sở dữ liệu.

mysqldump -u root -p mydatabase table1 > table1_backup.sql

Lệnh này sẽ chỉ lưu dữ liệu của table1 vào table1_backup.sql.

5.2 Chỉ xuất dữ liệu / chỉ xuất cấu trúc (schema)

  • Chỉ dữ liệu: mysqldump -u root -p mydatabase --no-create-info > data_only_backup.sql Sao lưu chỉ dữ liệu mà không bao gồm cấu trúc bảng.
  • Chỉ cấu trúc (schema):
    bash mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql
    Sao lưu chỉ cấu trúc (schema) của bảng.

5.3 Xuất có điều kiện

Để sao lưu chỉ dữ liệu phù hợp với các điều kiện cụ thể, hãy sử dụng tùy chọn --where.

mysqldump -u root -p mydatabase --where="created_at >= '2023-01-01'" > recent_data_backup.sql

Lệnh này sẽ chỉ sao lưu dữ liệu có cột created_at lớn hơn hoặc bằng ‘2023-01-01’.

6. Phương pháp phục hồi (Restore)

Để phục hồi cơ sở dữ liệu đã sao lưu bằng mysqldump, hãy sử dụng lệnh mysql. Phục hồi là quá trình sử dụng tệp sao lưu để khôi phục trạng thái của cơ sở dữ liệu.

6.1 Cú pháp cơ bản của lệnh phục hồi

mysql -u Tên người dùng -p Tên cơ sở dữ liệu < Tệp xuất.sql
  • -u Tên người dùng: Tên người dùng để kết nối với cơ sở dữ liệu.
  • -p: Nhắc nhập mật khẩu.
  • Tên cơ sở dữ liệu: Tên cơ sở dữ liệu đích để phục hồi.
  • < Tệp xuất.sql: Tệp xuất được sử dụng để phục hồi.

6.2 Ví dụ: Thực hiện phục hồi

mysql -u root -p mydatabase < backup.sql

Bằng lệnh này, dữ liệu sẽ được phục hồi từ tệp backup.sql vào mydatabase.

6.3 Lưu ý khi phục hồi

  • Nếu cơ sở dữ liệu cần phục hồi không tồn tại, bạn cần tạo cơ sở dữ liệu trước.
  • Việc phục hồi một lượng lớn dữ liệu có thể mất thời gian, vì vậy điều quan trọng là phải lập kế hoạch trước.

7. Các phương pháp hay nhất cho mysqldump

7.1 Lập lịch sao lưu

Tạo tập lệnh cho mysqldump và tự động hóa việc sao lưu định kỳ bằng cách sử dụng các trình lập lịch như cron. Ví dụ tập lệnh shell sau sẽ sao lưu toàn bộ cơ sở dữ liệu vào lúc nửa đêm hàng ngày.

#!/bin/bash
mysqldump -u root -p'password' --all-databases > /path/to/backup/all_databases_$(date +%F).sql

7.2 Mã hóa tệp sao lưu

Vì các tệp sao lưu chứa thông tin nhạy cảm, nên khuyến nghị sử dụng các công cụ như gpg để mã hóa chúng.

gpg -c /path/to/backup/all_databases_$(date +%F).sql

7.3 Tương thích giữa các phiên bản

Khi di chuyển dữ liệu giữa các phiên bản MySQL khác nhau, cần chú ý đến các vấn đề không tương thích. Trước khi nâng cấp phiên bản, hãy mô phỏng quy trình sao lưu và phục hồi trong môi trường thử nghiệm để xác nhận tính tương thích.

  1. Phục hồi định nghĩa bảng: mysqldump --all-databases --no-data --routines --events > dump-defs.sql Lệnh này sẽ xuất chỉ cấu trúc bảng, sau đó phục hồi trong môi trường phiên bản mới để kiểm tra tính tương thích.
  2. Phục hồi dữ liệu: mysqldump --all-databases --no-create-info > dump-data.sql Sau khi xác nhận định nghĩa bảng tương thích, chỉ phục hồi dữ liệu.
  3. Xác minh trong môi trường thử nghiệm:
    Để kiểm tra tính tương thích giữa các phiên bản, hãy thực hiện sao lưu và phục hồi trong môi trường thử nghiệm và xác nhận rằng chúng hoạt động bình thường trước khi di chuyển sang môi trường sản phẩm.

7.4 Lưu trữ và xác minh bản sao lưu

  • Lưu trữ bản sao lưu an toàn:
    Lưu các tệp sao lưu vào bộ nhớ ngoài hoặc đám mây và cập nhật chúng thường xuyên. Lưu trữ ngoài trang web giúp bảo vệ dữ liệu khỏi các sự cố vật lý.
  • Xác minh phục hồi định kỳ:
    Thường xuyên kiểm tra phục hồi để xác nhận rằng bản sao lưu có thể được phục hồi chính xác. Điều quan trọng là không được bỏ qua việc xác minh phục hồi để phòng trường hợp bản sao lưu không hợp lệ.

8. Khắc phục sự cố

8.1 Các lỗi thường gặp và cách xử lý

  • Lỗi: @@GLOBAL.GTID_PURGED cannot be changed:
    Lỗi này xuất hiện khi xảy ra sự cố liên quan đến GTID trong MySQL 8.0. Tránh lỗi này bằng cách sử dụng tùy chọn --set-gtid-purged=COMMENTED để bình luận cấu hình GTID. mysqldump -u root -p mydatabase --set-gtid-purged=COMMENTED > backup.sql
  • Lỗi: Thiếu dung lượng đĩa:
    Nếu hết dung lượng đĩa khi sao lưu cơ sở dữ liệu lớn, hãy nén bản sao lưu hoặc thay đổi nơi lưu trữ. Nén bản sao lưu bằng gzip như sau: mysqldump -u root -p mydatabase | gzip > backup.sql.gz
  • Lỗi: Thiếu quyền:
    Nếu người dùng cơ sở dữ liệu không có đủ quyền, sao lưu hoặc phục hồi sẽ thất bại. Cấp các quyền cần thiết (SELECT, LOCK TABLES, SHOW VIEW, v.v.) rồi thử lại.

8.2 Vấn đề tương thích phiên bản

Các vấn đề tương thích giữa các phiên bản MySQL khác nhau được giải quyết bằng cách kiểm tra trước khi nâng cấp phiên bản. Đặc biệt khi di chuyển từ MySQL 5.7 lên 8.0, khuyến nghị sử dụng tùy chọn --no-data để chỉ phục hồi định nghĩa bảng và kiểm tra tính tương thích.

  • Kiểm tra không tương thích:
    Mô phỏng quá trình di chuyển trong môi trường thử nghiệm trước khi nâng cấp phiên bản để xác định các vấn đề tiềm ẩn. Chú ý đến các tính năng hoặc cú pháp không tương thích và sửa đổi tập lệnh SQL nếu cần.

9. Tóm tắt

mysqldump là một công cụ đáng tin cậy và mạnh mẽ để sao lưu và phục hồi cơ sở dữ liệu MySQL. Bài viết này đã giải thích một cách toàn diện từ cách sử dụng cơ bản đến các tùy chọn nâng cao, các phương pháp hay nhất và khắc phục sự cố. Bằng cách áp dụng kiến thức này, bạn có thể bảo vệ và quản lý cơ sở dữ liệu một cách hiệu quả bằng mysqldump.

Bằng cách áp dụng các phương pháp hay nhất như lập lịch sao lưu và mã hóa tệp, bạn có thể đảm bảo an toàn dữ liệu và tăng độ tin cậy trong hoạt động cơ sở dữ liệu. Sử dụng mysqldump một cách phù hợp để chuẩn bị cho các sự cố cơ sở dữ liệu.

10. Tài liệu tham khảo và tài nguyên bổ sung

Tham khảo tài nguyên này để tìm hiểu sâu hơn về mysqldump và áp dụng vào thực tế. Ngoài ra, hãy thực hiện sao lưu định kỳ và xác minh phục hồi để duy trì an toàn cơ sở dữ liệu và chuẩn bị cho trường hợp mất dữ liệu không mong muốn.