Hướng dẫn 5 cách backup và restore MySQL Database

Cách 1: Sử dụng mysqldump để sao lưu và khôi phục MySQL database

Mysqldump là một lệnh để tạo ra một bản sao lưu của cơ sở dữ liệu MySQL. Nó tạo ra một file .sql chứa các câu lệnh mà sau này dùng để khôi phục dữ liệu và các bảng trong database. Lệnh mysqldump cũng có thể xuất ra các file với định dạng như XML hoặc CSV. Ngoài ra mysqldump còn được dùng để di chuyển MySQL database sang một MySQL server mới.

Phần tiếp theo sẽ là hướng dẫn chi tiết cách sử dụng câu lệnh mysqldump:

1. Cú pháp mysqldump

Để backup cơ sở dữ liệu MySQL thì cú pháp chung của lệnh là như sau:

mysqldump [options] > file.sql

Trong đó:

• option là các tùy chọn của câu lệnh
• File .sql là tệp cơ sở dữ liệu được xuất ra trong quá trình sao lưu.

Trong quá trình lệnh thực thi, bạn sẽ nhận được yêu cầu nhập username và password

2. Sao lưu MySQL database đơn giản

Chúng ta thường sẽ sử dụng mysqldump để backup một database. Câu lệnh trong trường hợp này sẽ là:

mysqldump -u root -p database_name > database_name.sql

Trong đó:

• database_name là tên của cơ sở dữ liệu chúng ta cần sao lưu
• database_name.sql là tên file được tạo ra sau khi backup.

Sau khi nhập lệnh trên, tiếp theo bạn sẽ cần điền password của tài khoản root để quá trình sao lưu được diễn ra. Nếu bạn có một cơ sở dữ liệu với kích thước lớn, tiến trình này sẽ cần nhiều thời gian hơn.

Nếu trong trường hợp bạn đăng nhập với user có quyền tạo backup mà không cần nhập pass thì có thể bỏ qua đoạn lệnh -u và -p:

mysqldump database_name > database_name.sql

3. Cách sao lưu cùng lúc nhiều database

Để sao lưu cùng lúc nhiều cơ sở dữ liệu thì cũng rất đơn giản. Chúng ta sử dụng thêm tùy chọn –databases và tiếp theo là tên những database bạn cần backup. Thêm dấu cách vào giữa tên các database để phân cách chúng.

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

Trong câu lệnh trên chúng ta sao lưu hai cơ sở dữ liệu là database_name_a và database_name_b vào file databases_a_b.sql

4. Sao lưu tất cả database trong máy chủ

Tương tự như trường hợp trên, chúng ta sao lưu cùng lúc toàn bộ cơ sở dữ liệu vào một file duy nhất. Đây là câu lệnh sử dụng:

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

Trong đó –all-databases là tùy chọn sao lưu toàn bộ cơ sở dữ liệu.

5. Sao lưu database vào nhiều file

Câu lệnh mysqldump không có option để backup từng cơ sở dữ liệu vào nhiều tập tin khác nhau. Tuy nhiên chúng ta có thể dùng vòng lặp FOR để có kết quả tương tự.

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

Kết quả là mỗi cơ sở dữ liệu sẽ được sao lưu vào một tập tin riêng.

6. Tạo 1 file nén khi sao lưu database

Nếu database có dữ liệu quá lớn, chúng ta nên nén file xuất ra dưới dạng gzip để dễ khôi phục sau này:

mysqldump database_name | gzip > database_name.sql.gz

7. Thêm thời gian vào cuối file backup

Chúng ta có thể thêm thời gian vào cuối mỗi file, cách này sẽ giúp chúng ta dễ dàng phân biệt trong trường hợp có quá nhiều tệp backup trong cùng một thư mục:

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

Phần $(date +%Y%m%d) sẽ thêm thông tin năm, tháng và ngày vào cuối file backup.

8. Khôi phục lại database đã sao lưu

Chúng ta sử dụng câu lệnh mysql để khôi phục lại các bản sao lưu:

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

Dòng lệnh đầu tiên bạn sẽ tạo ra một cơ sở dữ liệu có tên là database_name. Ở dòng lệnh thứ hai bạn thực hiện gán file backup đã tạo trước đó vào cơ sở dữ liệu mới này.

9. Khôi phục lại 1 database từ 1 file sao lưu tất cả dữ liệu

Trong ví dụ trước đây mình đã sử dụng tùy chọn –all-databases để sao lưu toàn bộ MySQL database vào một file. Để khôi phục lại tất cả dữ liệu này vào một database mới chúng ta sử dụng tùy chọn –one-database. Câu lệnh sẽ như sau:

mysql --one-database database_name < all_databases.sql

10. Export và Import một database trong 1 dòng lệnh

Chúng ta có thể kết hợp quá trình sao lưu và khôi phục trong một câu lệnh bằng cách sử dụng câu lệnh:

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

Trong câu lệnh trên, chúng ta xuất file backup của cơ sở dữ liệu có tên database_name, đồng thời import nó vào cơ sở dữ liệu mới tên là remote_database_name nằm trong một máy chủ MySQL khác là remote_host. Đừng quên bạn cần tạo cơ sở dữ liệu mới trong máy chủ từ xa để có chỗ import vào nhé.

11. Sao lưu tự động với cron job

