数据备份和恢复

MySQL 数据备份与恢复全指南：策略、工具与实践

数据备份是数据库运维的核心环节，旨在应对人为误操作、软硬件故障、安全攻击等场景下的数据丢失风险。MySQL 提供了逻辑备份和物理备份两大类方案，各有适用场景。本文详细解析各类备份与恢复方法，帮助选择合适的策略。

备份的核心场景

备份的必要性体现在以下场景：

数据丢失场景

• 人为误操作（如误删表、更新错误）；
• 软件 Bug 或硬件故障（如磁盘损坏、数据库崩溃）；
• 恶意攻击（如数据篡改、勒索）。

非数据丢失场景

• 时间点恢复（如回滚到某一历史状态）；
• 环境搭建（开发 / 测试环境同步生产数据）；
• 数据迁移（跨服务器、跨版本迁移）。

逻辑备份：基于 SQL 或文本的备份

逻辑备份通过导出数据的逻辑结构（表定义）和内容（SQL 语句或文本） 实现，优势是跨平台性好、可编辑，但速度较慢，适合中小型数据量。

生成 INSERT 语句备份（mysqldump）

mysqldump 是 MySQL 自带的逻辑备份工具，可导出数据库的表结构和数据为 SQL 脚本（包含 CREATE TABLE 和 INSERT 语句）。

核心参数与示例

# 基本语法
mysqldump [选项] 数据库名 [表名] > 备份文件.sql

# 常用参数说明
--add-drop-database  # 备份前先删除数据库（避免冲突）
--lock-all-tables    # 锁定所有表（只读），保证备份一致性（适合 MyISAM）
--single-transaction # 基于事务备份（适合 InnoDB，不锁表，保证一致性）
--master-data=2      # 记录 binlog 位置（主从复制场景，注释掉 CHANGE MASTER 语句）
--events -R --triggers  # 备份事件、存储过程、触发器
-w "条件"            # 按条件导出数据（如只备份某段时间的数据）

实用示例

# 1. 备份单个数据库（含所有表、存储过程、事件）
mysqldump -u root -p --databases test_db --events -R --triggers > test_db_backup.sql

# 2. 备份指定表，按条件过滤数据
mysqldump -u root -p test_db user --where "age > 18" > user_adult_backup.sql

# 3. InnoDB 表一致性备份（不锁表）
mysqldump -u root -p --single-transaction test_db > test_db_innodb_backup.sql

# 4. MyISAM 表一致性备份（锁表防止写入）
mysqldump -u root -p --lock-all-tables test_db > test_db_myisam_backup.sql

恢复方法

通过 mysql 命令或 source 语句执行备份脚本：

# 命令行恢复
mysql -u root -p test_db < test_db_backup.sql

# 登录 MySQL 后恢复
mysql> use test_db;
mysql> source /path/to/test_db_backup.sql;

纯文本备份（字段分隔格式）

将表数据导出为特定分隔符的文本文件（如 CSV），占用空间小，适合大批量数据迁移。

导出方法

（1）SELECT ... INTO OUTFILE

直接在 MySQL 中执行，导出单表数据为文本：

-- 导出 user 表，逗号分隔字段，双引号包裹字符串，换行分隔行
SELECT * FROM user INTO OUTFILE '/tmp/user_backup.txt'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

（2）mysqldump -T 选项

通过 mysqldump 同时导出表结构（.sql）和数据（.txt）：

# 导出 test_db 的 user 表到 /tmp/backup 目录
mysqldump -u root -p -T /tmp/backup test_db user \
--fields-terminated-by=',' \
--fields-enclosed-by='"' \
--lines-terminated-by='\n';
# 生成文件：/tmp/backup/user.sql（表结构）和 /tmp/backup/user.txt（数据）

恢复方法

（1）LOAD DATA INFILE

登录 MySQL 后导入文本文件：

-- 导入 /tmp/user_backup.txt 到 user 表
LOAD DATA INFILE '/tmp/user_backup.txt' INTO TABLE user
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

（2）mysqlimport 工具

