1. 需求拆解先搞清楚要什么再选命令在Linux环境导出MySQL数据这件事看起来只是敲一条命令的事情但我在实际处理过的几十个场景里真正让人返工重来的从来不是命令写错而是导出之前没想清楚要什么。有人为了做一次全量归档直接用mysqldump单线程跑了六个小时业务高峰磁盘IO被打满也有人为了给业务方导一份报表用上了物理备份工具结果对方只是想在表格软件里看一眼数据。这些都是选型错误带来的成本。所以我把这套流程拆成两个阶段先判定需求再落到命令。需求判定其实就三个问题。第一数据体量。几百兆、几个G、几十G、上T对应的技术路线完全不一样。逻辑导出在几个G这个量级最舒服超过百G单线程基本不现实得上并行工具或者物理方案。第二导出期间能不能影响线上。这是最关键的一条。生产库上一个没加参数的mysqldump会给所有表加读锁秒级导出的还好跑几十分钟的导出就是事故。所以要先知道目标表是什么存储引擎、有没有大事务在跑、从库延迟多少。第三导出的下游用途。是为了恢复整个实例、迁移到别的机器、给分析同事做离线计算、给第三方做数据交换、还是单纯的合规归档。用途决定了你要导的是结构还是数据、要不要带上存储过程和触发器、文件格式是SQL还是CSV、要不要拆成多文件。把这三个问题回答清楚剩下的路径就基本唯一了。下面这张表是我按常见场景整理的选型对照实际出方案前我会先对着它扫一遍。导出方式典型数据量对线上影响输出形态适用场景mysqldump百M ~ 几十G可控制在很低单文件SQL全库/单库/单表逻辑备份、跨版本迁移SELECT INTO OUTFILE单表百万 ~ 千万行低一次查询裸数据文件单表数据导出、ETL、给下游算数mysql 客户端重定向小结果集低文本/TSV临时报表、脚本化小批量导出并行逻辑导出工具几十G ~ 几百G中等需控并发多文件按表拆分大库快速备份、恢复时间敏感物理备份百G ~ T级中等依赖IO二进制文件集实例级备份、TB级快速恢复从库导出任意极低各家形态生产库绝对不能碰时的唯一正解这张表里有个容易被忽略的细节对线上影响这一列其实跟工具无关跟你的参数和时机有关。同一个mysqldump加不加--single-transaction就是两种性质一个是元数据加读锁几秒钟一个是给全表加锁到导出结束。工具本身没有好坏参数才是分水岭。另外一个我经常提醒团队的点导出和导入要成对设计。很多人只想着怎么把数据弄出来忘了这份文件将来怎么进去。格式带不带转义、字符集是什么、NULL 怎么表示、时间字段是带时区还是不带这些在导入端会全部还回来。我见过最典型的案例是导出的 CSV 用逗号分隔但某个文本字段里恰好有逗号导入的时候整张表列错位回滚重建花了半天。所以在动手敲命令之前把这份文件将来谁读、用什么读一并想好能省掉后面 80% 的扯皮。2. mysqldump覆盖面最广参数也最容易写错2.1 核心参数背后的真实逻辑mysqldump是绝大多数人第一个接触的导出工具也是我认为最值得吃透的一个。它的默认行为和历史包袱都很多不理解参数背后的机制抄来的命令基本都是半成品。先说最核心的--single-transaction。它做的事情是在导出开始时开启一个可重复读REPEATABLE READ级别的事务并立刻取一次一致性快照。之后所有的SELECT都基于这个快照读取看到的是导出起点那一刻的数据。这意味着导出过程中业务可以继续写入导出文件里不会出现前半部分是旧数据、后半部分是新数据的撕裂。但这里有三个必须知道的前提只对事务型引擎有效。InnoDB 是安全的MyISAM 不是。如果库里混着 MyISAM 表那张表在导出期间依然会被加读锁业务写入会阻塞。DDL 会毁掉快照一致性。如果导出期间有人对正在导的表执行了ALTER TABLE快照可能被破坏甚至报错中断。所以导出前先确认没有正在跑的 DDL 变更工单。不能和--lock-all-tables混用。这两个参数是互斥的写了会直接报错退出。再说--quick。这个参数的作用是让驱动逐行取结果、逐行写文件而不是把整个结果集先拉进内存。大表导出不加它mysqldump 进程的内存占用会和表大小成正比几G的表足以把一台小内存机器的OOM打出来。虽然新版默认已经开启但我在脚本里依然会显式写出来因为脚本可能在任何版本上运行。字符集参数--default-character-setutf8mb4也是最常见的问题源。不指定的话导出的文件编码取决于服务端默认字符集跨机器导入时中文乱码的概率非常高。我的做法是导出脚本里永远显式指定字符集并且和源库、目标库保持一致不依赖任何默认值。完整一点的常用模板长这样mysqldump \ --host10.0.0.10 --port3306 \ --userexport_user --password*** \ --single-transaction \ --quick \ --default-character-setutf8mb4 \ --hex-blob \ --routines --triggers --events \ --set-gtid-purgedOFF \ --databases app_db \ /data/backup/app_db_$(date %Y%m%d_%H%M%S).sql几个参数值得单独展开。--hex-blob是二进制字段的保险遇到BLOB、BINARY类型时不开启会以原始字节写入编码转换过程中极易损坏开启后以十六进制形式输出虽然文件变大但可逆性有保证。--routines --triggers --events负责把存储过程、函数、触发器、定时事件一起带走默认是不导的很多人在恢复后才发现业务逻辑丢了。--set-gtid-purgedOFF在把数据导到非 GTID 环境、或者不希望目标库继承源库 GTID 信息时必须加上否则导入会直接报错。还有一个容易翻车的参数是--tz-utc它默认开启会把TIMESTAMP字段按 UTC 时间导出。如果源库和目标库的时区设置不一致导入之后时间会整体偏移几小时。我的经验是同版本、同时区的迁移保持默认跨时区迁移要么两端都统一成 UTC要么显式加--skip-tz-utc并在导入后核对时间字段。2.2 大库导出的分批、压缩与传输策略当库大到一定程度单文件mysqldump会同时遇到三个瓶颈单线程速度慢、单文件几十G不好传输也不好恢复、中途失败要全部重来。这时候就得改策略。第一个手段是按表拆分。指定库名加表名导出单表或者在脚本里遍历information_schema.tables拿到表清单后循环导出每张表一个文件。好处是失败的粒度小、可以并行、恢复时可以只恢复某张表。for tbl in $(mysql -N -e SELECT table_name FROM information_schema.tables \ WHERE table_schemaapp_db AND table_typeBASE TABLE); do mysqldump --single-transaction --quick \ --default-character-setutf8mb4 \ app_db $tbl /data/backup/tables/${tbl}.sql done第二个手段是按条件切分大表。全表几千万行的时候即便按表拆了单表文件依然巨大。这时可以用--where按主键区间切片比如按 ID 每 500 万一段导出每段一个文件。切片键必须是有序且分布均匀的字段主键自增是最理想的。mysqldump --single-transaction --quick --no-create-info \ --whereid 1 AND id 5000000 app_db orders orders_part1.sql注意--no-create-info只在第一个分片里需要导结构后面的分片只导数据否则导入时会重复建表报错。第三个手段是边导边压缩。这一步能省掉大量磁盘空间和传输时间代价是CPU稍微多一点。管道直接接gzip或者并行压缩工具都可以实测压缩比通常在三到五倍对文本型数据效果更好。mysqldump --single-transaction --quick app_db \ | gzip -6 /data/backup/app_db.sql.gz第四个手段是直接管道传输到远端不落本地磁盘。这在磁盘空间紧张的时候特别有用省掉了一次写盘。传输链路自己把握走内网专线或者已有的文件传输通道即可。注意压缩和传输管道有个隐藏风险——管道中间任何一环失败mysqldump进程可能收不到退出信号看起来还在跑但数据其实已经不完整了。所以脚本里要用set -o pipefail让管道失败向上传递并且导出后必须做完整性校验不能只看退出码。关于mysqldump还有一条实用经验导出前先把max_allowed_packet和net_write_timeout在会话里调大。偶尔会遇到单行数据特别长比如存了大段JSON的情况包大小不够会直接报错中断或者网络稍微抖动一下连接就断。在导出脚本开头加一句SET SESSION net_write_timeout600;就能规避掉大部分这类偶发问题。3. SELECT INTO OUTFILE单表落地最快的一条路3.1 权限与目录限制绕不过去SELECT ... INTO OUTFILE的逻辑很干脆让服务端把查询结果直接写成文件。它的速度优势明显因为没有客户端往返、没有逐行拼接SQL的额外开销几百万行的表通常几十秒就能落盘。但它有两条硬性限制很多人第一次用就卡在这里。第一文件只能写在数据库服务端所在的机器上不是执行客户端所在的机器。你在跳板机上执行这条语句文件出现在数据库服务器上需要再从那边搬走。这一点和mysqldump完全不同也是新手最容易混淆的地方。第二必须有FILE权限且目录受secure_file_priv约束。这个系统变量可能是NULL禁用文件导入导出、某个具体路径只能在这个目录下读写、或者空不限制。用下面这条命令确认SHOW VARIABLES LIKE secure_file_priv;如果返回值是NULL那这条路直接封死只能改配置后重启很多托管型数据库根本不给你改的权限。如果是具体路径导出语句里的文件名就必须落在这个目录下写别的地方会报The MySQL server is running with the --secure-file-priv option错误。另外它还有一个特性目标文件不能已经存在。同名文件在语句会直接报错不会覆盖。所以脚本里要用时间戳命名或者先清理旧文件。一条完整的导出语句大致是这样SELECT id, order_no, amount, created_at FROM app_db.orders WHERE created_at 2025-01-01 INTO OUTFILE /data/export/orders_202501.csv CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n;3.2 分隔符、NULL 与回灌的对称设计这一节是这条路线的真正难点也是我见过最多数据被悄悄改坏的地方。INTO OUTFILE默认的字段分隔符是制表符\t默认转义字符是反斜杠\默认的NULL表示是\N。也就是说如果你不加任何修饰子句导出的是一个用制表符分隔、带反斜杠转义的文本文件跟很多人想象中的标准 CSV 完全不是一回事。这里有几个必须处理好的点给下游表格软件读的数据要显式指定FIELDS TERMINATED BY ,并且加OPTIONALLY ENCLOSED BY 让文本字段带引号否则字段里出现逗号就会列错位。反斜杠转义会让某些解析器出错。导给第三方系统时我一般加上ESCAPED BY 关闭转义同时确保字段值里没有裸引号或者依赖ENCLOSED BY 处理。NULL和空字符串必须区分清楚。默认的\N表示NULL空字符串就是空。如果下游把\N当成普通文本处理数据语义就错了。如果你希望用空表示NULL可以在SELECT里用IFNULL(col, )显式转换但这样一来NULL和空串就再也分不开了要先确认业务能不能接受。中文编码要显式声明。八版本之后CHARACTER SET子句可以写在语句里不写就跟着列字符集走跨环境传递时容易出问题。导出只是半程回灌才是验证设计是否正确的时刻。LOAD DATA INFILE是对称的入口参数要和导出时严格对应LOAD DATA INFILE /data/export/orders_202501.csv INTO TABLE app_db.orders CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES;这里IGNORE 1 LINES用于跳过表头。但要注意INTO OUTFILE默认不写表头如果需要表头得把列名拼成一个UNION ALL查询或者在导出后用sed之类的方式补上第一行。我一般选择导出后补表头因为拼查询会拖慢导出速度而且列类型要手动转成字符串很啰嗦。还有一条实战经验单表数据超过千万行时INTO OUTFILE会生成一个巨大的单文件导入时同样慢。更好的做法是按主键区间分批导出多个文件每批控制在几百万行导入时循环执行失败了只重跑那一个分片。4. mysql 客户端重定向轻量脚本化的灵活打法4.1 -e 与 -B 的配合方式很多人不知道其实mysql命令行客户端本身就能当导出工具用。原理很简单用-e执行一条查询把标准输出重定向到文件-B参数让输出变成以制表符分隔的批处理格式不带表格边框和列名对齐的装饰字符。mysql -h 10.0.0.10 -u export_user -p*** -B -e \ SELECT id, order_no, amount FROM app_db.orders WHERE id 10000 \ /data/export/orders_small.tsv-B模式下有几个细节值得说清楚。字段之间用制表符分隔行之间用换行分隔NULL会被输出成字面量NULL四个字符——注意这和INTO OUTFILE的\N不一样两套机制对 NULL 的表示是不一致的脚本里混用会出问题。另外-B模式下默认会输出一行列名作为表头如果不想要表头加-N参数。这套打法的优势在于灵活和轻。它不需要FILE权限不需要secure_file_priv不受服务端目录限制输出直接落在你执行命令的机器上。小结果集、临时报表、脚本里需要动态拼查询的场景这是最省事的路子。但它的短板也很明显所有数据都要经过客户端内存和网络往返单行数据过长会碰到包大小限制数据量大时速度远不如前面两种方式。我一般只在小结果集几万行以内和需要动态拼接WHERE条件的场景用它。4.2 脚本化封装让导出任务可重复执行真正让这套方式发挥价值的是脚本化。散装命令谁都会敲能稳定重复执行的脚本才是生产可用的。我这边常用的一个封装思路是把连接信息、导出目录、文件名规则、失败重试全部收进一个脚本调用方只要传库名和查询条件。#!/bin/bash set -euo pipefail OUT_DIR/data/export TS$(date %Y%m%d_%H%M%S) NAMEreport_${TS}.tsv TMP${OUT_DIR}/.${NAME}.tmp mysql -h 10.0.0.10 -u export_user -p${DB_PASS} -B -N \ -e SELECT id, order_no, amount, created_at FROM app_db.orders WHERE created_at DATE_SUB(NOW(), INTERVAL 1 DAY) \ $TMP # 先写临时文件成功后再改名避免下游读到半截文件 mv $TMP ${OUT_DIR}/${NAME} gzip -6 ${OUT_DIR}/${NAME}这个脚本里有三个我认为必须保留的设计。set -euo pipefail保证任何一步失败都能中断而不是默默产出一个残缺文件。先写.tmp临时文件、成功后再重命名这样下游的读取程序永远不会读到写了一半的文件这个习惯在定时任务里能救命。最后是导出后立刻压缩历史文件堆积时的磁盘压力会小很多。再加一层的话可以引入重试逻辑。网络抖动、连接数打满、短暂锁等待这些都是一过性问题重试两三次大多能过去。但要注意重试只对可重入的操作安全如果导出脚本里包含清理旧文件之类的动作重试前要确认不会把上一次成功的产物删掉。5. 物理备份与从库导出大库绕不开的两条路5.1 什么时候必须放弃逻辑导出逻辑导出前面几种方式本质上是把数据重新组织成 SQL 语句或者文本中间要经过解析、拼接、写入单位时间的吞吐是有上限的。几十G的库还能忍上百G、T级的库单线程逻辑导出动辄十几个小时这已经不是优化参数能解决的问题了。到了这个量级物理备份才是正解。它的思路是直接拷贝数据文件不经过SQL层解析速度取决于磁盘IO而不是数据库逻辑处理能力几个T的数据在合理配置下可能一两个小时就能完成。常见工具是 XtraBackup 这类开源方案基本流程是热备份在线拷贝不阻塞写入加上 prepare 阶段把拷贝过程中产生的日志回放到数据文件里使其达到一致状态。xtrabackup --backup \ --target-dir/data/backup/full \ --host10.0.0.10 --userbackup_user --password*** \ --streamxbstream --compress \ | gzip /data/backup/full_$(date %Y%m%d).xb.gz用物理备份要接受几个约束条件源库和目标库的数据库大版本必须一致跨大版本恢复基本不可行目标机器要能承载同样的数据文件结构恢复时必须先执行 prepare不能直接拷进去就用。另外它对IO的冲击是持续性的放在业务高峰跑同样会把磁盘打满所以时机选择依然重要。选型上我的判断标准很简单恢复时间目标在几小时以内的实例级备份上物理单表、单库、跨版本迁移、需要人工检查内容的用逻辑。这两种不是替代关系实际生产里往往是物理备份做底座、逻辑导出做精细补充。5.2 从从库导出生产库的最后一道保险不管用哪种方式有一条原则我认为不容让步能不在主库上导就别在主库上导。只要架构里有从库导出任务就应该打到从库上。这带来两个好处一是主库完全不受影响二是可以更激进地调整参数比如加大读取并发、放开缓冲把导出速度提上去。但从从库导这件事本身也有技巧。最朴素的做法是直接在从库上跑mysqldump --single-transaction它读到的是从库当前的一致性快照。问题在于如果从库正在回放主库的日志导出期间数据会持续变化。虽然单事务保证了你看到的是快照但这个快照对应的主库位点并不确定做时间点恢复时不好对齐。更稳的做法是先把从库的回放线程暂停让从库静止在一个确定的位点上导出完成后再恢复。-- 在从库上执行 STOP SLAVE SQL_THREAD; SHOW SLAVE STATUS\G -- 记录当前的 Relay_Master_Log_File 和 Exec_Master_Log_Pos拿到确定的位点之后导出的数据就和这个位点严格对应后面做增量恢复或者数据核对都有据可依。导出结束再START SLAVE SQL_THREAD;恢复回放。这个窗口期内从库是落后主库的所以时间要控制住别一导就是几小时把延迟拖成事故。如果没有从库、又是托管型数据库不给物理备份权限那就只能退回到分批逻辑导出 拉到低峰时段的组合。这种情况下的核心是把大任务切成可控的小任务按表、按时间区间、按主键范围切分每个小任务独立可重试整体进度可观测。我处理过的一个案例里一张十几亿行的流水表最终是切成两百多个分片、用脚本串行跑了一整夜才在业务低峰窗口内完成的。6. 一致性、锁与故障排查实录6.1 三类高频故障的定位链路导出这件事命令写对只是起点真正花时间的是出问题之后的定位。我梳理下来绝大多数故障落在三类里。第一类锁等待和业务阻塞。现象是导出开始后业务接口大面积超时。定位顺序是先在数据库里看当前有哪些锁等待SELECT * FROM performance_schema.data_lock_waits; SHOW PROCESSLIST;如果看到阻塞的源头是导出会话在等SELECT的元数据锁或者表锁那基本就是丢了这个参数--single-transaction。另一个常见原因是导出期间有 DDL 在执行DDL 需要排他元数据锁会跟导出会话互相等待。这种情况没有优雅解法只能中断导出、等 DDL 完成再来。第二类连接中断和包过大。现象是导出跑了一半报Lost connection或者Packet too large文件被截断但退出码不一定非零。这类问题的排查要分网络和参数两头看。参数侧先确认max_allowed_packet够不够、net_write_timeout是不是太短网络侧看是不是走了会主动断长连接的中间层。我的习惯是在导出脚本里显式设置会话级参数并且用pipefail加事后校验来兜底。第三类磁盘和文件系统问题。现象是导出中途报No space left on device或者文件写入被拒绝。这个最好提前防导出前用df -h看清目标目录的可用空间按数据量 × 压缩比 × 1.5预留余量。还有一个隐蔽的坑是inode 耗尽按表拆分导出会生成大量小文件几百万个文件把 inode 用光报错信息和磁盘满很像但df -h显示还有空间得用df -i才能看出来。6.2 常见问题速查表我把这些年遇到的情况整理成一张表出问题时先对着扫一遍通常能省掉一半的排查时间。报错或现象可能原因处理方式Access denied; you need the FILE privilege账号缺 FILE 权限单独授权或改用客户端重定向方式secure-file-priv相关错误导出目录不在允许范围改到指定目录或调整系统变量后重启Lost connection during query网络中断、超时太短调大net_write_timeout、max_allowed_packet改用本地落盘Packet too large单行数据超包大小调大会话级max_allowed_packetNo space left on device磁盘满或 inode 耗尽df -h与df -i都看一遍导出文件比预期小很多进程被中断、管道失败未传递加set -o pipefail导出后做行数比对导入后中文乱码字符集未显式指定两端统一utf8mb4导出时显式声明导入后时间整体偏移--tz-utc与时区设置不一致核对源目标时区必要时--skip-tz-utc导入报 GTID 相关错误目标环境不认源库 GTID加--set-gtid-purgedOFF恢复后存储过程、触发器缺失默认不导出例程补--routines --triggers --events恢复后视图报 DEFINER 错误原定义者的账号在目标库不存在导出前改写 DEFINER或导入后批量调整INTO OUTFILE报文件已存在目标文件不允许覆盖文件名带时间戳或先删除旧文件导入时列错位字段含分隔符且未加引号包裹导出时OPTIONALLY ENCLOSED BY 导出期间业务大面积超时缺少--single-transaction补参数或改到从库执行6.3 校验怎么证明这份文件是对的这一节是我认为最该被反复强调、但实际最少人做的部分。导出的数据行数对不对、内容有没有被截断、编码是不是正确的如果不做校验等于把风险延后到恢复那一刻才爆发而那个时候往往是最没有余地的时刻。我常用的校验手段有三层按成本从低到高排。第一层是行数比对。导出前在源库记下目标表的准确行数导出后统计文件里的行数注意扣掉表头两边对上才算基本过关。这一步能发现绝大多数中断和截断问题成本几乎为零。# 源端行数 mysql -N -e SELECT COUNT(*) FROM app_db.orders WHERE created_at 2025-01-01 # 文件行数csv 有表头时减 1 wc -l /data/export/orders_202501.csv第二层是内容摘要比对。行数对了但内容被改坏的情况是存在的尤其是编码转换和转义处理环节。对关键表可以取若干列做一次聚合校验比如行数、金额总和、最大ID源端和文件端各算一次数值一致就说明主体数据是完整的。第三层是抽样回灌验证。挑一个有代表性的分片在测试环境里完整导入一遍然后对几行关键记录做逐字段比对。这一步成本最高但只有它能真正验证导出的文件将来能被正确读回来。注意这三层校验里第三层是唯一能覆盖格式设计错误的。前两层只能证明数据没丢证明不了数据能被正确解析。所以涉及重要交付时我一定会在测试库上完整走一遍导出到导入的全流程而不是等生产出问题再回头查。7. 我个人踩过的几个坑说几个印象深的。有一次给业务方导一份明细数据对方要求 CSV我按常规用了逗号分隔加引号包裹一切看起来正常。结果对方用表格软件打开之后整列数据往右偏了一格。排查了半天发现是某个备注字段里含有换行符——LINES TERMINATED BY \n这条规则被字段内部的换行符破坏了一行数据在文件里变成了两行。最终的解法是在导出前把这个字段的换行统一替换成空格或者干脆换成\r\n作为行分隔、并在字段级做转义处理。只要导出格式是分隔文本就必须假设字段里什么字符都可能有这是我这几年最深刻的一条教训。另一次是跨版本迁移源库是老版本、目标库是新版本用mysqldump导出来之后导入一直报错。原因是导出文件里带了一堆源库特有的系统变量设置和字符集写法新版本不认。解决办法是在导出时加上--set-gtid-purgedOFF并用--skip-add-locks、去掉文件头部的兼容性语句或者干脆只导数据不导结构表结构由目标端单独建。跨大版本迁移时永远优先考虑结构手工建、数据单独导不要指望一条命令搞定所有事。还有一个特别隐蔽的导出脚本跑了三个月都正常第四个月开始偶尔失败。查了很久才发现是文件堆积导致目录下的文件数接近上限某些操作开始变慢甚至失败。从那以后我所有的导出脚本都加上了保留策略只保留最近 N 天的产物超期的自动清理。自动化任务一定要配清理逻辑否则它迟早会以你想不到的方式失败。最后分享一个我一直在用的小习惯给每次导出任务建一条记录写上开始时间、数据量、耗时、文件大小、校验结果、执行账号。这条记录不占多少成本但事后要回答上个月那份数据是什么时候导的、导了多少、谁导的这类问题时它能省掉大量翻日志的时间。数据导出这件事做对一次不难难的是每一次都能说清楚它是怎么回事。
1. 需求拆解先搞清楚要什么再选命令在Linux环境导出MySQL数据这件事看起来只是敲一条命令的事情但我在实际处理过的几十个场景里真正让人返工重来的从来不是命令写错而是导出之前没想清楚要什么。有人为了做一次全量归档直接用mysqldump单线程跑了六个小时业务高峰磁盘IO被打满也有人为了给业务方导一份报表用上了物理备份工具结果对方只是想在表格软件里看一眼数据。这些都是选型错误带来的成本。所以我把这套流程拆成两个阶段先判定需求再落到命令。需求判定其实就三个问题。第一数据体量。几百兆、几个G、几十G、上T对应的技术路线完全不一样。逻辑导出在几个G这个量级最舒服超过百G单线程基本不现实得上并行工具或者物理方案。第二导出期间能不能影响线上。这是最关键的一条。生产库上一个没加参数的mysqldump会给所有表加读锁秒级导出的还好跑几十分钟的导出就是事故。所以要先知道目标表是什么存储引擎、有没有大事务在跑、从库延迟多少。第三导出的下游用途。是为了恢复整个实例、迁移到别的机器、给分析同事做离线计算、给第三方做数据交换、还是单纯的合规归档。用途决定了你要导的是结构还是数据、要不要带上存储过程和触发器、文件格式是SQL还是CSV、要不要拆成多文件。把这三个问题回答清楚剩下的路径就基本唯一了。下面这张表是我按常见场景整理的选型对照实际出方案前我会先对着它扫一遍。导出方式典型数据量对线上影响输出形态适用场景mysqldump百M ~ 几十G可控制在很低单文件SQL全库/单库/单表逻辑备份、跨版本迁移SELECT INTO OUTFILE单表百万 ~ 千万行低一次查询裸数据文件单表数据导出、ETL、给下游算数mysql 客户端重定向小结果集低文本/TSV临时报表、脚本化小批量导出并行逻辑导出工具几十G ~ 几百G中等需控并发多文件按表拆分大库快速备份、恢复时间敏感物理备份百G ~ T级中等依赖IO二进制文件集实例级备份、TB级快速恢复从库导出任意极低各家形态生产库绝对不能碰时的唯一正解这张表里有个容易被忽略的细节对线上影响这一列其实跟工具无关跟你的参数和时机有关。同一个mysqldump加不加--single-transaction就是两种性质一个是元数据加读锁几秒钟一个是给全表加锁到导出结束。工具本身没有好坏参数才是分水岭。另外一个我经常提醒团队的点导出和导入要成对设计。很多人只想着怎么把数据弄出来忘了这份文件将来怎么进去。格式带不带转义、字符集是什么、NULL 怎么表示、时间字段是带时区还是不带这些在导入端会全部还回来。我见过最典型的案例是导出的 CSV 用逗号分隔但某个文本字段里恰好有逗号导入的时候整张表列错位回滚重建花了半天。所以在动手敲命令之前把这份文件将来谁读、用什么读一并想好能省掉后面 80% 的扯皮。2. mysqldump覆盖面最广参数也最容易写错2.1 核心参数背后的真实逻辑mysqldump是绝大多数人第一个接触的导出工具也是我认为最值得吃透的一个。它的默认行为和历史包袱都很多不理解参数背后的机制抄来的命令基本都是半成品。先说最核心的--single-transaction。它做的事情是在导出开始时开启一个可重复读REPEATABLE READ级别的事务并立刻取一次一致性快照。之后所有的SELECT都基于这个快照读取看到的是导出起点那一刻的数据。这意味着导出过程中业务可以继续写入导出文件里不会出现前半部分是旧数据、后半部分是新数据的撕裂。但这里有三个必须知道的前提只对事务型引擎有效。InnoDB 是安全的MyISAM 不是。如果库里混着 MyISAM 表那张表在导出期间依然会被加读锁业务写入会阻塞。DDL 会毁掉快照一致性。如果导出期间有人对正在导的表执行了ALTER TABLE快照可能被破坏甚至报错中断。所以导出前先确认没有正在跑的 DDL 变更工单。不能和--lock-all-tables混用。这两个参数是互斥的写了会直接报错退出。再说--quick。这个参数的作用是让驱动逐行取结果、逐行写文件而不是把整个结果集先拉进内存。大表导出不加它mysqldump 进程的内存占用会和表大小成正比几G的表足以把一台小内存机器的OOM打出来。虽然新版默认已经开启但我在脚本里依然会显式写出来因为脚本可能在任何版本上运行。字符集参数--default-character-setutf8mb4也是最常见的问题源。不指定的话导出的文件编码取决于服务端默认字符集跨机器导入时中文乱码的概率非常高。我的做法是导出脚本里永远显式指定字符集并且和源库、目标库保持一致不依赖任何默认值。完整一点的常用模板长这样mysqldump \ --host10.0.0.10 --port3306 \ --userexport_user --password*** \ --single-transaction \ --quick \ --default-character-setutf8mb4 \ --hex-blob \ --routines --triggers --events \ --set-gtid-purgedOFF \ --databases app_db \ /data/backup/app_db_$(date %Y%m%d_%H%M%S).sql几个参数值得单独展开。--hex-blob是二进制字段的保险遇到BLOB、BINARY类型时不开启会以原始字节写入编码转换过程中极易损坏开启后以十六进制形式输出虽然文件变大但可逆性有保证。--routines --triggers --events负责把存储过程、函数、触发器、定时事件一起带走默认是不导的很多人在恢复后才发现业务逻辑丢了。--set-gtid-purgedOFF在把数据导到非 GTID 环境、或者不希望目标库继承源库 GTID 信息时必须加上否则导入会直接报错。还有一个容易翻车的参数是--tz-utc它默认开启会把TIMESTAMP字段按 UTC 时间导出。如果源库和目标库的时区设置不一致导入之后时间会整体偏移几小时。我的经验是同版本、同时区的迁移保持默认跨时区迁移要么两端都统一成 UTC要么显式加--skip-tz-utc并在导入后核对时间字段。2.2 大库导出的分批、压缩与传输策略当库大到一定程度单文件mysqldump会同时遇到三个瓶颈单线程速度慢、单文件几十G不好传输也不好恢复、中途失败要全部重来。这时候就得改策略。第一个手段是按表拆分。指定库名加表名导出单表或者在脚本里遍历information_schema.tables拿到表清单后循环导出每张表一个文件。好处是失败的粒度小、可以并行、恢复时可以只恢复某张表。for tbl in $(mysql -N -e SELECT table_name FROM information_schema.tables \ WHERE table_schemaapp_db AND table_typeBASE TABLE); do mysqldump --single-transaction --quick \ --default-character-setutf8mb4 \ app_db $tbl /data/backup/tables/${tbl}.sql done第二个手段是按条件切分大表。全表几千万行的时候即便按表拆了单表文件依然巨大。这时可以用--where按主键区间切片比如按 ID 每 500 万一段导出每段一个文件。切片键必须是有序且分布均匀的字段主键自增是最理想的。mysqldump --single-transaction --quick --no-create-info \ --whereid 1 AND id 5000000 app_db orders orders_part1.sql注意--no-create-info只在第一个分片里需要导结构后面的分片只导数据否则导入时会重复建表报错。第三个手段是边导边压缩。这一步能省掉大量磁盘空间和传输时间代价是CPU稍微多一点。管道直接接gzip或者并行压缩工具都可以实测压缩比通常在三到五倍对文本型数据效果更好。mysqldump --single-transaction --quick app_db \ | gzip -6 /data/backup/app_db.sql.gz第四个手段是直接管道传输到远端不落本地磁盘。这在磁盘空间紧张的时候特别有用省掉了一次写盘。传输链路自己把握走内网专线或者已有的文件传输通道即可。注意压缩和传输管道有个隐藏风险——管道中间任何一环失败mysqldump进程可能收不到退出信号看起来还在跑但数据其实已经不完整了。所以脚本里要用set -o pipefail让管道失败向上传递并且导出后必须做完整性校验不能只看退出码。关于mysqldump还有一条实用经验导出前先把max_allowed_packet和net_write_timeout在会话里调大。偶尔会遇到单行数据特别长比如存了大段JSON的情况包大小不够会直接报错中断或者网络稍微抖动一下连接就断。在导出脚本开头加一句SET SESSION net_write_timeout600;就能规避掉大部分这类偶发问题。3. SELECT INTO OUTFILE单表落地最快的一条路3.1 权限与目录限制绕不过去SELECT ... INTO OUTFILE的逻辑很干脆让服务端把查询结果直接写成文件。它的速度优势明显因为没有客户端往返、没有逐行拼接SQL的额外开销几百万行的表通常几十秒就能落盘。但它有两条硬性限制很多人第一次用就卡在这里。第一文件只能写在数据库服务端所在的机器上不是执行客户端所在的机器。你在跳板机上执行这条语句文件出现在数据库服务器上需要再从那边搬走。这一点和mysqldump完全不同也是新手最容易混淆的地方。第二必须有FILE权限且目录受secure_file_priv约束。这个系统变量可能是NULL禁用文件导入导出、某个具体路径只能在这个目录下读写、或者空不限制。用下面这条命令确认SHOW VARIABLES LIKE secure_file_priv;如果返回值是NULL那这条路直接封死只能改配置后重启很多托管型数据库根本不给你改的权限。如果是具体路径导出语句里的文件名就必须落在这个目录下写别的地方会报The MySQL server is running with the --secure-file-priv option错误。另外它还有一个特性目标文件不能已经存在。同名文件在语句会直接报错不会覆盖。所以脚本里要用时间戳命名或者先清理旧文件。一条完整的导出语句大致是这样SELECT id, order_no, amount, created_at FROM app_db.orders WHERE created_at 2025-01-01 INTO OUTFILE /data/export/orders_202501.csv CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n;3.2 分隔符、NULL 与回灌的对称设计这一节是这条路线的真正难点也是我见过最多数据被悄悄改坏的地方。INTO OUTFILE默认的字段分隔符是制表符\t默认转义字符是反斜杠\默认的NULL表示是\N。也就是说如果你不加任何修饰子句导出的是一个用制表符分隔、带反斜杠转义的文本文件跟很多人想象中的标准 CSV 完全不是一回事。这里有几个必须处理好的点给下游表格软件读的数据要显式指定FIELDS TERMINATED BY ,并且加OPTIONALLY ENCLOSED BY 让文本字段带引号否则字段里出现逗号就会列错位。反斜杠转义会让某些解析器出错。导给第三方系统时我一般加上ESCAPED BY 关闭转义同时确保字段值里没有裸引号或者依赖ENCLOSED BY 处理。NULL和空字符串必须区分清楚。默认的\N表示NULL空字符串就是空。如果下游把\N当成普通文本处理数据语义就错了。如果你希望用空表示NULL可以在SELECT里用IFNULL(col, )显式转换但这样一来NULL和空串就再也分不开了要先确认业务能不能接受。中文编码要显式声明。八版本之后CHARACTER SET子句可以写在语句里不写就跟着列字符集走跨环境传递时容易出问题。导出只是半程回灌才是验证设计是否正确的时刻。LOAD DATA INFILE是对称的入口参数要和导出时严格对应LOAD DATA INFILE /data/export/orders_202501.csv INTO TABLE app_db.orders CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES;这里IGNORE 1 LINES用于跳过表头。但要注意INTO OUTFILE默认不写表头如果需要表头得把列名拼成一个UNION ALL查询或者在导出后用sed之类的方式补上第一行。我一般选择导出后补表头因为拼查询会拖慢导出速度而且列类型要手动转成字符串很啰嗦。还有一条实战经验单表数据超过千万行时INTO OUTFILE会生成一个巨大的单文件导入时同样慢。更好的做法是按主键区间分批导出多个文件每批控制在几百万行导入时循环执行失败了只重跑那一个分片。4. mysql 客户端重定向轻量脚本化的灵活打法4.1 -e 与 -B 的配合方式很多人不知道其实mysql命令行客户端本身就能当导出工具用。原理很简单用-e执行一条查询把标准输出重定向到文件-B参数让输出变成以制表符分隔的批处理格式不带表格边框和列名对齐的装饰字符。mysql -h 10.0.0.10 -u export_user -p*** -B -e \ SELECT id, order_no, amount FROM app_db.orders WHERE id 10000 \ /data/export/orders_small.tsv-B模式下有几个细节值得说清楚。字段之间用制表符分隔行之间用换行分隔NULL会被输出成字面量NULL四个字符——注意这和INTO OUTFILE的\N不一样两套机制对 NULL 的表示是不一致的脚本里混用会出问题。另外-B模式下默认会输出一行列名作为表头如果不想要表头加-N参数。这套打法的优势在于灵活和轻。它不需要FILE权限不需要secure_file_priv不受服务端目录限制输出直接落在你执行命令的机器上。小结果集、临时报表、脚本里需要动态拼查询的场景这是最省事的路子。但它的短板也很明显所有数据都要经过客户端内存和网络往返单行数据过长会碰到包大小限制数据量大时速度远不如前面两种方式。我一般只在小结果集几万行以内和需要动态拼接WHERE条件的场景用它。4.2 脚本化封装让导出任务可重复执行真正让这套方式发挥价值的是脚本化。散装命令谁都会敲能稳定重复执行的脚本才是生产可用的。我这边常用的一个封装思路是把连接信息、导出目录、文件名规则、失败重试全部收进一个脚本调用方只要传库名和查询条件。#!/bin/bash set -euo pipefail OUT_DIR/data/export TS$(date %Y%m%d_%H%M%S) NAMEreport_${TS}.tsv TMP${OUT_DIR}/.${NAME}.tmp mysql -h 10.0.0.10 -u export_user -p${DB_PASS} -B -N \ -e SELECT id, order_no, amount, created_at FROM app_db.orders WHERE created_at DATE_SUB(NOW(), INTERVAL 1 DAY) \ $TMP # 先写临时文件成功后再改名避免下游读到半截文件 mv $TMP ${OUT_DIR}/${NAME} gzip -6 ${OUT_DIR}/${NAME}这个脚本里有三个我认为必须保留的设计。set -euo pipefail保证任何一步失败都能中断而不是默默产出一个残缺文件。先写.tmp临时文件、成功后再重命名这样下游的读取程序永远不会读到写了一半的文件这个习惯在定时任务里能救命。最后是导出后立刻压缩历史文件堆积时的磁盘压力会小很多。再加一层的话可以引入重试逻辑。网络抖动、连接数打满、短暂锁等待这些都是一过性问题重试两三次大多能过去。但要注意重试只对可重入的操作安全如果导出脚本里包含清理旧文件之类的动作重试前要确认不会把上一次成功的产物删掉。5. 物理备份与从库导出大库绕不开的两条路5.1 什么时候必须放弃逻辑导出逻辑导出前面几种方式本质上是把数据重新组织成 SQL 语句或者文本中间要经过解析、拼接、写入单位时间的吞吐是有上限的。几十G的库还能忍上百G、T级的库单线程逻辑导出动辄十几个小时这已经不是优化参数能解决的问题了。到了这个量级物理备份才是正解。它的思路是直接拷贝数据文件不经过SQL层解析速度取决于磁盘IO而不是数据库逻辑处理能力几个T的数据在合理配置下可能一两个小时就能完成。常见工具是 XtraBackup 这类开源方案基本流程是热备份在线拷贝不阻塞写入加上 prepare 阶段把拷贝过程中产生的日志回放到数据文件里使其达到一致状态。xtrabackup --backup \ --target-dir/data/backup/full \ --host10.0.0.10 --userbackup_user --password*** \ --streamxbstream --compress \ | gzip /data/backup/full_$(date %Y%m%d).xb.gz用物理备份要接受几个约束条件源库和目标库的数据库大版本必须一致跨大版本恢复基本不可行目标机器要能承载同样的数据文件结构恢复时必须先执行 prepare不能直接拷进去就用。另外它对IO的冲击是持续性的放在业务高峰跑同样会把磁盘打满所以时机选择依然重要。选型上我的判断标准很简单恢复时间目标在几小时以内的实例级备份上物理单表、单库、跨版本迁移、需要人工检查内容的用逻辑。这两种不是替代关系实际生产里往往是物理备份做底座、逻辑导出做精细补充。5.2 从从库导出生产库的最后一道保险不管用哪种方式有一条原则我认为不容让步能不在主库上导就别在主库上导。只要架构里有从库导出任务就应该打到从库上。这带来两个好处一是主库完全不受影响二是可以更激进地调整参数比如加大读取并发、放开缓冲把导出速度提上去。但从从库导这件事本身也有技巧。最朴素的做法是直接在从库上跑mysqldump --single-transaction它读到的是从库当前的一致性快照。问题在于如果从库正在回放主库的日志导出期间数据会持续变化。虽然单事务保证了你看到的是快照但这个快照对应的主库位点并不确定做时间点恢复时不好对齐。更稳的做法是先把从库的回放线程暂停让从库静止在一个确定的位点上导出完成后再恢复。-- 在从库上执行 STOP SLAVE SQL_THREAD; SHOW SLAVE STATUS\G -- 记录当前的 Relay_Master_Log_File 和 Exec_Master_Log_Pos拿到确定的位点之后导出的数据就和这个位点严格对应后面做增量恢复或者数据核对都有据可依。导出结束再START SLAVE SQL_THREAD;恢复回放。这个窗口期内从库是落后主库的所以时间要控制住别一导就是几小时把延迟拖成事故。如果没有从库、又是托管型数据库不给物理备份权限那就只能退回到分批逻辑导出 拉到低峰时段的组合。这种情况下的核心是把大任务切成可控的小任务按表、按时间区间、按主键范围切分每个小任务独立可重试整体进度可观测。我处理过的一个案例里一张十几亿行的流水表最终是切成两百多个分片、用脚本串行跑了一整夜才在业务低峰窗口内完成的。6. 一致性、锁与故障排查实录6.1 三类高频故障的定位链路导出这件事命令写对只是起点真正花时间的是出问题之后的定位。我梳理下来绝大多数故障落在三类里。第一类锁等待和业务阻塞。现象是导出开始后业务接口大面积超时。定位顺序是先在数据库里看当前有哪些锁等待SELECT * FROM performance_schema.data_lock_waits; SHOW PROCESSLIST;如果看到阻塞的源头是导出会话在等SELECT的元数据锁或者表锁那基本就是丢了这个参数--single-transaction。另一个常见原因是导出期间有 DDL 在执行DDL 需要排他元数据锁会跟导出会话互相等待。这种情况没有优雅解法只能中断导出、等 DDL 完成再来。第二类连接中断和包过大。现象是导出跑了一半报Lost connection或者Packet too large文件被截断但退出码不一定非零。这类问题的排查要分网络和参数两头看。参数侧先确认max_allowed_packet够不够、net_write_timeout是不是太短网络侧看是不是走了会主动断长连接的中间层。我的习惯是在导出脚本里显式设置会话级参数并且用pipefail加事后校验来兜底。第三类磁盘和文件系统问题。现象是导出中途报No space left on device或者文件写入被拒绝。这个最好提前防导出前用df -h看清目标目录的可用空间按数据量 × 压缩比 × 1.5预留余量。还有一个隐蔽的坑是inode 耗尽按表拆分导出会生成大量小文件几百万个文件把 inode 用光报错信息和磁盘满很像但df -h显示还有空间得用df -i才能看出来。6.2 常见问题速查表我把这些年遇到的情况整理成一张表出问题时先对着扫一遍通常能省掉一半的排查时间。报错或现象可能原因处理方式Access denied; you need the FILE privilege账号缺 FILE 权限单独授权或改用客户端重定向方式secure-file-priv相关错误导出目录不在允许范围改到指定目录或调整系统变量后重启Lost connection during query网络中断、超时太短调大net_write_timeout、max_allowed_packet改用本地落盘Packet too large单行数据超包大小调大会话级max_allowed_packetNo space left on device磁盘满或 inode 耗尽df -h与df -i都看一遍导出文件比预期小很多进程被中断、管道失败未传递加set -o pipefail导出后做行数比对导入后中文乱码字符集未显式指定两端统一utf8mb4导出时显式声明导入后时间整体偏移--tz-utc与时区设置不一致核对源目标时区必要时--skip-tz-utc导入报 GTID 相关错误目标环境不认源库 GTID加--set-gtid-purgedOFF恢复后存储过程、触发器缺失默认不导出例程补--routines --triggers --events恢复后视图报 DEFINER 错误原定义者的账号在目标库不存在导出前改写 DEFINER或导入后批量调整INTO OUTFILE报文件已存在目标文件不允许覆盖文件名带时间戳或先删除旧文件导入时列错位字段含分隔符且未加引号包裹导出时OPTIONALLY ENCLOSED BY 导出期间业务大面积超时缺少--single-transaction补参数或改到从库执行6.3 校验怎么证明这份文件是对的这一节是我认为最该被反复强调、但实际最少人做的部分。导出的数据行数对不对、内容有没有被截断、编码是不是正确的如果不做校验等于把风险延后到恢复那一刻才爆发而那个时候往往是最没有余地的时刻。我常用的校验手段有三层按成本从低到高排。第一层是行数比对。导出前在源库记下目标表的准确行数导出后统计文件里的行数注意扣掉表头两边对上才算基本过关。这一步能发现绝大多数中断和截断问题成本几乎为零。# 源端行数 mysql -N -e SELECT COUNT(*) FROM app_db.orders WHERE created_at 2025-01-01 # 文件行数csv 有表头时减 1 wc -l /data/export/orders_202501.csv第二层是内容摘要比对。行数对了但内容被改坏的情况是存在的尤其是编码转换和转义处理环节。对关键表可以取若干列做一次聚合校验比如行数、金额总和、最大ID源端和文件端各算一次数值一致就说明主体数据是完整的。第三层是抽样回灌验证。挑一个有代表性的分片在测试环境里完整导入一遍然后对几行关键记录做逐字段比对。这一步成本最高但只有它能真正验证导出的文件将来能被正确读回来。注意这三层校验里第三层是唯一能覆盖格式设计错误的。前两层只能证明数据没丢证明不了数据能被正确解析。所以涉及重要交付时我一定会在测试库上完整走一遍导出到导入的全流程而不是等生产出问题再回头查。7. 我个人踩过的几个坑说几个印象深的。有一次给业务方导一份明细数据对方要求 CSV我按常规用了逗号分隔加引号包裹一切看起来正常。结果对方用表格软件打开之后整列数据往右偏了一格。排查了半天发现是某个备注字段里含有换行符——LINES TERMINATED BY \n这条规则被字段内部的换行符破坏了一行数据在文件里变成了两行。最终的解法是在导出前把这个字段的换行统一替换成空格或者干脆换成\r\n作为行分隔、并在字段级做转义处理。只要导出格式是分隔文本就必须假设字段里什么字符都可能有这是我这几年最深刻的一条教训。另一次是跨版本迁移源库是老版本、目标库是新版本用mysqldump导出来之后导入一直报错。原因是导出文件里带了一堆源库特有的系统变量设置和字符集写法新版本不认。解决办法是在导出时加上--set-gtid-purgedOFF并用--skip-add-locks、去掉文件头部的兼容性语句或者干脆只导数据不导结构表结构由目标端单独建。跨大版本迁移时永远优先考虑结构手工建、数据单独导不要指望一条命令搞定所有事。还有一个特别隐蔽的导出脚本跑了三个月都正常第四个月开始偶尔失败。查了很久才发现是文件堆积导致目录下的文件数接近上限某些操作开始变慢甚至失败。从那以后我所有的导出脚本都加上了保留策略只保留最近 N 天的产物超期的自动清理。自动化任务一定要配清理逻辑否则它迟早会以你想不到的方式失败。最后分享一个我一直在用的小习惯给每次导出任务建一条记录写上开始时间、数据量、耗时、文件大小、校验结果、执行账号。这条记录不占多少成本但事后要回答上个月那份数据是什么时候导的、导了多少、谁导的这类问题时它能省掉大量翻日志的时间。数据导出这件事做对一次不难难的是每一次都能说清楚它是怎么回事。
