引言

在软件开发和系统管理中，导入和导出数据库是日常的核心任务。无论是为了进行数据备份、灾难恢复、环境复制，还是将数据迁移到新的服务器，数据转储文件都扮演着关键角色。本教程将深入探讨如何利用命令行工具 mysqldump 和 mysql，在 MySQL 或 MariaDB 环境中高效地执行这些操作。我们将涵盖从完整数据库的导出与导入，到特定表的处理，再到用户权限的迁移，以及针对大型 SQL 文件的优化策略。

核心要点速览：

• mysqldump 是创建逻辑备份的标准工具，它将数据库的结构（schema）和数据导出为 .sql 文本文件，便于跨系统传输和恢复。
• 使用 mysql 客户端可以将 mysqldump 生成的转储文件导入到新的或现有数据库中。在导入前，请务必确保目标数据库已经创建。
• 选择性导出表：可以通过在 mysqldump 命令后明确指定表名，只导出数据库中的特定表。
• 用户账户和权限需要单独迁移，因为它们存储在 mysql 系统数据库中。可以通过导出相关的授权表或生成 GRANT 语句来实现。
• 压缩导出（例如配合 gzip）可以显著节省存储空间并加快数据传输速度，导入时也支持直接解压缩。
• 对于大型 InnoDB 数据库，使用 mysqldump 的 --single-transaction 和 --quick 选项可以创建一致的、非阻塞的快照，并有效减少内存消耗。
• 处理超大型 SQL 文件时，可以使用 split 命令将其拆分为更小的文件块，以避免导入过程中可能出现的超时或内存问题。
• 通过 mysqldump 的 --add-drop-table 标志可以避免导入时常见的“表已存在”（“Table already exists”）错误。在执行任何关键导入操作前，务必仔细检查或根据目标环境修改转储文件。

前置条件

要成功导入或导出 MySQL 或 MariaDB 数据库，您需要满足以下条件：

• 一台配置了非 root 用户的虚拟机或服务器，并授予 sudo 权限。
• 已安装并运行 MySQL 或 MariaDB 数据库服务。
• 在数据库服务器中已创建一个示例数据库用于练习（可选，但推荐）。

数据库的整体导出与导入

无论是进行备份还是环境复制，整体导出和导入数据库是最常见的操作。

导出 MySQL 或 MariaDB 数据库

使用 mysqldump 工具将整个数据库导出为一个 SQL 文本文件。

mysqldump -u username -p database_name > data-dump.sql

• username：用于登录数据库的用户名（通常是 root 或拥有相应权限的用户）。
• database_name：您希望导出的数据库的名称。
• data-dump.sql：用于存储导出内容的 SQL 文件名。

此命令执行时，终端通常不会有输出。您可以通过查看文件内容的前几行来确认其是否为有效的 SQL 转储文件：

head -n 5 data-dump.sql

文件顶部的内容应类似于：

-- MySQL dump 10.13  Distrib 5.7.16, for Linux (x86_64)
--
-- Host: localhost    Database: database_name
-- ------------------------------------------------------
-- Server version       5.7.16-0ubuntu0.16.04.1

导入 MySQL 或 MariaDB 数据库

要导入现有的数据库转储文件，通常需要先创建一个新的数据库。

1. 登录 MySQL shell：

   mysql -u root -p
   

   输入密码后进入 MySQL 命令行界面。

2. 创建新的数据库： 例如，创建一个名为 new_database 的数据库。

   CREATE DATABASE new_database;
   

3. 退出 MySQL shell： 按下 CTRL+D 或输入 exit;。

4. 从命令行导入转储文件：

   mysql -u username -p new_database < data-dump.sql
   

   导入成功后，命令行通常也不会有任何输出。您可以通过再次登录 MySQL shell，然后执行 USE new_database; 和 SHOW TABLES; 命令来验证数据是否已成功导入。

特定表的导出与导入

有时您可能只需要导出或导入数据库中的特定表，这在处理大型数据库、进行局部调试或仅迁移部分数据时非常有用。

导出特定表

在 mysqldump 命令的数据库名称后指定要导出的表名，用空格分隔：

mysqldump -u username -p database_name table1 table2 > selected-tables.sql

示例： 导出 store 数据库中的 users 表和 orders 表：

mysqldump -u root -p store users orders > users-orders.sql

您可以再次使用 head -n 10 users-orders.sql 命令来确认文件内容。

导入特定表

导入特定表的语法与导入完整数据库类似，但需要确保目标数据库已存在：

mysql -u username -p target_database < selected-tables.sql

示例： 将 users-orders.sql 文件导入到 test_store 数据库中：

