mysqldump备份与恢复实战：参数组合、锁事务与定时脚本全解析

说个现实情况不少老MySQL DBA是“备份全靠mysqldump恢复全靠运气”的状态。手头确实跑着定时备份可真到要恢复的那天要么文件导入一半报错要么导出来的表结构里少了存储过程和触发器要么备份过程中把线上库锁了半天。mysqldump看起来就是一行命令的事真正用好的关键全在参数组合和场景判断上。这篇文章我打算把mysqldump的使用方法完整梳理一遍从逻辑备份和物理备份的取舍、常用命令的参数组合逻辑到按库/表/条件导出的各种姿势、恢复操作的检查清单、备份期间的锁与事务影响最后附上一份可以直接拿去改的定时备份脚本。适合刚接手数据库维护、正在搭备份方案、或者准备做跨版本迁移的读者。文章不会从头讲MySQL怎么装只讲mysqldump怎么用到位。1. 为什么数据库备份首选mysqldump逻辑备份与物理备份的取舍1.1 mysqldump到底做了什么mysqldump本质上是一个客户端工具它连上MySQL服务端把表结构和表中的数据翻译成一条条SQL语句输出到一个文件里。你打开备份文件会看到建表语句CREATE TABLE、插入语句INSERT INTO以及一堆SET参数、锁表和恢复初始状态的语句。这里要跟物理备份做个明确区分逻辑备份mysqldump把数据变成文本SQL跟数据库版本、操作系统平台基本无关。物理备份如Percona XtraBackup直接拷贝MySQL的数据文件ibd、frm等备份速度快恢复速度也快但只能在同版本甚至同平台的MySQL上使用。用一句话类比mysqldump是给你一份带图文排版说明书的购物清单物理备份是直接把整个超市仓库搬走。前者更灵活后者更粗暴高效。1.2 它最擅长的几个场景根据我日常运维和帮人救火的经验mysqldump最合适的场景有这么几类中、小型数据库的常规备份单库几个GB到几十GBmysqldump完全扛得住。跨版本、跨平台迁移比如MySQL 5.7迁到8.0Windows迁到Linux源码包迁到Docker都用它导出再导入避开物理文件不兼容的问题。库/表/行级别的选择性导出你不需要整个实例只想导某几张表或者某个时间段的订单数据mysqldump灵活。只要结构不要数据生成建表语句用于评审、文档、或者给其他系统建对应表。实际工作中不少同事会把MySQL表结构抽出来做数据字典再转成其他数据库的建模参考——这种情况第一反应就是mysqldump --no-data。从从库备份可以连接只读从库执行mysqldump不压主库。1.3 什么时候别用mysqldump别过度迷信它。遇到下面这些情况建议换XtraBackup或企业版备份工具库非常大单实例数据量到几百GB、上TBmysqldump的恢复是逐条执行INSERT恢复时间可能是备份时间的数倍线上根本等不起。对RPO/RTO要求很高生产核心库要求分钟级甚至秒级恢复逻辑备份做不到需要物理备份binlog增量组合。表基本是MyISAMMyISAM存储引擎没有事务mysqldump备份时只能锁表全程阻塞写入这种库直接改InnoDB或者寻找其他工具。下面这张表是我做方案选型时经常参考的对比对比维度mysqldump物理备份XtraBackup备份产物SQL文本数据文件拷贝备份速度慢逐行查询导出快文件级拷贝恢复速度很慢逐条执行SQL快文件就位即可跨版本迁移友好基本不友好粒度控制库、表、行都可以实例级为主对在线业务影响配合参数可减少锁表本身影响小但需要磁盘空间适用规模几十GB以内较舒服几百GB以上首选原则就一条工具不分好坏分场景。mysqldump的好处是处处可用坏处是大到一定程度就不好用了。2. 先把最常用的命令跑通一条备份命令里的参数组合逻辑2.1 一条生产环境常用的备份命令先别急着记几十个参数只需要把这条命令吃透mysqldump \ -u backup_user -p \ -h 127.0.0.1 -P 3306 \ --single-transaction \ --routines --triggers --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ --max-allowed-packet256M \ --hex-blob \ --databases yourdb yourdb_$(date %F).sql逐项解释为什么这么写这是重点。--single-transaction不写这个参数mysqldump会默认对表加读锁整个备份过程业务写全部堵住。加上它备份会在InnoDB引擎上开启一个可重复读的隔离事务基于一致性快照读取数据备份期间不阻塞线上写入。这条参数是“不影响线上业务”的核心。--routines --triggers --events这三个参数分别导出存储过程/函数、触发器、定时事件。不写的话备份文件里只有表和视图恢复之后你的业务逻辑就缺了一大块而且不容易发现。很多“备份成功了但恢复后功能不对”的故障就是缺了这三个参数。--set-gtid-purgedOFFMySQL 5.6以后引入GTID8.0默认开启。开启GTID的实例导出时备份文件会带一句SET GLOBAL.GTID_PURGED...。如果你把这份文件导入一个已经有其他GTID记录的实例大概率报ERROR 3546。加这个参数后文件里不写GTID相关信息导入更顺滑。但如果你是在搭建基于GTID的主从复制反而要把这个参数去掉并配合--master-data2获取位点。后续章节会再展开。--default-character-setutf8mb4字符集问题非常阴险。不加这个参数导出时按服务端默认字符集来读遇到中文乱码、表情符号变问号都很常见。utf8mb4是MySQL里最通用的字符集导出导入都指定它是减少乱码问题的基础动作。--max-allowed-packet256M备份文件里如果有特别大的行比如带大字段、长文本默认的max_allowed_packet过小会导出失败报错信息类似“Packets larger than max_allowed_packet are not allowed”。如果库里没有大字段用默认值也行但直接设大一点省得半夜备份失败。--hex-blob涉及BINARY、VARBINARY、BLOB这类二进制类型时以十六进制形式导出避免中间环节把它当普通字符做字符集转换导致内容损坏。--databases yourdb带这个参数后备份文件里会包含CREATE DATABASE IF NOT EXISTS yourdb和USE yourdb语句。恢复的时候不用手动建库选库直接导入即可。如果不带这个参数备份文件是针对当前默认库的表备份恢复时需要先建库再指定库名导入。生产环境我习惯带--databases因为备份文件更“自包含”误操作概率更小。2.2 参数之间怎么搭配再补几个跟上面命令配套的常见搭配逻辑。如果希望插入语句带上完整的列名可以加--complete-insert。这在你把数据导入到一张列顺序不完全相同的表时很有用。代价是文件变大恢复变慢所以默认mysqldump是开启--opt优化开关包含扩展插入、快速模式等的extended-insert会把多条记录合并成一条INSERT恢复速度更快。很多人会听说--opt默认开启它实际组合了这些行为--add-drop-table、--add-locks、--create-options、--disable-keys、--extended-insert、--lock-tables、--quick、--set-charset。也就是说默认情况下备份文件里会带上DROP TABLE IF EXISTS恢复时会先把目标表删掉再重建。这是便利也是危险——对生产库恢复时千万注意。旧版MySQL还有一个隐藏知识点--quick默认打开作用是边查询边输出到文件而不是把所有结果先放进内存。如果你看到有人在老版本里显式加--quick就是这个原因。新版客户端这个行为更完善但了解它有助于理解为什么大库备份时mysqldump进程占用内存并不高。2.3 常用参数速查表整理了一份常被我拿来翻的参数表按用途分类参数作用什么时候一定要用--single-transaction基于事务一致性快照备份不锁写InnoDB在线备份--routines导出存储过程和函数库里有业务存过--triggers导出触发器库里有触发器的场景--events导出定时事件库里有事件调度器任务--set-gtid-purgedOFF导出的SQL不带GTID信息普通备份/导入已有数据的库--master-data2在注释里记录binlog文件名和位点克隆实例、搭从库--no-data只导出表结构需要建表语句--where条件按条件导出行数据按时间段/ID范围导出--no-create-info只导出数据不导出结构已建好表只补数据--complete-insertINSERT带全列名目标表列顺序不一致--hex-blob二进制字段转十六进制库里有BLOB/BINARY类型--compress网络传输时压缩远程备份网络带宽有限--default-character-set指定客户端交互字符集防中文乱码必加-T/--tab生成制表符分隔的txt文件需要外部ETL读取纯文本这些参数不用全背把最常用的五六条记牢剩下的遇到场景再查。3. 按需备份的几种常用姿势库、表、条件与远程3.1 全库备份与多库备份全库备份有两种写法效果不一样# 写法A不带--databases只导出当前选择库下的表 mysqldump -u user -p --single-transaction yourdb yourdb.sql # 写法B带--databases文件包含建库和切库语句 mysqldump -u user -p --single-transaction --databases yourdb yourdb.sql写法A在恢复时必须先手动创建数据库然后mysql -u user -p yourdb yourdb.sql写法B直接导入就能把库建好。多库备份则是mysqldump -u user -p --single-transaction --databases db1 db2 db3 multi_db.sql我建议生产环境用带--databases的写法理由有三点备份文件自带库名导入时不会出现“忘记USE”这种低级错误文件开头会写DROP DATABASE IF EXISTS吗不会默认只对表做DROP所以安全性更高将来无论手动恢复还是脚本处理文件的自包含性都更好。3.2 单表和多表备份表备份的语法是“库名写在前面表名跟在后面”mysqldump -u user -p --single-transaction yourdb orders orders.sql mysqldump -u user -p --single-transaction yourdb orders order_items users core_tables.sql单表备份最常见的使用场景是线上配置表需要同步给测试环境、某张表被人清空了需要紧急恢复、给外包同事交付一张表的数据。多表备份适合导出“逻辑上相关的一组表”。注意这种写法不带--databases备份文件里不含建库USE语句恢复时需要指定目标库。如果导入到与导出不同的库名直接mysql -u user -p otherdb orders.sql即可很灵活。3.3 带条件导出只导出想要的数据带--where参数可以只导符合条件的行。这个功能非常实用比如导出某个月份的订单做分析mysqldump -u user -p --single-transaction \ yourdb orders \ --wherecreate_time 2024-01-01 AND create_time 2024-02-01 \ --no-create-info \ orders_202401.sql这里我加了--no-create-info因为目标环境里表结构已经建好了只需要数据。如果你连结构带数据一起要就把这个参数去掉。关于--where有两个容易踩的坑一是条件里的字符串值容易被Shell解释掉所以外层用双引号、里面用单引号包裹字符串值二是条件只能是SELECT语句中WHERE后面的表达式不能写得像完整SQL。如果条件复杂先写一条SELECT确认结果条数再拿去mysqldump执行避免导出几百万行进文件。3.4 只导出结构不导出数据用--no-data参数mysqldump -u user -p --no-data --routines --triggers \ --databases yourdb yourdb_schema.sql哪怕只导出结构也建议带上--routines --triggers --events。不然你拿到的“结构”少了存储过程和触发器回头评审或迁移还是会漏东西。这个场景经常被忽略的价值是做数据字典和跨系统表结构同步。比如把MySQL表结构转成其他时序数据库的超级表结构时第一手资料就是这份结构SQL。把表名、字段名、字段类型、注释全部解析出来比对着SHOW CREATE TABLE一张张看高效得多。3.5 远程备份与从从库导出mysqldump完全支持远程连接mysqldump -u backup_user -p \ -h 192.168.1.10 -P 3306 \ --single-transaction \ --databases appdb | gzip appdb.sql.gz远程备份要注意两点确保备份账号的权限只读即可不要给超级账号长期用于备份如果目标库开启了SSL客户端连接参数可能需要追加--ssl-mode相关配置否则可能出现SSL连接错误导致备份失败。更推荐的做法是从从库备份。数据量大的主库不应该让它承担备份的压力。语法上也只要把-h指向从库地址。这里有个专业参数--dump-slave2它可以从从库导出数据并且在备份文件注释里记录“当时主库的binlog文件名和位点”对搭建新从库特别有用。这里不展开主从搭建细节只说一句它和--single-transaction配合时锁的窗口非常短。3.6 压缩与流式处理大库备份不要直接落成明文SQL文件太占磁盘。管道直接压缩mysqldump -u user -p --single-transaction \ --databases bigdb | gzip bigdb_$(date %F).sql.gz恢复时解压管道灌入gunzip -c bigdb_$(date %F).sql.gz | mysql -u user -p bigdb这里有个我踩过的坑在管道方式下如果mysqldump中途失败管道后面的gzip往往还是会生成一个不完整的文件而且返回码可能还是0。脚本判断备份是否成功不能只看最后命令是否提示完成必须同时检查mysqldump的退出码。Bash里可以用PIPESTATUS数组来获取管道中每个命令的退出状态mysqldump --databases yourdb | gzip yourdb.sql.gz if [ ${PIPESTATUS[0]} -ne 0 ]; then echo mysqldump failed fi这个小细节能避免很多“备份文件看起来存在实际是坏的”惨案。4. 恢复操作与导入前的环境检查清单4.1 两种常见导入方式导出是技术导入也同样要命。两条最常用的恢复命令# 方式一Shell重定向导入 mysql -u root -p yourdb /backup/yourdb_2025-01-01.sql # 方式二进入mysql客户端后执行 mysql -u root -p mysql source /backup/yourdb_2025-01-01.sql;方式一适合脚本化批量恢复方式二适合人工在交互环境操作。如果文件里已经包含了CREATE DATABASE和USE语句导入时可以直接不指定库名mysql -u root -p /backup/yourdb_2025-01-01.sql这里要特别提醒如果你的导入文件较大且目标环境的max_allowed_packet小于导出时的值会出现“Got a packet bigger than max_allowed_packet bytes”错误。恢复前先检查两边参数SHOW VARIABLES LIKE max_allowed_packet;4.2 导入正式环境前必须检查的东西恢复操作比备份操作更容易造成事故核心原因是数据覆盖不可逆。我的习惯是无论多急导入前都把下面几项过一遍第一确认目标实例版本。MySQL 8.0把一些旧排序规则去掉了比如utf8mb4_0900_ai_ci只存在于8.0而5.7里根本没有。反过来低版本导出的SQL在8.0导入一般没有问题但特殊sql_mode比如NO_AUTO_CREATE_USER在8.0已经被移除可能报错。第二检查目标库sql_mode。如果目标比源更严格比如开启了STRICT_TRANS_TABLES、NO_ZERO_DATE原本能正常写入的数据在恢复时可能直接报错中断。第三检查备份文件里的字符集设置。head -n 50 yourdb.sql看一下SET NAMES那几行是什么建议统一改成utf8mb4并在导入命令里加上--default-character-setutf8mb4防止中文乱码。第四确认目标表可覆盖。mysqldump默认--add-drop-table文件里自带DROP TABLE IF EXISTS导入时会把目标同名表直接删掉重建。如果目标是生产库而文件又是旧数据这一下就是灾难。所以导入前必须确认“这份文件就是要用这些表对应的数据版本”。第五确认磁盘空间。解压后的SQL文件体积会明显大于压缩包。一个比较保守的预估导入后的数据体积约为SQL文件体积的1.5倍左右。可以先df -h看剩余空间。4.3 恢复过程中的常见报错与应对我自己的恢复事故和帮别人处理的恢复问题里出现频率最高的报错大概是这些ERROR 1064 (42000): You have an error in your SQL syntax通常是版本间语法不兼容比如源库用了目标库不支持的RANGE分区语法或者排序规则不认识。应对办法是导入前把文件里的问题语法手工替换掉或者导出时加上--skip-opt后用sed替换排序规则。ERROR 1410 (42000): You are not allowed to create a user with GRANT备份文件里如果包含权限授权语句而你用的恢复账号没有权限管理权限就会卡住。这种情况可以把文件里的GRANT相关行筛掉只恢复数据。ERROR 1227 (42000): Access denied/ERROR 1044 (42000): Access denied恢复账号缺少对目标库的访问或写入权限。给账号加上ALL PRIVILEGES ON db.*即可。ERROR 3546 (HY000): GLOBAL.GTID_PURGED cannot be changedGTID冲突前面提过解决方案是导出时加--set-gtid-purgedOFF或者在空库实例上先清空GTID记录再导入。还有一个容易被忽略的情况恢复大文件过程中网络或终端中断会造成表只建了一半或数据只导到一半。所以我会建议恢复时用nohup后台执行并重定向日志nohup mysql -u root -p yourdb yourdb.sql restore.log 21 tail -f restore.log4.4 一套更稳妥的恢复流程与其等文件导入到一半报错再排查不如提前把结构和数据分开。我的标准恢复流程是这样的先恢复结构文件不带--no-create-info的数据文件或专门的--no-data结构文件。用SHOW TABLES和统计关键表数量确认结构齐全。再恢复数据文件加--no-create-info导出防止重复执行DROP TABLE。最后单独恢复函数、触发器、事件如果当初单独导出了。恢复完成后跑几条和源库对账的SQL比如SELECT COUNT(*)主表行数、最大值/最小值做数据一致性抽验。分开的好处是结构问题第一时间暴露不会跟数据进度混在一起数据文件里没有DROP TABLE即使重复执行也只是插重复数据不会删表重建定位问题时不用在几万行日志里翻到最后一行。5. 备份时的锁与事务如何避免备份拖垮线上5.1 不加参数它会先锁全库很多人第一次用mysqldump备份一个正在运行的线上库跑完才发现业务在备份期间写入全部超时。原因就是默认行为下mysqldump会执行FLUSH TABLES WITH READ LOCK获取一个全局读锁然后对每张表执行LOCK TABLES。库里的写操作全部被阻塞直到备份结束。这套机制保证了备份的一致性但代价很大。一个几十分钟备份线上就等于被迫进入只读状态。如果你发现“某个时间点业务写入突然全部变慢持续了很长时间”备份任务是最先要怀疑的对象。MySQL锁表的排查手段很多但回头看备份方案的锅占了很大比例。5.2 --single-transaction到底靠什么避免锁表这个参数是InnoDB存储引擎出现后最大的福利。它的原理是在备份开始时启动一个事务隔离级别为可重复读REPEATABLE READ然后通过MVCC机制获取一个一致性快照。后续导出数据都是读这个快照不跟线上写入抢行锁和表锁因此业务写入完全不受影响。这里有一个很容易被误解的点--single-transaction不是一瞬间完成备份而是“备份期间所有读取都发生在同一个事务的快照上”。所以备份期间其它事务提交的新数据不会出现在备份文件里这恰恰是我们要的一致性不是bug。还要注意这个参数只对InnoDB表有效。如果库里存在MyISAM表备份时它依然会回退到锁表方式。所以在备份前最好确认库内所有表都是InnoDBSELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA yourdb AND ENGINE InnoDB;如果查出来有MyISAM表要么花时间迁移成InnoDB要么接受锁表代价把备份时间放到业务低峰。5.3 锁窗口在哪里配合--master-data2的特殊情况很多人以为加了--single-transaction就完全无锁了。严格来说并非全程无锁。当你想同时记录binlog位点比如为了搭从库会加上--master-data2。此时mysqldump需要先获取全局读锁拿到当前binlog文件和位点再启动一致性快照事务最后释放全局读锁。也就是说锁窗口从“整个备份过程”缩短成了“获取位点的那一瞬间”通常也就几十毫秒到一两秒业务基本无感。同样--dump-slave2也是类似逻辑它从从库读取位点时会对从库有一瞬间的锁定但影响很小。理解这个锁窗口你才能判断“mysqldump到底会不会影响线上”。5.4 备份对线上性能的其他隐性影响不加锁不代表没有性能影响。mysqldump本质是全表扫描会产生大量磁盘IO和CPU消耗还会把InnoDB的buffer pool里的热数据挤出去。如果业务高峰期执行一个超大库备份即使没有锁表也可能把查询性能拉低。实际操作中我建议这样规避优先从从库执行备份主库只负责业务。备份时间固定到凌晨低峰期跟业务方提前对齐。备份进程用nice和ionice降低调度优先级nice -n 10 ionice -c 2 -n 7 mysqldump ...远程备份时加--compress减少网络消耗代价是消耗少量CPU网络瓶颈时利大于弊。备份完及时检查主从延迟和慢查询数量作为备份“副作用”的观测指标。5.5 备份期间的DDL要格外小心--single-transaction的一致性快照能保证数据一致但备份期间如果有人执行ALTER TABLE可能出现一种微妙的结果备份里表结构和数据来自不同的时间点。比如结构已经改成新字段但快照数据还是旧结构下的。所以线上如果有比较频繁的DDL操作最好避开这些窗口跑全量备份。MySQL 8.0本身对在线DDL支持更好但mysqldump的原理决定了它做不到“跨DDL绝对一致”。最稳妥的备份窗口是业务低峰、没有大DDL、没有大批量数据清理任务。6. 这些坑我建议你提前踩备份恢复踩坑清单6.1 字符集和排序规则是重灾区字符集问题几乎每隔一段时间就要跳出来一次。典型情况是从库导出文件换台机器导入中文全部变成问号或乱码或者emoji表情直接变成“??”。解决办法是“导出、导入两头堵”导出时加--default-character-setutf8mb4导入时也加--default-character-setutf8mb4文件开头的SET NAMES也确认是utf8mb4。如果库内某些表的字段本身就是latin1还要先搞清楚原始字符集再统一。贪图省事不改字符集后面处理乱码的成本会翻倍。更隐蔽的是排序规则。5.7导出的库排序规则常见utf8mb4_general_ci8.0新建的库默认utf8mb4_0900_ai_ci。当你要把8.0备份导入5.7时就会遇到不识别排序规则的问题。相对实用的方法是导出时加--skip-opt再用sed把utf8mb4_0900_ai_ci替换成utf8mb4_general_ci或者建议目标环境升级到匹配版本。这个没有银弹只能看两边版本再定。6.2 GTID导致的导入失败怎么避开前面说了--set-gtid-purgedOFF的用法。再展开讲一下在GTID模式下备份文件里会有一个SET GLOBAL.GTID_PURGEDxxx:1-N语句它表示“源库上已经执行过哪些GTID事务”。导入到目标库时如果目标库也执行过其他GTID事务两者交集不为空MySQL就会拒绝执行。所以两条明确建议只是普通备份和数据迁移不涉及克隆实例、搭建从库导出时加--set-gtid-purgedOFF把GTID信息从文件里去掉。如果你确实需要借用GTID信息来搭从库那么目标实例应当是一个干净的、还没有任何GTID事务的新实例导入时保留文件里的GTID语句即可。如果导入时已经撞上了ERROR 3546也别慌。可以检查目标库GTID状态SHOW GLOBAL VARIABLES LIKE gtid_purged; SELECT GLOBAL.GTID_EXECUTED;清空或调整后重试但前提是确认不会影响现有复制链路。这块操作面比较敏感没把握时建议先在克隆环境演练。6.3 备份文件校验和恢复演练比参数更重要一条命令总能跑通难的是长期稳定、随时可恢复。有个观念我要反复强调备份文件存在≠备份可用。文件不会自己告诉你它坏了只有当你真正去恢复它的时候才知道。我的日常习惯备份结束立刻做基本校验文件大小非0、末尾存在Dump completed标记、gzip能解压完整。用gzip -t验证压缩文件完整性。每个月把全量备份恢复到一台测试实例跑几条数据对比SQL确认行数和关键字段值一致。大版本升级或迁移前不提前演练的备份几乎等于没有备份。很多迁移翻车都发生在“备份好了但恢复不了”这个环节。面试里常被问到的数据库可靠性问题答案往往也是“恢复演练”三个字。别让备份变成一种心理安慰。6.4 版本兼容性导致的隐藏问题跨版本导入导出的兼容问题除了排序规则还有几个容易忽视的点SQL模式比如5.7允许的NO_AUTO_CREATE_USER8.0已经不支持导入时直接报错。系统表结构8.0的mysql系统库跟5.7差别很大备份时只导出业务库别把mysql库导来导去。客户端版本8.0默认用户认证插件是caching_sha2_password老版本mysqldump客户端可能连不上。所以mysqldump客户端版本尽量跟服务端一致至少不要差太多。处理思路是导出前先看源库版本导入前先看目标版本双方版本差异大时先在小环境做兼容性测试再执行正式恢复。6.5 其它容易被忽略的小坑还有一个我差点栽了的点--max-allowed-packet在导出和导入两侧都要设置只改一侧不够。而且脚本定时任务里如果通过环境变量或配置文件读密码要小心进程列表里ps能看到明文密码。更安全的做法是把账号密码写到~/.my.cnf并且把文件权限设成600。[client] userbackup_user passwordYourPassword这样命令行里就不用带-p避免密码出现在shell历史或进程列表中。另外mysqldump备份文件里的版本注释行如-- Dump completed之前还有一行版本说明有时带回车符在Windows上编辑或传输可能造成文件换行问题。如果发现导入时报莫名其妙的语法错误先检查文件有没有被一些编辑器或者FTP工具转成带^M的格式。7. 定时备份脚本与策略落地可以直接拿去改的版本7.1 一个够用的全库定时备份脚本原理讲得再多最后还是要落地。下面这个脚本是我在自己环境里精简过的版本按库循环备份、gzip压缩、按保留天数清理并且记录每次执行的退出码。你可以直接复制改成自己的用户名密码路径就能用#!/usr/bin/env bash BACKUP_DIR/data/backup/mysql RETENTION_DAYS7 MYSQL_HOST127.0.0.1 MYSQL_PORT3306 MYSQL_USERbackup_user DATE$(date %Y%m%d_%H%M%S) LOG_FILE$BACKUP_DIR/backup_${DATE}.log mkdir -p $BACKUP_DIR # 这里使用--databases全量备份如需多库请自行调整 mysqldump \ -u $MYSQL_USER \ -h $MYSQL_HOST -P $MYSQL_PORT \ --single-transaction \ --routines --triggers --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ --max-allowed-packet256M \ --hex-blob \ --all-databases \ $BACKUP_DIR/all_databases_${DATE}.sql 2 $LOG_FILE if [ $? -eq 0 ]; then gzip -f $BACKUP_DIR/all_databases_${DATE}.sql echo $DATE backup success $LOG_FILE else echo $DATE backup failed $LOG_FILE # 这里可以接入你的告警脚本 exit 1 fi # 清理超过保留天数的备份 find $BACKUP_DIR -type f -name *.sql.gz -mtime $RETENTION_DAYS -exec rm -f {} \;注意两点脚本里用了--all-databases会把所有库打到一个文件如果你的实例里有超大业务库我更推荐按库循环导出便于单独恢复脚本只做了简单退出码判断生产级别建议再加一行校验文件大小是否大于某个阈值。按库循环导出的核心逻辑是这样for db in $(mysql -u $MYSQL_USER -h $MYSQL_HOST -N -e SHOW DATABASES; | grep -v -E ^(information_schema|performance_schema|sys|mysql)$); do mysqldump -u $MYSQL_USER -h $MYSQL_HOST \ --single-transaction --routines --triggers --events \ --set-gtid-purgedOFF --default-character-setutf8mb4 \ --databases $db \ $BACKUP_DIR/${db}_${DATE}.sql gzip -f $BACKUP_DIR/${db}_${DATE}.sql done7.2 定时任务配置把脚本放到/opt/scripts/backup_mysql.sh赋予执行权限chmod x /opt/scripts/backup_mysql.sh然后编辑crontabcrontab -e加入以下行表示每天凌晨2点执行0 2 * * * /opt/scripts/backup_mysql.sh /var/log/mysql_backup.log 21凌晨2点通常是业务最低峰、日志清理任务还没开始的窗口。如果你的业务时区或访问模式不同根据实际访问量统计来调整。全量备份频率建议至少每天一次同时配合binlog可以做增量恢复这样能把数据恢复的时间窗口缩小到分钟级。7.3 备份结果校验与告警思路光靠crontab跑还不够得让备份结果“可观测”。我的做法是每次备份完成追加写一行日志再写一个小脚本定时检查当天是否有成功记录没有就触发告警。告警通道可以接企业IM或邮件这里不展开具体实现。有这样一套校验兜底日常维护会安心很多检查备份目录里今天的.sql.gz文件是否生成且大小非0。用gzip -t验证备份文件完整性。每周抽一个备份文件用恢复演练脚本在测试实例跑一遍统计主表行数。检查磁盘空间避免备份文件把/data分区写满。最后再分享一点个人体会很多人在mysqldump上栽跟头不是因为命令不够熟而是因为只把“备份”当成终点没把“恢复”当成目标。我早期也吃过这个亏备份任务每天都在跑文件都好好躺在磁盘上直到某次线上误操作需要恢复数据才发现备份文件在传输过程中损坏那一刻真的很绝望。从那以后我养成了两个习惯每次备份完先看退出码再看文件标志绝不放过“文件存在但内容不完整”的隐患每个月雷打不动做一次恢复演练把全量备份恢复到测试实例跑几条COUNT语句跟源库对账。这两个习惯看着笨但省下来的全是关键时刻的抢救时间。mysqldump能讲的东西很多参数、原理、锁机制、GTID这些都可以慢慢学但最根本的一句话我始终记着能成功恢复的备份才叫备份。