MySQL Backup và Restore: Hướng dẫn Toàn Diện từ Cơ bản đến Nâng cao

Giới thiệu về MySQL Import/Export

MySQL Import và Export là hai tính năng thiết yếu khi làm việc với cơ sở dữ liệu. Chúng cho phép bạn sao lưu dữ liệu, di chuyển database giữa các server, hoặc chia sẻ dữ liệu với các thành viên khác trong team. Tuy nhiên, việc thực hiện Import/Export không đơn giản như vẻ ngoài của nó, đặc biệt là với các database lớn.

Trong bài viết này, chúng ta sẽ khám phá mọi khía cạnh của Import/Export trong MySQL: từ các lệnh cơ bản nhất đến các kỹ thuật nâng cao để xử lý downtime, đảm bảo data consistency, và hiểu rõ cách hoạt động của format dump.

Phần 1: Các Khái niệm Cơ bản

Export là gì?

Export (hay còn gọi là dump) là quá trình xuất dữ liệu từ MySQL database ra một file văn bản (thường là file .sql). File này chứa tất cả các lệnh SQL cần thiết để tái tạo lại database và dữ liệu của nó.

Import là gì?

Import là quá trình ngược lại - lấy dữ liệu từ file .sql và đưa nó vào MySQL database. Quá trình này sẽ thực thi tất cả các lệnh SQL trong file để tái tạo lại cấu trúc và dữ liệu.

Tại sao cần Export/Import?

Backup và Restore: Tạo bản sao lưu dữ liệu để phòng chống mất dữ liệu
Data Migration: Di chuyển database từ server này sang server khác
Development: Tạo bản sao của production database cho môi trường dev/test
Data Sharing: Chia sẻ dữ liệu giữa các team thành viên
Version Control: Kiểm soát phiên bản schema và dữ liệu

Phần 2: Các Lệnh Cơ bản

Lệnh Export (mysqldump)

Lệnh cơ bản để export database:

mysqldump -u username -p password database_name > backup.sql

Giải thích:

-u username: Tên người dùng MySQL
-p password: Mật khẩu (có thể bỏ -p và nhập mật khẩu sau)
database_name: Tên database cần export
> backup.sql: Chuyển hướng output vào file

Ví dụ thực tế:

mysqldump -u root -p myapp_db > myapp_db_backup_2026-02-03.sql
# Hoặc không hiển thị mật khẩu (an toàn hơn):
mysqldump -u root -p myapp_db > backup.sql
# Nhập mật khẩu khi được yêu cầu

Lệnh Import (mysql)

Lệnh cơ bản để import database:

mysql -u username -p password database_name < backup.sql

Ví dụ thực tế:

mysql -u root -p myapp_db < myapp_db_backup_2026-02-03.sql

Export với các tùy chọn phổ biến

# Export chỉ schema (cấu trúc), không export data
mysqldump -u root -p --no-data database_name > schema_only.sql

# Export chỉ data, không export schema
mysqldump -u root -p --no-create-info database_name > data_only.sql

# Export một bảng cụ thể
mysqldump -u root -p database_name table_name > table_backup.sql

# Export nhiều bảng
mysqldump -u root -p database_name table1 table2 table3 > tables_backup.sql

# Export tất cả databases
mysqldump -u root -p --all-databases > all_databases_backup.sql

# Export với nén (gzip) để tiết kiệm dung lượng
mysqldump -u root -p database_name | gzip > backup.sql.gz

# Import từ file nén
gunzip < backup.sql.gz | mysql -u root -p database_name

Phần 3: Lưu ý Quan trọng và Tip & Trick

Lưu ý quan trọng

1. Luôn kiểm tra file backup trước khi xóa cơ sở dữ liệu

# Kiểm tra dung lượng file
ls -lh backup.sql

# Kiểm tra nội dung (xem vài dòng đầu)
head -50 backup.sql

