Practical MySQL & MariaDB

Hướng dẫn mysqldump: Cách sao lưu và khôi phục cơ sở dữ liệu MySQL (kèm ví dụ)

未分類
目次

1. Giới thiệu

Sao lưu và khôi phục cơ sở dữ liệu là nền tảng của quản lý dữ liệu và thiết yếu cho các 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ụ hiệu quả và linh hoạt để tạo sao lưu cơ sở dữ liệu. Trong hướng dẫn này, chúng tôi sẽ giải thích mọi thứ từ cách sử dụng cơ bản của mysqldump đến các tùy chọn nâng cao, phương pháp khôi phục và khắc phục sự cố chi tiết. Ở phần cuối của bài viết, chúng tôi cũng sẽ giới thiệu các thực tiễn tốt nhất và các tài nguyên bổ sung, vì vậy hãy sử dụng tài liệu này như một tham chiếu hữu ích để thành thạo 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 sao lưu các cơ sở dữ liệu MySQL. Bạn có thể dump toàn bộ cơ sở dữ liệu, các bảng cụ thể, hoặc chỉ dữ liệu thỏa mãn một số điều kiện nhất định dưới dạng một script SQL. Tập tin dump này có thể được dùng để khôi phục dữ liệu hoặc di chuyển nó sang một máy chủ mới.

2.2 Các trường hợp sử dụng phổ biến

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

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 cho mysqldump như sau:

mysqldump -u username -p database_name > output_file.sql
  • -u username : Tên người dùng dùng để truy cập cơ sở dữ liệu.
  • -p : Yêu cầu bạn nhập mật khẩu.
  • database_name : Tên của cơ sở dữ liệu bạn muốn sao lưu.
  • > output_file.sql : Đường dẫn/tên đích cho tập tin dump.

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

  • -h hostname : Tên máy chủ của máy chủ cơ sở dữ liệu (mặc định là localhost).
  • -P port_number : 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 sao lưu toàn bộ dữ liệu trong mydatabase vào tập tin backup.sql. Nếu bạn bao gồm ngày trong tên tập tin sao lưu để kiểm soát phiên bản, việc theo dõi lịch sử sao lưu sẽ trở nên dễ dàng hơn.

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. Nó hữu ích khi bạn muốn thực hiệ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)

Sử dụng tùy chọn này khi bạn muốn sao lưu chỉ cấu trúc bảng mà không bao gồm dữ liệu. Ví dụ, nó hữu ích khi bạn muốn xuất chỉ 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)

Sử dụng tùy chọn này khi bạn muốn sao lưu chỉ dữ liệu thỏa mãn các điều kiện cụ thể. Ví dụ, để sao lưu chỉ các bản ghi mà cột is_active có giá trị 1:

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

4.4 --ignore-table

Sử dụng tùy chọn này để loại trừ các bảng cụ thể khỏi sao lưu. Nó hữu ích khi có những bảng bạn không muốn đưa vào.

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

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

5.1 Dump Only Specific Tables

Nếu bạn muốn sao lưu chỉ các bảng cụ thể, hãy 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 lưu chỉ dữ liệu từ table1 vào table1_backup.sql.

5.2 Dump Data Only / Schema Only

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

5.3 Conditional Dump

Để sao lưu chỉ dữ liệu thỏa mãn các điều kiện cụ thể, 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 sao lưu chỉ dữ liệu mà created_at là vào hoặc sau ngày 1 tháng 1 năm 2023.

6. Cách Khôi Phục

Để khôi phục một cơ sở dữ liệu đã được sao lưu bằng mysqldump, sử dụng lệnh mysql. Khôi phục là quá trình sử dụng tệp sao lưu để đưa cơ sở dữ liệu trở lại trạng thái trước đó.

6.1 Cú pháp Khôi phục Cơ bản

mysql -u username -p database_name < dump_file.sql
  • -u username : Tên người dùng được dùng để kết nối tới cơ sở dữ liệu.
  • -p : Yêu cầu bạn nhập mật khẩu.
  • database_name : Tên của cơ sở dữ liệu đích.
  • < dump_file.sql : Tệp dump được dùng để khôi phục.

