Cách sao lưu và khôi phục cơ sở dữ liệu mysql bằng mysqldump

Hướng dẫn này giải thích cách sao lưu và khôi phục cơ sở dữ liệu MySQL hoặc MariaDB từ dòng lệnh bằng tiện ích mysqldump.

Các tệp sao lưu được tạo bởi tiện ích mysqldump về cơ bản là một tập hợp các câu lệnh SQL có thể được sử dụng để tạo lại cơ sở dữ liệu gốc. Lệnh mysqldump cũng có thể tạo các tệp ở định dạng CSV và XML.

Bạn cũng có thể sử dụng tiện ích mysqldump để chuyển cơ sở dữ liệu MySQL của bạn sang một máy chủ MySQL khác.

Cú pháp lệnh Mysqldump

Trước khi đi vào cách sử dụng lệnh mysqldump, hãy bắt đầu bằng cách xem lại cú pháp cơ bản.

Các biểu thức tiện ích mysqldump có dạng sau:

mysqldump > file.sql

• options - Tệp tùy chọn mysqldump file.sql - Tệp kết xuất (sao lưu)

Để sử dụng lệnh mysqldump, máy chủ MySQL phải có thể truy cập và chạy.

Sao lưu cơ sở dữ liệu MySQL đơn

Trường hợp sử dụng phổ biến nhất của công cụ mysqldump là sao lưu một cơ sở dữ liệu.

Ví dụ: để tạo bản sao lưu cơ sở dữ liệu có tên là database_name bằng cách sử dụng thư mục root người dùng và lưu nó vào một tệp có tên là database_name.sql bạn sẽ chạy lệnh sau:

mysqldump -u root -p database_name > database_name.sql

Bạn sẽ được nhắc nhập mật khẩu root. Sau khi xác thực thành công, quá trình kết xuất sẽ bắt đầu. Tùy thuộc vào kích thước cơ sở dữ liệu, quá trình có thể mất một thời gian.

mysqldump database_name > database_name.sql

Sao lưu nhiều cơ sở dữ liệu MySQL

Để sao lưu nhiều cơ sở dữ liệu MySQL bằng một lệnh, bạn cần sử dụng tùy chọn --database theo sau là danh sách các cơ sở dữ liệu bạn muốn sao lưu. Mỗi tên cơ sở dữ liệu phải được phân tách bằng dấu cách.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

Lệnh trên sẽ tạo một tệp kết xuất chứa cả cơ sở dữ liệu.

Sao lưu tất cả cơ sở dữ liệu MySQL

Sử dụng tùy chọn --all-databases để sao lưu tất cả các cơ sở dữ liệu MySQL:

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

Giống như với ví dụ trước, lệnh trên sẽ tạo một tệp kết xuất duy nhất chứa tất cả các cơ sở dữ liệu.

Sao lưu tất cả các cơ sở dữ liệu MySQL vào các tệp riêng biệt

Tiện ích mysqldump không cung cấp tùy chọn sao lưu tất cả các cơ sở dữ liệu để tách các tệp nhưng chúng tôi dễ dàng đạt được điều đó với một vòng lặp FOR bash đơn giản:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

Lệnh trên sẽ tạo một tệp kết xuất riêng cho mỗi cơ sở dữ liệu bằng cách sử dụng tên cơ sở dữ liệu làm tên tệp.

Tạo một bản sao lưu cơ sở dữ liệu MySQL nén

Nếu kích thước cơ sở dữ liệu rất lớn, đó là một ý tưởng tốt để nén đầu ra. Để làm điều đó, chỉ cần chuyển đầu ra sang tiện ích gzip và chuyển hướng nó đến một tệp như dưới đây:

mysqldump database_name | gzip > database_name.sql.gz

Tạo một bản sao lưu với dấu thời gian

mysqldump database_name > database_name-$(date +%Y%m%d).sql

Lệnh trên sẽ tạo một tệp có định dạng database_name-20180617.sql sau database_name-20180617.sql

Khôi phục kết xuất MySQL

Bạn có thể khôi phục kết xuất MySQL bằng công cụ mysql . Cú pháp chung của lệnh như sau:

mysqld database_name < file.sql

Trong hầu hết các trường hợp, bạn sẽ cần tạo một cơ sở dữ liệu để nhập vào. Nếu cơ sở dữ liệu đã tồn tại, trước tiên bạn cần xóa nó.

Trong ví dụ sau, lệnh đầu tiên sẽ tạo một cơ sở dữ liệu có tên là database_name và sau đó nó sẽ nhập kết xuất database_name.sql liệu_name.sql vào nó:

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

Khôi phục cơ sở dữ liệu MySQL đơn từ kết xuất MySQL đầy đủ

mysql --one-database database_name < all_databases.sql

Xuất và nhập cơ sở dữ liệu MySQL trong một lệnh

Thay vì tạo một tệp kết xuất từ ​​một cơ sở dữ liệu và sau đó nhập bản sao lưu vào cơ sở dữ liệu MySQL khác, bạn có thể sử dụng một lớp lót sau:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

Lệnh trên sẽ dẫn đầu ra tới máy khách mysql trên máy chủ từ xa và nó sẽ nhập nó vào cơ sở dữ liệu có tên remote_database_name . Trước khi chạy lệnh, đảm bảo cơ sở dữ liệu đã tồn tại trên máy chủ từ xa.

Tự động sao lưu với Cron

Tự động hóa quá trình sao lưu cơ sở dữ liệu cũng đơn giản như tạo một công việc định kỳ, thứ sẽ chạy lệnh mysqldump tại thời điểm được chỉ định.

Để thiết lập sao lưu tự động cơ sở dữ liệu MySQL bằng cronjob, hãy làm theo các bước dưới đây:

1. Tạo một tệp có tên .my.cnf trong thư mục nhà người dùng của bạn:

   sudo nano ~/.my.cnf

   Sao chép và dán văn bản sau vào tệp.my.cnf.

   user = dbuser password = dbpasswd

   Đừng quên thay thế dbuser và dbpasswd bằng mật khẩu người dùng và mật khẩu người dùng.

   Hạn chế quyền của tệp thông tin đăng nhập để chỉ người dùng của bạn có quyền truy cập vào nó:

   chmod 600 ~/.my.cnf

   Tạo một thư mục để lưu trữ các bản sao lưu:

   mkdir ~/db_backups

   Mở tệp crontab người dùng của bạn:

   crontab -e

   Thêm công việc định kỳ sau sẽ tạo bản sao lưu tên cơ sở dữ liệu mydb mỗi ngày vào lúc 3 giờ sáng:

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   Đừng quên thay thế username bằng username thực tế của bạn.

Bạn cũng có thể tạo một cronjob khác để xóa mọi bản sao lưu cũ hơn 30 ngày:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Tất nhiên, bạn cần điều chỉnh lệnh theo vị trí sao lưu và tên tệp. Để tìm hiểu thêm về lệnh find, hãy kiểm tra Cách tìm tệp trong Linux bằng hướng dẫn Dòng lệnh.

Phần kết luận

Hướng dẫn này chỉ bao gồm những điều cơ bản, nhưng nó nên là một khởi đầu tốt cho bất kỳ ai muốn tìm hiểu cách tạo và khôi phục cơ sở dữ liệu MySQL từ dòng lệnh bằng tiện ích mysqldump.

Bạn cũng có thể kiểm tra hướng dẫn về cách đặt lại mật khẩu root của MySQL trong trường hợp bạn quên nó.

sao lưu mysq mariadb mysqldump