做运维和开发这些年MySQL备份恢复是我觉得最值得花时间吃透的一个基本功。很多新手觉得备份就是把整个库导出来存着可真到了线上环境数据库几百个G甚至几个T全库导出既慢又占空间很多时候你只需要把某个业务库、甚至某张核心表单独备份和恢复——比如误删了某个用户的表要快速把这张表捞回来。今天这篇就专门讲MySQL单库和单表的备份恢复把命令、参数、恢复细节和踩坑点一并打包。这篇文章的主线很明确先用最小可用命令解决“怎么备份单库、怎么备份单表”的问题再拆解每个关键参数背后到底在干什么最后把恢复的各种姿势和常见报错整理成速查表。不管你是刚入行的后端开发还是要接手生产库运维的同学照着操作都能落地。相比动不动就全库导出掌握单库单表粒度会让你的日常备份更灵活恢复速度也快一个量级。1. 为什么必须掌握MySQL单库单表的备份恢复1.1 全量备份不万能单库单表才是日常高频操作很多人一提到MySQL备份第一反应就是mysqldump全库导出。这个思路本身没错但放到真实业务里有两个问题时间和空间。我见过一个实际案例某业务库总大小接近800GB其中90%是日志表和归档数据真正高频读写的核心库只有几十GB。全库备份一次要跑将近两个小时每天备份占用的磁盘空间巨大恢复的时候更是煎熬。更尴尬的是有一次线上只需要恢复一张被误删的订单表如果只有全库备份就得把800GB全部导回去再挑数据费时费力还容易把其他正常数据搞乱。所以单库备份和单表备份不是全库备份的“简化版”而是完全不同的使用场景单库备份适用于一个实例上跑了多个业务库的情况。比如一套MySQL实例同时支撑订单库、用户库、商品库某个业务要发布新版本单独备份它对应的库就够了不影响其他库。单表备份适用于核心表单独保护和快速恢复。比如用户表、订单表这种变更频繁的表定期单独导出万一被误删或者被异常数据覆盖可以只恢复这一张表不用惊动整个库。另外还有一个高频需求是“把远程库的某张表同步到本地”本质上也是单表备份恢复的变体源库先导出这张表目标库导入。理解了单表备份这个操作你自然就会了。1.2 先搞清楚备份工具mysqldump的核心原理mysqldump是MySQL自带的逻辑备份工具官网每个版本都会同步发布不需要额外安装。它的核心原理说白了就是连接到MySQL服务端把表结构CREATE TABLE语句和表数据INSERT INTO语句一条条查出来然后写进一个SQL文本文件里。这个文件就是常说的dump文件。因为dump文件里存的是一行行SQL语句所以它有很强的可读性和可移植性你可以直接打开看内容可以用grep搜索某条数据可以把文件从MySQL 5.7导出的数据导入到MySQL 8.0甚至可以跨平台从Windows上的MySQL导出在Linux上的MySQL导入。这是物理备份直接拷贝数据文件做不到的。逻辑备份的代价是速度相对慢因为它本质上是“查一遍所有的数据再写SQL”数据量大了以后会很耗时。所以行业内的一般做法是数据量在几十GB量级、或者只备份部分核心表时用mysqldump数据量到了TB级别就改用物理备份工具比如XtraBackup直接拷贝文件。这篇文章聚焦的是单库、单表这种轻量级场景mysqldump完全够用且最通用。1.3 逻辑备份与物理备份怎么选我建议分三个维度来判断恢复粒度如果你只需要恢复某张表、某个库逻辑备份的dump文件最合适直接导入即可物理备份通常是以整个实例或整个库为单位恢复单表比较麻烦。数据量级单表数据量超过几GBmysqldump导出就会明显变慢物理备份拷贝文件要快得多。跨版本迁移从5.7迁到8.0逻辑备份几乎无障碍物理备份因为底层文件格式差异跨大版本基本不可用。单库和单表的备份恢复把mysqldump玩明白了日常90%的需求都能覆盖。接下来的命令和参数是这篇文章的核心干货。2. 单库与单表备份的完整命令与参数拆解2.1 单库备份一条命令搞定但这些参数必须懂备份单个库的标准命令是这样的mysqldump -u用户名 -p密码 -h主机地址 --single-transaction --set-gtid-purgedOFF --default-character-setutf8mb4 数据库名 /备份路径/库名_$(date %F).sql这里每个参数都不是随便加的逐个说清楚--single-transaction这是InnoDB引擎下最重要的参数。它会在导出开始时开启一个一致性的读事务让mysqldump基于这个事务的快照去读取数据。什么意思呢就是导出的过程中业务仍然可以正常写入不会锁表而且导出的数据是一致性的——不会出现“导了一半数据被修改导致前后对不上”的情况。--set-gtid-purgedOFF如果数据库开启了GTID模式dump文件默认会包含SET GLOBAL.GTID_PURGED相关的语句。如果在5.7导出的文件导入8.0或者目标实例的GTID状态和源库不一致恢复时会报错。加上这个参数可以避免很多莫名其妙的问题。--default-character-setutf8mb4强制指定客户端和导出文件的字符集为utf8mb4防止中文乱码。这里的坑在后面恢复部分还会再提到。-h如果不加默认连本地socket。跨主机备份时一定要显式指定主机地址另外执行mysqldump的机器上要有MySQL客户端工具否则会报command not found。如果你只想备份表结构、不要数据加--no-data只想备份数据、不要建表语句加--no-create-info。这两个参数在做结构变更、数据迁移时很常用。2.2 单表备份备份多个表与指定条件的玩法单表备份的命令长这样mysqldump -u用户名 -p密码 -h主机地址 --single-transaction --set-gtid-purgedOFF 数据库名 表名 /备份路径/表名_$(date %F).sql和单库备份唯一的区别就是库名后面再跟上表名。注意这个表名是不带反引号的如果表名里有特殊字符才需要加反引号。实际工作中经常遇到这些变体备份多张表用空格隔开mysqldump -u用户名 -p密码 --single-transaction 数据库名 表a 表b 表c 多表备份.sql只备份某张表的部分数据加--where条件。比如导出用户表中最近一年的数据mysqldump -u用户名 -p密码 --single-transaction 数据库名 用户表 --wherecreate_time 2024-01-01 用户表_近一年.sql这个--where后面可以接任意SQL条件对于大表做分区归档、或者只保留关键数据非常实用。经验是--where条件里的字段最好走索引否则在几千万行的大表上执行mysqldump时查询条件的筛选本身就会拖慢导出速度。2.3 备份文件内容解析拿到dump文件后怎么看备份完成之后我强烈建议你打开dump文件看一眼。一个标准dump文件的结构大致是-- MySQL dump 10.13 Distrib 8.0.33, for Linux (x86_64) -- Host: localhost Database: order_db -- ------------------------------------------------------ -- Server version 8.0.33 /*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT */; -- 各种SET语句字符集、外键检查开关、SQL模式等 -- 建表语句 CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) DEFAULT NULL, ... ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 数据部分 LOCK TABLES order_info WRITE; INSERT INTO order_info VALUES (1, NO20240801, ...); UNLOCK TABLES;这里有几个值得注意的细节第一文件开头的SET语句有一堆/*!40101 SET ... */这种带版本号的注释不是普通注释而是“可执行注释”——MySQL在4.01及以上版本会真正执行里面的SQL。很多恢复异常就出在这些SET语句上比如目标库字符集和源库不一致时。第二默认的dump文件里数据部分会先LOCK TABLES ... WRITE再INSERT最后UNLOCK TABLES。也就是说导入的时候会锁住目标表避免写入冲突。第三文件末尾通常有Dump completed on字样这表示文件导出完整。养成习惯恢复之前先确认这个文件是完整的别恢复了一半才发现文件损坏。3. 恢复单库与单表的实操全过程3.1 单库恢复mysql命令和source命令两种方式恢复单库本质上就是把dump文件里的SQL语句重新执行一遍。最常用的方式是重定向mysql -u用户名 -p密码 -h主机地址 目标数据库名 /备份路径/库名_2024-08-01.sql注意这里的目标数据库一定要提前创建好CREATE DATABASE IF NOT EXISTS 目标库名 DEFAULT CHARSET utf8mb4;如果不存在的库直接用mysql dump.sql会报ERROR 1049 (42000): Unknown database。另一种方式是在mysql命令行里用source命令mysql -u用户名 -p密码 -h主机地址进入交互界面后执行USE 目标库名; SOURCE /备份路径/库名_2024-08-01.sql;两种方式没有本质区别source命令适合你需要在导入过程中观察输出、分段执行时使用重定向方式适合在脚本里自动化执行。恢复整个库时有个最关键的细节dump文件里自带的建库语句不一定有。mysqldump导出时如果不加--databases或--all-databases参数dump文件里是只有建表语句、没有建库语句的。所以如果你用上面这种“库名.sql”的方式备份恢复前手动建库是必须步骤。如果你希望dump文件自己带建库语句备份时这么写mysqldump -u用户名 -p密码 --databases 数据库名 带建库语句.sql这样恢复的时候就不需要手动建库了直接导入即可。3.2 单表恢复如何把一张表恢复到指定库单表恢复的常规操作是mysql -u用户名 -p密码 目标数据库名 表名.sql但这里有一个经常踩的坑如果目标库里已经存在同名表默认情况下直接导入会报错因为建表语句执行失败。MySQL的dump文件里本身没有DROP TABLE IF EXISTS除非你加--add-drop-table参数。所以单表恢复我的标准流程是先处理旧表-- 方式一备份旧表再把原表改名 RENAME TABLE 目标表名 TO 目标表名_备份_20240801; -- 方式二直接删除旧表前提是你确认旧表数据不重要或者已经备份过 DROP TABLE IF EXISTS 目标表名;然后再执行导入mysql -u用户名 -p密码 目标数据库名 表名.sql还有更精细的用法如果你只想恢复某张表的部分数据可以先建好表然后用--ignore-table的方式只导入数据。不过实际执行中最稳妥的还是按上面的“先改名或删表、再导入”来做。另一个高频率场景是把一张表恢复到另一个库里。比如线上库误删了user_info从备份库恢复过来mysqldump -u用户名 -p密码 --single-transaction --set-gtid-purgedOFF backup_db user_info user_info.sql mysql -u用户名 -p密码 online_db user_info.sql只要目标库online_db里没有同名表这条链路就能直接把表从备份库搬到线上库。3.3 远程同步单表先导出再导入的落地流程“把远程库的这张表同步到本地”这个需求本质就是一次跨主机的单表备份恢复。流程其实不复杂但跨主机有几个小坑值得注意。第一步在远程源库执行导出。如果远程数据库只允许指定IP访问mysqldump的机器IP必须加白否则会报Host xxx is not allowed to connect to this MySQL server。第二步把dump文件传到本地。文件不大直接用scpscp 用户名远程主机:/备份路径/表名.sql /本地备份路径/文件大一点的可以用rsync断点续传避免网络不稳定导致文件损坏。第三步本地恢复mysql -u用户名 -p密码 本地目标库 表名.sql我实测下来这个流程最大的坑不是命令本身而是字符集。源库如果是latin1或gbk导出的dump文件即使看起来正常导入到本地utf8mb4库后中文可能直接变成乱码。解决办法是在导出的命令里强制指定字符集比如源库是gbk的话mysqldump -u用户名 -p密码 --default-character-setgbk 数据库名 表名 表名.sql导入端也指定对应的字符集mysql -u用户名 -p密码 --default-character-setutf8mb4 数据库名 表名.sql这样导出和导入的字符集转换链是可控的中文不会出问题。如果已经导出一份乱码文件也别慌用iconv转换文件编码可以挽救但最好的做法永远是导出之前就指定正确的字符集。4. 常见问题与排查技巧实录4.1 高频报错速查与解决方案我在平时操作和带新人过程中整理了一份最常见的报错速查表按出现频率排序报错信息原因解决方案ERROR 1049 (42000): Unknown database目标库不存在先CREATE DATABASE再导入ERROR 1050 (42S01): Table already exists目标表已存在DROP TABLE或RENAME TABLE旧表ERROR 1142 (42000): SELECT command denied导出账号权限不足给账号加SELECT、SHOW VIEW、LOCK TABLES权限ERROR 1227 (42000): Access denied导入账号权限不足给导入账号加目标库的ALL PRIVILEGESERROR 2002 (HY000): Cant connect to local MySQL server through socket本地socket连接失败加上-h 127.0.0.1走TCP连接或确认MySQL服务正常启动ERROR 1419 (HY000): You do not have the SUPER privilegedump文件里有DEFINER权限要求恢复时用有SUPER权限的账号或去掉DEFINER中文乱码导出/导入字符集不一致统一指定--default-character-setutf8mb4其中DEFINER这个坑值得单独强调。如果你在别人的dump文件里看到类似CREATE DEFINERroot192.168.1.10这样的语句导入时MySQL会校验这个账号是否存在、当前账号是否有对应权限。跨环境恢复时这个账号在目标库往往不存在直接报错。解决办法是用sed把DEFINER去掉sed -i s/DEFINER[^]*[^]*//g 表名.sql这个命令在生产环境非常实用尤其是当你拿到的是别人导出的dump文件。4.2 没有备份时如何通过binlog找回被删的表热词里有一个现实场景生产库没有备份但删除了某个用户下的所有表这种情况怎么恢复先说结论没有备份不代表完全没救前提是数据库开启了binlog日志并且日志还在。binlog是MySQL的二进制日志记录的是所有数据变更操作。只要binlog保留着理论上可以把数据库恢复到删除操作之前的任意时间点。这种场景是单表恢复的一个高难度进阶版原理就是先找一份基础备份哪怕是很久以前的再通过binlog“重放”从基础备份时间点到删除之前的所有变更。标准操作思路是第一步确认binlog文件都在SHOW BINARY LOGS;第二步找到删除操作对应的位置。可以用mysqlbinlog工具查看binlog内容定位到DROP TABLE或DELETE FROM语句所在的位置。第三步用基础备份恢复出删除前的状态然后用mysqlbinlog把删除之前的binlog变更重新导入mysqlbinlog --stop-datetime2024-08-01 10:00:00 /var/lib/mysql/binlog.000012 | mysql -u用户名 -p密码这个方案能不能成功完全取决于binlog保留的时间和基础备份的完整性。所以我为什么一直强调要定期做备份并且建议至少保留最近7天的备份文件——因为binlog配合备份才能完成真正的时间点恢复只靠binlog没有基础备份要从头开始重放几个月的数据耗费的时间几乎不可接受。4.3 几条保命级的运维建议最后把我这些年踩坑换来的建议整理一下。这些不是命令层面的操作但比命令更重要。其一备份脚本一定要加执行结果通知。我见过不少团队备份任务在凌晨两三点失败因为没人看日志直到需要恢复数据时才傻眼。哪怕是最简单的echo重定向到日志文件也比什么都不留强。其二恢复之前先做恢复演练。备份文件本身可能因为是磁盘满、网络中断等原因早已损坏定期拿备份文件在测试库试恢复一遍是验证备份文件可用的唯一靠谱方式。我自己就遇到过dump文件中间有坏块、导入到一半报错的情况幸好是在演练中发现的。其三给mysqldump账号单独建一个专用账号权限最小化。创建一个backup账号只给它SELECT、SHOW VIEW、LOCK TABLES、RELOAD权限不要直接使用root导数据。这样即使备份脚本被人拿到也无法对生产库做破坏性的写操作。其四大表导出时注意磁盘空间。一个10GB的表dump文件可能也是好几GB磁盘不够会导致导出到一半写不进去产生一个残缺文件。建议导出前用df -h确认磁盘剩余空间。其五不要在生产高峰期跑大型mysqldump。虽然--single-transaction不锁表但导出超大数据量时对IO和CPU的占用依然明显可能拖慢线上业务。把备份任务安排在低峰期执行这是对线上负责。我在实际工作中逐渐养成的习惯是核心单表每天备份一次业务库每周备份一次每份dump文件都附带一个md5sum校验值。恢复之前先校验文件完整性再操作。这套流程陪我扛过好几次线上事故也让我在需要快速恢复单表数据时从来不需要惊慌失措地翻找旧文档。单库和单表的备份恢复本质上就是在“全量备份太重”和“完全不备份太危险”之间找一个平衡点。把mysqldump的常用参数吃透、把恢复流程练熟、把binlog这个最后的保险绳系好日常运维中的数据恢复需求你基本都能从容应对。