技术博客 Blog 数据库备份

mysqldump 高效导出 MySQL 数据库指导

下载免费试用版

适用于多种数据备份

mysqldump 高效导出 MySQL 数据库指导

2026-07-10

在日常维护工作中，经常需要执行数据导出操作，mysqldump 是一种常用的数据导出工具。它内置了多种功能参数，本文将列举一些常用的使用方法。

什么是 mysqldump？

mysqldump 是 MySQL 的一个客户端程序，用于导出数据库。它主要生成一系列 SQL 语句，并将这些语句封装成一个文件。该文件包含重建数据库所需的所有 SQL 命令，例如 CREATE DATABASE、CREATE TABLE、INSERT 等。它可用于轻量级、快速的数据库迁移或恢复，是 MySQL 数据库执行逻辑备份的一种方式。

如何使用 mysqldump 导出 MySQL 数据库？

1. 导出所有数据库

此命令导出所有数据库，包括系统数据库。

mysqldump -uroot -proot --all-databases > /tmp/all.sql

2. 从数据库 db1 和 db2 导出全部数据

mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql

导出多个数据库。该选项之后的所有后续参数均被视为数据库名称。

在创建每个数据库之前，添加一条 DROP DATABASE 语句。

mysqldump -uroot -p --databases test --add-drop-database

3. 从 db1 导出表 a1 和 a2

注意：导出指定的数据表仅适用于单个数据库，且其导出内容与导出整个数据库不同。指定数据表的导出文本不包含数据库创建语句，仅包含DROP TABLE、CREATE TABLE和INSERT语句。

mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql

覆盖 --databases 参数，并指定要导出的表。

mysqldump -uroot -p --host=localhost --databases test --tables test

导出所有表空间。

mysqldump -uroot -p --databases test --all-tablespaces

导出时排除所有表空间信息。

mysqldump -uroot -p --databases test --no-tablespaces

在创建每个数据表之前添加一条 DROP TABLE 语句（默认启用，可使用 --skip-add-drop-table 参数禁用）。

mysqldump -uroot -p --databases test

mysqldump -uroot -p --databases test --skip-add-drop-table

4. 条件导出：从 db1 数据库的 a1 表中导出 id=1 的记录

条件导出只能在单个表上执行。

仅导出符合指定 WHERE 条件的记录。如果该条件包含命令解释器、空格或特殊字符，则必须加引号。

mysqldump -uroot -p --host=localhost --databases db1 --where="user='root'"

5. 仅导出表结构，不包含数据

mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql

仅导出数据，不添加 CREATE DATABASE 语句。

mysqldump -uroot -p --host=localhost --databases db1 --no-create-db

仅导出数据，不添加 CREATE TABLE 语句。

mysqldump -uroot -p --host=localhost --databases db1 --no-create-info

排除特定的数据表。若需忽略多个数据表，请为每个数据表分别指定该选项。每个数据表都必须同时指定数据库名和表名，例如：--ignore-table=database.table1 --ignore-table=database.table2 ...

mysqldump -uroot -p --host=localhost --databases db1 --ignore-table=mysql.user

导出数据时使用延迟插入模式 (INSERT DELAYED)。

mysqldump -uroot -p --databases db1 --delayed-insert

使用带有多个 VALUES 子句的 INSERT 语句。这可以减小导出文件大小并加快导入速度。默认启用；使用 --skip-extended-insert 可禁用此功能。

mysqldump -uroot -p --databases db1

mysqldump -uroot -p --databases db1 --skip-extended-insert

6. 服务器间导出与导入数据

mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2

这意味着将服务器 h1 上数据库 db1 的所有数据导出，并导入到服务器 h2 上的数据库 db2 中。数据库 db2 必须已存在，否则将发生错误。

使用命名管道连接到 MySQL。

mysqldump -uroot -p --host=localhost --all-databases --pipe

mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp

启用客户端与服务器之间的通信压缩。

mysqldump -uroot -p --all-databases --compress

