MySQL 数据库导出命令速查手册
前言
在数据库管理中，数据导出是一项至关重要的操作。无论是为了数据备份、迁移、分析，还是与其他系统进行数据交换，掌握高效、准确的数据导出方法都必不可少。MySQL 提供了多种强大的导出工具和命令，其中最核心、最灵活的当属 mysqldump。此外，SELECT ... INTO OUTFILE 命令也在特定场景下非常有用。
本手册旨在为开发者和数据库管理员提供一个全面、易于查阅的 MySQL 导出命令指南。
1. mysqldump：通用数据导出利器
mysqldump 是 MySQL 自带的逻辑备份工具，它可以生成一组 SQL 语句，执行这些语句可以重建原始的数据库对象和数据。这是最常用、功能最强大的导出工具。
1.1 核心语法
mysqldump 的基本命令格式如下：
bash
mysqldump -u [用户名] -p[密码] [选项] [数据库名] [表名] > [导出文件名.sql]
关键参数说明:
* -u [用户名]: 指定连接数据库的用户名。
* -p[密码]: 指定用户的密码。注意：-p 和密码之间不能有空格。如果留空，系统会提示你输入密码，这是更安全的方式。
* -h [主机名]: 指定要连接的数据库服务器地址，默认为 localhost。
* -P [端口号]: 指定数据库服务器的端口号，默认为 3306。
* > [导出文件名.sql]: 将标准输出重定向到文件。这是 mysqldump 生成 SQL 脚本的标准方式。
1.2 常用导出场景
1.2.1 导出单个完整数据库（包含结构和数据）
这是最常见的需求，用于完整备份一个数据库。
bash
mysqldump -u root -p mydatabase > mydatabase_backup.sql
1.2.2 只导出数据库结构（不含数据）
当你需要复制一个数据库的表结构给新项目时，此命令非常有用。
bash
mysqldump -u root -p --no-data mydatabase > mydatabase_schema.sql
1.2.3 只导出数据（不含建表语句）
适用于只需要数据，而目标数据库已有相同结构的场景。
bash
mysqldump -u root -p --no-create-info mydatabase > mydatabase_data.sql
1.2.4 导出一个或多个指定表
如果你只需要备份或迁移特定的几个表。
“`bash
导出一个表
mysqldump -u root -p mydatabase users > users_table.sql
导出多个表
mysqldump -u root -p mydatabase users orders products > tables_backup.sql
“`
1.2.5 导出所有数据库
一次性备份服务器上的所有数据库。
bash
mysqldump -u root -p --all-databases > all_databases_backup.sql
1.2.6 导出时排除某些表
使用 --ignore-table 选项，可以备份数据库的同时，忽略掉不需要的表（例如日志表、临时表）。
bash
mysqldump -u root -p mydatabase --ignore-table=mydatabase.logs --ignore-table=mydatabase.temp_data > mydatabase_partial_backup.sql
1.2.7 导出存储过程、函数、触发器和事件
默认情况下，mysqldump 不会导出存储过程和函数。你需要显式指定它们。
bash
mysqldump -u root -p mydatabase --routines --triggers --events > mydatabase_with_routines.sql
* --routines: 导出存储过程和函数。
* --triggers: 导出触发器（现代版本默认开启，但显式指定更保险）。
* --events: 导出事件。
1.2.8 保证 InnoDB 数据一致性导出
对于使用 InnoDB 存储引擎的表，--single-transaction 选项可以在不锁表的情况下，创建一个快照，从而保证导出数据的一致性。
bash
mysqldump -u root -p --single-transaction mydatabase > mydatabase_consistent_backup.sql
此选项会在开始导出前，向服务器发送一个 START TRANSACTION 语句。它只对事务性存储引擎（如 InnoDB）有效，对 MyISAM 等非事务性引擎无效。
1.3 重要选项详解
| 选项 | 别名 | 描述 |
|---|---|---|
--all-databases |
-A |
导出所有数据库。 |
--databases |
-B |
指定要导出的一个或多个数据库，会在导出文件中包含 CREATE DATABASE 和 USE 语句。 |
--no-data |
-d |
只导出数据库的结构定义，不包含任何行数据。 |
--no-create-info |
-t |
只导出数据，不添加 CREATE TABLE 语句。 |
--tables |
覆盖 --databases 或 -B 选项，指定要导出的表名列表。 |
|
--where |
-w |
只导出满足给定 WHERE 条件的记录。例如: --where="user_id > 100"。 |
--single-transaction |
为 InnoDB 提供一致性快照备份，避免锁表。 | |
--routines |
-R |
导出存储过程和函数。 |
--triggers |
导出表的触发器。 | |
--events |
-E |
导出事件调度器的事件。 |
--default-character-set |
指定导出时使用的字符集，如 utf8mb4，避免乱码问题。 |
|
--set-gtid-purged=OFF |
在使用 GTID 复制的环境中，从主库导出数据到从库时，添加此选项可以避免从库出错。 | |
--master-data[=value] |
用于主从复制。自动记录导出时刻的二进制日志（binlog）文件名和位置。value=1 会生成 CHANGE MASTER 语句，value=2 会将其注释掉。 |
|
--compress |
-C |
在客户端和服务器之间传输数据时启用压缩。 |
2. SELECT ... INTO OUTFILE：快速导出为文本文件
这个 SQL 命令允许你将一个查询的结果直接快速地保存到服务器主机上的一个文件中。它非常适合将表数据导出为 CSV、TSV 或其他纯文本格式，以便于用 Excel 打开或导入到其他系统中。
2.1 核心语法
sql
SELECT column1, column2, ...
FROM table_name
WHERE condition
INTO OUTFILE '/path/to/your/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
子句说明:
* INTO OUTFILE '/path/to/your/file.csv': 指定导出文件的绝对路径和名称。
* FIELDS TERMINATED BY ',': 定义字段之间的分隔符，CSV 文件通常用逗号。
* OPTIONALLY ENCLOSED BY '"': 定义包围字段的字符，通常用于包含特殊字符（如逗号）的字符串字段。
* LINES TERMINATED BY '\n': 定义每条记录的结束符（换行符）。
2.2 导出场景示例
将 users 表中所有用户数据导出为 CSV 文件：
sql
SELECT user_id, user_name, email, registration_date
FROM users
INTO OUTFILE '/var/lib/mysql-files/users_export.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
2.3 限制与安全注意事项
-
secure_file_priv变量限制:
出于安全考虑，MySQL 通过secure_file_priv全局变量限制了文件导入和导出的目录。- 如果该变量值为空，则没有限制。
- 如果为
NULL（默认值），则禁止所有导入导出操作。 - 如果为一个目录路径（例如
/var/lib/mysql-files/），则文件必须位于该目录下。
你可以通过SHOW VARIABLES LIKE 'secure_file_priv';来查看当前设置。
-
服务器端操作:
SELECT ... INTO OUTFILE是一个服务器端操作。文件是创建在 MySQL 服务器的主机上，而不是你执行命令的客户端机器上。你必须拥有对服务器目标目录的写权限。 -
权限要求:
执行此命令的用户必须拥有FILE权限。 -
文件不能覆盖:
出于安全原因，此命令不能覆盖一个已存在的文件。你必须先手动删除旧文件。
3. 使用图形化界面（GUI）工具
对于不习惯命令行的用户，市面上有很多优秀的 GUI 工具也提供了方便的导出功能。这些工具通常将 mysqldump 的复杂选项封装在用户友好的界面中。
- phpMyAdmin: Web 界面的 MySQL 管理工具，在 “导出” 标签页中可以选择数据库、表、格式（SQL, CSV, XML等）以及各种高级选项。
- Navicat: 功能强大的跨平台数据库管理工具，右键点击数据库或表，选择 “转储 SQL 文件…” 或 “导出向导”，可以进行非常详细的配置。
- DBeaver: 开源的通用数据库工具，同样支持右键点击对象，在 “工具” -> “备份/恢复” 中进行导出操作。
- DataGrip: JetBrains 出品的数据库 IDE，提供了强大的导出功能，支持多种格式，并且可以高度自定义。
这些工具的导出功能本质上还是调用了 mysqldump 或者类似 SELECT ... INTO OUTFILE 的逻辑，但提供了更直观的操作体验。
总结
| 工具/命令 | 优点 | 缺点 | 最佳适用场景 |
|---|---|---|---|
mysqldump |
功能最全面、最灵活，支持结构、数据、存储过程等所有对象；跨平台，易于脚本化自动化。 | 命令选项较多，学习曲线稍陡；对于超大型数据库（TB级别），恢复速度可能较慢。 | 日常备份、数据库迁移、版本控制、开发环境搭建。 |
SELECT ... INTO OUTFILE |
速度极快，直接从存储引擎读取数据写入文件；格式自定义灵活。 | 只能导出单个查询结果；受 secure_file_priv 限制；文件生成在服务器端；需要 FILE 权限。 |
将表数据导出为 CSV/TXT 等文本格式，用于数据分析或与其他系统交换数据。 |
| GUI 工具 | 操作直观、简单，无需记忆复杂命令；通常集成了多种导出格式。 | 依赖图形界面；不如命令行灵活，不易于进行大规模自动化。 | 快速的手动导出、数据探索、给非技术人员使用。 |
选择哪种导出方式取决于你的具体需求。对于需要自动化和精确控制的场景，mysqldump 是不二之选。对于需要快速生成文本文件的场景，SELECT ... INTO OUTFILE 效率最高。而对于日常的手动操作，GUI 工具则能提供极大的便利。