# Đếm số lượng INSERT statements
grep -c "INSERT INTO" backup.sql

2. Đảm bảo có đủ dung lượng disk

Khi import, MySQL cần một khoảng dung lượng trống tạm thời. Cần chuẩn bị dung lượng tối thiểu là 2-3 lần kích thước file backup.

# Kiểm tra dung lượng trống
df -h

3. Character Encoding

Nếu database sử dụng UTF-8, đảm bảo export/import cùng encoding:

# Export với UTF-8 encoding
mysqldump -u root -p --default-character-set=utf8mb4 database_name > backup.sql

# Import với UTF-8 encoding
mysql -u root -p --default-character-set=utf8mb4 database_name < backup.sql

Tip & Trick

Tip 1: Sử dụng option `--quick` để tối ưu hóa performance

mysqldump -u root -p --quick database_name > backup.sql

Option này giúp giảm memory usage và tăng tốc độ export, đặc biệt hữu ích với các database lớn.

Tip 2: Export tất cả database cùng lúc

mysqldump -u root -p --all-databases > full_backup.sql

Tip 3: Sử dụng progress indicator

# Có thể cài đặt pv (pipe viewer) để xem progress
mysqldump -u root -p database_name | pv > backup.sql

Tip 4: Parallel export với mydumper (công cụ mạnh mẽ hơn)

Nếu database rất lớn, hãy cân nhắc sử dụng mydumper - một công cụ có thể export song song:

# Install mydumper (trên Mac)
brew install mydumper

# Export parallel
mydumper -u root -p password -B database_name -o ./dump

# Import từ mydumper
myloader -u root -p password -B database_name -d ./dump

Tip 5: Tạo backup tự động với cron job

# Thêm vào crontab để chạy hàng ngày lúc 2AM
0 2 * * * /usr/local/bin/mysqldump -u root -p'password' --all-databases | gzip > /backup/mysql_`date +\%Y\%m\%d_\%H\%M\%S`.sql.gz

Tip 6: Verify backup bằng cách restore vào database test

# Tạo test database
mysql -u root -p -e "CREATE DATABASE test_restore;"

# Restore vào test database
mysql -u root -p test_restore < backup.sql

# Kiểm tra số bảng
mysql -u root -p test_restore -e "SHOW TABLES;"

Tip 7: Sử dụng SSH tunnel nếu backup remote server

# Backup từ remote server qua SSH
ssh user@remote_server "mysqldump -u root -p password database_name" > backup.sql

Phần 4: Hiểu rõ Format MySQL Dump

Cấu trúc của file MySQL Dump

Một file dump tiêu chuẩn bao gồm các phần:

-- MySQL dump 10.11
--
-- Host: localhost    Database: myapp_db
-- Server version   5.7.36-log

/*!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 utf8mb4 */;
/*!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=@@OLD_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 `users`
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `users`
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'John Doe','john@example.com'),(2,'Jane Smith','jane@example.com');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

-- Restore settings
/*!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 */;

Giải thích chi tiết các phần

1. Header và Metadata

-- MySQL dump 10.11
--
-- Host: localhost    Database: myapp_db
-- Server version   5.7.36-log

Phần này chỉ là comment, thông tin về phiên bản dump và server.

2. Cài đặt Character Set và Timezone

/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET TIME_ZONE='+00:00' */;

Các dòng này rất quan trọng - chúng đảm bảo rằng import sẽ sử dụng cùng character set và timezone với export.

3. Vô hiệu hóa Checks quan trọng

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@OLD_FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

Tại sao lại vô hiệu hóa? Khi import, nếu bạn không tắt các constraint này, MySQL sẽ kiểm tra từng INSERT, làm chậm quá trình. Sau khi hoàn thành, chúng sẽ được bật lại.

Tác động:

Tắt: Import nhanh hơn 10-100 lần
Rủi ro: Có khả năng data sai lệch nếu file dump bị hỏng