7. 将主数据库的二进制日志位置和文件名追加到导出文件中

在从服务器上运行此选项，等同于执行 SHOW SLAVE STATUS。

将其设置为 1 会在数据文件中输出一条 CHANGE MASTER 命令。

将其设置为 2 会在 CHANGE MASTER 命令前添加注释。

此选项启用 --lock-all-tables，除非指定了 --single-transaction。

执行后，--lock-tables 会自动禁用。--dump-slave 的默认值为 1。

mysqldump -uroot -proot --dump-slave=1 --database db1 > /tmp/db1.sql

mysqldump -uroot -proot --dump-slave=2 --database db1 > /tmp/db1.sql

导出事件。

mysqldump -uroot -p --all-databases --events

8. 导出前清空日志

注意：若导出多个数据库（使用 --databases 或 --all-databases 参数），日志将按每个数据库分别刷新；若使用 --lock-all-tables 或 --master-data，则日志仅刷新一次，同时锁定所有表。如需在导出过程中同步刷新日志，请将 --lock-all-tables 或 --master-data 与 --flush-logs 一起使用。

mysqldump -uroot -p --all-databases --flush-logs

9. 确保导出期间状态一致

导出数据前，此选项会执行一条 BEGIN SQL 语句。BEGIN 不会阻塞任何应用程序，并确保导出过程中的数据库一致性。该选项仅适用于多版本存储引擎（例如 InnoDB）。此选项与 --lock-tables 互斥，因为 LOCK TABLES 会隐式提交所有待处理事务。导出大表时，建议配合使用 --quick 选项。

mysqldump -uroot -p --host=localhost --all-databases --single-transaction

10. 导出存储过程和函数

mysqldump -uroot -p --host=localhost --all-databases --routines

另一种方法：使用 SELECT...INTO OUTFILE 导出 MySQL 数据

SELECT...INTO OUTFILE 是 MySQL 中用于将查询结果导出到文件的语法。在 MySQL 中，您可使用该语句轻松将数据导出为文本文件。

基本用法：

SELECT column1, column2, ...  
INTO OUTFILE 'file_path'  
FROM your_table  
WHERE your_conditions;

示例：

SELECT id, name, email  
INTO OUTFILE '/tmp/user_data.csv'  
FIELDS TERMINATED BY ','  
LINES TERMINATED BY '\n'  
FROM users;

在上述 SQL 语句中，我们从 users 表中选取了 id、name 和 email 列，并将结果写入 /tmp/user_data.csv 文件。FIELDS TERMINATED BY ',' 指定列分隔符（逗号），LINES TERMINATED BY '\n' 指定行分隔符（换行符）。

重要提示：

执行 SELECT...INTO OUTFILE 需要具备相应的权限，且输出文件所在的目录必须是 MySQL 服务器具有写入权限的位置。

在以下示例中，我们将数据从 runoob_tbl 表导出到 /tmp/runoob.txt 文件中：

mysql> SELECT * FROM runoob_tbl  
    -> INTO OUTFILE '/tmp/runoob.txt';

您可以使用命令选项指定输出格式。以下示例以 CSV 格式导出数据：

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'  
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'  
    -> LINES TERMINATED BY '\r\n';

SELECT ... INTO OUTFILE 的特性：

LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的逆向操作。若要将数据库数据写入文件，请使用 SELECT ... INTO OUTFILE；若要将该文件重新读入数据库，则使用 LOAD DATA INFILE。
以 SELECT...INTO OUTFILE 'file_name' 形式编写的 SELECT 语句会将所选行写入文件。由于该文件在服务器主机上创建，因此您必须拥有 FILE 权限才能使用此语法。
输出文件不得已存在，以防止数据篡改。
您需要一个具有服务器登录权限的账户才能检索该文件。否则，SELECT ... INTO OUTFILE 将无法使用。
在 UNIX 系统上，文件创建后即可读取，且其权限归属于 MySQL 服务器。这意味着您虽可读取该文件，但可能无法删除它。