mysql -u root -p test_store < users-orders.sql

此操作将只在 test_store 数据库中重新创建并填充 users 和 orders 表。如果这些表已经存在，它们将被转储文件中的数据覆盖（除非转储文件配置了跳过创建表的选项）。

用户权限的导出与迁移

在迁移数据库时，仅仅复制数据是不够的，还需要确保用户账户及其关联的权限也一同迁移。这些信息通常存储在 mysql 系统数据库中，mysqldump 默认不会导出这些内部数据库。

迁移用户账户及其权限主要有两种方法：

方法一：直接转储 mysql 系统数据库中的授权表

此方法直接复制了存储用户账户和权限的关键内部表，适用于在相似的 MySQL/MariaDB 服务器版本之间进行迁移。

1. 导出用户账户和权限相关的表：

   mysqldump -u root -p mysql user db tables_priv columns_priv procs_priv > users_and_privileges.sql
   

   重要提示：切勿尝试转储整个 mysql 数据库，因为它包含了可能与目标服务器不兼容的内部元数据。这里我们只选择性地导出与用户、数据库权限、表权限、列权限和存储过程权限相关的表。

2. 在目标服务器上导入这些授权表：

   mysql -u root -p mysql < users_and_privileges.sql
   

3. 重新加载权限： 导入完成后，必须手动重新加载权限表，使更改生效。

   mysql -u root -p -e "FLUSH PRIVILEGES;"
   

   或者登录 MySQL shell 执行：

   FLUSH PRIVILEGES;
   

方法二：生成 GRANT 语句（推荐用于可移植性）

此方法更加灵活，尤其适用于在不同 MySQL/MariaDB 版本之间进行迁移，或者当您需要对权限进行更精细的控制时。它通过提取实际定义用户权限的 GRANT 语句来实现迁移。

1. 生成 GRANT 命令：

   mysql -B -N -u root -p -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user WHERE user NOT IN ('mysql.infoschema', 'mysql.session', 'mysql.sys', 'root')" \
   | mysql -B -N -u root -p \
   | sed 's/$/;/' > all_user_grants.sql
   

   这个复杂的命令会查询 mysql.user 表，为所有非系统用户生成 SHOW GRANTS 语句，然后执行这些 SHOW GRANTS 语句，并将它们的输出（即实际的 GRANT 命令）追加到 all_user_grants.sql 文件中。

2. 审查和导入 GRANT 脚本： 打开 all_user_grants.sql 文件进行审查。您可以根据需要删除不希望迁移的用户账户或特定的权限行。然后，在新服务器上运行该脚本：

   mysql -u root -p < all_user_grants.sql
   

   虽然并非严格必要，但在导入完成后运行 FLUSH PRIVILEGES; 仍然是一个良好的习惯，可以确保所有权限立即生效。

大型 SQL 文件的处理与导入优化

处理大型数据库时，导入过程可能会变得非常缓慢或占用大量系统资源。以下是一些管理和加速大型数据库转储文件导入的策略：

1. 使用压缩以节省空间和时间

通过管道将 mysqldump 的输出直接导入 gzip 以创建压缩文件。在导入时，则使用 gunzip 进行解压缩并直接导入到数据库。

导出（压缩）：

mysqldump -u username -p database_name | gzip > database_name.sql.gz

导入（解压缩）：

gunzip < database_name.sql.gz | mysql -u username -p database_name

2. 暂时禁用外键检查与唯一约束

在导入大量数据时，禁用外键检查 (foreign_key_checks) 和唯一性检查 (unique_checks) 可以显著提高导入速度，因为数据库不需要在每次插入时都进行这些复杂的验证。同时，禁用自动提交 (autocommit)，将所有操作作为一个大的事务来处理，也能减少磁盘写入和日志开销。

在 .sql 文件的开头添加以下语句（或在导入前手动执行）：

SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;

导入结束后，务必重新启用它们并提交更改：

SET foreign_key_checks = 1;
SET unique_checks = 1;
COMMIT;

3. 使用 --quick 和 --single-transaction 标志

导出大型 InnoDB 数据库时，这两个 mysqldump 选项至关重要：

• --single-transaction：创建一个事务隔离级别为 REPEATABLE READ 的快照。这意味着在导出过程中，即使有其他客户端正在修改数据，导出的数据也是一致的，且不会阻塞其他读写操作。这对于生产环境中的在线备份尤其重要。
• --quick：强制 mysqldump 一行一行地从服务器检索数据，而不是一次性将所有数据加载到内存中。这可以大大减少内存使用，防止因为内存不足而导致导出失败。

mysqldump -u username -p --single-transaction --quick database_name > database_name.sql