4. Lock Table

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES ...;
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

LOCK TABLES WRITE:

Chặn các quá trình khác đọc/ghi vào bảng
Đảm bảo consistency khi export
Sau khi export xong, MySQL tự động unlock

DISABLE KEYS:

Tắt tạm thời các index
Cho phép insert nhanh hơn
Sau insert, rebuild index (ENABLE KEYS)

Tác động:

Insert 1 triệu records mà không rebuild index: ~5 giây
Insert 1 triệu records mà rebuild index sau mỗi record: ~5 phút
Chênh lệch: 60 lần

5. Restore Settings

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

Đây là phần quan trọng để khôi phục lại các setting gốc sau import hoàn tất.

Phần 5: Xử lý Downtime và Data Consistency

Vấn đề 1: Downtime khi Export/Import

Nguyên nhân downtime

Lock tables: Khi MySQL lock table để export, không ai có thể đọc/ghi
Import time: Thời gian để execute tất cả các INSERT statements
Index rebuild: Tái xây dựng index sau khi import

Ví dụ thực tế: Database 10GB

Export time: 5 phút (lock table trong 5 phút)
Upload to remote server: 10 phút
Import time: 30 phút (rebuild index)
Total downtime: 45 phút

Giải pháp 1: Zero-downtime migration (sử dụng MySQL Replication)

-- Trên Master (server cũ)
SHOW MASTER STATUS;  -- Lấy binary log position

-- Trên Slave (server mới) - setup replication
CHANGE MASTER TO
  MASTER_HOST='old_server_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;

START SLAVE;

-- Chờ Slave catch up
SHOW SLAVE STATUS\G  -- Kiểm tra Seconds_Behind_Master

-- Khi sync hoàn tất, switch application sang server mới

Giải pháp 2: Sử dụng intermediate server

# Thay vì direct export/import, sử dụng pipe:
mysqldump -u root -p --quick source_db | mysql -h target_server -u root -p target_db

# Điều này giảm disk I/O và downtime

Giải pháp 3: Chia nhỏ quá trình (per-table)

# Export từng bảng riêng biệt
for table in $(mysql -u root -p database -e "SHOW TABLES" | grep -v Tables_in)
do
  mysqldump -u root -p database $table > $table.sql
  # Có thể import từng bảng vào server mới
done

Vấn đề 2: Data Bị Sai Lệch Khi Import

Nguyên nhân

Incomplete export: File bị cắt ngắn (disk hết dung lượng, process bị kill)
Character encoding mismatch: UTF-8 vs Latin1
Foreign key constraint violation: Data không hợp lệ
Duplicate key error: Dữ liệu trùng lặp
Data size mismatch: Kiểu dữ liệu thay đổi

Giải pháp: Verify dữ liệu sau import

# 1. Kiểm tra số lượng records
echo "Source database:"
mysql -u root -p source_db -e "SELECT COUNT(*) FROM users;"

echo "Target database:"
mysql -u root -p target_db -e "SELECT COUNT(*) FROM users;"

# 2. Kiểm tra checksum (chi tiết hơn)
# Trong MySQL, sử dụng:
SELECT MD5(GROUP_CONCAT(MD5(CONCAT_WS(',', id, name, email)) ORDER BY id))
FROM users;

# So sánh giá trị hash này giữa source và target

Best Practice: Tạo migration script

#!/bin/bash

set -e  # Exit nếu có error

SOURCE_DB="source_db"
TARGET_DB="target_db"
TARGET_SERVER="192.168.1.100"
BACKUP_FILE="/tmp/migration_$(date +%Y%m%d_%H%M%S).sql"

echo "Starting migration..."

# 1. Export source
echo "Exporting source database..."
mysqldump -u root -p \
  --quick \
  --lock-tables=false \
  --single-transaction \
  $SOURCE_DB > $BACKUP_FILE

