使用命令行导入和导出MySQL数据库

掌握从命令行进行MySQL数据库导入和导出操作，是任何数据库管理员或后端工程师不可或缺的技能。`mysqldump`工具将数据库导出为可移植的`.sql`文件，其中包含完整重建架构和数据所需的所有DDL和DML语句，而`mysql`客户端命令则处理相反的操作——将该文件流式传输回正在运行的MySQL实例。

本指南涵盖所有实际场景：单数据库导出、多数据库转储、仅结构备份、压缩传输、字符集处理以及安全导入工作流——包括在生产环境中导致静默数据损坏或恢复失败的边缘情况。

前提条件

在执行本指南中的任何命令之前，请验证以下内容：

• MySQL Server（5.7、8.0或8.4）已安装且`mysqld`进程正在运行
• `mysqldump`和`mysql`二进制文件位于系统`PATH`中（使用`which mysqldump`确认）
• 您持有的MySQL账户至少具有导出所需的`SELECT`、`LOCK TABLES`、`SHOW VIEW`和`TRIGGER`权限；导入所需的`CREATE`、`INSERT`、`ALTER`和`DROP`权限
• 目标位置有足够的磁盘空间——压缩转储在导入时可能扩展5–10倍
• 您拥有服务器的shell访问权限（本地终端、SSH或托管的VPS Hosting环境）

使用mysqldump导出数据库

`mysqldump`是MySQL捆绑的标准逻辑备份工具。它将数据库对象序列化为人类可读的SQL脚本。与Percona XtraBackup等物理备份工具不同，`mysqldump`与存储引擎无关，可跨MySQL版本甚至MariaDB分支使用。

1. 导出单个数据库

“`bash

mysqldump -u [username] -p [database_name] > [filename].sql

“`

参数说明：

• `-u [username]` — 用于连接的MySQL账户
• `-p` — 触发交互式密码提示（切勿在共享系统上以`-p[password]`方式内联传递密码；它在`ps aux`输出中可见）
• `[database_name]` — 要导出的目标架构
• `> [filename].sql` — 将标准输出重定向到输出文件

示例：

“`bash

mysqldump -u root -p mydatabase > mydatabase_backup.sql

“`

这将生成一个包含`CREATE TABLE`、`INSERT`和`ALTER TABLE`语句的文件，足以从头重建`mydatabase`。

关键边缘情况：默认情况下，`mysqldump`在转储开始时会获取全局读锁（`FLUSH TABLES WITH READ LOCK`）。在高流量InnoDB服务器上，请改用`–single-transaction`以在不阻塞写入的情况下获取一致快照：

“`bash

mysqldump -u root -p –single-transaction mydatabase > mydatabase_backup.sql

“`

`–single-transaction`仅在InnoDB表上可靠运行。如果您的数据库包含MyISAM表，则锁定是不可避免的。

2. 导出多个数据库

“`bash

mysqldump -u [username] -p –databases db1 db2 > multiple_databases_backup.sql

“`

`–databases`标志指示`mysqldump`在输出中包含`CREATE DATABASE`和`USE`语句，使转储自包含。如果没有此标志，这些语句将被省略，转储将假定在导入时已选择目标数据库。

示例：

“`bash

mysqldump -u root -p –databases db1 db2 > multiple_databases_backup.sql

“`

3. 导出所有数据库

“`bash

mysqldump -u root -p –all-databases > all_databases_backup.sql

“`

这将导出连接用户可以访问的每个架构，包括`mysql`、`information_schema`和`performance_schema`系统数据库。避免跨主要MySQL版本边界导入系统数据库——MySQL 5.7和8.0之间的权限表架构发生了重大变化，将旧的`mysql`架构导入全新的8.0实例将损坏身份验证。

要排除系统架构：

“`bash

mysqldump -u root -p –all-databases

–ignore-table=mysql.user

–ignore-table=mysql.db

> all_user_databases_backup.sql

“`

4. 仅导出表结构（无数据）

“`bash

mysqldump -u root -p –no-data mydatabase > structure_only.sql

“`

这对于架构版本控制、代码审查或搭建空白暂存环境非常有价值。输出仅包含`CREATE TABLE`、`CREATE VIEW`、`CREATE PROCEDURE`及类似的DDL——不含`INSERT`行。