4. 将 SQL 文件拆分成更小的块

对于超大型转储文件，一次性导入可能会导致超时或内存溢出。使用 split 命令将其分割成更小的、可管理的片段（例如，每 5000 行一个文件）：

split -l 5000 large_dump.sql chunk_

这会生成 chunk_aa, chunk_ab, chunk_ac 等文件。然后，您可以按顺序导入这些块：

for file in chunk_*; do
  mysql -u username -p database_name < "$file"
done

5. 批量数据导入：LOAD DATA INFILE

如果您的数据是 .csv 或 .tsv 等纯文本格式，并且您需要将它们导入到现有的表中，LOAD DATA INFILE 命令通常比通过 SQL INSERT 语句导入快得多。

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','      -- 字段分隔符，例如逗号
LINES TERMINATED BY '\n'      -- 行分隔符，例如换行符
IGNORE 1 LINES;               -- 跳过文件中的第一行（通常是标题行）

注意：为了使用 LOAD DATA INFILE，MySQL/MariaDB 服务器上的 local_infile 配置需要启用，并且文件路径必须是服务器可访问的路径。

常见错误与避免策略

在导入和导出 MySQL 或 MariaDB 数据库时，一些常见的疏忽可能导致数据丢失或操作失败。了解这些问题并采取预防措施至关重要。

1. 导入到错误的数据库 问题：未仔细检查目标数据库名称，导致数据被导入到非预期位置，甚至覆盖了现有数据。 避免：在执行导入命令前，务必仔细检查目标数据库名称。导入后，可以使用 USE your_database; SHOW TABLES; 和 SELECT COUNT(*) FROM your_table; 等命令确认数据是否正确导入。

2. 忘记创建目标数据库 问题：mysql 导入命令假定目标数据库已存在。如果数据库不存在，导入会失败。 避免：在导入之前，始终先使用 CREATE DATABASE database_name; 命令创建目标数据库。或者，在导出时使用 mysqldump 的 --databases 选项，这会在转储文件中包含 CREATE DATABASE 和 USE 语句。

3. 使用不正确的凭据或权限不足 问题：用于导出或导入的数据库用户没有足够的权限（例如 SELECT、LOCK TABLES、INSERT、CREATE 等）。 避免：确保用户拥有执行所需操作的所有权限。如果不确定，可以使用 root 用户进行操作（仅限安全环境或测试环境）。在生产环境中，应使用具有最小必要权限的用户。

4. 导出时未使用 --add-drop-table 问题：如果导入的数据库中已经存在同名表，并且转储文件不包含删除表的语句，导入会因“表已存在”（“Table already exists”）错误而失败。 避免：在 mysqldump 命令中添加 --add-drop-table 标志。这会使得转储文件在每个 CREATE TABLE 语句之前包含 DROP TABLE IF EXISTS 语句，确保表在重新创建前被安全删除。

5. 跳过用户权限迁移 问题：标准的数据库转储（mysqldump database_name）不包含用户账户和权限信息。如果在迁移后不单独处理，新服务器上的应用程序可能无法连接数据库。 避免：务必按照本教程“用户权限的导出与迁移”部分的方法，单独导出和导入用户账户和权限信息，并在导入后运行 FLUSH PRIVILEGES;。

6. 未验证字符集和排序规则兼容性 问题：源数据库和目标数据库的字符集或排序规则（collation）不匹配，可能导致导入后文本乱码或数据比较行为异常。 避免：在导出和导入时，考虑使用 --default-character-set 选项来指定字符集。在迁移前，检查并确保源和目标服务器的 character_set_server 和 collation_server 设置一致。

7. 未优化大型文件导入 问题：直接导入一个巨大的 .sql 文件可能耗时过长，甚至因内存或连接超时而失败。 避免：采用本教程“大型 SQL 文件的处理与导入优化”部分介绍的策略，如使用压缩、禁用外键检查和自动提交、拆分文件或考虑 LOAD DATA INFILE。

8. 忽略文件权限或文件路径 问题：指定的 .sql 文件不存在于正确的路径，或者当前用户没有读取该文件的权限。 避免：在执行导入命令前，使用 ls -l /path/to/your/file.sql 确认文件路径的正确性及文件权限。

通过避免这些常见错误，可以节省大量时间，防止潜在的数据丢失，并确保 MySQL 或 MariaDB 数据库操作的流程更加顺畅。在执行任何关键的导入或迁移任务之前，强烈建议在非生产环境中进行充分的测试。

常见问题解答 (FAQs)

1. mysqldump 与二进制备份有何区别？

