Trong quản trị cơ sở dữ liệu MySQL, việc sao lưu và phục hồi dữ liệu là các tác vụ thiết yếu. Đối với những tập dữ liệu lớn hoặc khi cần di chuyển dữ liệu giữa các hệ thống, các lệnh SELECT INTO OUTFILE và LOAD DATA INFILE cung cấp một phương pháp hiệu quả và linh hoạt để xuất và nhập dữ liệu dưới dạng tệp văn bản.

Cấu hình MySQL Server

Để đảm bảo MySQL có thể đọc hoặc ghi tệp vào các vị trí mong muốn trên hệ thống tệp, bạn cần cấu hình một số tham số trong tệp cấu hình my.cnf (hoặc my.ini trên Windows). Cần thêm hoặc điều chỉnh các dòng sau dưới phần [mysqld]:

secure-file-priv="/var/lib/mysql-files"
max_allowed_packet=512M
local_infile=ON

• secure-file-priv: Tham số này giới hạn thư mục mà các hoạt động đọc/ghi tệp của MySQL có thể truy cập. Đặt nó thành một đường dẫn cụ thể (ví dụ: /var/lib/mysql-files trên Linux hoặc C:/ProgramData/MySQL/MySQL Server 8.0/Uploads trên Windows) để tăng cường bảo mật. Nếu để trống hoặc đặt NULL sẽ có các ý nghĩa bảo mật khác nhau.
• max_allowed_packet: Đặt giá trị này đủ lớn để chứa các gói dữ liệu lớn, đặc biệt khi xử lý các chuỗi dài hoặc các trường dữ liệu lớn trong quá trình xuất/nhập.
• local_infile=ON: Tham số này cho phép sử dụng câu lệnh LOAD DATA LOCAL INFILE, cho phép MySQL đọc tệp từ máy khách kết nối.

Sau khi thay đổi cấu hình, hãy khởi động lại dịch vụ MySQL để các thay đổi có hiệu lực.

Sao lưu dữ liệu bằng SELECT INTO OUTFILE

Lệnh SELECT INTO OUTFILE cho phép bạn xuất kết quả của một câu lệnh SELECT trực tiếp ra một tệp văn bản trên máy chủ MySQL. Điều này hữu ích để tạo bản sao lưu dữ liệu hoặc trích xuất dữ liệu cho mục đích phân tích.

SELECT
    product_code,
    product_name,
    category_id,
    unit_price,
    stock_quantity,
    last_update
FROM
    product_inventory
INTO OUTFILE '/var/lib/mysql-files/product_data_backup.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• INTO OUTFILE '/var/lib/mysql-files/product_data_backup.csv': Chỉ định đường dẫn và tên tệp đầu ra. Tệp này sẽ được tạo trên máy chủ MySQL trong thư mục được cấu hình bởi secure-file-priv.
• FIELDS TERMINATED BY ',': Định nghĩa dấu phân cách giữa các trường dữ liệu (ở đây là dấu phẩy).
• OPTIONALLY ENCLOSED BY '"': Chỉ định rằng các trường chuỗi có thể được bao quanh bởi dấu ngoặc kép.
• LINES TERMINATED BY '\n': Định nghĩa dấu kết thúc mỗi dòng (ở đây là ký tự xuống dòng).

Phục hồi dữ liệu bằng LOAD DATA LOCAL INFILE

Lệnh LOAD DATA LOCAL INFILE được sử dụng để nhập dữ liệu từ một tệp văn bản vào một bảng trong cơ sở dữ liệu. Từ khóa LOCAL ngụ ý rằng tệp đang được đọc từ hệ thống tệp của máy khách kết nối, chứ không phải máy chủ MySQL.

LOAD DATA LOCAL INFILE '/path/to/client/files/product_data_backup.csv'
INTO TABLE temporary_product_staging
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(product_id_col, item_name_col, category_id_col, price_col, quantity_col, updated_ts);

• INFILE '/path/to/client/files/product_data_backup.csv': Đường dẫn tới tệp dữ liệu trên máy khách.
• INTO TABLE temporary_product_staging: Tên bảng đích mà dữ liệu sẽ được nhập vào.
• ESCAPED BY '\\': Ký tự thoát (escape character) được sử dụng trong tệp để xử lý các ký tự đặc biệt.
• (product_id_col, item_name_col, ...): Danh sách các cột trong bảng đích mà dữ liệu từ tệp sẽ được ánh xạ vào. Thứ tự phải khớp với thứ tự các trường trong tệp CSV.

Ví dụ Cấu trúc Bảng

Để minh họa, giả sử chúng ta có bảng nguồn product_inventory và sẽ nhập dữ liệu vào một bảng tạm thời temporary_product_staging có cấu trúc tương tự:

Bảng Nguồn: product_inventory

CREATE TABLE `product_inventory` (
  `product_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Mã sản phẩm duy nhất',
  `product_code` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Mã sản phẩm',
  `product_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'Tên sản phẩm',
  `category_id` INT(11) NULL DEFAULT NULL COMMENT 'Mã danh mục',
  `unit_price` DECIMAL(10, 2) NULL DEFAULT 0.00 COMMENT 'Giá đơn vị',
  `stock_quantity` INT(11) NULL DEFAULT 0 COMMENT 'Số lượng trong kho',
  `last_update` TIMESTAMP(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Thời gian cập nhật cuối',
  PRIMARY KEY (`product_id`) USING BTREE,
  UNIQUE INDEX `idx_product_code` (`product_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

Bảng Đích: temporary_product_staging

CREATE TABLE `temporary_product_staging` (
  `product_id_col` INT(11) NOT NULL COMMENT 'Mã sản phẩm từ tệp',
  `item_name_col` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'Tên sản phẩm từ tệp',
  `category_id_col` INT(11) NULL DEFAULT NULL COMMENT 'Mã danh mục từ tệp',
  `price_col` DECIMAL(10, 2) NULL DEFAULT NULL COMMENT 'Giá từ tệp',
  `quantity_col` INT(11) NULL DEFAULT NULL COMMENT 'Số lượng từ tệp',
  `updated_ts` TIMESTAMP(0) NULL DEFAULT NULL COMMENT 'Thời gian cập nhật từ tệp'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

Cấp quyền người dùng

Để thực hiện các thao tác đọc và ghi tệp, người dùng MySQL phải có quyền FILE. Bạn có thể cấp quyền này bằng cách sử dụng lệnh GRANT:

GRANT FILE ON *.* TO 'backup_user'@'localhost' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;

Thay thế 'backup_user' bằng tên người dùng MySQL của bạn và 'your_password' bằng mật khẩu tương ứng. Quyền FILE cấp khả năng đọc và ghi tệp vào bất kỳ thư mục nào mà MySQL có quyền truy cập (tuân theo giới hạn của secure-file-priv).