mysqldump - A Database Backup Program

Nếu server của bạn bị tấn công tê liệt, hay gặp vấn đề về kỹ thuật, bạn nên có những biện pháp sao lưu (backups) - cho phép bạn nhanh chóng lấy trên server và chạy ngay với dữ liệu được cập nhật mới nhất (up-to-date). Cách đơn giản nhất để tạo 1 bản backup đó là tắt MySQL server, tạo nên 1 bản sao của thư mục dữ liệu (data directory) đến một vị trí an toàn, và sao chép nó trở lại nếu có yêu cầu. Tiện ích dumpmysql thực hiện logical backup - sao chép cấu trúc và dữ liệu bảng mà không sao chép dữ liệu thực tế. Nó tạo ra một tập các câu lệnh SQL mà có thể được thực thi để "sản sinh" những định nghĩa đối tượng cơ sở dữ liệu ban đầu và dữ liệu bảng. Nó dump một hoặc nhiều MySQL databases cho việc backup hay transfer đến SQL server khác. Lệnh mysqldump có thể export ra với dạng file CSV, XML, ...

Dumping a Database as SQL statements

Bạn có thể tạo nên 1 bản backup database bằng việc tạo một file bao gồm tất cả các câu lệnh SQL (SQL statement) cần thiết để re-create cấu trúc database đang tồn tại, và cả những câu lệnh để thêm dữ liệu. SQL statement là một dạng sao lưu tuyệt vời. Đây là một trong những cách dễ dàng nhất để sao lưu một cách an toàn dữ liệu của chúng ta từ việc trích xuất nó từ MySQL, ghi nó vào stable media (như đĩa CD hoặc DVD có chất lượng cao), và "cất giữ" nó ở nơi an toàn. Nếu như file SQL statements này chỉ chứa văn bản thuần, thì chúng ta có thể nén nó lại để giảm dung lượng lưu trữ. Chúng ta sẽ bắt đầu với 1 ví dụ đơn giản, backup database có tên là music, file output là music.sql. Để làm được, ta chạy lệnh sau đây trên terminal:

$ mysqldump --user=root --password=the_mysql_root_password --result-file=music.sql music
# Hoặc
$ mysqldump -u root -p --result-file=music.sql music
  • Điều này đang tạo ra 1 file "music.sql" trong thư mục hiện tại. Nếu bạn không có quyền write vào nó thì hãy đặc tả đường dẫn đến vị trí thư mục khác để lưu file này. VD: /tmp/music.sql (với hệ điều hành Linux hay Mac OS X) hay C:\music.sql (với hệ điêu hành Windows)
  • Và bây giờ hãy dùng Editor để mở file "music.sql", nó sẽ có dạng như sau:
-- MySQL dump 10.10
--
-- Host: localhost Database: music
-- ------------------------------------------------------
-- Server version 5.0.22
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `album`
--
DROP TABLE IF EXISTS `album`;
CREATE TABLE `album` (
 `artist_id` smallint(5) NOT NULL default '0',
 `album_id` smallint(4) NOT NULL default '0',
 `album_name` char(128) default NULL,
 PRIMARY KEY (`artist_id`,`album_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `album`
--
/*!40000 ALTER TABLE `album` DISABLE KEYS */;

/* somethings */

/*!40000 ALTER TABLE `track` DISABLE KEYS */;
LOCK TABLES `track` WRITE;
INSERT INTO `track` VALUES (0,'Do You Love Me?',2,1,'00:05:95'),
 (1,'Nobody's Baby Now',2,1,'00:03:87'),(2,'Loverman',2,1,'00:06:37'),
 (3,'Jangling Jack',2,1,'00:02:78'),(4,'Red Right Hand',2,1,'00:06:18'),
 (5,'I Let Love In',2,1,'00:04:25'),(6,'Thirsty Dog',2,1,'00:03:81'),
...
UNLOCK TABLES;
/*!40000 ALTER TABLE `track` ENABLE KEYS */;
/*!40103 SET [email protected]_TIME_ZONE */;
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

Bạn hãy ghi nhớ lại một vài đặc tính trong file dump này:

CREATE TABLE: lệnh pho phép tạo mới cho tất cả tables trong database DROP TABLE: lệnh cho phép load file trong MySQL của bạn mà không có lỗi khi mỗi table này đang tồn tại, tuy nhiên nó cũng làm mất dữ liệu trên server trong table này của database. INSERT: lệnh cho phép thêm dữ liệu vào table. LOCK TABLEUNLOCK TABLE: lệnh này để chắc chắn rằng chỉ 1 user cập nhật or sử dụng table khi đang thêm dữ liệu, tăng tốc độ cho quá trình này.

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

Bạn không cần lo lắng về điều này, chúng ta sẽ sử dụng những option để giải quyết nó.

mysqldump options

mysqldump có những options để kiểm soát table nào nên được locked trong quá trình dump, việc lưu trữ lại (restoring) 1 dump nên ghi đè bất kỳ bảng nào đang tồn tại. Những option này được nối như là những tham số, sau đây là danh sách các option hay sử dụng nhất:

STT Option Mô tả
1 add-drop-table Tạo lệnh DROP TABLE cho mỗi table, chắc chắn rằng bất kỳ table nào đang tồn tài đều sẽ bị xóa trước khi dump restore
2 add-locks Tạo lệnh LOCK TABLE trước mỗi lệnh INSERT và tương ứng có lệnh UNLOCK TABLE sau đó
3 all-databases Tạo một dump cho tất cả database trên server
4 create-options Bao gồm thông tin đặc tả MySQL, như là ENGINE, CHARSET, ... đi kèm sau thao tác tạo table
5 databases Tạo một dump để đặc tả database, nếu như bạn chỉ liệt kê 1 database thì lệnh CREATE DATABASE và USE sẽ được thêm vào
6 disable-keys Nói với MySQL để disable việc update index trong suốt thao tác INSERT cho MyISAM table. index được tạo sau khi tất cả dữ liệu đã được load
7 extended-insert Kết hợp lệnh INSERT cho nhiều hàng trong table
8 no-data Chỉ tạo lại cấu trúc database mà không có quá trình INSERT dữ liệu
9 where Dump những bản ghi mà được đặc tả thỏa mãn điều kiện
10 opt Cho phép mặc định từ MySQL v4.1 trở lên, cho phép các option: add-drop-table, add-locks, create-options, disable-keys, extendedinsert, lock-tables, quick, và set-charset. Bạn có thể disable một trong số các option này bằng việc cho thêm tiền tố skip- (VD: skip-set-charset)

Sau đây là một số cách thường sử dụng để dump:

  • Tạo file backup cho tất cả database trên MySQL server:
$ mysqldump -u root -p --result-file=outputfile.sql --all-databases

// VD: dump **music** và **wedding** database
$ mysqldump -u root -p --result-file=outputfile.sql --databases music wedding
  • Tạo file backup với đặc tả database:
$ mysqldump -u root -p --result-file=outputfile.sql database_name table_name
  • Tạo file backup đặc tả các tables trong database
$ mysqldump -u root -p --result-file=outputfile.sql database_name table_name
  • Tạo file backup đặc tả dữ liệu từ 1 table trong database:
$ mysqldump -u root -p --result-file=outputfile.sql database_name table_name where=where_clause

// VD:
$ mysqldump -u root -p --result-file=outputfile.sql --where="artist_name like 'N%'" music artist

Loading data from an SQL Dump File

Để load cấu trúc và dữ liệu trong dump file, bạn cần sử dụng lệnh sau:

mysql> SOURCE dumpfile.sql

Nếu file backup của bạn không có các lệnh CREATE DATABASEUSE, 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

Tài liệu tham khảo

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html