Bạn có thể cài đặt trước thời gian sao lưu và để quá trình diễn ra một cách tự động. Nhờ vậy cơ sở dữ liệu sẽ được sao lưu thường xuyên, liên tục. Để làm được điều này, chúng ta sẽ sử dụng đến cronjob theo các bước sau:

Bước 1: Tạo file .my.cnf trong thư mục home

sudo nano ~/.my.cnf

Thêm đoạn code sau vào file vừa tạo:

[client]
user = dbuser
password = dbpasswd

Trong đó dbuser và dbpasswd lần lượt là tên đăng nhập và mật khẩu của máy chủ MySQL

Bước 2: Thiết lập quyền truy cập cho file để chỉ có bạn hoặc người được bạn cấp quyền mới có thể đọc và sửa file này

chmod 600 ~/.my.cnf

Bước 3: Tạo directory để chứa file backup

mkdir ~/db_backups

Bước 4: Mở file crontab

crontab -e

Bạn thêm dòng lênh sau để tự động backup vào lúc 5 giờ sáng mỗi ngày:

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

Đừng quên thay thế username với tên username thật.

Bạn cũng có thể tự động xóa các file backup trong thời gian 45 ngày bằng cách tạo một file cronjob với nội dung:

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

Với /path/to/backups là đường dẫn tới thư mục chứa file backup.

Cách 2: Sử dụng phpMyAdmin để backupMySQL Database

Bước 1: Trong cPanel hoặc các bảng điều khiển hosting khác, bạn mở phpMyAdmin

Bước 2: Chọn cơ sở dữ liệu cần sao lưu ở bên tay trái

Bước 3: Chọn tab Export ở phần menu

Bước 4: Bạn chọn export method là Custom – display all possible options

Bước 5: Chọn các table mà bạn muốn sao lưu

Đánh dấu tick vào những bảng mà bạn muốn sao lưu. Trong trường hợp không chắc chắn nên sao lưu bảng nào thì bạn nên giữ nguyên như mặc định.

Bước 6: Trong mục Compression bạn lựa chọn Gzipped còn các mục khác bạn giữ nguyên

Bước 7: Nhấn nút Go để tải file xuống. File tải xuống sẽ có đuôi .sql.gz

Cách 3: Hướng dẫn sao lưu MySQL database và các tệp tin vào FTP Server

Đối với những quản trị viên website dày dặn kinh nghiệm và đang sử dụng một máy chủ ảo VPS, sẽ là tốt nhất nếu bạn sao lưu cơ sở dữ liệu MySQL và các tệp web server vào máy chủ FTP. Với giao thức FTP hoặc NAS, thông tin được an toàn trong quá trình truyền và nhận file. Bạn sẽ sử dụng lệnh mysqldump để backup lần lượt từng database, sau đó chuyển các file backup lên FTP server.

$ mysqldump -u root -h localhost -pmypassword faqs | gzip -9 > faqs-db.sql.gz

Cách 4: Sử dụng PHP để sao lưu MySQL database

Bạn có thể sử dụng đoạn code PHP dưới đây để sao lưu cơ sở dữ liệu MySQL với truy vấn SELECT INTO OUTFILE

<?php
include 'config.php';
include 'opendb.php';
$tableName = 'mypet';
$backupFile = 'backup/mypet.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);
include 'closedb.php';
?>

Để restore các file backup, bạn sử dụng truy vấn LOAD DATA INFILE:

<?
include 'config.php';
include 'opendb.php';
$ tableName = 'mypet';
$ backupFile = 'mypet.sql';
$ query = "LOAD DATA INFILE 'backupFile' INTO TABLE $ tableName";
$ result = mysql_query ($ truy vấn);
include 'closb.php';
?>

Cách 5: Hướng dẫn sao lưu MySQL database qua SSH

Trong phần quản trị hosting, bạn cần cho phép sử dụng SSH để truy cập vào máy chủ. SSH được tích hợp sẵn trong terminal của các máy Linux. Với các máy tính Windows, bạn có thể sử dụng các phần mềm thay thế khác như Git bash hoặc Putty

Một vài cách sao lưu MySQL Database khác

– Backup MySQL database và chuyển sang Amazon S3:

Chúng ta có thể tiến hành sao lưu cơ sở dữ liệu MySQL sang Amazon S3 bằng cách sử dụng một shell script. Về cơ bản, chúng ta sẽ chạy shell script sử dụng các lệnh mysqldump và gzip để nén file backup tương tự như phần trên sau đó dùng AWS CLI để upload file lên Amazon S3. Để tự động hóa quá trình này hàng ngày, chúng ta sử dụng câu lệnh cron

– Backup MySQL Database tự động trên máy tính Linux

Trong Linux, chúng ta sử dụng câu lệnh sau:

15 2 * * * root mysqldump -u root -pPASSWORD –all-databases | gzip > /mnt/disk2/database_`data ‘ %m-%d-%Y’`.sql.gz

– Sử dụng AutoMyQueryBackup để tự động backup MySQL database

automysqlbackup có đầy đủ các tùy chọn: backup một database, nhiều database hoặc tất cả database. File backup sẽ được nén dưới dạng gzip hoặc bzip2. Nó cũng sẽ thường xuyên dọn dẹp những bản backup cũ để không làm đầy ổ cứng.

– Sử dụng Shell Script trong Ubuntu để backup MySQL database

Chúc bạn backup và restore MySQL Database thành công!

Nguồn: bkhost

Mọi người cùng tìm kiếm: backup database mysql xampp