命令行批量导入，支持多表并发：

# 导入 user.txt 到 test_db 的 user 表
mysqlimport -u root -p test_db /tmp/backup/user.txt \
--fields-terminated-by=',' \
--fields-enclosed-by='"';

# 并发导入多个表（2 个线程）
mysqlimport -u root -p test_db --user-threads=2 \
/tmp/backup/user.txt /tmp/backup/order.txt;

物理备份：基于文件系统的备份

物理备份直接复制数据库的物理文件（如数据文件、日志文件），速度快，适合大型数据库，但依赖存储引擎和操作系统。

MyISAM 引擎备份

MyISAM 表由 3 类文件组成：

• .frm：表结构；
• .MYD：数据文件；
• .MYI：索引文件。

备份工具：mysqlhotcopy

专为 MyISAM 设计的热备份工具（需 Perl 环境），通过锁表 + 复制文件实现：

# 备份 test_db 的 user 表到 /tmp/backup 目录
mysqlhotcopy -u root -p test_db.user /tmp/backup/

# 备份整个数据库
mysqlhotcopy -u root -p test_db /tmp/backup/

恢复方法

直接复制备份文件到目标数据库目录：

# 停止 MySQL
systemctl stop mysqld

# 复制备份文件到数据目录（如 /var/lib/mysql/test_db）
cp /tmp/backup/test_db/* /var/lib/mysql/test_db/

# 授权（确保 MySQL 有权限访问）
chown -R mysql:mysql /var/lib/mysql/test_db/

# 启动 MySQL
systemctl start mysqld

InnoDB 引擎备份

InnoDB 表文件因配置不同而有差异：

• 共享表空间：数据存储在 ibdata1 等文件；
• 独立表空间（innodb_file_per_table=ON）：每个表对应 .ibd 文件（数据 + 索引）和 .frm 文件（表结构）。

（1）冷备份（数据库关闭状态）

适合停机维护场景，直接复制数据文件和日志文件：

# 1. 停止 MySQL
systemctl stop mysqld

# 2. 复制所有数据文件（默认目录 /var/lib/mysql）
cp -r /var/lib/mysql /tmp/innodb_backup/

# 3. 恢复时覆盖目标数据目录
rm -rf /var/lib/mysql/*
cp -r /tmp/innodb_backup/mysql/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

（2）热备份（数据库运行状态）

使用 xtrabackup（Percona 开源工具），支持 InnoDB 热备份（不锁表）：

# 安装 xtrabackup（以 CentOS 为例）
yum install percona-xtrabackup-80

# 1. 全量备份
xtrabackup --user=root --password=123456 --backup --target-dir=/tmp/innodb_full_backup/

# 2. 准备备份（确保数据一致性）
xtrabackup --user=root --password=123456 --prepare --target-dir=/tmp/innodb_full_backup/

# 3. 恢复（停止 MySQL 后）
xtrabackup --copy-back --target-dir=/tmp/innodb_full_backup/
chown -R mysql:mysql /var/lib/mysql/

备份策略选择

备份类型	工具	优势	劣势	适用场景
逻辑备份	mysqldump	跨平台、可编辑、支持条件导出	速度慢，不适合 TB 级数据	中小型数据库、需编辑备份内容
逻辑备份	文本文件	空间小、适合批量迁移	仅支持数据，需单独备份表结构	大数据量迁移、跨数据库导入
物理备份	mysqlhotcopy	速度快（MyISAM 专用）	仅支持 MyISAM，依赖文件系统	MyISAM 引擎、需要快速备份
物理备份	xtrabackup	热备份、支持 InnoDB、速度快	配置复杂，移植性差	大型 InnoDB 数据库、不停机备份

最佳实践

1. 定期备份：根据数据重要性设置周期（如每日全量 + 增量备份）；
2. 多副本存储：备份文件异地存储（如云存储、离线硬盘），防止单点故障；
3. 验证备份有效性：定期恢复测试，确保备份文件可正常使用；
4. 结合 binlog 实现时间点恢复：全量备份 + binlog 增量备份，可回滚到任意时间点