# 2. Verify export
echo "Verifying backup file..."
if [ ! -s $BACKUP_FILE ]; then
  echo "Error: Backup file is empty!"
  exit 1
fi

# 3. Import to target
echo "Importing to target database..."
mysql -h $TARGET_SERVER -u root -p $TARGET_DB < $BACKUP_FILE

# 4. Verify import
echo "Verifying import..."
SOURCE_COUNT=$(mysql -u root -p $SOURCE_DB -e "SELECT COUNT(*) FROM users;" | tail -1)
TARGET_COUNT=$(mysql -h $TARGET_SERVER -u root -p $TARGET_DB -e "SELECT COUNT(*) FROM users;" | tail -1)

if [ "$SOURCE_COUNT" -eq "$TARGET_COUNT" ]; then
  echo "Migration successful! Records: $SOURCE_COUNT"
  rm $BACKUP_FILE
else
  echo "Error: Record count mismatch!"
  echo "Source: $SOURCE_COUNT, Target: $TARGET_COUNT"
  exit 1
fi

Giải pháp: Tùy chọn `--single-transaction`

# Sử dụng single-transaction thay vì lock-tables
mysqldump -u root -p \
  --single-transaction \
  --quick \
  database_name > backup.sql

Tác động:

Sử dụng InnoDB transaction snapshot
Không lock table
Zero downtime cho read/write operations
Yêu cầu: InnoDB engine (không hoạt động với MyISAM)

Bảng so sánh: Các phương pháp Export

Phương pháp	Speed	Downtime	Consistency	Use Case
Standard mysqldump	Chậm	Cao (lock)	Cao	Small databases, one-time backup
--single-transaction	Trung bình	Thấp	Cao	InnoDB databases, production
--lock-tables=false	Nhanh	Thấp	Trung bình	Large databases, tolerate small inconsistency
Replication	N/A	Zero	Cao	Large databases, critical systems
mydumper (parallel)	Rất nhanh	Thấp	Cao	Very large databases

Phần 6: Các Tình Huống Thực Tế

Tình huống 1: Import bị fail giữa chừng

Vấn đề:

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (111)

Giải pháp:

# 1. Kiểm tra MySQL service
sudo systemctl status mysql

# 2. Restart MySQL
sudo systemctl restart mysql

# 3. Resume import từ vị trí bị fail
# Thêm option --force để skip errors (cẩn thận!)
mysql -u root -p database_name < backup.sql --force

Tình huống 2: File backup quá lớn (>2GB)

Vấn đề:

Slow I/O
Khó quản lý
Tốn dung lượng

Giải pháp:

# 1. Nén file
mysqldump -u root -p database_name | gzip > backup.sql.gz

# File 10GB -> 2-3GB khi nén

# 2. Chia thành nhiều file nhỏ
mysqldump -u root -p database_name > backup_$(date +%Y%m%d).sql
split -b 100m backup_*.sql backup_part_

# 3. Sử dụng mydumper (export parallel)
mydumper -u root -p password -B database_name -o ./dump

Tình huống 3: Foreign key constraint violation

Vấn đề:

ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails

Giải pháp:

-- Tạm thời tắt foreign key checks
SET FOREIGN_KEY_CHECKS=0;

-- Chạy import ở đây
-- mysql -u root -p database < backup.sql

-- Bật lại
SET FOREIGN_KEY_CHECKS=1;

-- Kiểm tra dữ liệu
SELECT * FROM table_with_fk WHERE fk_id NOT IN (SELECT id FROM parent_table);

Phần 7: Best Practices Tổng Hợp

Luôn sử dụng --single-transaction cho InnoDB databases

mysqldump -u root -p --single-transaction --quick database > backup.sql

Nén file backup

mysqldump -u root -p database | gzip > backup.sql.gz

Giữ backup nhiều phiên bản

mkdir -p /backup/mysql
mysqldump -u root -p database > /backup/mysql/backup_$(date +%Y%m%d_%H%M%S).sql.gz