6.2 Ví dụ: Thực hiện Khôi phục

mysql -u root -p mydatabase < backup.sql

Lệnh này khôi phục dữ liệu vào mydatabase từ tệp backup.sql.

6.3 Lưu ý Quan trọng khi Khôi phục

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

7. Các Thực hành Tốt nhất cho mysqldump

7.1 Lên lịch Sao lưu

Tự động hoá việc sao lưu định kỳ bằng cách viết script mysqldump và sử dụng bộ lập lịch như cron. Trong ví dụ script shell dưới đây, một bản sao lưu đầy đủ của tất cả các cơ sở dữ liệu được thực hiện mỗi ngày vào lúc nửa đêm.

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

7.2 Mã hoá Tệp Sao lưu

Vì các tệp sao lưu có thể chứa thông tin nhạy cảm, nên nên mã hoá chúng bằng các công cụ như gpg.

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

7.3 Tương thích Phiên bản

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

  1. Khôi phục định nghĩa bảng : mysqldump --all-databases --no-data --routines --events > dump-defs.sql Lệnh này chỉ sao lưu cấu trúc bảng, sau đó bạn khôi phục nó trong môi trường phiên bản mới để kiểm tra tính tương thích.
  2. Khôi phục dữ liệu : mysqldump --all-databases --no-create-info > dump-data.sql Sau khi xác nhận các định nghĩa bảng tương thích, chỉ khôi phục 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, thực hiện sao lưu và khôi phục trong môi trường thử nghiệm. Sau khi xác nhận mọi thứ hoạt động đúng, tiến hành di chuyển trong môi trường sản xuất.

7.4 Lưu trữ và Xác minh Sao lưu

  • Lưu trữ sao lưu một cách an toàn : Lưu các tệp sao lưu trên bộ nhớ ngoài hoặc trên đám mây, và cập nhật chúng thường xuyên. Lưu trữ ngoài site giúp bảo vệ dữ liệu khỏi các sự cố vật lý.
  • Kiểm tra khôi phục thường xuyên : Thực hiện các bài kiểm tra khôi phục định kỳ để xác nhận rằng sao lưu có thể được khôi phục đúng cách. Quan trọng là không bỏ qua việc xác minh khôi phục trong trường hợp sao lưu trở nên 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 khắc phục

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

8.2 Các 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ó thể được giải quyết bằng cách thử nghiệm trước khi nâng cấp. Đặc biệt, khi di chuyển từ MySQL 5.7 lên 8.0, nên khôi phục chỉ các định nghĩa bảng bằng tùy chọn --no-data và kiểm tra tính tương thích.

  • Kiểm tra các bất tương thích : Trước khi nâng cấp, mô phỏng quá trình di chuyển trong môi trường thử nghiệm để xác định các vấn đề tiềm ẩn. Theo dõi các tính năng hoặc cú pháp không tương thích, và chỉnh sửa các script SQL khi 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à khôi phục các cơ sở dữ liệu MySQL. Trong bài viết này, chúng tôi đã đề cập đến mọi thứ từ cách sử dụng cơ bản đến các tùy chọn nâng cao, các thực tiễn tốt nhất và cách 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 của mình một cách hiệu quả hơn bằng mysqldump.

Bằng cách áp dụng các thực tiễn tốt nhất như lên lịch sao lưu và mã hoá tệp, bạn có thể nâng cao bảo mật dữ liệu và tăng độ tin cậy cho các hoạt động cơ sở dữ liệu. Sử dụng mysqldump một cách đúng đắn để chuẩn bị cho các vấn đề tiềm ẩn của 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 thêm về mysqldump và áp dụng nó trong các tình huống thực tế. Ngoài ra, bằng cách thực hiện sao lưu định kỳ và xác minh quá trình khôi phục, bạn có thể duy trì an toàn cho cơ sở dữ liệu và sẵn sàng đối phó với mất dữ liệu bất ngờ.