mysqldump 参数拾遗：在遗忘与铭记之间

光头老王 發表於 2026-4-17 02:07:00

mysqldump 参数拾遗：在遗忘与铭记之间

mysqldump工具在灾备恢复、DB间数据同步(含Server实例间同步）、主从搭建等场景下都拥有一席之地。可以说，它是DBA常用工具之一，所以就要求我们对其参数真正的了解掌握，特别是一些常用容易搞混、忽略的参数。因而，对mysqldump 那些被忽略的“摆渡”指令，要常常“淘”出来，Review 下。
<h3>一. 影响DB的参数</h3>
影响DB的参数，换句话说，DB作用域的参数，那些DB在mysqldump的作用范围内。
<h4>1.1 --all-databases</h4>
添加此参数就是备份所有的DB，例如
<div class="cnblogs_code">
<pre>$> mysqldump --all-databases > dump.sql</pre>
</div>
<h4>1.2 --databases</h4>
通过此参数指定DB，例如：
<div class="cnblogs_code">
<pre>$> mysqldump --databases db1 db2 db3 > dump.sql</pre>
</div>
The --databases option causes all names on the command line to be treated as database names.
<h4>1.3 参数中即没有--all-databases也没有--databases</h4>
<div class="qk-md-paragraph">当 mysqldump 命令中既没有 --all-databases 也没有 --databases 参数时，它的行为会根据提供的参数数量而有所不同。</div>
<div class="qk-md-paragraph">简单来说，第一个参数会被视为数据库名，其后的所有参数则被视为表名。具体来讲，当只提供一个参数时，<code class="qk-md-code">mysqldump</code> 会备份这个指定的整个数据库；当提供两个或更多参数时，第一个参数是数据库名，后面的参数则是该数据库中需要备份的特定表。</div>
<div class="qk-md-paragraph">例如：</div>
<div class="qk-md-paragraph">
<div class="cnblogs_code">
<pre>mysqldump mydb > backup.sql</pre>
</div>
说明：这条命令会备份 mydb 数据库中的所有表。
<div class="cnblogs_code">
<pre>mysqldump mydb table1 table2 > backup.sql</pre>
</div>
说明：这条命令只会备份 mydb 数据库中的 table1 和 table2 这两张表。
</div>
英文官网的原文
Without this option【指--databases】, mysqldump treats the first name as a database name and those following as table names.
<h3>二.使用--all-databases 或 --databases对备份还原的影响</h3>
影响很多，在此强调一点：
使用--all-databases 或  --databases：生成的备份文件会包含 CREATE DATABASE 和 USE 语句。
With --all-databases or --databases, mysqldump writes CREATE DATABASE and USE statements prior to the dump output for each database.
不使用 --all-databases 或  --databases：生成的备份文件不包含 CREATE DATABASE 和 USE 语句。
<h4>2.1 生成的备份文件会包含 CREATE DATABASE 和 USE 语句</h4>
恢复时：可以直接导入，备份文件会自动创建数据库并选择它。例如
<div class="cnblogs_code">
<pre>mysql -u root -p < backup.sql</pre>
</div>
优点：适合跨服务器恢复，因为它能确保数据库被创建。
<h4>2.2 不包含 CREATE DATABASE 和 USE  语句</h4>
恢复时：你必须先手动创建目标数据库（如果它不存在），并在导入时指定该数据库。例如：
<div class="cnblogs_code">
<pre>mysql -u root -p mydb < backup.sql</pre>
</div>
或者,通过source的方式
<div class="cnblogs_code">
<pre>mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql</pre>
</div>
优点：可以将数据导入到一个与原来名字不同的数据库中。
<h4>2.3 CREATE DATABASE的解读 </h4>
生成的主要命令如下：
<div class="cnblogs_code">
<pre>CREATE DATABASE /*!32312 IF NOT EXISTS */ `mydb`
/*!40100 DEFAULT CHARACTER SET utf8mb4 */;</pre>
</div>
（1）/*!32312 IF NOT EXISTS */: 这是一个条件注释。
它的意思是：如果数据库不存在才创建。 前面的数字 32312 代表 MySQL 版本号（3.23.12）。只有当目标数据库版本高于此版本时，才会执行 IF NOT EXISTS。这是一种兼容旧版本的保护机制，防止因数据库已存在而报错。 （2）反引号mydb : 数据库名会被反引号包裹，防止库名包含特殊字符或与保留字冲突。 （3）/*!40100 DEFAULT CHARACTER SET utf8mb4 */: 这也是一个条件注释。 它会显式指定字符集（如 utf8mb4 或 latin1），确保恢复数据时，新库的字符集与备份时一致，避免乱码。
<h4>2.4 回顾自动生成的建表的语句</h4>
当使用 mysqldump 默认或加上 --add-drop-table 参数时，生成的 SQL 结构如下：
<div class="cnblogs_code">
<pre>-- 1. 安全删除旧表（默认或使用了 --add-drop-table）
DROP TABLE IF EXISTS `class_students`;

-- 2. 保存当前客户端字符集设置
/*!40101 SET @saved_cs_client = @@character_set_client */;

-- 3. 设置本次建表的字符集环境（关键！防止乱码）
/*!50503 SET character_set_client = utf8mb4 */;

-- 4. 核心建表语句
CREATE TABLE `class_students` (
`student_id` int NOT NULL AUTO_INCREMENT,
`student_name` varchar(50) NOT NULL,
`gender` enum('男','女') NOT NULL,
`age` tinyint unsigned NOT NULL,
`birth_date` date DEFAULT NULL,
-- ... 其他字段 ...
`class_id` int NOT NULL,
PRIMARY KEY (`student_id`),
KEY `idx_class_id` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 5. 恢复之前的客户端字符集设置
/*!40101 SET character_set_client = @saved_cs_client */;</pre>
</div>
<h3>三.参数 --single-transaction</h3>
--single-transaction 是一个在进行 MySQL 在线备份时非常关键的参数，它的核心作用是在不锁表的情况下，为 InnoDB 存储引擎的表创建一个数据一致的备份。 这对于生产环境至关重要，因为它可以避免备份操作阻塞正常的业务写入，实现所谓的“热备份”。
<code class="qk-md-code" data-spm-anchor-id="5176.28103460.0.i34.39f22988ffePN1">--single-transaction </code>参数通过利用 InnoDB 引擎的 MVCC (多版本并发控制) 特性来工作。
注意：
（1）仅对 InnoDB 表有效. （2）无法避免元数据锁（MDL）等待 虽然它不锁表，但在备份开始时，它需要获取表的元数据锁。如果此时有长时间未提交的事务，或者有 DDL 操作（如 ALTER TABLE）正在执行，mysqldump 进程可能会因为等待元数据锁而被阻塞，直到这些操作完成。 （3）不能与 --lock-tables 同时使用 --single-transaction 和 --lock-tables 是互斥的。mysqldump 默认会开启 --lock-tables，因此使用 --single-transaction 时，它会自动禁用 --lock-tables。
<h3>四.参数--flush-logs</h3>
这个参数非常有意思，很有使用价值，但是也往往容易被忽略。关于此参数的使用，简单举几个例子。
例子1，我需要将DB1中的两张表的数据实时同步到DB2。自己通过Python脚本实现，实时同步借助模块python-mysql-replication。脚本启动时需要告诉从那个地方开始监控（收听）变更，即需要传递两个参数 log_file 和 log_pos。
<div class="cnblogs_code">
<pre> log_file: Set replication start log file
 log_pos: Set replication start log pos (resume_stream should be
 true)</pre>
</div>
如果在通过mysqldump做全量同步时，指定了--flush-logs，参数 log_file 和 log_pos就非常容易获取了。 log_file 就是执行命令新产生的binlog文件；log_pos就是4。
例子2，如果是基于 mysqldump 搭建搭的建备份还原的机制（针对不太大的关键数据表），在每次mysqldump中做一个--flush-logs，对【Establishing a Backup Policy】、【Point-in-Time (Incremental) Recovery】都很有帮助。---可快速定位 log_file 和 log_pos。
此外，--flush-logs对搭建主从、故障隔离（或者说对故障现场隔离，某一个文件前后），都有一定的帮助。
当然，此参数不是万能，但是最好在一定场景下可以想到，恰好出现。
<h3>五.参数--set-gtid-purged</h3>
是一个与 GTID（全局事务标识符）复制模式紧密相关的关键参数。它的核心作用是控制备份文件中是否包含用于设置目标服务器 GTID 状态的语句，这直接决定了备份数据导入后，是否会参与或影响现有的主从复制环境。
当 MySQL 开启了 GTID 模式 (gtid_mode=ON) 时，mysqldump 的默认行为（AUTO）会在导出的 SQL 文件中自动添加两条关键语句：
语句1
<div class="cnblogs_code">
<pre>SET @@SESSION.SQL_LOG_BIN= 0;</pre>
</div>
作用：在当前会话中关闭二进制日志（binlog）的记录。
后果：导入这些数据时产生的所有操作都不会被记录到 binlog 中。由于从库是通过读取主库的 binlog 来同步数据的，这意味着这次导入的数据不会同步到任何从库。
语句2
<div class="cnblogs_code">
<pre>SET @@GLOBAL.GTID_PURGED='...';</pre>
</div>
作用：将源数据库的 GTID 执行集合设置到目标数据库中。 后果：这会让目标数据库“认为”这些 GTID 对应的事务已经被执行过了。
--set-gtid-purged 参数就是用来控制是否在备份文件中包含上述两条语句的。
<table border="0">
<tbody>
<tr>
<td>参数值</td>
<td>是否在文件中添加<code class="qk-md-code">SET ...</code>语句</td>
<td>适用场景</td>

</tr>
<tr>
<td><code class="qk-md-code">AUTO</code> (默认)</td>
<td>是 (如果源库开启了 GTID)</td>
<td>适用于构建新的从库或全库恢复。基于一个全量备份来搭建一个新的从库时，导入备份后，新从库的 <code class="qk-md-code">gtid_purged</code> 变量被设置，它知道自己已经“拥有”了主库到备份点为止的所有事务。让新从库的数据和 GTID 状态与主库在备份时刻完全一致。此时再执行 <code class="qk-md-code">CHANGE MASTER TO</code> 命令，复制就能从正确的位置无缝开始。</td>

</tr>
<tr>
<td><code class="qk-md-code">ON</code></td>
<td>是 (如果源库未开启 GTID 则会报错)</td>
<td>与 AUTO<code class="qk-md-code"></code> 类似，但更严格。明确指定需要 GTID 信息。</td>

</tr>
<tr>
<td><code class="qk-md-code">OFF</code></td>
<td>否</td>
<td>
适用于部分数据恢复、数据迁移或克隆开发库。导入操作会正常记录 binlog，并生成新的 GTID，不影响现有复制拓扑。
例1，在主库上进行部分数据恢复 (使用 OFF)。当你在主库上误删了某个表，需要用备份文件恢复这一个表时，必须使用 OFF，恢复操作会像正常业务一样产生 binlog 和新的 GTID，并安全地复制到所有从库，保证数据一致性。
例2，数据迁移或克隆 (使用 OFF)。当你想把生产库的某个库克隆到测试环境，或者将数据从一个复制集群迁移到另一个独立的集群时，必须使用 OFF，数据导入后会生成属于目标集群的全新 GTID，完全独立，互不干扰，避免 GTID 冲突。如果使用默认的 AUTO，备份文件中包含的 GTID 集合会被导入到目标库。如果目标库本身也是一个复制集群的一部分，这些“外来”的 GTID 会污染其 GTID 执行历史，极有可能导致复制中断。 

</td>

</tr>

</tbody>

</table>
<h3>六.参数--master-data</h3>
--master-data 参数是搭建 MySQL 主从复制（Master-Slave Replication）时的神器。它的核心作用非常明确：在备份文件中自动记录当前二进制日志（Binlog）的文件名和位置（Position）。
当使用 --master-data 时，mysqldump 会在生成的 SQL 文件头部插入一条 CHANGE MASTER TO 语句。
<table>
<thead>
<tr><th align="left">参数值</th><th align="left">行为描述</th><th align="left">适用场景</th></tr>

</thead>
<tbody>
<tr>
<td align="left"><code>--master-data=1</code></td>
<td align="left">直接执行。生成的 <code>CHANGE MASTER TO</code> 语句是有效 SQL。当你导入备份文件时，这条语句会被立即执行，自动配置好主从关系。</td>
<td align="left">适用于自动化脚本，导入即配置。</td>

</tr>
<tr>
<td align="left"><code>--master-data=2</code></td>
<td align="left">注释形式。生成的 <code>CHANGE MASTER TO</code> 语句会被 <code>--</code> 注释掉。它不会自动执行，只是把信息留在那里供你查看或手动复制执行。</td>
<td align="left">适用于人工操作，比较安全，防止误操作覆盖现有的复制配置。</td>

</tr>

</tbody>

</table>
说明：
（1）mysqldump 的 --master-data 参数的默认值是 1。这意味着，如果你在命令中只写了 --master-data 而没有指定具体的数值（例如 --master-data=2），系统会自动按照 --master-data=1 的逻辑执行。
（2）为了保证获取坐标时数据的一致性，默认情况下它会执行 FLUSH TABLES WITH READ LOCK（全局读锁）。如果你同时使用了 --single-transaction，则不会锁表，而是利用 InnoDB 的事务特性来获取一致性坐标。可以理解为加上 --single-transaction 后，mysqldump 会在 InnoDB 事务开启的瞬间获取 Binlog 坐标，既保证了坐标与数据的一致性，又避免了锁表。
（3）DB实例如果已经开启了GTID，那么搭建主从，就不要考虑--master-data了；mysqldump中也就不要出现--master-data了。
  
来源：https://www.cnblogs.com/xuliuzai/p/19880193

頁: [1]

琼殿技术论坛's Archiver

mysqldump 参数拾遗：在遗忘与铭记之间