Test restore trước khi sử dụng

# Tạo test database
mysql -u root -p -e "CREATE DATABASE test_restore;"
mysql -u root -p test_restore < backup.sql
# Kiểm tra dữ liệu
mysql -u root -p test_restore -e "SELECT COUNT(*) FROM all_tables_sum;"

Sử dụng replication cho production systems
- Zero downtime
- Data consistency đảm bảo
- Có thể rollback nếu cần

Monitor disk space

# Cảnh báo khi disk gần hết
df -h | grep -E '8[5-9]%|9[0-9]%'

Logging và Monitoring

# Ghi log tất cả backup
mysqldump -u root -p database > backup.sql 2>&1 | tee -a /var/log/mysql_backup.log

Kết Luận

Import/Export là một kỹ năng quan trọng cho mọi DBA và developer. Hiểu rõ cách hoạt động, format của dump, cũng như các vấn đề liên quan đến downtime và data consistency sẽ giúp bạn thực hiện các tác vụ migration một cách an toàn và hiệu quả.

Các điểm chính cần nhớ:

Sử dụng --single-transaction cho zero-downtime backup
Luôn verify dữ liệu sau import
Nén file để tiết kiệm dung lượng
Test restore trên database test trước khi áp dụng vào production
Sử dụng replication cho các system critical

Với kiến thức này, bạn có thể tự tin quản lý MySQL database của mình!

Giới thiệu về MySQL Import/Export

Phần 1: Các Khái niệm Cơ bản

Export là gì?

Import là gì?

Tại sao cần Export/Import?

Phần 2: Các Lệnh Cơ bản

Lệnh Export (mysqldump)

Lệnh Import (mysql)

Export với các tùy chọn phổ biến

Phần 3: Lưu ý Quan trọng và Tip & Trick

Lưu ý quan trọng

1. Luôn kiểm tra file backup trước khi xóa cơ sở dữ liệu

2. Đảm bảo có đủ dung lượng disk

3. Character Encoding

Tip & Trick

Tip 1: Sử dụng option --quick để tối ưu hóa performance

Tip 2: Export tất cả database cùng lúc

Tip 3: Sử dụng progress indicator

Tip 4: Parallel export với mydumper (công cụ mạnh mẽ hơn)

Tip 5: Tạo backup tự động với cron job

Tip 6: Verify backup bằng cách restore vào database test

Tip 7: Sử dụng SSH tunnel nếu backup remote server

Phần 4: Hiểu rõ Format MySQL Dump

Cấu trúc của file MySQL Dump

Giải thích chi tiết các phần

1. Header và Metadata

2. Cài đặt Character Set và Timezone

3. Vô hiệu hóa Checks quan trọng

4. Lock Table

5. Restore Settings

Phần 5: Xử lý Downtime và Data Consistency

Vấn đề 1: Downtime khi Export/Import

Nguyên nhân downtime

Ví dụ thực tế: Database 10GB

Giải pháp 1: Zero-downtime migration (sử dụng MySQL Replication)

Giải pháp 2: Sử dụng intermediate server

Giải pháp 3: Chia nhỏ quá trình (per-table)

Vấn đề 2: Data Bị Sai Lệch Khi Import

Nguyên nhân

Giải pháp: Verify dữ liệu sau import

Best Practice: Tạo migration script

Giải pháp: Tùy chọn --single-transaction

Bảng so sánh: Các phương pháp Export

Phần 6: Các Tình Huống Thực Tế

Tình huống 1: Import bị fail giữa chừng

Tình huống 2: File backup quá lớn (>2GB)

Tình huống 3: Foreign key constraint violation

Phần 7: Best Practices Tổng Hợp

Kết Luận

Mục lục

Tip 1: Sử dụng option `--quick` để tối ưu hóa performance

Giải pháp: Tùy chọn `--single-transaction`