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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@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 TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_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 TABLE và UNLOCK 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:

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 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

Tài liệu tham khảo

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