5. 导出特定表

“`bash

mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

“`

请注意，以这种方式导出特定表时，`mysqldump`不包含`CREATE DATABASE`或`USE`语句。您必须确保目标数据库在导入前已存在。

6. 导出存储过程、触发器和事件

默认情况下，`mysqldump`包含触发器，但省略存储过程、函数和计划事件。要进行完整的应用程序备份：

“`bash

mysqldump -u root -p

–routines

–triggers

–events

–single-transaction

mydatabase > full_backup.sql

“`

忘记`–routines`是导致应用程序恢复失败最常见的原因之一——架构和数据存在，但业务逻辑丢失。

7. 压缩导出

将输出直接通过`gzip`管道传输，可将文件大小减少60–80%：

“`bash

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

“`

对大型数据库进行最大压缩（以CPU时间为代价）：

“`bash

mysqldump -u root -p mydatabase | gzip -9 > mydatabase_backup.sql.gz

“`

使用mysql客户端导入数据库

`mysql`命令行客户端通过stdin重定向接受SQL文件，并针对目标服务器按顺序执行每条语句。

1. 导入到现有数据库

“`bash

mysql -u [username] -p [database_name] < [filename].sql

“`

示例：

“`bash

mysql -u root -p mydatabase < mydatabase_backup.sql

“`

重要提示：如果`.sql`文件是使用`–databases`或`–all-databases`导出的，它已经包含`CREATE DATABASE`和`USE`指令。在这种情况下，不要在命令行上指定数据库名称——这样做会产生冲突：

“`bash

mysql -u root -p < all_databases_backup.sql

“`

2. 导入到新数据库

目标数据库必须在导入之前存在。MySQL不会从裸表转储中自动创建它。

步骤1 — 创建数据库：

“`bash

mysql -u root -p -e "CREATE DATABASE newdatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

步骤2 — 导入转储：

“`bash

mysql -u root -p newdatabase < mydatabase_backup.sql

“`

始终在创建数据库时明确指定字符集和排序规则。依赖服务器默认值是编码不匹配的常见原因，尤其是在具有不同`character_set_server`配置的服务器之间迁移时。

3. 导入压缩转储

“`bash

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

或使用`zcat`（在大多数Linux发行版上等效）：

“`bash

zcat mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

4. 带进度监控的导入

对于大型转储，标准`mysql`客户端不提供任何反馈。使用`pv`（管道查看器）显示实时进度条：

“`bash

pv mydatabase_backup.sql | mysql -u root -p mydatabase

“`

使用`apt install pv`或`yum install pv`安装`pv`。在处理多GB级生产数据库的Dedicated Server上，这种可见性在运营上至关重要。

mysqldump与其他备份方法的比较

对于在托管的VPS with cPanel上运行多个生产数据库的环境，`mysqldump`由于其兼容性和简单性，仍然是最广泛支持的选项。

高级配置和边缘情况

正确处理字符集

字符集不匹配是导致导入损坏的不成比例的主要原因。最安全的方法是在每个阶段都明确指定：

“`bash

mysqldump -u root -p

–default-character-set=utf8mb4

mydatabase > mydatabase_backup.sql

“`

“`bash

mysql -u root -p

–default-character-set=utf8mb4

mydatabase < mydatabase_backup.sql

“`

注意：MySQL中的`utf8`是一个3字节子集，无法存储4字节Unicode字符（表情符号、某些CJK表意文字）。新数据库始终使用`utf8mb4`。

加速大型导入

默认情况下，MySQL在转储中的每条`INSERT`语句后执行完整提交。对于大型数据集，这会极其缓慢。在导入会话开始时添加以下内容：

“`bash

mysql -u root -p mydatabase <<EOF

SET foreign_key_checks = 0;

SET unique_checks = 0;

SET autocommit = 0;

SOURCE /path/to/mydatabase_backup.sql;

COMMIT;

SET foreign_key_checks = 1;

SET unique_checks = 1;

EOF

“`

或者，使用`–extended-insert`（默认启用）和`–disable-keys`导出，以批量插入并在数据加载后延迟索引重建。

使用Cron自动化备份

`/etc/cron.d/mysql-backup`中的生产级自动备份条目：

“`bash

