MySQL 数据备份与导入导出命令详解

一、mysqldump：通用逻辑备份方案

mysqldump 是 MySQL 社区版中最常用的逻辑备份客户端实用程序。它通过向服务器发送 SQL 语句（如 CREATE TABLE 和 INSERT），生成一个可恢复的 SQL 脚本文件。相比物理备份，逻辑备份具有跨版本兼容性好、便于查看和修改内容的优势。

语法基础

调用 mysqldump 主要有三种形式：

• 备份指定库中的部分表：mysqldump [options] db_name [tables]
• 备份指定列表中的多个数据库：mysqldump [options] --database DB1 [DB2 DB3...]
• 备份服务器上的所有数据库：mysqldump [options] --all-databases

第1步：备份单个数据库（结构与数据）

适用于日常关键业务数据的完整快照。

# 导出 mysql 库的结构和数据至 mysql_backup.sql
# -u: 用户名, -p: 密码提示符(生产环境建议不直接显示在命令中)
mysqldump -uroot -p123456 mysql > mysql_backup.sql

注意：默认情况下，生成的 SQL 文件中不包含创建目标数据库本身（CREATE DATABASE）的语句。如果需要在另一台全新服务器上恢复，需先手动创建同名数据库。

第2步：批量备份多个指定数据库

当需要同时保护多个业务库时，使用 --database 选项。

# 同时备份 mysql 和 test 库
mysqldump -u root -p123456 --database mysql test > my_databases.sql

区别：此方式生成的 SQL 文件中会包含创建这两个数据库的语句，恢复时更为便捷。

第3步：执行全库备份

# 备份所有系统库及用户库
mysqldump -u root -p123456 --all-databases > all_databases.sql

第4步：仅导出表结构（不含数据）

适用于代码版本控制或生成数据库 schema 文档的场景。

# -d: 不导出数据, --add-drop-table: 每条 create 前加 drop table 语句
mysqldump -u root -p123456 -d mysql > mysql_define.sql

第5步：带压缩的全量备份

对于大型数据库，直接在导出过程中使用 gzip 压缩，可大幅节省磁盘空间并提高 I/O 效率。

# 使用管道将输出重定向至 gzip 压缩
mysqldump -u root -p123456 mysql | gzip > mysql.sql.gz

二、还原逻辑备份文件

将上述命令生成的备份数据恢复到 MySQL 服务器中。操作前请确保目标数据库已存在（除非备份文件中包含建库语句）且权限充足。

方式 A：命令行标准输入

# 从 SQL 脚本还原
mysql -u root -p123456 db_name < backup-file.sql

方式 B：SQL 内部源命令

# 在 MySQL 客户端内部执行
mysql -e "source /path-to/backup-file.sql" db_name

方式 C：还原压缩备份

# 先解压再导入，或使用管道实时处理
gunzip < mysql.sql.gz | mysql -u root -p123456 test

风险提示：逻辑备份还原速度受网络带宽和服务器性能影响较大，且无法保证数据的一致性（如果在备份期间有并发写入）。如需严格一致性，请配合锁表或使用二进制日志（Binlog）进行增量修复。

三、高速文本数据交换：SELECT INTO OUTFILE

SELECT ... INTO OUTFILE 是 MySQL 用于将查询结果集以纯文本格式导出到服务器主机文件系统的高效命令。它是 LOAD DATA INFILE 的逆向操作，常用于与其他应用程序（如报表工具、爬虫脚本）交换数据。

安全限制：出于安全考虑，该命令只能写入服务器指定的目录（通常由 secure_file_priv 变量控制），且不能覆盖已有文件。

实战：导出为 CSV 格式

以下命令将 mysql.user 表导出为标准的逗号分隔值文件，适用于 Excel 或其他软件打开。

SELECT * INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ','        # 字段间用逗号分隔
OPTIONALLY ENCLOSED BY '"'     # 字符串字段可选地用双引号包裹
LINES TERMINATED BY '\n'       # 记录间换行
FROM mysql.user;

局限性：此方法仅导出数据内容，不导出表结构（DDL）。若需在无结构的数据库中还原，必须搭配 LOAD DATA INFILE 使用。

四、高速文本数据导入：LOAD DATA INFILE

LOAD DATA INFILE 是 MySQL 中最快的数据加载方式，比传统的 INSERT 语句快约 20 倍。它读取服务器本地（或指定路径）的文本文件，并按指定格式将其解析后插入表中。

实战：将 CSV 数据导入表

假设之前已从 mysql.user 导出了 /tmp/result.txt，现在将其导入到 test 数据库的 user 表中：

LOAD DATA LOCAL INFILE '/tmp/result.txt' 
INTO TABLE test.user
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'

关键参数说明

• LOCAL：指示客户端从客户端机器读取文件而非服务器机器。这在跨机器传输数据时非常有用，但需要服务器配置允许。
• REPLACE INTO vs INSERT INTO：如果使用 REPLACE，遇到主键冲突时会删除旧记录并插入新记录；使用 INSERT 则会在冲突时报错。

五、文件格式配置速查

在编写 SELECT ... INTO OUTFILE 和 LOAD DATA INFILE 时，格式定义必须一致，否则会导致数据错位。核心配置如下：

以上就是 MySQL 导入导出命令的详细解析，更多关于数据库优化的资料请关注本站其它相关文章！