MySQL备份表全攻略：四种方式对比与选型指南

处理过线上数据的人大概都遇到过这种需求马上要改一张表的字段或数据先复制一份留底或者要把线上订单表拖回本地排查问题又或者想拿一张大表做性能测试但又怕污染业务数据。MySQL 备份一张表看命令好像每个都会一点但真正选型时很多人会懵——有人只知道 mysqldump有人只会 CREATE TABLE AS SELECT结果备份到一半发现索引全丢了或者恢复时被外键卡住。MySQL 里常见的备份表方式实际上有四类库内直接复制CTAS、LIKE INSERT SELECT、逻辑导出导入mysqldump、纯文本搬运SELECT INTO OUTFILE LOAD DATA INFILE。这四者解决的是不同场景下的诉求没有绝对的好坏只有合不合适。这篇文章我会把这四种方式逐个拆开讲了原理、命令、常用参数也把实际使用中踩过的坑一并列出来适合刚接触 MySQL 的开发同学也适合需要经常处理数据迁移、表结构变更回滚的运维和 DBA 参考。1. 先判断你的备份诉求再选方式——四种方式的血缘关系很多人上来就问“哪个备份方式最快”这其实是没问到点子上。MySQL 备份一张表本质上只有两个维度需要关心备份结果要不要保留完整结构数据要不要跨实例移动想清楚这两个问题方式基本就定了。第一个维度是结构完整度。如果备份之后只是拿来做临时测试不涉及恢复和历史归档那结构丢了也无所谓但如果是给表结构变更做回滚准备主键、索引、自增属性、外键一个都不能少。第二个维度是数据是否要跨服务器。如果备份结果只在本库里生成新表CTAS 和 LIKE INSERT 就够了如果要把表迁到另一台机器、另一个环境你最终一定会得到文件那就要靠 mysqldump 或 OUTFILE。顺着这两个维度四种方式的血缘关系也清楚了CREATE TABLE AS SELECT直接在库内复制数据和列类型索引约束全丢最快最省事。CREATE TABLE LIKE INSERT INTO SELECT先在库内完整复刻一张空表结构、索引都在再往里面灌数据。mysqldump把表结构和数据一起写成 SQL 文件逻辑备份的代表跨服务器迁移的默认选项。SELECT INTO OUTFILE LOAD DATA INFILE只导出纯数据文本几乎不做结构处理但导入导出速度是四者中最猛的。我见过不少事故都是因为没想清楚需求就随手备份。比如有人用 CTAS 备份了线上表准备做结构变更改到一半发现备份表连主键都没有回滚时数据没法按预期恢复。也有人用 mysqldump 备份百 GB 大表导出占了几个小时的磁盘 IO结果业务高峰期被拖垮。这些问题的根源不是工具不好用而是没按场景选对方式。所以下文讲具体命令之前建议你先记住这张“血缘图”后面看每章就不会乱。2. 方式一CREATE TABLE AS SELECT一条 SQL 搞定快速复制2.1 最小可用操作CTAS 全称是 CREATE TABLE AS SELECT确实是一条 SQL 就能完成备份CREATE TABLE orders_bak_20250115 AS SELECT * FROM orders;执行完一张名为 orders_bak_20250115 的新表就出现了数据也一并复制进去。不需要额外工具不需要导出文件在客户端里跑一下就行。如果只是想备份部分数据加个 WHERE 条件CREATE TABLE orders_bak_20250115 AS SELECT * FROM orders WHERE create_time 2024-01-01;这条命令在很多公司内部被当成“最快备份法”新手尤其爱用因为它确实太好写了。2.2 它为什么快代价在哪CTAS 快的核心原因在于整个过程都在数据库内部完成没有网络传输没有文件落地新表的数据直接从原表的查询结果集写入存储引擎。相比 mysqldump 导出再导入省掉了两头的 IO 和 SQL 解析成本。但它的代价非常明显新表只会继承 SELECT 出来的列名和数据类型索引、主键、唯一约束、自增属性、外键、默认值通通不会复制。也就是说你备份出来的表本质上只是一张“长得有点像原表的裸表”。比如原表 orders 有主键 id、唯一索引 order_no、普通索引 idx_user_idCTAS 之后新表里这些索引一个都没有。原表 id 是 AUTO_INCREMENT 自增列备份表里 id 只是一个普通 INT插入时如果不手动指定 id后续写入完全没有自增能力。如果原表某些列带着 DEFAULT CURRENT_TIMESTAMP备份表的默认值也会变空。网上有人把 CTAS 说成“MySQL 的 COPY TABLE 命令”这个说法是误导的。准确说它只是“复制结果集”不是“复制表定义”。所以它更适合的场景是临时研究数据、测试 SQL 性能、导出一份数据快照给数据分析同学而不是严肃的备份恢复链路。2.3 操作中的注意事项以及如何补结构如果你确实要用 CTAS并且事后需要索引请务必记得手动补ALTER TABLE orders_bak_20250115 ADD PRIMARY KEY (id), ADD UNIQUE KEY uk_order_no (order_no), ADD KEY idx_user_id (user_id);执行 CREATE TABLE AS SELECT 时MySQL 实际上会执行一条带一致性快照读的 SELECT 语句在 REPEATABLE READ 隔离级别下语句开始时能看到一个一致的视图期间原表的并发写入不会阻塞这个备份过程。在大表上执行时要注意两个实际问题一是磁盘空间新表会完整占用一份数据先确认剩余空间二是语句执行期间会消耗较多 IO业务高峰期慎跑。还要留意一点如果原表属于分区表CTAS 不会保留分区定义新表就是一个普通表。如果你备份的是千万级以上大表CTAS 过程中排序或临时表可能导致临时空间暴涨建议评估后再执行。3. 方式二CREATE TABLE LIKE INSERT INTO SELECT结构最完整的库内复制3.1 两步走的完整操作如果你既想在数据库内部直接生成备份表又希望结构完整那就要把备份拆成两步-- 第一步完整复制表结构 CREATE TABLE orders_bak_20250115 LIKE orders; -- 第二步把数据灌进去 INSERT INTO orders_bak_20250115 SELECT * FROM orders;还可以分两步再配合条件复制部分数据CREATE TABLE orders_bak_20250115 LIKE orders; INSERT INTO orders_bak_20250115 SELECT * FROM orders WHERE status 1;对比 CTAS 那条一条龙命令这看起来多敲了一行但结果完全不同。第一步的 CREATE TABLE LIKE 会把原表的列定义、主键、唯一索引、普通索引、自增属性、默认值、外键全部复制过来。第二步 INSERT INTO ... SELECT 再把查询到的数据写进这张空表。两步合在一起既保留了结构又拿到了数据。3.2 为什么两部分缺一不可很多人会自作聪明地省略第一步直接执行 INSERT INTO new_table SELECT * FROM old_table然后报错“Table doesnt exist”。这显然不行因为 MySQL 不会像 CTAS 那样隐式建表。反过来只执行第一步、不执行第二步备份出来的就是一张空壳表没有任何数据并不能起到备份作用。这里顺便讲一下 CREATE TABLE LIKE 在结构保留上的边界因为它并不是 100% 原样复制。官方文档里明确过LIKE 不会复制触发器也不会复制分区定义。如果原表定义了 DELETE/UPDATE 触发器或是一张分区表LIKE 创建出来的备份表里都没有这些属性。因此严格意义上这种备份方式更适用于普通表。真实业务里遇到分区表备份我个人会优先选择 mysqldump后面会讲为什么。3.3 大表优化的实际经验INSERT INTO ... SELECT 的本质是单条 SQL在 InnoDB 里它会在语句开始时拿到一个一致的读视图所以备份出来的数据相对一致。但如果表非常大比如几个亿行这一步会长时间占用事务和 undo 空间对系统的影响不可忽视。我自己的经验是分两种处理第一种如果在低峰期备份直接一条 INSERT SELECT 跑完干净利落。但要注意目标表在第一步里已经带着索引了插入几亿行时维护索引的代价非常高速度会明显慢于 CTAS。如果数据量大到离谱可以先把第一步生成的备份表里的索引删掉插入完成后再重建索引CREATE TABLE orders_bak_20250115 LIKE orders; ALTER TABLE orders_bak_20250115 DROP INDEX idx_user_id, DROP INDEX idx_create_time, DROP PRIMARY KEY; INSERT INTO orders_bak_20250115 SELECT * FROM orders; ALTER TABLE orders_bak_20250115 ADD PRIMARY KEY (id), ADD KEY idx_user_id (user_id), ADD KEY idx_create_time (create_time);这个操作在千万级以上的场景下往往比带着索引直接灌数据快 30% 到 50%。代价是备份过程中表的完整结构有一段时间是缺失的如果做回滚演练要等索引重建完成才算真正可用。第二种如果业务不能停写而你又想分批备份按主键范围切片插入在逻辑上是可行的但要注意一致性问题。每一条 INSERT ... SELECT 都是一个独立的语句快照分批执行期间原表如果持续有数据写入备份表里的数据就不是同一个时间点的。它可以用于“大概留个底”的场景但不能当作严格一致性备份。真要严格一致就别分批用 mysqldump --single-transaction 更合适。4. 方式三mysqldump 单表导出再导入跨服务器迁移的默认选项4.1 导出一条命令mysqldump 是最经典的逻辑备份工具单表备份的命令并不复杂mysqldump -u backup_user -p \ --single-transaction \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ your_db orders /data/backup/orders_20250115.sql执行完会得到一个 SQL 文件里面既有 CREATE TABLE 语句也有 INSERT 数据语句把表结构和数据都打包了。复制这个文件到另一台机器再用 mysql 客户端导入就能完整恢复一张表mysql -u restore_user -p your_db /data/backup/orders_20250115.sql默认情况下备份文件里包含了 DROP TABLE IF EXISTS、CREATE TABLE、INSERT 等语句所以导入到目标库时会自动处理同名表。这个特性对单表恢复很友好但如果你导入时目标表里有不想丢的数据就要先看清楚备份文件头部的 DROP 语句。4.2 关键参数逐项解释很多人在备份时只是机械地敲mysqldump -u root -p db table backup.sql这在高可用环境或 GTID 开启的 MySQL 5.7 / 8.0 里容易出问题。几个关键参数说明一下--single-transaction对 InnoDB 表开启一个可重复读事务通过 MVCC 拿到一致性快照备份期间不锁业务表允许正常读写。这是在线备份的关键必须加。--set-gtid-purgedOFF实例开启 GTID 后默认导出的备份文件里会包含类似SET GLOBAL.GTID_PURGED...的语句。导入到目标库时如果目标库的 GTID 状态和源库不一致会直接报错。单表备份做普通迁移建议显式关掉。--default-character-setutf8mb4统一字符集避免导出导入过程中出现中文乱码。这个参数在 MySQL 5.7 和 8.0 里都推荐显式指定。--no-data只导出表结构不带数据。--no-create-info只导数据不导建表语句。--whereid 100000只导出满足条件的数据适合备份部分数据。--max-allowed-packet512M大表备份时增加单次报文上限防止大字段写入时备份失败。客户端和服务端的默认上限可能不够。--column-statistics0从 MySQL 8.0 导出的备份文件导入到 5.7 时建议加这个参数否则经常报Unknown table COLUMN_STATISTICS in information_schema这个问题我在跨版本迁移时遇到过不止一次。4.3 导入与排错导入单表时最常见的三个问题我都实际踩过第一个是 GTID 报错。备份时如果不加 --set-gtid-purgedOFF导入时可能提示GTID_PURGED cannot be changed。解决办法是重新备份或者在导入前确认目标库的 GTID 状态。单一做表备份时直接加上 --set-gtid-purgedOFF 最省心。第二个是 max_allowed_packet 太小。当一个字段非常大比如上 MB 的 TEXT 内容备份文件里的 INSERT 语句可能超过默认值导入时报Packet too large。解决方法是导入时加大参数mysql --max-allowed-packet512M -u restore_user -p your_db orders_20250115.sql第三个是外键约束。如果备份的表有外键且关联表还没有就绪导入时可能失败。通常可以导入前先关闭外键检查SET FOREIGN_KEY_CHECKS0; -- 执行导入内容 SET FOREIGN_KEY_CHECKS1;另外提醒一点mysqldump 备份的是逻辑 SQL导入时会逐条执行 INSERT速度在四种方式里是最慢的。如果是几十 GB 的单表导出和导入都会比较折磨人尽量放在维护窗口执行。对于超大单表我通常会把 mysqldump 用在“跨环境迁移 需要完整结构”的场景而不会用它做大库的常规备份。5. 方式四SELECT INTO OUTFILE LOAD DATA INFILE纯文本高速搬运5.1 导出从表到 CSV 文件第三种文件型备份方式是 SELECT INTO OUTFILE它直接把查询结果写到服务器本地的文本文件里SELECT id, order_no, amount, create_time INTO OUTFILE /var/lib/mysql-files/orders_20250115.csv FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n FROM orders;执行完成后服务器上会生成一个 CSV 格式的文件。这里有两个容易忽视的点一是文件生成在数据库服务器本地不是你的客户端机器所以要去服务器上看二是 MySQL 的 secure_file_priv 参数限制了导出路径默认通常只允许写到某个指定目录下比如 /var/lib/mysql-files/。你可以通过下面这条命令查看实际限制SHOW VARIABLES LIKE secure_file_priv;如果这个值是一个目录路径那导出文件只能写在那里如果是空字符串表示不限制如果是 NULL表示完全禁止导出。遇到权限问题时需要联系 DBA 调整 secure_file_priv 配置或者把文件路径换成允许的目录。5.2 导入LOAD DATA INFILE 的逆操作有导出自然有导入LOAD DATA INFILE 负责把文本文件搬回表里LOAD DATA INFILE /var/lib/mysql-files/orders_20250115.csv INTO TABLE orders_bak FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n (id, order_no, amount, create_time);注意两点第一LOAD DATA 默认只负责数据导入不会帮你建表所以目标表要先建好结构得提前准备好。第二如果 CSV 的列顺序和表结构不一致一定要在语句末尾写出列映射列表否则数据错位会非常难排查。SELECT INTO OUTFILE导出的文件中NULL 值默认会显示成\N导入时\N会被还原为 NULL。如果 CSV 是从其他系统来的空字符串和 NULL 混在一起可以在 LOAD DATA 语句里用 SET 做转换LOAD DATA INFILE /tmp/data.csv INTO TABLE orders_bak FIELDS TERMINATED BY , (id, order_no, amount, create_time) SET create_time NULLIF(create_time, );5.3 速度为什么快以及权限坑LOAD DATA INFILE 之所以快是因为它绕过了完整 SQL 解析和逐条的 INSERT 执行路径直接把文本数据流式装载进存储引擎。在 InnoDB 里面对百万行级别的数据它的导入速度通常比 mysqldump 恢复快一个数量级。正因如此它特别适合需要大批量交换纯数据的场景比如从业务库导出一份订单明细给数据分析环境或者把外部 CSV 批量灌入临时表。权限和配置方面有四个坑服务端需要 FILE 权限普通账号可能没有需要授权。如果文件不在 secure_file_priv 限制的目录内导出导入都会被拒。MySQL 8.0 默认可能关闭 local_infile如果你使用 LOAD DATA LOCAL INFILE 从客户端机器上传文件会报The used command is not allowed with this MySQL version。解决办法是开启服务端参数SET GLOBAL local_infile 1;客户端连接时还要带上--local-infile1mysql --local-infile1 -u root -p your_db这种方式完全不管表结构建表需要你提前准备。如果目标库里已经有一张结构一致的表直接 LOAD 进去即可如果没有先用 CREATE TABLE LIKE 或 mysqldump 的建表语句把结构准备好。6. 四种方案横向对比与我的选型逻辑6.1 一张表看清全局前四章把每种方式的命令和原理都过了一遍这里我用一张表做个横向对比方便你以后查阅对比维度CTASLIKE INSERT SELECTmysqldumpOUTFILE LOAD DATA结构保留度低索引/主键/自增全丢高几乎完整保留高含触发器/分区策略仅数据无结构是否生成文件否直接新表否直接新表是SQL 文件是CSV/文本文件跨服务器迁移不适合不适合最适合适合但需先建表一致性语句级快照语句级快照--single-transaction 快照普通SELECT快照大表速度较快一般受索引维护影响慢最快典型场景快速造测试表、临时分析表同库内完整结构备份、回滚准备跨环境迁移、逻辑备份归档大批量纯数据交换、对接分析系统注意上表中 OUTFILE 的“一致性”列我用的是“普通 SELECT 快照”就是说在 InnoDB 里 SELECT INTO OUTFILE 本身也能读到语句开始时的 MVCC 视图但因为它只导出数据整体备份链路的一致性取决于你建目标表时是否同步了结构。严格讲它更像“数据导出”而不是标准的备份方案。6.2 几个典型场景的选型案例我把实际工作中遇到过的几种诉求摆出来对照选择会更直观。场景一开发环境要做 SQL 优化想把线上百万行订单表拖一份到本地分析。线上表结构有没有索引不重要反正本地测试 SQL 时索引不一定一样。这种我会选 CTAS一条命令建一张新表速度快且不产生中间文件。场景二准备给订单表加一个新字段担心上线后要回滚先备份一份完整结构。这种我会选 LIKE INSERT SELECT因为它保留了主键、索引和默认值回滚时直接把备份表重命名顶上去就行最省心。场景三把一张表从测试环境迁到预发环境。这种必然涉及两台服务器文件是绕不开的。我会用 mysqldump加 --single-transaction 和 --set-gtid-purgedOFF导出后传到目标机器再导入。虽然慢但结构完整、过程可控。场景四数仓同学要求把订单表导出成 CSV 供外部系统分析。这种我会用 SELECT INTO OUTFILE导出时写好字段分隔符和包围符几百万行数据几分钟就搞定。根本不需要纠结表和索引因为外部系统只认文本。6.3 备份之后验证不能省最后说句实在话无论用哪种方式备份完成后一定要验证这是最容易偷懒也最容易出事的环节。至少要做两个检查行数一致性和关键字段抽样比对。行数检查很直接SELECT COUNT(*) FROM orders; SELECT COUNT(*) FROM orders_bak_20250115;如果想进一步核对数据一致性小表可以直接用 CHECKSUM TABLECHECKSUM TABLE orders, orders_bak_20250115;大表不建议用 CHECKSUM全表扫描算校验的开销不小我一般会在备份表上对主键最大值、记录数、以及几个业务关键字段做抽样统计对比比如 SUM(order_amount)、MAX(id)、MIN/MAX(create_time)基本可以快速发现大问题。备份这件事最怕的不是命令不会写而是做完之后拍脑袋觉得“应该没问题”。四种方式我这些年都用过现在遇到备份诉求时会先问自己三个问题结构完不完整数据要不要跨服务器表有多大回答完选型基本就清晰了。希望这篇分享能帮你少踩一些我踩过的坑。