Linux MySQL定时备份脚本实战：mysqldump+crontab自动化与恢复验证

简介Linux环境下MySQL定时备份脚本是一份面向运维工程师和数据库管理人员的实用工具包用于解决Linux服务器中数据库定期备份、压缩存储与过期备份清理等常见运维需求。压缩包共2个文件包含1个shell备份脚本和1个txt定时任务配置说明整包大小仅1KB结构精简易上手。shell脚本围绕mysqldump全库导出、gzip压缩、按日期命名备份文件、自动创建备份目录以及通过find保留最近N天备份等功能展开并预留了主机、用户名、密码、备份目录等环境变量供按需修改txt说明则清晰演示如何用crontab -e添加每日凌晨备份计划以及用crontab -l验证任务是否生效。借助这份资源读者可快速搭建起一套自动化的MySQL数据保护机制降低漏备和误操作风险。资源已有435人学习适合希望提升数据库备份规范性的Linux用户参考。1. 先说清楚linux mysql 定时备份脚本到底解决什么问题一台服务器跑着 MySQL数据量不大不小公司没配专职 DBA备份这件事很容易变成“想起来才做一次”。等真的误删了一张表、硬盘突然报错、或者上线前改崩了数据才意识到上一次完整备份已经是三个月前——这种场景下linux mysql 定时备份脚本就是那个兜底的方案用 Linux 自带的 crontab 调度配上 mysqldump 做逻辑备份把每日全量备份自动落盘再按策略清理旧文件。它不需要装额外的备份软件不依赖云厂商的商业备份服务一套纯命令就能在任意 Linux 服务器上落地。这套方案适合单机或主从架构里规模不大的 MySQL几十 G 以内也适合刚接手一批服务器、需要快速给所有实例补上“每日备份”这个安全网的运维。它能解决的核心问题不是“备份得最快”而是“每天都稳定有一份可恢复的完整数据”。接下来的章节会从最小可用脚本开始逐步讲调度、清理、踩坑和恢复验证每一步都给出可以直接抄的写法。2. 先写第一版可用脚本mysqldump 的关键参数与最小代码2.1 为什么选 mysqldump 而不是直接拷贝数据文件常见做法是用 mysqldump 做逻辑备份而不是直接把 /var/lib/mysql 下的数据文件 tar 走。逻辑备份导出的是 SQL 文本恢复时通过 mysql 客户端导入即可最大的好处是跨版本兼容——从 MySQL 5.7 备份出来的 SQL基本可以导入到 MySQL 8.0而物理备份直接拷贝数据文件要求目标实例的大版本、甚至小版本都尽量一致否则恢复时容易因为文件格式不兼容而翻车。另一个理由是粒度。mysqldump 可以只备份指定库、指定表也可以排除某几张日志大表物理备份通常是以整个实例为单位。如果你只想每天备份业务库、跳过几百 G 的日志表mysqldump 的灵活性要高得多。缺点也很明显备份和恢复都慢数据量上到几十 G 以后全量备份可能要跑一两个小时恢复更是同样量级。但中小项目完全能接受这也是它在实战里依然被大量使用的原因。2.2 最小可用的备份脚本从变量到落盘我一般会先写一个带变量的脚本把目录、连接信息和备份命令都拆开方便后续改库、改保留天数。下面这个版本可以解决 90% 的单机场景#!/bin/bash # mysql_daily_backup.sh # 每日全量备份指定 MySQL 数据库gzip 压缩后按日期命名 BACKUP_DIR/data/mysql_backup DB_HOST127.0.0.1 DB_PORT3306 DB_USERbackup_user DB_NAMEmy_app LOG_FILE/data/logs/mysql_backup.log DATE_TAG$(date %Y%m%d_%H%M%S) mkdir -p $BACKUP_DIR mkdir -p $(dirname $LOG_FILE) # mysqldump 出的 SQL 直接管道给 gzip避免中间落一个未压缩的大文件 mysqldump \ --host$DB_HOST \ --port$DB_PORT \ --user$DB_USER \ --single-transaction \ --quick \ --routines \ --triggers \ --events \ --databases $DB_NAME 2$LOG_FILE \ | gzip $BACKUP_DIR/${DB_NAME}_${DATE_TAG}.sql.gz status${PIPESTATUS[0]} if [ $status -eq 0 ]; then echo $(date %F %T) backup ok: ${DB_NAME}_${DATE_TAG}.sql.gz $(du -h $BACKUP_DIR/${DB_NAME}_${DATE_TAG}.sql.gz | cut -f1) $LOG_FILE else echo $(date %F %T) backup FAILED, mysqldump exit code: $status $LOG_FILE fi这段代码的逻辑不算复杂但参数值得逐一说清楚。--single-transaction是关键中的关键它让 mysqldump 在 InnoDB 引擎上用一致性快照读取数据不产生锁表行为业务可以继续写入。注意它只对 InnoDB 生效如果库里还有 MyISAM 表备份时仍然会锁表这个后面避坑章节会再展开。--quick是边查询边输出避免大表把所有结果都塞进内存。--routines --triggers --events分别导出存储过程、触发器、事件调度器这三样东西漏掉任何一个恢复出来的库都是残缺的。--databases $DB_NAME后跟库名列表会在备份文件里带上 CREATE DATABASE 和 USE 语句恢复时自动建库。PIPESTATUS[0]取的是管道中 mysqldump 的退出码因为直接用$?拿到的是 gzip 的结果mysqldump 中途失败也可能被 gzip 掩盖。这一点是很多人备份“看起来很成功、恢复时才发现文件是坏的”的常见原因。2.3 连接配置与密码不要把密码写死在脚本里上面脚本里没有出现密码参数这不是遗漏而是有意为之。mysqldump 支持在命令行直接--passwordxxx但这样做密码会出现在ps aux的进程列表里任何能登录这台机器的用户都能看到等于把数据库密码贴在了公告栏上。常见的替代方案是在脚本所在用户的 home 目录下放一个.my.cnfmysqldump 会自动读取[mysqldump] host127.0.0.1 port3306 userbackup_user password你的密码文件创建后必须收紧权限否则 MySQL 客户端会直接忽略它chmod 600 ~/.my.cnf权限不对时 mysqldump 会报警告“Using a password on the command line interface can be insecure”或者干脆不读取这个文件导致连接失败。这个方案在 crontab 场景下尤其重要因为 cron 环境里的进程列表同样会被记录不能因为“反正只有 root 能看到”就放松警惕。备份账号建议单独创建只授予所需库的 SELECT、LOCK TABLES、SHOW VIEW 等权限不要直接用 root 跑备份。3. 用 crontab 钉住备份时间调度语法、环境变量与密码安全3.1 备份时间定在几点业务低峰期的选择逻辑定时备份绝不是“随便定个凌晨三点”就行要结合业务特点来选。常见做法是选凌晨 2 点到 4 点之间这个时间段大多数业务系统处于低峰但也要排除两类情况一是业务自己的日切、对账、跑批任务二是其他运维任务。很多公司会有凌晨同步数据的任务比如从生产库抽数据到数仓如果你的备份时间和这个任务重叠两边同时大压力读取磁盘和 CPU备份时长会被明显拉长。我一般的判断顺序是先看服务器上的监控曲线确认哪个时段磁盘读和 CPU 利用率最低再问业务有没有固定时间点的定时任务最后把备份时间定在两者都不冲突的窗口。这里还要注意时区问题云服务器默认可能是 UTC 时间crontab 按系统时间执行如果你习惯用北京时间推算一定要先date确认一下服务器本地时间否则“凌晨两点执行”可能变成“上午十点执行”正好撞上白天业务高峰。3.2 crontab 写法与输出重定向别让日志被 /dev/null 吃掉crontab 的配置用crontab -e编辑crontab -l查看当前用户的所有定时任务。备份任务一行搞定30 2 * * * /bin/bash /data/scripts/mysql_daily_backup.sh /data/logs/mysql_backup_cron.log 21这一行的六个字段分别是分、时、日、月、周30 2 * * *表示每天 2 点 30 分执行。很多人在这一步踩坑脚本里明明写了日志crontab 这行又追加一个 /data/logs/... 21于是脚本里的 echo 和 mysqldump 的报错会被重复写入两个地方。这不是大问题但排查时容易混乱。更干净的做法是脚本内部统一管日志crontab 行只保留/dev/null 21或者干脆不写重定向让 cron 把输出通过邮件发给 root——不过大部分服务器没配邮件服务所以还是建议重定向到专门的文件。cron 环境和交互式 shell 环境不一样PATH 变量往往只剩/usr/bin:/bin。如果你的 mysqldump 安装在/usr/local/mysql/bin下直接写mysqldump命令在 cron 里会报 command not found。解决办法是在脚本开头显式声明 PATH或者调用时写绝对路径export PATH/usr/local/mysql/bin:/usr/bin:/bin另外脚本必须加执行权限chmod x /data/scripts/mysql_daily_backup.sh并在 crontab 里用/bin/bash显式调用。这两个细节不处理就会出现“手动跑一切正常cron 一到点就什么都不发生”的灵异现象实际原因往往只是环境变量或权限问题。3.3 时间重叠与手动执行撞车flock 互斥锁还有一个调度场景容易忽略备份脚本执行时间超过预期比如数据量涨了原来 20 分钟跑完现在要跑 50 分钟而 crontab 固定每天跑下一次触发时上一次还没结束。两个 mysqldump 同时跑磁盘 IO 互相拖累最后两个备份都可能超时甚至失败。更隐蔽的场景是运维人员白天手动执行脚本排查问题恰好又撞上凌晨的 cron 没跑完。常见的解决办法是给脚本加一把互斥锁用 Linux 自带的 flock 就能实现#!/bin/bash exec 9/tmp/mysql_backup.lock if ! flock -n 9; then echo $(date %F %T) another backup process is running, skip /data/logs/mysql_backup.log exit 1 fi # 以下是原本的备份逻辑这段代码的原理是打开一个锁文件并绑定到文件描述符 9flock -n尝试获取锁拿不到就立即返回失败不会阻塞等待。这样即使 cron 和手动执行同时触发也只会有一个人真正跑备份另一个直接退出。锁文件放在 /tmp 下即可重启后会自动释放不会留下脏状态。4. 备份的保留与清理定时不只是定时还要自动清过期文件4.1 全量备份保留多久磁盘成本与恢复窗口的平衡备份不是只写不删否则磁盘迟早被撑爆备份任务就会因为写不进文件而失败。“保留多久”取决于你的恢复需求和磁盘成本常见做法是中小项目保留 7 到 30 天。保留 7 天意味着最多只能恢复到 7 天前的数据但磁盘占用只有 30 天方案的约四分之一保留 30 天则能覆盖“误操作两周后才被发现”的场景代价是要给备份预留更大空间。估算磁盘占用有一个简单公式单次备份文件平均大小 × 保留天数 × 1.5 的安全系数。比如每天备份约 2G保留 30 天预留 90G 到 100G 比较稳妥。如果你还要做每周一次的额外长期保留比如“近 7 天日备份 近 4 周周备份”那就要单独设计两套命名和清理规则日备按天清理周备按周清理不能混用同一个删除命令否则会把周备一起删掉。4.2 用 find 按时间清理旧备份参数与边界清理旧文件的标准写法是 find 加 mtime 加 delete下面这段可以直接放进备份脚本末尾BACKUP_DIR/data/mysql_backup RETAIN_DAYS30 LOG_FILE/data/logs/mysql_backup.log find $BACKUP_DIR -maxdepth 1 -type f -name *.sql.gz -mtime ${RETAIN_DAYS} -print -delete $LOG_FILE 21这条命令的意思是在备份目录下查找一层以内的 .sql.gz 文件修改时间超过 30 天的打印路径后直接删除。-maxdepth 1是防止 find 递归进子目录误删其他文件-print会把删了哪些文件记到日志里方便事后审计。-mtime 30是按 24 小时取整的“整 30 天前”对每日执行的 cron 来说边界基本准确。这里有一个值得注意的细节删除操作不可逆尤其是配合-delete这种参数一个路径写错可能把整个目录清空。我建议新脚本第一次上线时先跑find $BACKUP_DIR -maxdepth 1 -type f -name *.sql.gz -mtime 30 -print不带-delete确认列出的都是确实要删的旧文件再正式启用完整命令。另外不要在脚本里把BACKUP_DIR写成/如果是 root 执行且命令写错了后果不堪设想。4.3 不要把鸡蛋放在一个篮子里异地备份与保留策略同步同一台机器的磁盘如果整个损坏备份文件和源数据会一起消失所以“备份”这个动作真正要防的是单点故障。常见做法是至少把备份放到另一个物理磁盘或另一台服务器。跨机器传输用 rsync 或 scp 都行配合 SSH 密钥可以做到免密自动执行rsync -avz --timeout60 /data/mysql_backup/ backupremote-server:/data/backup_store/ $LOG_FILE 21注意 rsync 只是把文件推过去远端磁盘同样会越积越多所以远端也要有对应的清理策略不能只传不删否则半年后远端磁盘满了rsync 会一直报错。如果你的部署环境是公有云把备份推到对象存储是另一个常见路线本质和 rsync 一样只是传输工具换成云厂商提供的命令行客户端。无论是推远端还是推对象存储都要在脚本里加传输成功的判断只有 rsync 返回 0 才算备份链路完整。5. 定时备份脚本 5 个常见坑现象、原因、解法5.1 手动执行正常、cron 不执行环境变量与解释器的锅现象手动/bin/bash /data/scripts/mysql_daily_backup.sh跑得飞快备份文件正常生成但第二天一看 /data/mysql_backup 下空空如也cron 日志里也什么都没有。原因cron 执行时的 PATH 和环境变量和交互式 shell 不一样mysqldump如果不在/usr/bin:/bin下就找不到脚本没有执行权限、或者 crontab 里写的是相对路径也会导致 cron 静默失败。解决脚本开头加入export PATH/usr/local/mysql/bin:/usr/bin:/bincrontab 里写绝对路径/bin/bash /data/scripts/mysql_daily_backup.sh并确认脚本有chmod x。改完后手动跑一次再用crontab -l确认配置确实写进去了。5.2 备份文件 0 字节或明显变小连接失败和权限不足现象备份脚本每天都显示执行成功日志里也没有报错但备份文件只有几个字节解压后内容为空或者文件大小突然比前一天小了一大截。原因mysqldump 连接失败、密码错误、或者账号缺少表的 SELECT 权限时命令会向 stderr 输出错误但因为脚本只重定向了 stderr 到日志又没人看或者管道里 gzip 仍然生成了空文件最后就得到一个“假的成功”。权限不足的典型报错是 Access denied但日志如果没人读就等同于没有报错。解决脚本里一定要判断PIPESTATUS[0]同时把 stderr 完整记录每次备份结束后检查文件大小是否大于某个阈值比如小于 1MB 就告警。首次配置时先手动跑一次用gzip -t 备份文件验证压缩包完整性确认没问题再交给 cron。5.3 备份期间业务锁等待MyISAM 表和 DDL 操作现象备份窗口内业务侧出现 lock wait timeout 报错尤其是某些报表查询特别卡或者备份过程中一个 ALTER TABLE 卡了很久。原因--single-transaction只对 InnoDB 表生效MyISAM 表在备份时会被锁住另外即使全是 InnoDB如果有 DDL 语句在备份期间执行也需要等元数据锁备份和 DDL 互相等待最后超时。解决如果库里有 MyISAM 表尽早改成 InnoDB这是治本无法立刻改的用--skip-lock-tables会牺牲一致性不建议。更稳妥的做法是把备份任务指向从库或至少在业务低峰跑并且通知研发避免在备份窗口执行 DDL。需要的话用--ignore-table库.表把几个特别大的、不重要的日志表排除掉。5.4 前一轮没跑完、后一轮又启动flock 互斥缺失现象数据量涨了以后备份从原来的 20 分钟变成 1 小时某天登录服务器发现同时有多个 mysqldump 进程备份目录里多了好几个同一天的重复文件磁盘空间告急。原因crontab 固定时间触发不会判断上一个任务是否结束脚本执行时间超过调度间隔或者运维手动执行撞上 cron就会产生重叠。解决参考 3.3 的 flock 写法在脚本最前面加互斥锁拿不到锁直接退出。这个改动成本极低但能彻底解决重复备份和 IO 争抢的问题。建议锁文件用绝对路径并在日志里记录“skip because another backup is running”方便确认确实发生过重叠。5.5 恢复时才发现备份是坏的从没验证过现象灾难真的来了拿着备份文件恢复结果报错找不到表、少了一堆存储过程、或者解压到一半 gzip 提示 CRC 错误。原因备份脚本里少了--routines --triggers --events导致只有表和索引没有存储过程和触发器或者是 mysqldump 中途失败但脚本没报警生成了残缺的压缩包又或者是备份文件传了一半就断了但文件名看不出异常。解决脚本参数按 2.2 的写法补全每次备份结束后检查 exit code再用gzip -t测压缩包完整性。最重要的是建立恢复演练机制不要等灾难发生才去验证备份这件事放到最后一章展开。6. 恢复验证才是终点每周做一次“假恢复”检查备份的意义在于恢复一个从来没有被验证过能恢复的备份严格来说只是一堆占磁盘的 SQL 文本。我现在养成的习惯是每日备份跑完至少做一次“冒烟检查”确保文件完整、关键表在里面每周再抽一份备份真正恢复到临时实例里查一下数据。冒烟检查不需要起 MySQL一条命令就能做BACKUP_FILE$(ls -t /data/mysql_backup/*.sql.gz | head -1) gzip -t $BACKUP_FILE echo 压缩包完整: $BACKUP_FILE if zcat $BACKUP_FILE | grep -q -- Dump completed; then echo dump 结束标记存在备份结构完整 else echo 警告: 备份文件可能不完整 fi-- Dump completed是 mysqldump 正常结束时的尾部标记如果 mysqldump 中途崩溃这个标记会缺失。每周的恢复验证则要起一个临时实例常见做法是在本地用 mysqld 初始化一个空实例或者用 Docker 起一个一次性 MySQL 容器把备份文件导入进去再查表数量和关键表的最新数据docker run -d --name mysql-restore-check -e MYSQL_ALLOW_EMPTY_PASSWORDyes -p 3307:3306 mysql:5.7 zcat $BACKUP_FILE | docker exec -i mysql-restore-check mysql -uroot docker exec mysql-restore-check mysql -uroot -e SHOW TABLES FROM my_app;验证完以后docker rm -f mysql-restore-check删掉临时容器。这套流程跑过一次以后你对“这个备份到底能不能用”心里就有底了。早年间我跳过恢复验证结果在一次真实事故里发现备份的存储过程全丢只能手工补脚本从那以后再也不敢让备份只停留在“跑完就算完事”。每周十几分钟换来的是真出事时的从容希望帮到你。本文还有配套的精品资源点击获取