• mysqldump (逻辑备份)：生成包含 SQL 语句的文本文件。它独立于数据库服务器的版本和操作系统，因此具有很强的可移植性，适用于跨版本或跨平台迁移。缺点是恢复速度相对较慢，对于非常大的数据库可能效率不高。
• 二进制备份 (物理备份)：直接复制磁盘上的实际数据文件（如 InnoDB 的 .ibd 文件、.frm 文件等）。它速度快，是恢复整个服务器或大型数据库的理想选择。但缺点是与服务器的文件结构和版本紧密绑定，通常只能恢复到相同或兼容版本的服务器上。

2. 能否将 MySQL 转储导入 MariaDB？

通常情况下是可以的。MySQL 和 MariaDB 在语法和功能上保持了高度兼容性，尤其是在其主要版本范围内。然而，如果转储文件包含特定于某个新 MySQL 版本独有的功能或语法（例如某些较新的 JSON 函数或特定的 SQL 语句），则在导入 MariaDB 时可能需要进行少量调整。

3. 如何只导出数据库的架构（不含数据）？

使用 mysqldump 的 --no-data 标志：

mysqldump -u username -p --no-data database_name > schema_only.sql

这会创建一个只包含 CREATE TABLE、CREATE VIEW、CREATE PROCEDURE 等语句的转储文件，不包含 INSERT 数据行。

4. 导入时出现“table already exists”错误怎么办？

此错误表示目标数据库中已经存在与转储文件中同名的表。解决方法有：

• 在导出时添加 --add-drop-table 标志：这将使转储文件在每个 CREATE TABLE 语句之前包含 DROP TABLE IF EXISTS 语句，确保在重新创建表之前先删除它们。
• 在导入前创建新的空数据库：这是最简单、最安全的做法，确保目标数据库是干净的。
• 手动编辑转储文件：如果只需要导入部分表，可以打开 data-dump.sql 文件，删除冲突表的 CREATE TABLE 和 INSERT 语句。

5. 能否一次性导出多个数据库？

可以。使用 mysqldump 的 --databases 选项，后跟用空格分隔的数据库名称列表：

mysqldump -u username -p --databases db1 db2 db3 > multi-database-dump.sql

这将为每个数据库生成 CREATE DATABASE 和 USE 语句，并将它们的数据一并导出。

6. 如何导出服务器上的所有数据库？

使用 mysqldump 的 --all-databases 选项：

mysqldump -u root -p --all-databases > all_databases.sql

这将创建包含所有用户数据库以及系统数据库（如 mysql, information_schema, performance_schema, sys）的完整备份。此选项通常用于完整服务器迁移或灾难恢复场景。

7. 将转储导入实时数据库安全吗？

应谨慎操作。如果转储文件包含 DROP TABLE、TRUNCATE TABLE 或 INSERT 语句，它可能会覆盖、删除或复制现有数据。在对生产环境进行任何导入操作之前，务必：

• 在测试环境中进行全面测试：确保导入过程和结果符合预期。
• 备份实时数据库：在导入前对生产数据库进行完整备份，以防万一。
• 审查转储文件：仔细检查转储文件中的 SQL 语句，特别是那些具有破坏性或可能影响现有数据的命令。

8. 为什么导入耗时过长？

导入过程缓慢可能有多种原因：

• 数据集过大：导入的数据量巨大。
• 外键检查已启用：每个插入或更新操作都需要验证外键约束。
• 频繁的索引更新：每次插入数据时，相关的索引都需要更新。
• 自动提交已启用：每个 INSERT 语句都作为一个单独的事务提交，增加了 I/O 开销。
• 服务器资源不足：CPU、内存或磁盘 I/O 成为瓶颈。

优化建议：

• 在导入前禁用外键检查 (SET foreign_key_checks = 0;) 和自动提交 (SET autocommit = 0;)。
• 确保 mysqldump 导出时使用了 --quick 和 --single-transaction 标志。
• 考虑使用压缩（gzip）来减少传输时间。
• 对于纯数据导入，使用 LOAD DATA INFILE 命令可能更快。
• 如果文件过大，可以拆分成小文件分批导入。

总结

本教程详细讲解了如何使用 mysqldump 和 mysql 工具在 MySQL 或 MariaDB 中执行数据库的导出和导入操作。我们从基础的完整数据库迁移讲起，逐步深入到选择性地处理特定表、迁移用户权限，并提供了针对大型 SQL 文件的高效处理策略。掌握这些技术对于日常的数据库备份、服务器迁移、开发环境复制以及故障恢复至关重要。通过熟练运用这些命令行工具和优化技巧，您将能够更可靠、高效地管理开发和生产系统中的数据库。

关于

关注我获取更多资讯

📢 公众号

💬 个人号