mysqldump 这个名字凡是跟 MySQL 打过交道的人都不陌生但真正敢说“会用它”的人其实不多。它解决的是备份问题核心价值就是把数据库里的表结构和数据原原本本地转写成一组 SQL 语句保存成一个可读、可迁移的文件。日常做迁移、测试服造数、临时快照或者搭建从库时mysqldump 都是最直接的工具。这篇文章我会从它底层怎么工作讲起再把常用参数背后的选型逻辑、备份恢复实操、自动化脚本写法以及我这么多年来踩过的坑一次性整理出来。适合刚入门的后端、运维也适合写过很多次命令但没认真抠过参数的人。1. 认识 mysqldump它到底在备份什么1.1 逻辑备份与物理备份的区别首先得明确一个概念mysqldump 是逻辑备份工具它导出的不是硬盘上的物理数据文件而是一堆 SQL 语句。物理备份则不同比如直接拷贝 MySQL 数据目录下的.ibd文件或者利用文件系统快照备份整个目录它是把数据文件原样复制下来。这两种方式的差异直接影响备份文件的大小、恢复速度和适用场景。逻辑备份生成的.sql文件一般比实际数据体积大不少因为每条 INSERT 语句还带了字段名、引号、逗号等字符开销恢复时要把每条 SQL 重新解析、执行、更新索引速度通常比物理恢复慢得多。但优点也很明显文件是纯文本可以直接打开看内容可以修改建表语句甚至可以跨小版本恢复。用一个生活化类比逻辑备份相当于把家具拆散装进纸箱到新房子再按图纸一件件组装物理备份则是直接请搬家公司把整个房子连墙体一起搬过去。搬房子快但新地方得完全兼容拆箱子慢但灵活性高。1.2 mysqldump 的工作原理mysqldump 本质上是一个客户端程序它连接 MySQL 服务端之后会做几件事先通过SHOW CREATE TABLE拿到每张表的建表语句再执行SELECT * FROM 表名把数据一行行读出来结合元数据信息生成 INSERT 语句最后输出到标准输出。你在命令行里用重定向它才会写入文件。为了保证备份出的数据是一致的mysqldump 有一套锁和事务机制。对于 InnoDB 表如果加了--single-transaction参数备份开始时会先启动一个事务利用 InnoDB 的 MVCC 多版本控制拿到一个一致性快照之后备份进程读取数据时业务上的增删改操作不会被阻塞相当于大家各自看各自的版本。对于 MyISAM 表就没有这么好的待遇因为 MyISAM 不支持事务mysqldump 只能对表加读锁备份期间任何写入都会被堵住。这就是为什么很多老 DBA 反复强调InnoDB 表备份一定要加--single-transaction。不加的话即使表是 InnoDBmysqldump 也会退化成用LOCK TABLES锁整个库备份期间所有写业务都得停摆。1.3 适用场景与局限mysqldump 最适合的数据量级我个人经验是单表数据几千万行以内、整库不超过几十 GB 的场景。用它做日常逻辑备份、迁移、测试数据导出都很顺手。但它也有明显天花板。第一单线程全表扫描加 INSERT 生成备份速度有限数据量到了 TB 级备份耗时和恢复耗时都难以接受。第二mysql 客户端导入大文件时单条 SQL 解析、索引维护都很吃性能恢复速度远不如物理备份。第三mysqldump 只能做某一时刻的完整备份做不到增量。增量还依赖 binlog 或第三方工具。所以我的建议是生产环境大库用物理备份工具做主力备份mysqldump 做辅助或迁移手段中小库mysqldump 就足够了。2. 常用参数与选型逻辑关键参数到底怎么选2.1 基础语法与连接参数先看最基础的命令形式mysqldump -u用户名 -p密码 -h主机地址 -P端口 库名 输出文件.sql注意-p后面如果直接跟密码中间不能有空格比如-p123456。但我强烈不建议这么写原因有两个一是密码会出现在 shell 历史记录里二是用ps查进程时也能看到明文密码。安全一点的做法是让终端交互式提示输入密码或者把账号密码写进配置文件。用配置文件的写法是在~/.my.cnf里加[client] userbackup password你的密码然后把文件权限改成 600。这样命令行里只需要写mysqldump参数从配置文件读取密码就不会暴露在进程列表里。生产环境的自动化脚本我一般建议用--defaults-extra-file/etc/mysql/backup.cnf单独指定一个备份专用配置文件权限设置成仅 root 可读比密码硬编码在脚本里安全得多。另外--host和--port如果不写默认连接本地走的是 Unix socket。如果本地有多个 MySQL 实例一定要显式指定--socket否则可能连错实例导致备份出来根本不是你以为的那个库。2.2 备份范围控制库、表、结构、数据mysqldump 后面接的库名和表名决定了备份范围。我平时最常用的几种写法# 备份单库 mysqldump -uuser -p dbname dbname.sql # 备份多个库 mysqldump -uuser -p --databases db1 db2 multi.sql # 备份整个实例 mysqldump -uuser -p --all-databases all.sql # 备份单张表 mysqldump -uuser -p dbname tablename table.sql这里有个特别容易踩的坑不加--databases备份单库时生成的 SQL 文件里只有建表语句和数据没有CREATE DATABASE和USE dbname;。恢复的时候目标库必须已经存在否则 MySQL 会报ERROR 1049: Unknown database。加了--databases之后文件里会自动带上创建库和切换库的语句恢复时就不用手动建库了。只备份结构或只备份数据也是常见需求# 只备份结构 mysqldump -uuser -p --no-data dbname schema.sql # 只备份数据 mysqldump -uuser -p --no-create-info dbname data.sql前者用于保存表定义、迁移表结构后者常用于把数据灌入一个已经建好的空白库。2.3 关键参数深入解析参数是 mysqldump 的重头戏选对了是利器选错了是事故。下面几个参数值得认真理解。--single-transaction是最核心的 InnoDB 一致性参数。它的原理是执行START TRANSACTION WITH CONSISTENT SNAPSHOT利用 MVCC 拿到一个一致读视图。备份期间其他会话的 DML 不会被阻塞。但要注意它解决不了 DDL 的干扰。如果备份过程中有人执行了ALTER TABLE大表 DDL 可能触发隐式提交导致后读到的数据和前面不一致。所以即使加了--single-transaction也尽量在业务低峰期执行并且备份前确认没有 DDL 任务跑着。--source-data2是搭建从库时的关键参数。它会额外记录 binlog 的文件名和位置以注释形式写到备份文件里。值设置为 2表示写入CHANGE MASTER TO语句但默认不执行方便恢复后手动指定主库位置。老版本对应的是--master-data2在 MySQL 8.0.26 之后推荐用--source-data。需要特别提醒当--source-data和--single-transaction一起使用时mysqldump 会先在事务开始前执行一次短暂的FLUSH TABLES WITH READ LOCK获取 binlog 位置这个锁一般只持续几秒钟但如果是高并发写入的核心库也需要评估一下影响。--routines、--triggers、--events决定是否备份存储过程、函数、触发器和事件。存储过程和事件默认不导出很多人备份完才发现业务依赖的存储过程全没了。触发器默认会导出但为了显式表达意图我把三个参数习惯性一起加上mysqldump -uuser -p --routines --triggers --events dbname backup.sql--set-gtid-purgedOFF是开启 GTID 时绕不开的参数。如果源库开启了 GTID导出文件默认会包含SET GLOBAL.GTID_PURGED相关内容。恢复到目标库时如果目标库启用了 GTID 模式这个设置很容易报ERROR 1840或GTID_PURGED can only be set when GTID_EXECUTED is empty。如果你只是做普通恢复不打算搭建主从建议备份时显式加--set-gtid-purgedOFF让文件更干净。反过来如果备份是为了搭建从库通常要保留 GTID 信息不能随便关。--hex-blob用来导二进制类型列比如 BLOB、BINARY。二进制数据如果按原字节输出可能因为字符集转换或终端转义而损坏。加上--hex-blob后mysqldump 会把这些列输出成十六进制字符串恢复时由 MySQL 转换成原始字节。如果库里没有这类字段加不加无所谓有的话漏加这个参数就有概率拿到一个恢复不出来的备份文件。2.4 参数组合建议参数不是越多越好但核心的组合值得固定下来。我平时在中小型 InnoDB 库上备份用这套mysqldump -u用户 -p \ --single-transaction \ --quick \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ --hex-blob \ dbname dbname.sql--quick让 mysqldump 边查询边输出而不是把所有结果缓冲到内存再写对大表内存占用更友好。实际上--opt这个快捷参数默认就已经包含了--quick、--add-drop-table、--add-locks、--extended-insert等选项所以很多版本不写--quick也是生效的。但显式写出来能让后来读脚本的人更清楚你关心什么。--default-character-setutf8mb4是为了防止字符集错乱。备份端的默认字符集和数据库实际字符集不一致时导出的中文可能变成乱码或问号。统一指定 utf8mb4 是现在最通用的做法。下面用一张表快速速查几个高频参数的用途参数作用什么时候用--single-transactionInnoDB 一致性快照不锁 DMLInnoDB 库备份首选--no-data只要结构不要数据迁移表结构--no-create-info只要数据不要建表语句灌入已有空库--routines导出存储过程和函数业务有存储过程时必须加--events导出定时事件业务有 Event 时必须加--source-data2记录 binlog 位置搭建从库时使用--set-gtid-purgedOFF跳过 GTID 设置普通恢复场景避免 GTID 报错--hex-blob二进制列转 HEX表里有 BLOB/BINARY 字段时3. 实操过程从命令行到自动化备份脚本3.1 环境准备与权限很多人用 root 备份图省事这是埋雷。root 的权限太大备份脚本一旦被入侵等于把整个数据库的钥匙交给别人。更规范的做法是创建一个备份专用账号只授予必要权限CREATE USER backuplocalhost IDENTIFIED BY 强密码; GRANT SELECT, SHOW VIEW, RELOAD, LOCK TABLES, REPLICATION CLIENT, EVENT ON *.* TO backuplocalhost; GRANT TRIGGER ON 你的库.* TO backuplocalhost;几个权限分别对应什么我解释一下SELECT是读取数据必需SHOW VIEW是备份视图需要RELOAD用于执行FLUSH TABLES WITH READ LOCKLOCK TABLES用于行不通事务时锁表REPLICATION CLIENT用于SHOW MASTER STATUS拿到 binlog 位置EVENT和TRIGGER分别用于备份事件和触发器。备份账号不一定需要全局EVENT权限但给全局也无妨只要别给 DML 权限就行。我遇到过因为权限不足导致备份失败的情况报错一般是mysqldump: Couldnt execute SHOW VARIABLES LIKE GTID_MODE: Access denied这种就是账号没有REPLICATION CLIENT或SHOW VARIABLES需要的权限。解决方法是按上面的授权语句补上权限而不是直接换 root。3.2 实战全库备份全库备份前先想清楚是备份整个 MySQL 实例还是某个业务库。备份整个实例用mysqldump -ubackup -p --all-databases --single-transaction --routines --triggers --events --set-gtid-purgedOFF /backup/full_$(date %F).sql备份全库时--all-databases会把mysql系统库一起备份。系统库里有用户表如果你是想完整迁移整个实例到新机器这么做没问题如果只是为了备份业务数据没必要每次把系统库也带上而且系统库里可能有权限表恢复时反而容易出问题。所以我更推荐按业务库分别备份。备份完成后一定要检查文件是否完整。最简单的办法是看文件尾部有没有结束标记tail -n 5 /backup/full_$(date %F).sql如果最后一行是类似-- Dump completed on 2025-...说明 mysqldump 正常跑完了。如果没有这个标记哪怕文件看着挺大也可能是备份中断不能拿去做恢复。这个习惯我坚持了很久也是被坑出来的。3.3 实战单表备份与恢复单表备份的命令很轻量mysqldump -ubackup -p --single-transaction --hex-blob dbname tablename table.sql恢复时如果目标库不存在先建库CREATE DATABASE targetdb DEFAULT CHARACTER SET utf8mb4;然后导入mysql -uuser -p targetdb table.sql这里有个细节备份文件默认包含DROP TABLE IF EXISTS所以恢复时如果目标表已经存在会被先删掉再重建。也就是说用这种方式做数据同步会把目标表里的旧数据全部覆盖。如果你只是想往一张已有表里追加部分数据应该用--no-create-info加自定义条件导出而不是直接整表恢复。按条件导出数据是我经常用到的功能mysqldump -ubackup -p dbname tablename --whereid 10000 part_data.sql--where参数可以灵活过滤数据行适合提取某段时间的数据做测试。它生成的 SQL 里还是包含建表语句的恢复时同样会先删表要注意这一点。3.4 压缩与自动化备份脚本裸 SQL 文件占磁盘空间很大日常备份一定要压缩。管道压缩是最省事的写法mysqldump -ubackup -p --single-transaction dbname | gzip dbname_$(date %F).sql.gz但这样写有一个隐患管道的退出码默认看最后一个命令gzip。如果 mysqldump 中途失败gzip 可能依然成功退出脚本认为备份成功实际上输出的是一个残缺文件。解决办法是用set -o pipefail让管道中任何一个命令失败都使整条管道返回非零退出码。一个可落地的定时备份脚本长这样#!/bin/bash set -o pipefail BACKUP_DIR/backup/mysql DATE$(date %F) DB_NAMEmydb KEEP_DAYS7 mkdir -p ${BACKUP_DIR} mysqldump --defaults-extra-file/etc/mysql/backup.cnf \ --single-transaction \ --routines \ --triggers \ --events \ ${DB_NAME} | gzip ${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz if [ $? -eq 0 ]; then echo $(date %Y-%m-%d %H:%M:%S) 备份成功: ${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz else echo $(date %Y-%m-%d %H:%M:%S) 备份失败 exit 1 fi find ${BACKUP_DIR} -name ${DB_NAME}_*.sql.gz -mtime ${KEEP_DAYS} -delete使用--defaults-extra-file而不是在命令行写用户名密码脚本里就不会出现敏感信息。find清理语句保留 7 天的备份天数按自己磁盘空间调整。最后把脚本加到 crontab0 2 * * * /opt/scripts/mysql_backup.sh /var/log/mysql_backup.log 21定时任务时间建议选凌晨业务低峰同时要保证磁盘空间充足。我建议脚本里再加一行磁盘空间检查低于阈值就发告警否则备份到一半磁盘写满你会得到一个既不能恢复、又占着空间的坏文件。4. 数据恢复的完整流程别让备份形同虚设4.1 恢复前的准备每次做恢复之前我都要先确认三个问题目标库的 MySQL 版本是否和备份来源兼容备份文件是否包含CREATE DATABASE目标库现在是空库还是已有数据。这三个问题没搞清楚贸然导入轻则报错重则把线上数据冲掉。如果备份文件是在命令行不加--databases生成的恢复前必须手动建库CREATE DATABASE targetdb DEFAULT CHARACTER SET utf8mb4;如果备份文件是从开启了 GTID 的实例导出的并且导出的参数里没有加--set-gtid-purgedOFF恢复时可能遇到 GTID 相关报错。我处理这类问题的经验是如果是普通恢复最好在恢复会话里先暂时关闭 GTID 相关检查或者直接在备份阶段就把这个参数关掉。还有一点容易被忽视备份文件的第一行通常是SET NAMES utf8mb4;。如果目标库的字符集不是 utf8mb4导入时很可能出现乱码或者Illegal mix of collations错误。恢复前可以用file命令或head -n 20看一下备份文件的编码再决定恢复会话的字符集。4.2 通过重定向恢复单库备份文件最直接的恢复方式就是mysql -uuser -p targetdb backup.sql如果备份文件是用--databases导出的文件里自动带了CREATE DATABASE和USE导入时不需要指定库名mysql -uuser -p backup.sql数据量较大时我更喜欢进入 mysql 客户端后执行source命令而不是用 shell 重定向mysql SET NAMES utf8mb4; mysql source backup.sqlsource的好处是能看到每一条语句的执行结果和耗时出错时可以及时定位是哪一段 SQL 有问题。重定向导入则更安静适合无人值守的恢复流程。恢复过程中要留意FOREIGN_KEY_CHECKS。mysqldump 生成的备份文件默认会在开头设置SET FOREIGN_KEY_CHECKS0;结尾设回 1目的就是让表可以按任意顺序导入避免外键约束校验影响恢复速度。如果你用的是自己拼 SQL 的备份方式恢复前最好手动执行SET FOREIGN_KEY_CHECKS0; SET UNIQUE_CHECKS0;这能明显加快导入速度但也意味着如果数据本身有外键问题不会立刻暴露出来。恢复完成后记得重新开启检查。4.3 部分恢复与表空间从全库备份中只恢复某一个库常用--one-database参数。假设备份文件full.sql里包含多个库而我只想恢复其中的business_dbmysql -uuser -p --one-database business_db full.sql这个参数会让 mysql 客户端只执行USE business_db之后的 SQL其他库的建表和数据都被忽略。缺点也很明显mysql 仍然会解析整个文件如果备份文件有几十 GB哪怕只恢复一个库也要等文件全部读到末尾速度很慢。另一种做法是用awk把全量备份文件拆分出来。mysqldump 导出多库时每切换一个库会生成一行类似-- Current Database: business_db用脚本截取两个标记之间的内容就能得到一个独立库的备份文件。这种方式的优点是自己控制拆分逻辑可以针对某个库做单独恢复缺点是对 shell 编程有一定要求分割不好会导致 SQL 截断。我的建议是日常多库备份尽量每个库单独一个文件避免恢复时做这种麻烦事。4.4 恢复常见错误处理恢复过程中最常见的报错我汇总成一张速查表错误码错误信息原因与处理办法1049Unknown database备份文件里没有建库语句先手动建库再导入1062Duplicate entry目标表已有数据且没有 DROP TABLE确认是否要清空1064You have an error in your SQL syntax备份文件被损坏或版本不兼容检查文件完整性1227Access denied恢复账号权限不足检查建库建表权限1840GTID_PURGED can only be set...备份带 GTID 信息恢复库已有 GTID 执行记录需清理或备份时加--set-gtid-purgedOFF2002Cant connect to local MySQL serversocket 或连接地址不对确认--socket参数每次恢复我还会加一个保险操作恢复前把目标库结构先备份到一个临时文件万一恢复后发现不对还能把原结构找回来。特别是覆盖生产库的场景这条能救命。5. 常见问题与排查技巧实录5.1 备份文件只有几KB恢复后没有数据很多人在公司群里反馈“备份文件才几 KB恢复出来是空库”这通常不是数据真变少了而是 mysqldump 命令本身就没跑成功错误信息被重定向输出吞掉了。例如你执行mysqldump -ubackup -p dbname backup.sql 2error.log如果备份文件只有几 KB先看error.log里有没有Access denied、Unknown database之类的提示。如果没有重定向 stderr错误会直接打印在屏幕上但很多人只看到文件生成没注意到上方飘红报错。更隐蔽的情况是管道命令。使用mysqldump | gzip时gzip 正常退出不代表 mysqldump 成功。解决就是在 shell 脚本开头加set -o pipefail并检查管道退出码。备份完一定要 grep 结束标记这个习惯能在监控报警之前就堵住一批坏文件。5.2 线上备份锁表影响业务怎么办线上备份导致业务卡死多半是因为库里有 MyISAM 表或者备份时忘了加--single-transaction。MyISAM 不支持事务任何备份工具都只能在一致性要求下加读锁这是引擎能力问题不是 mysqldump 的 bug。应对思路分几条线。第一如果是 InnoDB 表确认--single-transaction生效并且不碰到 DDL。第二如果历史遗留 MyISAM 表优先考虑把引擎改成 InnoDB这是治本。第三如果没法改连接一台只读从库执行备份把锁表和读取的压力转移到从库。第四如果库非常大mysqldump 本身单线程扫全表时间很长建议换成物理备份工具或者直接用云平台快照功能。我实际遇到过一次坑备份过程中另一个组在跑ALTER TABLE导致 mysqldump 拿到不一致的数据恢复后部分表主键重复业务直接报错。从那以后凡是要在生产上跑全量备份我都会先看一眼近期有没有 DDL有就换时间。5.3 字符集与乱码问题乱码问题十个里有九个是字符集不一致。备份时用--default-character-setutf8mb4恢复时也要用同样的字符集mysql -uuser -p --default-character-setutf8mb4 targetdb backup.sql如果备份文件里表结构是latin1而数据库默认是utf8mb4导入时 MySQL 会做隐式转换可能把之前的字节变成不可读字符。这种情况不能盲目统一字符集要先SHOW CREATE TABLE看表的真实字符集。另外从文件中恢复时如果文件里有SET NAMES latin1;mysql 客户端在导入时会临时改变会话字符集。所以看文件开头很重要。我处理乱码问题是刻意练过的先看SET NAMES再看表结构里的DEFAULT CHARSET两者都确认后再决定恢复参数。5.4 备份文件过大如何分片单文件备份超过几 GB恢复时间会很长中间断掉又得从头再来。常用的分片思路有三种。第一种是按库按表拆开备份每个对象一个独立文件。这样某部分失败只需要恢复那部分不影响整体进度。第二种是利用 mysqldump 的--tab参数把每张表导出成一个建表 SQL 和一个数据文本文件mysqldump -uuser -p --tab/backup/mydb dbname这个功能要求备份账号有FILE权限而且 MySQL 的secure_file_priv必须允许写入目标路径。生成的数据文件是制表符分隔的文本恢复用LOAD DATA INFILE速度比执行 INSERT 快得多。但对机房权限和配置要求高适合数据仓库取数类的场景。第三种是使用并行逻辑备份工具比如 mydumper它能多线程导出多个表速度比 mysqldump 单线程快很多。不过这类工具本身有额外依赖和配置成本用在中小库上有点杀鸡用牛刀。我的原则很简单数据量没到百 GB 级别老老实实用 mysqldump 按库拆分最省心。最后分享一个自己的习惯我会在每次备份的产物旁边放一个几十字节的校验文件里面记录备份完成时间和文件大小脚本里还加了一道grep Dump completed的检测。备份是低频操作但出问题的高峰期往往就是换环境、重启服务、磁盘告警那几天。与其等到恢复时才发现备份坏了不如在生成那一刻就多花两秒钟确认一下。这套做法帮我在模拟演练和真实事故里都避过不少麻烦也把 mysqldump 这个老工具用扎实了。