一、聊聊为什么到今天还要认真写mysqldump我知道在座的各位可能已经在用各种图形化工具、云平台备份服务或者开源的物理备份方案了。但说句实在话我在处理日常运维和故障恢复的时候最常摸到的还是mysqldump。它确实老也确实朴素但它有一个非常实在的优势——它就是一条SQL语句的集合你拿到这份dump文件就能在当前任何一个MySQL版本上重建数据不挑环境不挑存储引擎不依赖特定目录权限。我自己经手过的场景里有几次是物理备份完全没法用最后靠mysqldump救回来的。比如某次某台机器的数据目录损坏XtraBackup的备份因为版本不匹配直接拒绝恢复而一个月前留下的mysqldump文件反而干干净净地导回了数据。从那时候起我就养成了一个习惯不管团队上了多高级的备份方案mysqldump这条兜底链路永远保留。这篇文章不会照抄官方文档里那几十个参数而是按“我实际在工作中怎么用”来写哪些参数是必须的哪些参数是坑不同场景下的命令长什么样备份完怎么校验恢复的时候又有什么容易被忽略的细节。如果你是刚接触MySQL的新手跟着走一遍就能上手如果你已经写了几年代码也可以看看有没有自己一直忽略的盲区。二、mysqldump的定位逻辑备份与物理备份怎么选2.1 逻辑备份和物理备份的本质区别先花点篇幅把概念理清因为很多人选错工具根源是没搞懂这两类备份在“备份了什么”上的差异。物理备份备份的是数据目录里的实际文件包括ibd文件、frm文件、redo log、undo log这些底层内容。恢复的时候直接把文件放回数据目录就行速度非常快还原出来的状态和备份那一刻几乎完全一致。逻辑备份则是把数据“翻译”成一条条SQL语句INSERT INTO、CREATE TABLE、CREATE DATABASE……mysqldump就是个典型的逻辑备份工具。恢复的时候相当于把备份里的SQL重放一遍让MySQL重新执行建表、插入数据的操作。从原理上你就会发现逻辑备份不依赖存储引擎不依赖文件系统甚至理论上可以跨大版本迁移。但代价就是慢——全量导出再导入面对上TB的数据量物理备份半小时能干完的活mysqldump可能要跑好几个小时。2.2 哪些场景该坚持用mysqldump结合我的经验下面这几类场景最好老老实实用mysqldump数据量在几十GB以内的小型或中型业务库导出和导入时间完全可接受跨版本升级场景比如从MySQL 5.7迁到8.0用逻辑导出再导入路径最稳迁移到不同存储引擎比如从MyISAM换成InnoDBmysqldump导出格式天然兼容只恢复某几张表的场景物理备份反而笨重dump文件可以单独提取归档、脱敏、临时环境搭建需要灵活筛选数据的场景。反过来说如果单库超过200GB或者对恢复时间目标RTO有很高要求那就别纠结了优先考虑物理备份方案。备份策略从来不是“哪个高级选哪个”而是“这个数据量级和故障等级下哪种方式来得及”。三、常用参数拆解哪些必须带、哪些是坑3.1 你必须先搞懂的一组核心参数mysqldump参数很多但在我日常脚本里反复出现的其实就十来个。下面按“我实际上手顺序”逐个说清楚。mysqldump \ -h 192.168.1.10 \ -P 3306 \ -u dump_user \ -p \ --single-transaction \ --quick \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --databases mydb \ /backup/mydb_$(date %F).sql--single-transaction这是InnoDB表备份的核心。它会在备份开始时开启一个可重复读的事务让整个导出过程看到的是同一个快照不锁表也不影响线上写入。这里有个前提就是你的表必须都是InnoDB引擎。如果库里还有MyISAM表这个参数就罩不住了MyISAM还是要锁表的。--quick默认开启的选项防止导出大表时把全部结果加载到内存而是逐行读取、逐行输出。对大库来说少了它极容易把内存打满。--routines / --triggers / --events分别导出存储过程、触发器、定时事件。这三个是经典漏网之鱼尤其是存储过程很多人备份了数据才发现恢复后少了一堆业务逻辑。--databases带上这个参数dump文件里会包含CREATE DATABASE和USE语句恢复时自动建库并切换到目标库。如果你只导出单表或指定库里的部分表就不要加这个参数。--set-gtid-purgedOFF在开启GTID的实例上不带这个参数导出的文件默认会包含SET GLOBAL.GTID_PURGED语句恢复到另一个开启了GTID的实例上时经常报错。不加还容易把目标实例的GTID执行历史搅乱。我建议常规逻辑备份都加上OFF。3.2 备份一致性--master-data与--source-data的选择如果只备份单机数据上面那组命令就够了。但如果后续还要做基于binlog的增量恢复或者搭建从库就需要在备份文件里记录“备份时刻对应的binlog坐标”。传统参数是--master-data2它会额外生成一条CHANGE MASTER TO语句同时会触发FLUSH TABLES WITH READ LOCK在备份刚开始的瞬间短暂锁表。MySQL 8.0.26开始官方引入了--source-data语义更清晰也能和--single-transaction配合。我的习惯是凡是做全量备份并且留着binlog的库都带上--source-data2。这里的关键点是“2”表示把CHANGE MASTER TO语句以注释形式写入dump文件。这样文件本身不会被误执行但你又能在文件头部找到binlog文件名和position后续增量恢复全靠它。3.3 常用参数对照表参数作用我的使用建议--single-transactionInnoDB一致性快照不锁表默认带上MyISAM表除外--quick逐行流式输出降低内存默认带上--routines导出存储过程和函数一定带上--triggers导出触发器一定带上--events导出事件调度器一定带上--databases导出建库和USE语句需要整库恢复时用--source-data2记录binlog坐标注释形式增量恢复必备--set-gtid-purgedOFF避免GTID冲突手动指定更稳--where按条件筛选行做部分数据导出时用--ignore-table跳过指定表某些大日志表不想备份时用3.4 容易被忽略的字符集与默认值细节如果你导出的是中文业务库强烈建议在命令里显式加一行--default-character-setutf8mb4。默认情况下mysqldump可能沿用客户端字符集如果客户端和库的字符集不一致导出来的中文极容易变成乱码或者出现字符集转换告警。还有一个看起来不起眼但其实很重要的细节mysqldump导出的CREATE TABLE语句里会保留字段的默认值定义。如果你备份前改过表结构比如把某个字段默认值从0改成了1恢复时这个默认值会一起建出来。这看着是好事但如果你只是把备份文件用来搭测试环境发现数据行为和生产不一致往往就是字段默认值被带过来的缘故。四、从零开始的备份实战完整命令与校验流程4.1 单库全量备份的完整流程先交代假设目标实例在192.168.1.10数据库名business有InnoDB的订单表、用户表也有几张MyISAM的历史归档表还挂着几个存储过程和定时事件。面对混合引擎的情况务必要重新考虑方案。我一贯的做法是分两步先专门处理InnoDB的核心表再把MyISAM的表在业务低峰期单独锁表导出。如果只是为了临时搭建环境整库一把锁也不是不可以但线上生产环境千万不要轻易锁全库。# 第一步导出InnoDB核心表启用单事务快照 mysqldump \ -h 192.168.1.10 \ -u dump_user \ -p \ --single-transaction \ --quick \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --databases business \ --ignore-tablebusiness.history_archive \ /backup/business_core_$(date %F).sql # 第二步单独导出MyISAM归档表业务低峰执行允许锁表 mysqldump \ -h 192.168.1.10 \ -u dump_user \ -p \ --quick \ business \ history_archive \ /backup/business_archive_$(date %F).sql这里要留意第二步命令里没有--databases库名后面直接跟表名这样导出文件里就不会有CREATE DATABASE语句导入时你可以自由选择放进哪个库。4.2 备份完成后的校验不做等于白备份我见过太多人备份完把文件往磁盘一丢就算完事结果真正要恢复的时候才发现文件只有几KB或者导出过程中断、SQL语句不完整。校验动作其实不复杂但必须养成习惯。第一步检查文件大小的变化趋势是否合理ls -lh /backup/*.sql如果你昨天的全量备份是2GB今天一下变成200MB先别急着夸自己备份快几乎肯定是中间断过。第二步在dump文件头部和尾部抽查关键语句head -n 50 /backup/business_core_$(date %F).sql tail -n 20 /backup/business_core_$(date %F).sql头部看有没有完整的SET NAMES、CREATE DATABASE、USE语句尾部看有没有Dump completed字样确认导出过程没有异常中断。更稳的校验方式是单独起一个临时实例把这个dump文件完整导入一遍然后跑几条关键统计SQL比如对比订单总数、用户总数、最大最小主键。生产环境每次全量备份都这样干有点奢侈但每周抽一次做完整恢复演练非常值得。4.3 大表备份性能优化经验单张表过亿行的时候mysqldump默认每行一条INSERT语句文件体积和导入速度都不太理想。这里有几个可调节的旋钮--extended-insert这个参数默认就是开启的它会把多行数据合并成一条INSERT语句插入。如果你看到命令里有人特意写--skip-extended-insert多半是为了让dump文件便于在某些场景下被第三方工具解析日常不要关。--net-buffer-length控制发送到网络缓冲区的数据包大小适当调高可以减少网络往返次数。一般不用动但如果你的备份走内网高速链路试试把它从默认值改到64MB。压缩备份文件导出时直接通过管道压缩比导出后再压缩省一半时间mysqldump ... | gzip /backup/business_$(date %F).sql.gz恢复的时候记得对应解压gunzip -c /backup/business_$(date %F).sql.gz | mysql -h 目标库 -u 用户名 -p上面这套组合下来我实测在普通SSD服务器上一个约30GB的业务库导出加压缩大约25到35分钟。具体数字取决于你的网络和CPU但方向是对的。五、增量备份怎么做mysqldump配合binlog的完整链路5.1 为什么全量备份永远不够再勤快的全量备份也很难覆盖极端情况备份完成后的下一个小时一条错误的DELETE把关键表清空了。这时候如果你只有昨天的全量备份意味着要丢掉整整一天的数据。完整的备份策略通常是“全量dump binlog增量”的组合拳。mysqldump负责周期性全量快照binlog负责记录快照之后的每一次数据变更。恢复的时候先恢复最近一次全量备份再把对应的binlog重放到故障发生前的那一刻。5.2 有了source-data坐标增量恢复才有抓手前面提到的--source-data2在这里派上用场了。恢复全量备份后你要从哪个binlog文件的哪个position开始重放就是从这个注释里读出来的。# 备份文件头部能看到类似这样的注释 # CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000023, MASTER_LOG_POS123456;然后目标库导入全量后用mysqlbinlog解析从该坐标开始的二进制日志mysqlbinlog \ --start-position123456 \ --stop-datetime2025-01-20 14:30:00 \ mysql-bin.000023 \ | mysql -h 目标库 -u 用户名 -p这里强烈建议加--stop-datetime把误操作的具体时间作为恢复终点避免把之后更多的坏操作也重放进去。5.3 日常增量恢复演练的简化步骤增量恢复比全量恢复更容易出错所以我自己每两周会做一次演练步骤基本固定准备一台干净的临时实例导入最近一次全量dump文件按备份文件头部的binlog坐标重放后续所有binlog对比临时实例和线上实例的关键数据总量确认无误后清空临时环境记录本次演练耗时。有这套演练在线上真的出问题时我不会心里没底。备份方案只有真刀真枪恢复过一次才算数。六、恢复数据时最容易踩的坑与处理思路6.1 用mysql命令导入时那些看不见的报错恢复数据最常见的姿势就是把dump文件直接喂给mysql客户端mysql -h 目标库 -u 用户名 -p /backup/business_$(date %F).sql但如果目标库的max_allowed_packet设置比源库小导入过程中会出现ERROR 1153 (08S01): Got a packet bigger than max_allowed_packet bytes的报错大字段、大文本、存储过程定义都可能触发。遇到这种情况导入前先把目标库的会话参数调大SET GLOBAL max_allowed_packet 1G;另一个容易忽视的坑是dump文件里带了SET NAMES utf8mb4但导入用的客户端字符集如果和目标库不一致依然可能出现乱码。稳妥做法是在导入命令里一并指定mysql --default-character-setutf8mb4 -h 目标库 -u 用户名 -p dump.sql6.2 只想恢复一张表没必要导入整个文件有一种尴尬场景线上某张表被误删了但你的备份是整库备份文件好几个GB。整库导入耗时太长而且会覆盖其他表的当前状态这肯定不行。正确的打开方式是用--tables配合--no-create-info或--no-data组合在备份阶段就把单表拆出来。比如备份时就单独导出每一张表或者写一个循环脚本每张表一个文件。恢复时只需要选对应的表文件导入。如果你手上已经只有整库dump文件不想重新备份也可以先导入到临时实例再从临时实例把那张表单独导出来最后导入生产库。流程多两步但安全可靠。6.3 从导出的SQL文件中抽取单表数据假设你手上只有一个整库dump文件现在只想知道某张表在备份那一刻的数据长什么样可以这样过滤awk /CREATE TABLE orders/,/Dump completed/ /backup/business_$(date %F).sql orders.sql严格来说在大文件里用awk抽取CREATE TABLE到文件末尾的范围并不总是精确的更稳妥的做法是先用grep定位CREATE TABLE出现在第几行grep -n CREATE TABLE \orders\ /backup/business_$(date %F).sql然后根据行号计算区间用sed抽取这种方法在几个GB的文件里也很快sed -n 12000,14500p /backup/business_$(date %F).sql orders.sql之后你就能单独导入这张表了。七、定时备份无需复杂工具写一个能用的备份脚本7.1 脚本设计的核心思路很多人一说定时备份就想上整套企业级软件其实一台摸得着的业务服务器mysqldump配合crontab就足够了。我自己的脚本分三块环境变量定义、备份函数、过期清理。写脚本时有几个关键点用MYSQL_PWD环境变量传递密码虽然方便但进程列表里会暴露更稳妥的是使用~/.my.cnf配置文件每次备份都记录日志日志里保留耗时、文件大小、导出是否成功的标记清理策略要明确本地保留7天异地或者对象存储保留1个月。7.2 可直接套用的备份脚本#!/bin/bash BACKUP_DIR/backup/mysql DATE$(date %F_%H%M) LOG_FILE/var/log/mysql_backup.log MYSQL_HOST127.0.0.1 MYSQL_USERdump_user DATABASESbusiness user center mkdir -p $BACKUP_DIR echo Backup start: $DATE $LOG_FILE for db in $DATABASES; do mysqldump \ -h $MYSQL_HOST \ -u $MYSQL_USER \ --single-transaction \ --quick \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --databases $db \ | gzip $BACKUP_DIR/${db}_${DATE}.sql.gz if [ $? -eq 0 ]; then echo $db backup success, size: $(du -h $BACKUP_DIR/${db}_${DATE}.sql.gz | cut -f1) $LOG_FILE else echo $db backup FAILED $LOG_FILE fi done # 清理7天前的备份 find $BACKUP_DIR -name *.sql.gz -mtime 7 -delete echo Backup end $LOG_FILE把脚本放进crontab每天凌晨2点执行0 2 * * * /usr/local/bin/mysql_backup.sh7.3 备份文件的异地保存脚本里只写本地保存肯定不够因为服务器磁盘坏了备份在本地一样丢。我的做法是把备份目录挂载到对象存储或者内网另一台机器上每天定时同步一次。做法很简单用rsync或同步工具即可rsync -avz /backup/mysql/ backup-server:/backup/mysql/如果条件允许更建议在备份机上通过对象存储API再存一份。注意定期做恢复测试别等到要用的时候才发现对象存储里的文件是坏的。八、还原演练中遇到的实际报错和处理链路8.1 报错Unknown table xxx in information_schema有次某同事做还原演练导入完全量备份后执行后续SQLMySQL报了这个错。线上环境里没有任何人手动建过这张表后面排查才发现原来是备份脚本里多了一个--all-databases把information_schema和performance_schema也导了出来。Duplicate的对象定义在导入时和系统库冲突了。这个排查链路让我意识到不是所有库都适合mysqldump。系统库里的表结构由MySQL自身管理把它们备份下来基本没有意义恢复时还会干扰实例初始化。从那以后我的备份清单里永远按业务库名一个个列出来不贪图方便用--all-databases。8.2 报错ERROR 1410 (42000) at line N: Binlogging on server not enabled严格说这个报错出现在恢复带有--set-gtid-purged相关语句的dump文件时。在未开启binlog的临时实例上导入MySQL会认为有非GTID事务混入而拒绝执行。遇到这种情况如果用不上GTID就回到最初的建议导出时加--set-gtid-purgedOFF。如果dump文件已经生成了临时实例又不想开GTID可以临时关闭binlog再导入mysql --init-commandSET SQL_LOG_BIN0; -u 用户名 -p dump.sql导入结束再把SQL_LOG_BIN恢复。这条命令在生产实例上做导入时也很有用能避免导入过程产生的binlog污染正式链路。8.3 报错Disk quota exceeded备份文件写不完整最典型的场景就是磁盘满了但mysqldump不会立刻失败而是持续运行然后中途退出。logs里可能只看到报错但文件已经存在。我的排查习惯是备份脚本里除了记录成功失败还要额外检查文件大小和新旧时间小于阈值就告警。另一层防线是提前监控磁盘使用率超过80%就要决定清理还是扩盘。某次生产环境就是备份目录悄悄满了备份文件只写了一半等真正要恢复时才发现那种感觉真的极其被动。8.4 一个通用的问题排查顺序如果你恢复数据时遇到任何报错我建议按下面这个顺序排查不要跳步确认源库和目标库的MySQL版本大版本是否兼容检查dump文件头部确认有没有GTID、SET NAMES、CHANGE MASTER相关语句定位报错行号用sed把那几行SQL单独抽出来看确认目标库的表结构、外键约束、字符集是否和源库匹配在临时实例上重放同样的导入复现问题修正后从最近一次成功的完整备份开始再次演练。九、关于备份验证和习惯的一些个人体会最后分享一点自己的体会。我们团队有一条规定任何备份方案上线前必须做一次完整的恢复演练并且演练结果要有记录。因为这个行业里的故障大多不是“没有备份”而是“备份在那里但恢复不了”。mysqldump的好处在于它是文本格式你能打开看、能检验、能抽取出了问题总能找到原因。坏处也明显就是慢但这是逻辑备份天然的特性又不是它的缺陷。我现在每一次写备份脚本都会在注释里写明三个内容备份目标、恢复步骤、验证方法。这样哪怕三个月后换了一个人接手也能照着文档完成一次恢复演练而不是对着脚本满脑门问号。备份这件事做成一条“不依赖某个人记忆”的标准化流程才算真正落地。