Export và Phương thức Backup trong MySQL

Cách đơn giản nhất của việc truy xuất dữ liệu vào trong một text file là sử dụng lệnh SELECT…INTO OUTFILE mà xuất một kết quả truy vấn một cách trực tiếp vào trong file trên Server host.

Export với lệnh SELECT…INTO OUTFILE trong MySQL

Cú pháp của lệnh này là tổ hợp một lệnh SELECT thông thường với INTO OUTFILE ten_file ở cuối. Định dạng output mặc định là giống như LOAD DATA, vì thế lệnh sau sẽ xuất bảng sinhvienk60 vào trong /tmp/vietjack.txt.

SELECT * FROM sinhvienk60 
    INTO OUTFILE '/tmp/vietjack.txt';

Bạn có thể thay đổi định dạng output bởi sử dụng các tùy chọn để chỉ cách trích dẫn và giới hạn các cột và các hàng. Để xuất bảng sinhvienk60 với định dạng CSV với các dòng CRLF, sử dụng lệnh:

SELECT * FROM passwd INTO OUTFILE '/tmp/vietjack.txt'
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';

Lệnh SELECT ... INTO OUTFILE có các thuộc tính sau:

Output file được tạo một cách trực tiếp bởi MySQL Server, vì thế ten_file nên chỉ ra nơi bạn xác định file đó để ghi dữ liệu trên Server host. Không có phiên bản LOCAL của lệnh như trong LOAD DATA.
Bản phải được trao quyền MySQL FILE để thực thi lệnh SELECT…INTO.
Output file phải là chưa tồn tại. Điều này giúp bạn ngăn cản MySQL từ việc ghi đè các file quan trọng.
Bạn nên có một tài khoản đăng nhập trên Server host hoặc theo phương thức nào khác để lấy file từ host đó. Nếu không, lệnh SELECT…INTO OUTFILE sẽ không có giá trị với bạn.
Với UNIX, file được tạo sẽ là có thể đọc bởi mọi người và được sở hữu bởi MySQL Server. Nghĩa là, mặc dù bạn có thể đọc được file đó, nhưng bạn không thể xóa nó.

Xuất bảng dưới dạng dữ liệu thô

Chương trình mysqldump được sử dụng để sao hoặc backup các bảng hoặc cơ sở dữ liệu. Chương trình này có thể viết bảng output hoặc ở dạng file dữ liệu thô hoặc dưới dạng một tập hợp các lệnh INSERT mà tái tạo các bản ghi trong bảng.

Để xuất một bảng dưới dạng file dữ liệu, bạn phải xác định một tùy chọn -- mà chỉ dẫn thư mục, nơi bạn muốn MySQL Server để ghi file đó.

Ví dụ, để xuất bảng sinhvienk60 từ cơ sở dữ liệu sinhvien vào một file trong thư mục /tmp, sử dụng lệnh sau:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp sinhvien sinhvienk60
password ******

Xuất nội dung hoặc định nghĩa bảng trong định dạng SQL

Để xuất một bảng trong định dạng SQL vào một file, sử dụng lệnh giống như:

$ mysqldump -u root -p sinhvien sinhvienk60 > dump.txt
password ******

Lệnh này sẽ tạo một file chứa nội dung như sau:

-- MySQL dump 8.23
--
-- Host: localhost    Database: sinhvien
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `sinhvienk60`
--

CREATE TABLE sinhvienk60 (
mssv INT NOT NULL AUTO_INCREMENT,
ho VARCHAR(255) NOT NULL,
ten VARCHAR(255) NOT NULL,
tuoi INT NOT NULL,
diemthi FLOAT(4,2) NOT NULL,
PRIMARY KEY (mssv)
) TYPE=MyISAM;

--
-- Dumping data for table `sinhvienk60`
--

INSERT INTO sinhvienk60 (ho, ten, diemthi)
VALUES ("Dinh Van", "Cao", 8);

INSERT INTO sinhvienk60 (ho, ten, diemthi)
VALUES ("Nguyen Van", "Thanh", 9);

INSERT INTO sinhvienk60 (ho, ten, diemthi)
VALUES ("Nguyen Hoang", "Manh", 7.5);

INSERT INTO sinhvienk60 (ho, ten, diemthi)
VALUES ("Tran Van", "Nam", 10);

Để xuất nhiều bảng, bạn xác định tất cả bảng đó sau tên cơ sở dữ liệu. Để xuất cả cơ sở dữ liệu, đừng xác định bất kỳ bảng nào sau cơ sở dữ liệu, như sau:

$ mysqldump -u root -p sinhvien > database_dump.txt
password ******

Để backup tất cả cơ sở dữ liệu có sẵn trên host của bạn, sử dụng lệnh sau:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

Tùy chọn --all--databases là có sẵn trong MySQL 3.23.12.

Phương thức này có thể được sử dụng để triển khai một chiến lược sao lưu cơ sở dữ liệu.

Sao chép bảng hoặc cơ sở dữ liệu tới Host khác

Nếu bạn muốn sao chép các bảng hoặc cơ sở dữ liệu từ một MySQL Server tới Server khác, thì sử dụng mysqldump với tên cơ sở dữ liệu và tên bảng.

Chạy lệnh sau tại source host. Lệnh này sẽ xuất toàn bộ cơ sở dữ liệu vào trong dump.txt file.

$ mysqldump -u root -p ten_database ten_bang > dump.txt
password *****

Bạn có thể sao chép toàn bộ cơ sở dữ liệu mà không cần sử dụng một tên bảng cụ thể như giải thích trên.

Bây giờ, dump.txt file trên host khác và sử dụng lệnh sau. Trước khi chạy lệnh, đảm bảo bạn có database_name đã được tạo trên Server đích.

$ mysql -u root -p ten_database < dump.txt
password *****

Một cách khác để thực hiện việc này mà không sử dụng một file trung gian là gửi output của mysqldump một cách trực tiếp thông qua mạng tới MySQL Server từ xa. Nếu bạn có thể kết nối tới cả hai Server từ host đó, nơi đặt source database, sử dụng lệnh này (đảm bảo bạn có thể truy cập với cả hai Server đó):

$ mysqldump -u root -p ten_database \
       | mysql -h other-host.com ten_database

Các bài học MySQL phổ biến khác tại AZCode: