MySQL 数据库导入导出命令详解

使用 mysqldump 进行数据库备份与恢复

mysqldump 是 MySQL 最常用的逻辑备份工具，可生成包含建表语句和插入数据的 SQL 文件。适用于全库备份、单表备份或跨平台迁移。

第1步：导出整个数据库

目的：生成包含数据库所有结构和数据的完整 .sql 备份文件。

操作入口：系统命令行终端（需具备数据库访问权限）。

动作：执行 mysqldump 命令。

重要参数：-u 指定用户名，-p 触发密码提示。

# 语法结构
mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql

# 实例：将 abc 数据库导出到当前目录
mysqldump -u root -p abc > abc.sql

第2步：导出指定单表

目的：仅备份数据库中特定的某张表，减小备份体积。

操作入口：系统命令行终端。

动作：在数据库名后追加表名。

# 语法结构
mysqldump -u 用户名 -p 数据库名 表名 > 导出的文件名.sql

# 实例：将 abc 数据库中的 users 表导出
mysqldump -u root -p abc users > abc_users.sql

第3步：仅导出数据库结构

目的：备份建表语句而不包含实际数据，常用于初始化新环境。

操作入口：系统命令行终端。

动作：添加 -d 参数表示无数据，--add-drop-table 会在每个 CREATE TABLE 前添加 DROP TABLE 以保证幂等性。

# 语法结构
mysqldump -u 用户名 -p -d --add-drop-table 数据库名 > 结构.sql

# 实例
mysqldump -u root -p -d --add-drop-table abc > abc_db_structure.sql

第4步：恢复数据库

目的：将备份的 .sql 文件还原到 MySQL 服务器中。

准备条件：

• 已登录 MySQL 客户端。
• 目标数据库已创建（或使用 CREATE DATABASE 新建）。

操作顺序：

# 1. 登录 MySQL
mysql -u root -p

# 2. 选择目标数据库
mysql> USE 数据库名;

# 3. 执行导入源文件
mysql> SOURCE /path/to/file.sql;

注意：如果使用 Windows 路径且报错，可先将 .sql 文件移至 MySQL bin 目录附近，或使用绝对路径。

使用 SELECT...INTO OUTFILE 导出文本数据

当需要将数据导出为 CSV、TSV 等纯文本格式以便其他应用程序读取时，可使用此方法。此方法直接在服务器端写入文件，比 mysqldump 更轻量，适合大数据量字段导出。

第5步：导出数据到文件

目的：查询结果直接保存为分隔文本文件。

操作入口：MySQL 客户端内或支持该语法的连接工具。

动作：编写带有 INTO OUTFILE 子句的 SELECT 语句。

# 默认情况：使用 TAB 分割字段，NEWLINE 分割记录
SELECT * FROM table_name INTO OUTFILE '/tmp/data.txt';

# 自定义分隔符示例：逗号分割，双引号包围字符串，换行分割记录
SELECT * FROM hx_9enjoy 
INTO OUTFILE '/tmp/data_custom.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
';

关键限制：

1. 权限检查：运行 MySQL 的用户必须有对目标文件夹的写权限。
2. 文件安全：为防止意外覆盖，如果目标文件已存在，命令会失败。
3. 不可压缩：无法直接在此命令中生成 .gz 或 .zip 压缩包，需先导出再压缩。

使用 LOAD DATA INFILE 还原文本数据

用于将之前导出的文本文件快速导入表中。相比 INSERT 语句，速度极快。

第6步：从文件导入数据

目的：将格式化文本数据加载回 MySQL 表。

操作入口：MySQL 客户端。

动作：执行 LOAD DATA INFILE 命令，若导出时使用了特殊分隔符，必须保持一致。

# 基本导入
LOAD DATA INFILE '/tmp/data.txt' INTO TABLE table_name;

# 带参数的导入（需匹配导出时的 FIELDS/LINES 定义）
LOAD DATA INFILE '/tmp/data_custom.txt' 
INTO TABLE table_name
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
';

第7步：解决乱码问题

问题：若源文件或目标表的字符集不一致（如 GBK 对 UTF-8），导入可能出现乱码。

解决方案：显式指定 CHARACTER SET。

# 假设原文件为 GBK 编码
LOAD DATA INFILE '/tmp/gbk_data.txt' 
INTO TABLE table_name 
CHARACTER SET gbk;

常见问题排查

• Error Code: 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
• 原因：MySQL 出于安全考虑限制了文件的导出位置。
• 解决：查看 secure_file_priv 变量值，将导出文件写入该允许目录，或在配置文件 my.cnf 中修改该设置（重启生效）。
• 权限被拒绝
• 确认 MySQL 进程用户（如 www-data, mysql）对目标路径具有读写权限。
• 导入中文乱码
• 检查导入语句中是否正确设置了 CHARACTER SET utf8 或 gbk，并确保表结构编码一致。

以上就是 MySQL 用作备份还原的导入和导出命令的详细解析，更多关于数据库迁移与管理的技术资料，欢迎持续关注本站！