Với mysqldump:
Ưu điểm: Tool này có hỗ trợ online backup với innodb. Tương thích tốt với mysql. Mọi biến thể mysql đều tích hợp tool này.
Nhược điểm: Một luồng nên cực kỳ chậm trong cả backup/restore. Không online backup với myisam.
Tình huống sử dụng: Thích hợp để dump một phần database hoặc một vài database với dung lượng nhỏ.
Vì sao cần 1 giải pháp backup database có dữ liệu lớn?
Mình từng làm việc với dữ liệu của MySQL có dung lượng > 10GB và hàng triệu record. Với hệ thống lớn như vậy, việc backup daily là 1 công việc thường xuyên và cần thiết vì mỗi ngày có hàng trăm, hàng ngàn record được thêm vào Database. Với hệ thống lớn như vậy, việc export dữ liệu nhanh chóng, an toàn và không ảnh hướng đến hệ thống đang chạy là điều hết sức cần thiết.
Bài này sẽ hướng dẫn bạn cách sử dụng mysqldump để backup dữ liệu trên MySQL.
- Đầu tiên, bạn cần nhấn tổ hợp phím Window + R, nhập cmd và nhân enter
- Nhập dòng lệnh sau: cd \xampp\mysql\bin để truy cập đến thư mục điều hướng của mysql
Giải pháp backup database có dữ liệu lớn
Sử dụng công cụ mysqldump của MySQL và MariaDB, với command sau:
mysqldump -h <mysql_host> -u<mysql_user> -p --single-transaction --quick --lock-tables=false <database_name> | gzip > /backupDB/backup.sql.gz
Với:
- <mysql_host>: Hostname, địa chỉ IP của MySQL Server
- <mysql_user>: MySQL User
- <database_name>: Tên của MySQL Database
- –single-transaction: Đặt MySQL ở mode REPEATABLE READ, nó là hữu dụng bảng CSDL sử dụng kiểu InnoDB mà không block ứng dụng (Non-Blocking)
- –quick: Nó sẽ trả về 1 bản ghi ở 1 thời điểm thay vì trả về cả bảng CSDL
- –lock-table=false, sẽ không khóa bảng khi export. Nó đảm bảo ứng dụng hoạt động khi export dữ liệu.
- gzip: Output file dướng dạng dữ liệu nén .gz (Giảm dung lượng file output)
Kết hợp 3 tham số –single-transaction, –quick, –lock-table sẽ giúp cho việc export dữ liệu được an toàn, ko ảnh hưởng tới ứng dụng đang chạy, tốn ít tài nguyên của hệ thống.
Hoặc đơn giản:
- Nhập dòng lệnh sau: mysqldump --user=root --password=myrootpwd mydatabase > backup.sql
Trong đó, root là user, myrootpwd là mật khẩu của user và mydatabase là tên cơ sở dữ liệu mà bạn muốn backup dữ liệu
- Sau khi backup thành công, file backup.sql sẽ nằm trong thư mục \xampp\mysql\bin, bạn truy cập vào đây để lấy về.
Khôi phục Database
Để khôi phục lại dữ liệu đã backup ở trên chúng ta cần thực hiện:
1. Giải nén file gz
gunzip backup.sql.gz
2. Import file sql
Sử dụng command mysql của MySQL & MariaDB
mysql -h <mysql_host> -u<mysql_user> -p <database_name> < backup.sql
Nhập password của MySQL User
Nhớ rằng, trong file dump này có 2 đặc tính không được đề cập đến, đó là:
CREATE DATABASE: lệnh để tạo database USE database_name: lựa chọn database
Nếu file backup của bạn không có các lệnh CREATE DATABASE và USE, thì bạn làm theo các bước sau để load file dump:
//
$ mysql - u root -p
// Xóa database nếu nó đang tồn tại
mysql> DROP DATABASE database_name;
//Tạo database
mysql> CREATE DATABASE new_database_name;
//Lựa chọn database
mysql> USE new_database_name;
//Load file dump
mysql> SOURCE dumpfile.sql