0 2 * * * root mysqldump -u backup_user -p'StrongPass'

–single-transaction –routines –triggers –events

mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz

“`

使用具有最低所需权限的专用MySQL用户，而不是`root`。使用以下命令创建：

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

使用.my.cnf保护凭据

在命令行上传递密码会将其暴露在shell历史记录和进程列表中。将凭据存储在`~/.my.cnf`中：

“`ini

[client]

user=root

password=YourSecurePassword

“`

立即设置权限：

“`bash

chmod 600 ~/.my.cnf

“`

完成此设置后，所有`mysqldump`和`mysql`命令将自动获取凭据，无需`-u`和`-p`标志。

远程数据库导出

从远程MySQL服务器转储数据库：

“`bash

mysqldump -h remote.server.com -P 3306 -u remoteuser -p remotedatabase > remote_backup.sql

“`

确保远程MySQL实例允许来自您IP地址的连接，并且防火墙中端口3306已开放。对于加密传输，通过SSH隧道：

“`bash

ssh -L 3307:127.0.0.1:3306 user@remote.server.com -N &

mysqldump -h 127.0.0.1 -P 3307 -u remoteuser -p remotedatabase > remote_backup.sql

“`

实用决策矩阵

关键技术要点

• 对InnoDB数据库始终使用`–single-transaction`，以避免在导出期间阻塞应用程序写入
• 始终明确指定`utf8mb4`——切勿依赖服务器默认字符集假设
• 在每次完整应用程序备份中包含`–routines`、`–triggers`和`–events`，否则可能丢失业务逻辑
• 切勿跨主要MySQL版本边界导入系统架构表（`mysql.*`）
• 将凭据存储在具有`chmod 600`权限的`~/.my.cnf`中——切勿将密码作为内联参数传递
• 对于超过10 GB的数据库，评估`mysqlpump`或MySQL Shell的并行转储工具，因为`mysqldump`将成为瓶颈
• 在依赖备份进行灾难恢复之前，通过在隔离环境中执行测试恢复来验证每个备份
• 托管多个客户端数据库时，使用独立的VPS Control Panels隔离环境，以防止恢复操作期间的跨租户访问
• 将数据库备份策略与连接到MySQL的任何面向Web的应用层上的有效SSL Certificate配合使用，以防止传输中的凭据拦截

常见问题

mysqldump和mysqlpump有什么区别？

`mysqldump`是单线程的，生成单个SQL文件——可靠且具有普遍兼容性。`mysqlpump`在MySQL 5.7.8中引入，支持同时并行导出多个数据库和表，显著减少多核服务器上的转储时间。但是，`mysqlpump`在混合引擎数据库的一致备份方面存在已知问题，不太适合跨版本迁移。

我可以将MySQL 5.7转储导入MySQL 8.0吗？

可以，但有注意事项。用户数据和应用程序架构可以干净地导入。但是，切勿直接导入`mysql`系统数据库——身份验证插件在8.0中从`mysql_native_password`更改为`caching_sha2_password`，导入旧的权限表将破坏身份验证。使用`CREATE USER`和`GRANT`语句手动重建用户。

为什么我的导入因外键约束而失败，提示”ERROR 1005: Can’t create table”？

当表以违反外键依赖关系的顺序导入时会发生这种情况。解决方法是在导入会话前添加`SET foreign_key_checks = 0;`，并在完成后追加`SET foreign_key_checks = 1;`。或者，使用`–single-transaction`导出，它在转储文件本身中保留引用完整性。

如何仅导出数据而不包含CREATE TABLE语句？

使用`–no-create-info`标志：`mysqldump -u root -p –no-create-info mydatabase > data_only.sql`。当您需要将数据重新加载到现有架构中而不更改其结构时，这非常有用。

在两个远程服务器之间传输MySQL转储最安全的方式是什么？

通过SSH直接管道传输转储，无需将中间文件写入磁盘：`mysqldump -u root -p sourcedb | ssh user@destination.server "mysql -u root -p targetdb"`。这比复制纯文本`.sql`文件更快、更安全，尤其是在磁盘配额受限的Shared Web Hosting环境中运行时。