尧图网站建设 
1. 单机备份的致命盲区为什么必须把数据送出去先讲个我早年踩过的真实事故。当时给一家电商公司维护数据库每天凌晨用 crontab 跑 mysqldump备份文件老老实实落在本机磁盘上日志显示每天都成功我心里还挺踏实。结果某天机房硬盘阵列出现坏道系统盘和数据盘接连报错最后整个服务器起不来。等运维把磁盘挂到别的机器上一看备份文件倒是还在但读取时大量坏块dump 文件根本解不开。那一刻我才彻底明白备份文件和源数据放在同一台机器上本质上等于没备份。后来我做数据库运维方案不管项目大小异地备份永远是必选项。所谓异地不一定非得是跨城市只要备份文件和源数据库不在同一个物理故障域内就算达标。比如源库在 A 机房的机器 A备份传送到同机房另一台物理机或者是对象存储都能规避掉很大一部分风险。如果预算充足跨机房、跨地域当然更好这是应对地震、火灾、整个机房断电这类极端情况的手段。除此之外异地备份还有一个非常实际的价值防止误操作和勒索类故障。数据库被误删、被恶意加密、被某个手滑的同事执行了不带 WHERE 的 UPDATE这些情况下本机备份很可能也跟着遭殃——比如攻击者拿到了服务器权限第一件事就是把备份文件删掉。但备份已经传到另一台机器上攻击者触达不到这时候你手里还有一份干净的历史数据恢复就是时间问题。这篇文章我会完整梳理一套我自己用了一年多的 MySQL 自动备份加异地传输方案。整套方案不依赖商业软件纯脚本加系统自带工具就能实现操作逻辑清晰适合中小型项目、个人服务器以及刚入门运维的开发者参考。我会把每一步的取舍、脚本里每个关键参数的含义、实际跑起来会遇到的坑都写清楚。2. 备份方式选型mysqldump 与物理备份的取舍2.1 两种主流备份方式的本质区别MySQL 的备份手段五花八门但抛开外围工具底层就两大类逻辑备份和物理备份。逻辑备份的代表就是 mysqldump它把数据库里的表结构、数据、触发器、存储过程等转换成一条条 SQL 语句导出成一个文本文件。恢复的时候执行这些 SQL就能把数据重新灌回数据库。这种方式的优点是跨版本恢复能力强比如从 MySQL 5.7 导出导入到 MySQL 8.0 基本没问题文件是纯文本可以直接 grep 查看内容、用 sed 做处理备份过程对存储引擎的依赖性低。缺点是备份和恢复都需要经过 SQL 解析层数据量大时速度偏慢而且恢复时是逐条执行语句非常耗时。物理备份则直接复制数据库的底层数据文件比如 InnoDB 的 ibd 文件、frm 文件、redo log 等。市面上常用的工具是 Percona XtraBackup。它的核心优势是备份速度快因为只是文件级别的复制恢复时也快适合几十 GB、上百 GB 的大库。缺点是版本和平台绑定较死MySQL 版本升级后老备份往往不能直接恢复而且它备份的是二进制文件没法像 mysqldump 那样灵活处理单表数据。我的选择逻辑很简单数据量在 10GB 以内无脑用 mysqldump。10GB 的库mysqldump 全量导出也就几分钟的事恢复时灌入 MySQL 也基本能接受。而且脚本处理逻辑简单依赖少任何一台装好 mysql-client 的机器就能跑。等库涨到几十 GB再切换到 XtraBackup 也不迟。2.2 使用 mysqldump 必须明确的参数细节直接跑mysqldump -u root -p虽然能导出但生产环境绝不能这么干。我给出一套个人认为比较稳妥的参数组合mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ --hex-blob \ --databases dbname \ /backup/dbname_$(date %F).sql参数作用为什么必须加--single-transaction在 InnoDB 引擎下开启一个一致性的快照事务进行备份不加的话备份期间如果有写入导出的数据可能是不一致的加了它备份过程不会锁表线上业务无感知--routines导出存储过程和函数很多项目的统计逻辑藏在存储过程里不导出恢复后就缺胳膊少腿--triggers导出触发器数据联动逻辑依赖触发器漏掉会引发数据完整性问题--events导出事件调度器定时清理任务等事件需要带过去--set-gtid-purgedOFF不导出 GTID 信息恢复时如果目标库已有 GTID 配置导出 GTID 会导致主从同步错乱--default-character-setutf8mb4指定字符集不指定的话碰到 emoji 或生僻字会乱码--hex-blob二进制字段以十六进制形式导出防止 BLOB 字段在导出过程中产生不可控的字符转义问题--databases dbname指定需要备份的库名指定库名后导出的文件会自动包含 CREATE DATABASE 语句恢复时不用手动建库这里特别强调--single-transaction这个参数。很多人以为 mysqldump 一定会锁表实际上在 InnoDB 引擎下加上这个参数备份是通过 MVCC 机制读取一致性快照的整个过程不会阻塞线上读写。但如果你的表用的是 MyISAM 引擎这个参数就不起作用了MyISAM 备份时照样锁表。所以如果库里还有 MyISAM 表建议提前把它转成 InnoDB或者做好备份期间业务可接受短暂锁表的心理准备。2.3 全量备份与增量备份的组合策略我日常采用的节奏是每天凌晨一次全量备份每 5 分钟一次 binlog 增量同步。这里说清楚“增量”是怎么实现的。MySQL 的 binlog 会记录所有数据变更操作。只要开启了 binlog从上次全量备份的时间点开始所有写入操作都记录在 binlog 里。恢复时先导入最近一次全量备份再重放从备份时间点至今的 binlog就能把数据库恢复到任意一个时间点。开启 binlog 需要在 MySQL 配置文件里加上[mysqld] server-id1 log-binmysql-bin binlog_formatrow expire_logs_days7binlog 的保留天数建议根据全量备份周期来定。如果每天全量备份一次binlog 保留 7 天意味着即使全量备份连续失败 6 天第 7 天仍有完整 binlog 可以追加上去不会丢数据。binlog_formatrow是很多生产环境推荐的格式它能记录行级别的变化恢复时更加精确但相对 statement 格式binlog 文件会更大一些磁盘空间要留足。增量的核心价值在于全量备份是每日的“地基”binlog 是地基之上的“砖块”。一旦某天凌晨 3 点数据库崩了而你最后一次全量备份是当天凌晨 0 点的那 0 点到 3 点之间的数据就要靠 binlog 重放来补。脚本只能负责全量备份binlog 的同步通常走另一个机制比如从库实时拉取或者用留存的 binlog 文件定期传输。如果是单库场景最省事的做法是定期把 binlog 文件也一起传送到异地机器上保留最近 3~7 天的 binlog配合全量备份就可以做时间点恢复。3. 本地备份脚本的完整实现与逐段解析3.1 脚本的目录规划与基础配置先看目录结构。好的目录规划能省掉后面无数麻烦我习惯这样安排/opt/mysql_backup/ ├── bin/ # 脚本文件目录 │ └── backup.sh ├── logs/ # 日志目录 │ └── backup.log └── data/ # 临时存放备份文件目录 └── mysql/ # 按日期归档的目录备份文件在本地不要留太久我一般只保留最近 3 天因为文件最终要传到异地机器本地只是中转站。本地留 3 天异地留 30 天这是一个存储成本和恢复灵活性的平衡点。如果你磁盘充裕保留 7 天也不是不行但一定不要让本机成为备份文件的主要归档点那样又绕回“单机故障全完蛋”的坑里了。3.2 备份脚本主体从备份到压缩再到清理下面这个脚本是我在线上环境实际跑的版本我按模块拆开讲而不是甩一段完整脚本让你自己猜。基础配置部分#!/bin/bash # MySQL 自动备份脚本 # 适用环境CentOS 7 / Ubuntu 18.04 BACKUP_BASE/opt/mysql_backup/data/mysql LOG_FILE/opt/mysql_backup/logs/backup.log MYSQL_HOST127.0.0.1 MYSQL_PORT3306 MYSQL_USERbackup_user MYSQL_PASSYourStrongPassword MYSQL_SOCKET/var/run/mysqld/mysqld.sock BACKUP_KEEP_DAYS3 DATE_TAG$(date %Y%m%d_%H%M%S) BACKUP_DIR${BACKUP_BASE}/${DATE_TAG}几点说明数据库账号千万不要用 root创建一个仅具备 SELECT、SHOW VIEW、TRIGGER 等只读权限的专用账号。备份账号权限越大一旦脚本被入侵影响面就越大。推荐的最小权限集合CREATE USER backup_userlocalhost IDENTIFIED BY YourStrongPassword; GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO backup_userlocalhost; GRANT PROCESS, RELOAD ON *.* TO backup_userlocalhost; FLUSH PRIVILEGES;这里重复一遍--single-transaction做一致性快照其实不一定需要 LOCK TABLES但 mysqldump 在准备阶段仍可能尝试获取某些元数据锁加上 LOCK TABLES 权限可以避免奇怪的权限报错。PROCESS 权限用于查看进程信息RELOAD 权限用于 FLUSH 操作这两个在部分 MySQL 版本中会被 mysqldump 内部逻辑调用。备份与压缩部分mkdir -p ${BACKUP_DIR} log() { echo $(date %Y-%m-%d %H:%M:%S) - $1 ${LOG_FILE} } log 开始备份数据库... MYSQL_PWD${MYSQL_PASS} mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ --hex-blob \ -h ${MYSQL_HOST} \ -P ${MYSQL_PORT} \ -u ${MYSQL_USER} \ --all-databases \ | gzip ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz if [ $? -eq 0 ]; then log 备份并压缩成功文件大小$(du -h ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz | cut -f1) else log 备份过程中出现错误请检查数据库连接和磁盘空间 exit 1 fi这里用--all-databases还是单库--databases我的建议是如果服务器上跑着不止一个业务库直接用--all-databases一把梭避免漏掉库。如果只有单一业务库用--databases dbname更干净可以避免把 mysql 系统库也导出来。MYSQL_PWD环境变量传密码的方式比在命令行里写-p更安全一点点因为命令行参数会被进程列表看到而环境变量只有当前用户能读到。当然更安全的是使用 MySQL 配置文件~/.my.cnf存放凭据但要注意该文件的权限必须设为 600。历史备份清理部分find ${BACKUP_BASE} -maxdepth 1 -type d -name 20* -mtime ${BACKUP_KEEP_DAYS} -exec rm -rf {} \; if [ $? -eq 0 ]; then log 已清理本地超过 ${BACKUP_KEEP_DAYS} 天的历史备份 else log 清理历史备份时出现异常请手动检查 fifind加-mtime 3表示查找修改时间在 3 天前的文件和目录。这里有个细节BACKUP_DIR是以日期时间命名的目录所以-mtime可以准确反映备份的创建时间。如果单纯用文件名正则去匹配再删除可能会有误删风险推荐用-mtime这种时间属性来判定。3.3 权限与执行脚本能跑起来的最后一步脚本写完后执行权限不能忘chmod x /opt/mysql_backup/bin/backup.sh先手动跑一遍确认无报错输出日志正常bash /opt/mysql_backup/bin/backup.sh手动执行通过后再配 crontab。这里有个容易忽略的问题crontab 环境变量和当前 shell 环境不一样mysqldump 很可能不在 crontab 的 PATH 里。所以脚本里的命令建议写绝对路径或者脚本开头加一段导出 PATHexport PATH$PATH:/usr/local/mysql/bin:/usr/bin:/bin如果不确定 mysqldump 安装在哪先执行which mysqldump确认路径再填进脚本。3.4 备份脚本实际运行时的常见报错与处理我在实际部署中遇到过几个高频报错这里直接列出来报错一mysqldump: Got error: 1556: You cant use locks with log tables.这个通常发生在使用--all-databases的时候因为 mysql 库里的 slow_log 表和 general_log 表是 CSV 引擎不支持锁表。解决方法有两个一是加上--skip-lock-tables参数但这样会失去 MyISAM 表的锁表保护二是用--ignore-tablemysql.slow_log --ignore-tablemysql.general_log跳过这两个表。我的实践是后者因为系统日志表对业务恢复没有实际价值。报错二Access denied; you need (at least one of) the RELOAD privilege(s) for this operation这是备份账号权限不足导致的。给账号补上 RELOAD 权限即可前面创建账号的语句里已经包含了。如果你用的是 MySQL 8.0还要注意默认认证插件是 caching_sha2_password部分老的驱动不支持需要确认备份主机上的 mysql-client 版本够新。报错三磁盘空间不足导致 gzip 写到一半失败这个很尴尬因为备份过程中你不会立刻发现等脚本返回非 0 退出码才看到。我在脚本里加了一个前置检查REQUIRED_SPACE$(du -sb /var/lib/mysql | cut -f1) AVAILABLE_SPACE$(df -B1 /opt/mysql_backup | awk NR2 {print $4}) if [ ${AVAILABLE_SPACE} -lt ${REQUIRED_SPACE} ]; then log 磁盘空间不足以完成备份期望至少 ${REQUIRED_SPACE} 字节实际可用 ${AVAILABLE_SPACE} 字节 exit 1 fi简单粗暴地以源数据目录大小作为备份文件大小的预估上限实际备份文件通常会小于源数据体积但多留余量总没错。4. 远程传输方案选型与 rsync 实战配置4.1 传输工具对比scp、rsync、对象存储、cloud sync备份文件生成之后最关键的一步就是把它送出去。这一步的选择直接影响传输效率和故障恢复的成功率。我对比过几种常见方案方案优势劣势适用场景scp简单直接系统自带无需额外安装不支持断点续传大文件传输中网络抖动就要重来备份文件很小1GB网络稳定rsync SSH支持断点续传、增量传输、文件校验传输失败可续传配置稍复杂目标端需要 rsync 和 SSH 服务大多数场景的首选rclone 对象存储上传到云厂商的 OSS/COS/S3无限容量天然异地依赖公网可能产生流量费用需要配置云密钥有云资源预算希望免运维云厂商 backup 服务全托管自动备份自动异地绑定云厂商无法自定义备份策略已深度使用某朵云我的建议是自建服务器之间优先 rsync上云优先 rclone。rsync 的增量特性在传输大文件时优势极强——第一次全量传输后后续每天的同名文件如果内容变化不大rsync 只传输差异部分而且支持中断续传这可比 scp 稳太多了。4.2 目标端配置rsync 服务端安装与模块定义rsync 有两种工作模式一种是直接用 SSH 通道传输另一种是走 rsync daemon 模式。我建议用 daemon 模式因为通过 SSH 模式时每次传输需要验证登录用户的凭据在 crontab 里安排免密登录会多一步配置daemon 模式则可以直接用 rsync 协议不依赖系统用户权限控制也清晰。目标端接收备份的异地机器安装并配置 rsync# Ubuntu/Debian apt-get update apt-get install -y rsync # CentOS/RHEL yum install -y rsync编辑/etc/rsyncd.confuid root gid root use chroot yes max connections 4 pid file /var/run/rsyncd.pid log file /var/log/rsyncd.log timeout 300 [mysql_backup] path /data/mysql_backup comment MySQL Backup Storage read only no list no auth users backup_rsync secrets file /etc/rsyncd.secrets创建密码文件权限设为 600echo backup_rsync:YourRsyncPassword /etc/rsyncd.secrets chmod 600 /etc/rsyncd.secrets启动 rsync 服务systemctl enable rsync systemctl start rsync这里有几个安全点需要特别强调use chroot yes让 rsync 进程锁定在模块目录内避免通过路径穿越访问系统其他目录。auth users配合secrets file传输前需要验证账号密码而不是裸奔在网络上。如果异地机器暴露在公网建议再加防火墙规则只允许源备份机的 IP 访问 873 端口firewall-cmd --permanent --add-rich-rulerule familyipv4 source address源机器IP port port873 protocoltcp accept firewall-cmd --reload4.3 源端传输脚本rsync 命令与重试机制源端的传输逻辑我集成到了主备份脚本里。备份和压缩成功后紧接着执行 rsync 传输REMOTE_HOSTbackup.example.com REMOTE_MODULEmysql_backup RSYNC_USERbackup_rsync RSYNC_PASSYourRsyncPassword export RSYNC_PASSWORD${RSYNC_PASS} REMOTE_DIR${DATE_TAG} SYNC_LOG/opt/mysql_backup/logs/rsync.log log() { echo $(date %Y-%m-%d %H:%M:%S) - $1 ${SYNC_LOG} } log 开始推送备份文件到异地服务器... for i in 1 2 3; do rsync -az --partial \ --password-file/etc/rsyncd.secrets.source \ ${BACKUP_DIR}/ \ ${RSYNC_USER}${REMOTE_HOST}::${REMOTE_MODULE}/${REMOTE_DIR}/ \ ${SYNC_LOG} 21 if [ $? -eq 0 ]; then log 第 ${i} 次传输成功 break else log 第 ${i} 次传输失败正在重试... sleep 10 fi done说明几个参数-a归档模式保留文件属性、权限、时间戳等。-z传输时压缩备份文件本身已经是 gzip 压缩过的按理说压缩效果有限但万一有未压缩的小文件还是能省带宽的。--partial保留传输中断时已传输的部分文件。这是 rsync 断点续传的关键没有这个参数中断后重传就得整个文件重新开始。--password-file从文件读取密码避免密码出现在命令行历史里。我这里在源端创建了一个单独的密码文件/etc/rsyncd.secrets.source内容只有一行YourRsyncPassword。注意和客户端密码文件不同客户端密码文件不需要写账号名只需要密码。for i in 1 2 3这个重试循环是我后来才加上的。之前没加重试有一回网络抖动导致 rsync 失败备份没传到异地偏偏那天源库也出了问题差点酿成事故。加个三重试每次间隔 10 秒虽然不能保证 100% 成功但能过滤掉大部分瞬时网络波动。4.4 为什么除了 rsync 还要留一份对象存储方案rsync 再稳也架不住目标机房整体故障。如果预算允许我非常推荐在 rsync 推送到自建服务器的同时再用 rclone 把备份文件同步到云对象存储一份。成本其实很低一个 10GB 的备份文件放到 OSS 标准存储一个月也就几块钱。但换取的是双副本异地冗余一处在自己的备份机一处在云上恢复可用性直接拉满。rclone 的安装与配置也不算麻烦curl https://rclone.org/install.sh | bash rclone configrclone config是交互式配置向导按提示选择对应的云存储类型填入 AccessKey 和 SecretKey创建一个 remote 名字。我用的是阿里云 OSS配置完后上传命令非常简单rclone copy /opt/mysql_backup/data/mysql/ remote:mysql-backup-bucket/ --transfers 4 --log-file/opt/mysql_backup/logs/rclone.log--transfers 4表示同时开启 4 个并发上传任务能显著加速大文件上传。rclone 还有--checkers参数控制并发检查文件差异的线程数一般保持默认就行。把这段逻辑加到 rsync 成功之后、脚本退出之前如果 rclone 传输失败也不要影响主流程的退出码用独立的日志记录即可——毕竟 rsync 已经保障了核心备份的异地落地对象存储是加分项不是必选项。5. crontab 定时任务配置与执行环境坑位排查5.1 crontab 配置时间窗口与频率的确定备份脚本全部就绪后需要把它挂到 crontab 里定时执行。我的建议是每天凌晨执行一次具体时间选在业务低峰期。电商类项目晚上 10 点后流量下降但有些夜间批处理任务会在凌晨 1~2 点跑所以我会避开这些时段选凌晨 3 点。金融类项目可能跨天结算凌晨 4~5 点才有空闲窗口这个要依据业务实际情况定。编辑当前用户的 crontabcrontab -e添加一行0 3 * * * /usr/bin/bash /opt/mysql_backup/bin/backup.sh /opt/mysql_backup/logs/cron.log 21这行的含义是每天凌晨 3 点整执行 backup.sh同时把标准输出和错误输出都追加到 cron.log 里。不建议写成/dev/null 21虽然这样不会收到 crontab 的系统邮件但遇到脚本静默失败时你根本没法排查。日志是排障的第一手材料宁可多占一点磁盘也要把所有输出都留下来。crontab 还需要注意一点如果服务器时间是 UTC那么0 3 * * *实际上是北京时间上午 11 点执行。很多云服务器默认时区是 UTC配置前先确认date timedatectl如果时区不对先设置timedatectl set-timezone Asia/Shanghai5.2 crontab 环境中 mysqldump 找不到的经典问题新手部署这个脚本最常遇到的坑手动执行一切正常挂到 crontab 后日志提示mysqldump: command not found。原因很简单crontab 执行任务时不加载用户的环境变量比如/etc/profile、~/.bashrcPATH 环境变量被重置为默认值PATH/usr/bin:/bin。如果你是用源码编译安装的 MySQLmysqldump 通常在/usr/local/mysql/bin这个路径不在 crontab 的默认 PATH 里自然找不到。解决思路有两个一是在 crontab 里显式指定 PATH0 3 * * * PATH/usr/local/mysql/bin:/usr/bin:/bin:/usr/local/bin /usr/bin/bash /opt/mysql_backup/bin/backup.sh /opt/mysql_backup/logs/cron.log 21二是在备份脚本开头引入环境变量我推荐这种脚本里加一行所有的执行环境都覆盖了source /etc/profile但这里有个前提/etc/profile里必须正确配置了 MySQL 的 PATH。如果之前是在命令行手动 export 的重启后 crontab 照样找不到。所以最稳妥的做法还是把 mysqldump 的绝对路径写死在脚本里或者用command -v mysqldump先在脚本里动态定位MYSQLDUMP_PATH$(command -v mysqldump) if [ -z ${MYSQLDUMP_PATH} ]; then MYSQLDUMP_PATH/usr/local/mysql/bin/mysqldump fi先动态探测探测不到再回退到默认路径这样既有兼容性也不会在环境异常时报出莫名奇妙的错误。5.3 定时任务日志监控与异常告警脚本挂到 crontab 只是开始真正的运维在后面。我强烈建议给备份任务加上健康检查至少做到每天看到备份成功的日志每周验证一次备份文件的大小是否在合理区间。日志监控我直接用最简单的 shell 方式每天早上看一眼backup.log里最新的几行。如果项目多、机器多可以写一个小脚本扫描当日日志里是否包含“成功”关键字没有就发告警通知。用企业微信机器人或者钉钉机器人发个 Webhook 请求就行。我实际用的告警逻辑大概长这样TODAY$(date %Y-%m-%d) SUCCESS_COUNT$(grep -c ${TODAY}.*成功 /opt/mysql_backup/logs/backup.log) if [ ${SUCCESS_COUNT} -lt 1 ]; then curl -s -X POST https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key你的key \ -H Content-Type: application/json \ -d {msgtype: text, text: {content: MySQL 备份疑似未成功执行请检查备份服务器}} fi这个逻辑我再解释一下SUCCESS_COUNT统计的是当天日志里含有“成功”字样的行数。由于备份脚本里有多处带“成功”字样的日志比如“备份并压缩成功”“传输成功”只要备份成功至少会出现一行所以统计到 0 行说明备份链路可能出了问题触发告警。逻辑不复杂但非常管用。5.4 定时任务执行时间过长的处理如果你的库比较大mysqldump 加 gzip 压缩可能不止跑几分钟可能跑半小时甚至更久。crontab 本身没有超时限制任务会一直执行到结束但要注意两个隐患一是脚本重入问题。如果上一个备份任务还没结束下一个时间点又到了crontab 会再起一个新进程两个 mysqldump 同时跑磁盘和 CPU 双重压力可能相互影响。解决方法是加一个 PID 锁脚本开头检查是否有同脚本实例在运行LOCK_FILE/tmp/mysql_backup.lock if [ -e ${LOCK_FILE} ]; then LOCK_PID$(cat ${LOCK_FILE}) if kill -0 ${LOCK_PID} 2/dev/null; then log 检测到备份脚本已在运行PID: ${LOCK_PID}本次执行跳过 exit 0 else rm -f ${LOCK_FILE} fi fi echo $$ ${LOCK_FILE} trap rm -f ${LOCK_FILE} EXITkill -0这个技巧很常用它不发送信号只检查进程是否存在。如果 PID 存在说明上一个任务还在跑直接退出本次执行如果进程不存在了说明上次执行是异常退出锁文件成了残留删掉重新获取锁。二是备份时长跨过本地清理窗口。比如备份在凌晨 3 点开始跑了 40 分钟而清理逻辑在备份结束之后才执行所以没问题。但如果你把清理命令也单独挂到 crontab 里就需要注意时间窗口不要造成边备份边清理的冲突。6. 异地恢复演练备份方案有效性的最终验证6.1 恢复演练为什么不能省很多人搭好了备份系统天天看到“备份成功”的日志就以为万事大吉。直到真出故障要恢复的时候才发现备份文件有问题——要么是 mysqldump 导出的 SQL 文件不完整要么是 gzip 解压报错要么是恢复目标环境的 MySQL 版本不兼容。这种“备份成功但没有恢复价值”的情况在业内并不少见。我见过最典型的一次某团队用 mysqldump 做备份但备份账号缺少 PROCESS 权限导致 mysqldump 导出时静默跳过了一些表日志里显示成功恢复时才发现少了好几个业务表。这种问题只有通过恢复演练才能暴露出来。所以我的习惯是每季度至少做一次恢复演练每次恢复演练用真实数据、真实流程完整走一遍从解压到导入再到校验的过程。演练环境用一台和生成环境版本一致的测试机器或者直接用 Docker 起一个临时 MySQL 实例。千万别在正式库上做恢复演练。6.2 从备份文件恢复到新库的完整过程假设有一个备份文件all_databases_20250105_030001.sql.gz现在要把数据恢复到一台全新的 MySQL 实例上。第一步解压备份文件zcat /data/mysql_backup/20250105_030001/all_databases_20250105_030001.sql.gz /tmp/restore.sql如果你确认解压后的 SQL 文件非常大比如 20GB建议不要直接解压到 /tmp而是解压到磁盘空间充足的目录。另外zcat解压需要可用的 gzip 版本如果遇到头部损坏的错误提示备份文件大概率已经损坏这时候要立刻检查异地备份的第二个副本。第二步导入 SQL 文件mysql -u root -p /tmp/restore.sql如果要指定字符集避免导入乱码可以加上mysql -u root -p --default-character-setutf8mb4 /tmp/restore.sql导入过程可能会比较长你可以用nohup放后台跑并记录日志nohup mysql -u root -p --default-character-setutf8mb4 /tmp/restore.sql /tmp/restore.log 21 第三步恢复后的数据校验这个步骤很多人会忽略但恰恰是恢复演练的核心。我通常做三件事检查库表数量是否与源库一致mysql -u root -p -e SELECT COUNT(*) FROM information_schema.tables WHERE table_schemayourdb;抽查几张核心业务表的行数与源库导出前的统计做对比。如果 mysqldump 导出时用了--single-transaction拿到的是某个时间点的一致性快照行数应该是确定的。跑一些关键查询比如导出某天订单的数量、用户总数等看结果是否符合预期。6.3 binlog 回放增量数据恢复的操作细节如果要恢复到某个更精确的时间点比如故障发生前的最后一秒就需要全量备份加 binlog 回放。这个流程我也走过操作如下假设全量备份是凌晨 0 点完成的故障发生在上午 10 点 15 分。先恢复凌晨 0 点的全量备份mysql -u root -p /backup/20250105_000001/all_databases_20250105_000001.sql.gz然后找到从凌晨 0 点到故障前的 binlog 文件。binlog 文件一般在数据目录下命名类似mysql-bin.000123。使用mysqlbinlog工具解析并回放mysqlbinlog \ --start-datetime2025-01-05 00:00:00 \ --stop-datetime2025-01-05 10:15:00 \ /var/lib/mysql/mysql-bin.000123 /var/lib/mysql/mysql-bin.000124 ... \ | mysql -u root -p如果 binlog 文件太多可以用通配符mysqlbinlog \ --start-datetime2025-01-05 00:00:00 \ --stop-datetime2025-01-05 10:15:00 \ /var/lib/mysql/mysql-bin.* \ | mysql -u root -p注意这里有个潜在问题如果某个 binlog 文件里包含了凌晨 0 点之前的事务直接全量回放会报错因为事务对应的数据可能不存在。--start-datetime和--stop-datetime可以帮我们过滤时间范围但严格来说datetime 过滤存在边界误差。更精确的做法是使用--start-position和--stop-position指定 binlog 事件的位置偏移量这需要先分析 binlog 找到准确的位点。对大多数恢复场景按时间过滤已经足够但要知道有这个误差存在。6.4 一份可供季度演练的检查清单演练不是走过场我给自己拟定了一份检查清单每次演练逐项打勾[ ] 异地的备份文件是否完整可解压解压后 SQL 文件不是 0 字节[ ] 恢复目标 MySQL 版本是否与源库一致或兼容[ ] 全部库表是否导入成功库表数量是否与源库一致[ ] 每张业务表行数是否与预期吻合抽查不低于 3 张表[ ] 核心业务查询是否正常返回无字符集乱码[ ] 应用的数据库连接指向恢复后的库能否正常启动和读写[ ] binlog 回放后数据是否追加到目标时间点事务是否一致每次演练结果记录下来有问题就尽快修正备份脚本或恢复流程。我在第一次演练时就发现 binlog 文件没有同步到异地机器导致时间点恢复只能恢复到全量备份那一刻后来专门加了一段 binlog 传输逻辑。如果当时没做演练真出事时只能干瞪眼。7. 方案扩展与实战经验补充7.1 多库多实例场景的备份策略调整如果你的服务器上跑了多个 MySQL 实例比如不同端口各跑一个那备份脚本里的MYSQL_HOST、MYSQL_PORT、MYSQL_SOCKET就不能只定义一份。我的做法是把实例信息写到一个配置文件数组里脚本遍历执行declare -A MYSQL_INSTANCES MYSQL_INSTANCES[instance1]3306:/var/run/mysqld/mysqld1.sock MYSQL_INSTANCES[instance2]3307:/var/run/mysqld/mysqld2.sock for INSTANCE_NAME in ${!MYSQL_INSTANCES[]}; do IFS: read -r PORT SOCKET ${MYSQL_INSTANCES[$INSTANCE_NAME]} mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ -h 127.0.0.1 \ -P ${PORT} \ -u ${MYSQL_USER} \ -p${MYSQL_PASS} \ --all-databases \ | gzip ${BACKUP_DIR}/${INSTANCE_NAME}_${DATE_TAG}.sql.gz done这种多实例备份方式每个实例导出的文件独立命名恢复时互不干扰。实测跑起来效果不错但要注意服务器的整体负载——多个 mysqldump 同时执行会显著增加 CPU 和磁盘 IO如果实例数太多建议每个实例的备份时间错开或者让它们串行执行。7.2 备份文件加密防止数据泄露的最后防线备份文件是纯 SQL 文本里面包含所有业务数据。如果异地备份文件被非法访问等于直接数据泄露。尤其备份目的地是对象存储时更要认真考虑加密。我用的方案是 GPG 对称加密在 gzip 压缩之后、rsync 传输之前做一层加密gpg --batch --yes \ --passphrase-file /opt/mysql_backup/.gpg_pass \ --symmetric \ --cipher-algo AES256 \ ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz加密后生成的是.sql.gz.gpg文件传输和存储都以加密文件为准。恢复时先解密再解压gpg --batch --yes \ --passphrase-file /opt/mysql_backup/.gpg_pass \ --decrypt \ ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz.gpg \ | zcat /tmp/restore.sql密钥文件的安全管理就很重要了。/opt/mysql_backup/.gpg_pass这个文件权限设为 600并且只在源备份机和解密恢复机上存在不要跟着备份文件一起传走。否则加密就失去了意义。7.3 备份成功的判定标准不能只看退出码脚本里我用了$?判断 mysqldump 是否成功但这是不够的。mysqldump 退出码为 0 只能说明命令正常结束并不代表数据完整。比如某个表在导出过程中被 DDL 变更或者磁盘空间刚好够 gzip 写入但文件不完整这些情况退出码未必是非 0。我在实际生产环境里增加了一重保险备份完成后检查文件大小和 SQL 文件尾部的完整性标记。SQL_FILE${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz MIN_SIZE1048576 # 1MB根据实际数据库大小调整 if [ ! -f ${SQL_FILE} ]; then log 备份文件不存在本次备份失败 exit 1 fi FILE_SIZE$(stat -c%s ${SQL_FILE}) if [ ${FILE_SIZE} -lt ${MIN_SIZE} ]; then log 备份文件异常偏小${FILE_SIZE} 字节可能是不完整备份请人工检查 exit 1 fi # 验证 gzip 文件完整性 if ! gzip -t ${SQL_FILE} 2/dev/null; then log gzip 完整性校验失败备份文件可能损坏 exit 1 figzip -t这个命令会测试压缩文件的完整性不实际解压内容速度很快。如果 gzip 头部或尾部 CRC 校验失败会立即返回非 0。这一层校验我强烈建议加上它能把“看似成功实则损坏”的情况拦截在本地。7.4 传输完成后的源端确认机制备份文件推到异地后如何确认远端真的写成功rsync 本身有校验机制但为了保险我还会在传输完成后再用 SSH 或 rsync daemon 检查一次远端文件大小和本地是否一致。如果走的是 rsync daemon 模式远端文件列表可以用rsync --list-only --password-file/etc/rsyncd.secrets.source \ ${RSYNC_USER}${REMOTE_HOST}::${REMOTE_MODULE}/${REMOTE_DIR}/对比输出中文件的字节数。也可以写个简单的对比脚本LOCAL_SIZE$(stat -c%s ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz) REMOTE_SIZE$(rsync --list-only --password-file/etc/rsyncd.secrets.source \ ${RSYNC_USER}${REMOTE_HOST}::${REMOTE_MODULE}/${REMOTE_DIR}/all_databases_${DATE_TAG}.sql.gz | awk {print $1}) if [ ${LOCAL_SIZE} ${REMOTE_SIZE} ]; then log 远端文件大小校验通过 else log 远端文件大小不一致本地 ${LOCAL_SIZE}远端 ${REMOTE_SIZE}请检查 fi这种显式校验比单纯依赖 rsync 的返回码更可靠因为即使 rsync 正常返回也可能因为某些网络代理或存储问题导致文件写不完整。多一层校验多一份安心。8. 整个方案落地后的运行效果与后续可扩展方向这套方案在我这边已经稳定跑了相当长一段时间。每天凌晨 3 点整备份脚本准时拉起mysqldump 导出 gzip 压缩rsync 推送到异地备份机rclone 同步到云对象存储全程大约 20 分钟跑完。我每天早上的例行公事就是看一眼日志里有没有红色警告再检查一下对象存储里今天的备份文件是不是按时出现了。到目前为止中途陆续发现并修复过几个问题账号权限不足、crontab 环境变量缺失、rsync 远端空间不够——每一个都是通过日志和告警发现的没有影响过真正的数据恢复。这套方案已经覆盖了从备份生成、压缩加密、异地传输、云上冗余、定时告警、恢复演练的完整闭环。如果你目前只有单机备份可以参考我在第 3 节的脚本先把本地备份做扎实如果你已经在做本地备份但还没异地传输重点看第 4 节的 rsync 配置如果你连恢复演练都没做过一定优先补上第 6 节的流程——再完美的备份方案恢复不了就等于没有备份。最后分享一个小技巧备份脚本里所有临时文件目录、日志目录、密码文件的权限统一设置为 700 或 600。别小看这一个权限设置备份文件里基本等于数据库全量数据如果目录权限放开任何能登录系统的用户都能把备份文件拷走你的数据保护就只剩一层窗户纸了。我用的目录权限是这样的/opt/mysql_backup目录设为 700/etc/rsyncd.secrets.source和 GPG 密码文件设为 600日志目录设为 750。细节做到位这套方案才算真正完整。 MySQL自动备份与异地容灾：从mysqldump到rsync的完整实战
1. 单机备份的致命盲区为什么必须把数据送出去先讲个我早年踩过的真实事故。当时给一家电商公司维护数据库每天凌晨用 crontab 跑 mysqldump备份文件老老实实落在本机磁盘上日志显示每天都成功我心里还挺踏实。结果某天机房硬盘阵列出现坏道系统盘和数据盘接连报错最后整个服务器起不来。等运维把磁盘挂到别的机器上一看备份文件倒是还在但读取时大量坏块dump 文件根本解不开。那一刻我才彻底明白备份文件和源数据放在同一台机器上本质上等于没备份。后来我做数据库运维方案不管项目大小异地备份永远是必选项。所谓异地不一定非得是跨城市只要备份文件和源数据库不在同一个物理故障域内就算达标。比如源库在 A 机房的机器 A备份传送到同机房另一台物理机或者是对象存储都能规避掉很大一部分风险。如果预算充足跨机房、跨地域当然更好这是应对地震、火灾、整个机房断电这类极端情况的手段。除此之外异地备份还有一个非常实际的价值防止误操作和勒索类故障。数据库被误删、被恶意加密、被某个手滑的同事执行了不带 WHERE 的 UPDATE这些情况下本机备份很可能也跟着遭殃——比如攻击者拿到了服务器权限第一件事就是把备份文件删掉。但备份已经传到另一台机器上攻击者触达不到这时候你手里还有一份干净的历史数据恢复就是时间问题。这篇文章我会完整梳理一套我自己用了一年多的 MySQL 自动备份加异地传输方案。整套方案不依赖商业软件纯脚本加系统自带工具就能实现操作逻辑清晰适合中小型项目、个人服务器以及刚入门运维的开发者参考。我会把每一步的取舍、脚本里每个关键参数的含义、实际跑起来会遇到的坑都写清楚。2. 备份方式选型mysqldump 与物理备份的取舍2.1 两种主流备份方式的本质区别MySQL 的备份手段五花八门但抛开外围工具底层就两大类逻辑备份和物理备份。逻辑备份的代表就是 mysqldump它把数据库里的表结构、数据、触发器、存储过程等转换成一条条 SQL 语句导出成一个文本文件。恢复的时候执行这些 SQL就能把数据重新灌回数据库。这种方式的优点是跨版本恢复能力强比如从 MySQL 5.7 导出导入到 MySQL 8.0 基本没问题文件是纯文本可以直接 grep 查看内容、用 sed 做处理备份过程对存储引擎的依赖性低。缺点是备份和恢复都需要经过 SQL 解析层数据量大时速度偏慢而且恢复时是逐条执行语句非常耗时。物理备份则直接复制数据库的底层数据文件比如 InnoDB 的 ibd 文件、frm 文件、redo log 等。市面上常用的工具是 Percona XtraBackup。它的核心优势是备份速度快因为只是文件级别的复制恢复时也快适合几十 GB、上百 GB 的大库。缺点是版本和平台绑定较死MySQL 版本升级后老备份往往不能直接恢复而且它备份的是二进制文件没法像 mysqldump 那样灵活处理单表数据。我的选择逻辑很简单数据量在 10GB 以内无脑用 mysqldump。10GB 的库mysqldump 全量导出也就几分钟的事恢复时灌入 MySQL 也基本能接受。而且脚本处理逻辑简单依赖少任何一台装好 mysql-client 的机器就能跑。等库涨到几十 GB再切换到 XtraBackup 也不迟。2.2 使用 mysqldump 必须明确的参数细节直接跑mysqldump -u root -p虽然能导出但生产环境绝不能这么干。我给出一套个人认为比较稳妥的参数组合mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ --hex-blob \ --databases dbname \ /backup/dbname_$(date %F).sql参数作用为什么必须加--single-transaction在 InnoDB 引擎下开启一个一致性的快照事务进行备份不加的话备份期间如果有写入导出的数据可能是不一致的加了它备份过程不会锁表线上业务无感知--routines导出存储过程和函数很多项目的统计逻辑藏在存储过程里不导出恢复后就缺胳膊少腿--triggers导出触发器数据联动逻辑依赖触发器漏掉会引发数据完整性问题--events导出事件调度器定时清理任务等事件需要带过去--set-gtid-purgedOFF不导出 GTID 信息恢复时如果目标库已有 GTID 配置导出 GTID 会导致主从同步错乱--default-character-setutf8mb4指定字符集不指定的话碰到 emoji 或生僻字会乱码--hex-blob二进制字段以十六进制形式导出防止 BLOB 字段在导出过程中产生不可控的字符转义问题--databases dbname指定需要备份的库名指定库名后导出的文件会自动包含 CREATE DATABASE 语句恢复时不用手动建库这里特别强调--single-transaction这个参数。很多人以为 mysqldump 一定会锁表实际上在 InnoDB 引擎下加上这个参数备份是通过 MVCC 机制读取一致性快照的整个过程不会阻塞线上读写。但如果你的表用的是 MyISAM 引擎这个参数就不起作用了MyISAM 备份时照样锁表。所以如果库里还有 MyISAM 表建议提前把它转成 InnoDB或者做好备份期间业务可接受短暂锁表的心理准备。2.3 全量备份与增量备份的组合策略我日常采用的节奏是每天凌晨一次全量备份每 5 分钟一次 binlog 增量同步。这里说清楚“增量”是怎么实现的。MySQL 的 binlog 会记录所有数据变更操作。只要开启了 binlog从上次全量备份的时间点开始所有写入操作都记录在 binlog 里。恢复时先导入最近一次全量备份再重放从备份时间点至今的 binlog就能把数据库恢复到任意一个时间点。开启 binlog 需要在 MySQL 配置文件里加上[mysqld] server-id1 log-binmysql-bin binlog_formatrow expire_logs_days7binlog 的保留天数建议根据全量备份周期来定。如果每天全量备份一次binlog 保留 7 天意味着即使全量备份连续失败 6 天第 7 天仍有完整 binlog 可以追加上去不会丢数据。binlog_formatrow是很多生产环境推荐的格式它能记录行级别的变化恢复时更加精确但相对 statement 格式binlog 文件会更大一些磁盘空间要留足。增量的核心价值在于全量备份是每日的“地基”binlog 是地基之上的“砖块”。一旦某天凌晨 3 点数据库崩了而你最后一次全量备份是当天凌晨 0 点的那 0 点到 3 点之间的数据就要靠 binlog 重放来补。脚本只能负责全量备份binlog 的同步通常走另一个机制比如从库实时拉取或者用留存的 binlog 文件定期传输。如果是单库场景最省事的做法是定期把 binlog 文件也一起传送到异地机器上保留最近 3~7 天的 binlog配合全量备份就可以做时间点恢复。3. 本地备份脚本的完整实现与逐段解析3.1 脚本的目录规划与基础配置先看目录结构。好的目录规划能省掉后面无数麻烦我习惯这样安排/opt/mysql_backup/ ├── bin/ # 脚本文件目录 │ └── backup.sh ├── logs/ # 日志目录 │ └── backup.log └── data/ # 临时存放备份文件目录 └── mysql/ # 按日期归档的目录备份文件在本地不要留太久我一般只保留最近 3 天因为文件最终要传到异地机器本地只是中转站。本地留 3 天异地留 30 天这是一个存储成本和恢复灵活性的平衡点。如果你磁盘充裕保留 7 天也不是不行但一定不要让本机成为备份文件的主要归档点那样又绕回“单机故障全完蛋”的坑里了。3.2 备份脚本主体从备份到压缩再到清理下面这个脚本是我在线上环境实际跑的版本我按模块拆开讲而不是甩一段完整脚本让你自己猜。基础配置部分#!/bin/bash # MySQL 自动备份脚本 # 适用环境CentOS 7 / Ubuntu 18.04 BACKUP_BASE/opt/mysql_backup/data/mysql LOG_FILE/opt/mysql_backup/logs/backup.log MYSQL_HOST127.0.0.1 MYSQL_PORT3306 MYSQL_USERbackup_user MYSQL_PASSYourStrongPassword MYSQL_SOCKET/var/run/mysqld/mysqld.sock BACKUP_KEEP_DAYS3 DATE_TAG$(date %Y%m%d_%H%M%S) BACKUP_DIR${BACKUP_BASE}/${DATE_TAG}几点说明数据库账号千万不要用 root创建一个仅具备 SELECT、SHOW VIEW、TRIGGER 等只读权限的专用账号。备份账号权限越大一旦脚本被入侵影响面就越大。推荐的最小权限集合CREATE USER backup_userlocalhost IDENTIFIED BY YourStrongPassword; GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO backup_userlocalhost; GRANT PROCESS, RELOAD ON *.* TO backup_userlocalhost; FLUSH PRIVILEGES;这里重复一遍--single-transaction做一致性快照其实不一定需要 LOCK TABLES但 mysqldump 在准备阶段仍可能尝试获取某些元数据锁加上 LOCK TABLES 权限可以避免奇怪的权限报错。PROCESS 权限用于查看进程信息RELOAD 权限用于 FLUSH 操作这两个在部分 MySQL 版本中会被 mysqldump 内部逻辑调用。备份与压缩部分mkdir -p ${BACKUP_DIR} log() { echo $(date %Y-%m-%d %H:%M:%S) - $1 ${LOG_FILE} } log 开始备份数据库... MYSQL_PWD${MYSQL_PASS} mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ --hex-blob \ -h ${MYSQL_HOST} \ -P ${MYSQL_PORT} \ -u ${MYSQL_USER} \ --all-databases \ | gzip ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz if [ $? -eq 0 ]; then log 备份并压缩成功文件大小$(du -h ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz | cut -f1) else log 备份过程中出现错误请检查数据库连接和磁盘空间 exit 1 fi这里用--all-databases还是单库--databases我的建议是如果服务器上跑着不止一个业务库直接用--all-databases一把梭避免漏掉库。如果只有单一业务库用--databases dbname更干净可以避免把 mysql 系统库也导出来。MYSQL_PWD环境变量传密码的方式比在命令行里写-p更安全一点点因为命令行参数会被进程列表看到而环境变量只有当前用户能读到。当然更安全的是使用 MySQL 配置文件~/.my.cnf存放凭据但要注意该文件的权限必须设为 600。历史备份清理部分find ${BACKUP_BASE} -maxdepth 1 -type d -name 20* -mtime ${BACKUP_KEEP_DAYS} -exec rm -rf {} \; if [ $? -eq 0 ]; then log 已清理本地超过 ${BACKUP_KEEP_DAYS} 天的历史备份 else log 清理历史备份时出现异常请手动检查 fifind加-mtime 3表示查找修改时间在 3 天前的文件和目录。这里有个细节BACKUP_DIR是以日期时间命名的目录所以-mtime可以准确反映备份的创建时间。如果单纯用文件名正则去匹配再删除可能会有误删风险推荐用-mtime这种时间属性来判定。3.3 权限与执行脚本能跑起来的最后一步脚本写完后执行权限不能忘chmod x /opt/mysql_backup/bin/backup.sh先手动跑一遍确认无报错输出日志正常bash /opt/mysql_backup/bin/backup.sh手动执行通过后再配 crontab。这里有个容易忽略的问题crontab 环境变量和当前 shell 环境不一样mysqldump 很可能不在 crontab 的 PATH 里。所以脚本里的命令建议写绝对路径或者脚本开头加一段导出 PATHexport PATH$PATH:/usr/local/mysql/bin:/usr/bin:/bin如果不确定 mysqldump 安装在哪先执行which mysqldump确认路径再填进脚本。3.4 备份脚本实际运行时的常见报错与处理我在实际部署中遇到过几个高频报错这里直接列出来报错一mysqldump: Got error: 1556: You cant use locks with log tables.这个通常发生在使用--all-databases的时候因为 mysql 库里的 slow_log 表和 general_log 表是 CSV 引擎不支持锁表。解决方法有两个一是加上--skip-lock-tables参数但这样会失去 MyISAM 表的锁表保护二是用--ignore-tablemysql.slow_log --ignore-tablemysql.general_log跳过这两个表。我的实践是后者因为系统日志表对业务恢复没有实际价值。报错二Access denied; you need (at least one of) the RELOAD privilege(s) for this operation这是备份账号权限不足导致的。给账号补上 RELOAD 权限即可前面创建账号的语句里已经包含了。如果你用的是 MySQL 8.0还要注意默认认证插件是 caching_sha2_password部分老的驱动不支持需要确认备份主机上的 mysql-client 版本够新。报错三磁盘空间不足导致 gzip 写到一半失败这个很尴尬因为备份过程中你不会立刻发现等脚本返回非 0 退出码才看到。我在脚本里加了一个前置检查REQUIRED_SPACE$(du -sb /var/lib/mysql | cut -f1) AVAILABLE_SPACE$(df -B1 /opt/mysql_backup | awk NR2 {print $4}) if [ ${AVAILABLE_SPACE} -lt ${REQUIRED_SPACE} ]; then log 磁盘空间不足以完成备份期望至少 ${REQUIRED_SPACE} 字节实际可用 ${AVAILABLE_SPACE} 字节 exit 1 fi简单粗暴地以源数据目录大小作为备份文件大小的预估上限实际备份文件通常会小于源数据体积但多留余量总没错。4. 远程传输方案选型与 rsync 实战配置4.1 传输工具对比scp、rsync、对象存储、cloud sync备份文件生成之后最关键的一步就是把它送出去。这一步的选择直接影响传输效率和故障恢复的成功率。我对比过几种常见方案方案优势劣势适用场景scp简单直接系统自带无需额外安装不支持断点续传大文件传输中网络抖动就要重来备份文件很小1GB网络稳定rsync SSH支持断点续传、增量传输、文件校验传输失败可续传配置稍复杂目标端需要 rsync 和 SSH 服务大多数场景的首选rclone 对象存储上传到云厂商的 OSS/COS/S3无限容量天然异地依赖公网可能产生流量费用需要配置云密钥有云资源预算希望免运维云厂商 backup 服务全托管自动备份自动异地绑定云厂商无法自定义备份策略已深度使用某朵云我的建议是自建服务器之间优先 rsync上云优先 rclone。rsync 的增量特性在传输大文件时优势极强——第一次全量传输后后续每天的同名文件如果内容变化不大rsync 只传输差异部分而且支持中断续传这可比 scp 稳太多了。4.2 目标端配置rsync 服务端安装与模块定义rsync 有两种工作模式一种是直接用 SSH 通道传输另一种是走 rsync daemon 模式。我建议用 daemon 模式因为通过 SSH 模式时每次传输需要验证登录用户的凭据在 crontab 里安排免密登录会多一步配置daemon 模式则可以直接用 rsync 协议不依赖系统用户权限控制也清晰。目标端接收备份的异地机器安装并配置 rsync# Ubuntu/Debian apt-get update apt-get install -y rsync # CentOS/RHEL yum install -y rsync编辑/etc/rsyncd.confuid root gid root use chroot yes max connections 4 pid file /var/run/rsyncd.pid log file /var/log/rsyncd.log timeout 300 [mysql_backup] path /data/mysql_backup comment MySQL Backup Storage read only no list no auth users backup_rsync secrets file /etc/rsyncd.secrets创建密码文件权限设为 600echo backup_rsync:YourRsyncPassword /etc/rsyncd.secrets chmod 600 /etc/rsyncd.secrets启动 rsync 服务systemctl enable rsync systemctl start rsync这里有几个安全点需要特别强调use chroot yes让 rsync 进程锁定在模块目录内避免通过路径穿越访问系统其他目录。auth users配合secrets file传输前需要验证账号密码而不是裸奔在网络上。如果异地机器暴露在公网建议再加防火墙规则只允许源备份机的 IP 访问 873 端口firewall-cmd --permanent --add-rich-rulerule familyipv4 source address源机器IP port port873 protocoltcp accept firewall-cmd --reload4.3 源端传输脚本rsync 命令与重试机制源端的传输逻辑我集成到了主备份脚本里。备份和压缩成功后紧接着执行 rsync 传输REMOTE_HOSTbackup.example.com REMOTE_MODULEmysql_backup RSYNC_USERbackup_rsync RSYNC_PASSYourRsyncPassword export RSYNC_PASSWORD${RSYNC_PASS} REMOTE_DIR${DATE_TAG} SYNC_LOG/opt/mysql_backup/logs/rsync.log log() { echo $(date %Y-%m-%d %H:%M:%S) - $1 ${SYNC_LOG} } log 开始推送备份文件到异地服务器... for i in 1 2 3; do rsync -az --partial \ --password-file/etc/rsyncd.secrets.source \ ${BACKUP_DIR}/ \ ${RSYNC_USER}${REMOTE_HOST}::${REMOTE_MODULE}/${REMOTE_DIR}/ \ ${SYNC_LOG} 21 if [ $? -eq 0 ]; then log 第 ${i} 次传输成功 break else log 第 ${i} 次传输失败正在重试... sleep 10 fi done说明几个参数-a归档模式保留文件属性、权限、时间戳等。-z传输时压缩备份文件本身已经是 gzip 压缩过的按理说压缩效果有限但万一有未压缩的小文件还是能省带宽的。--partial保留传输中断时已传输的部分文件。这是 rsync 断点续传的关键没有这个参数中断后重传就得整个文件重新开始。--password-file从文件读取密码避免密码出现在命令行历史里。我这里在源端创建了一个单独的密码文件/etc/rsyncd.secrets.source内容只有一行YourRsyncPassword。注意和客户端密码文件不同客户端密码文件不需要写账号名只需要密码。for i in 1 2 3这个重试循环是我后来才加上的。之前没加重试有一回网络抖动导致 rsync 失败备份没传到异地偏偏那天源库也出了问题差点酿成事故。加个三重试每次间隔 10 秒虽然不能保证 100% 成功但能过滤掉大部分瞬时网络波动。4.4 为什么除了 rsync 还要留一份对象存储方案rsync 再稳也架不住目标机房整体故障。如果预算允许我非常推荐在 rsync 推送到自建服务器的同时再用 rclone 把备份文件同步到云对象存储一份。成本其实很低一个 10GB 的备份文件放到 OSS 标准存储一个月也就几块钱。但换取的是双副本异地冗余一处在自己的备份机一处在云上恢复可用性直接拉满。rclone 的安装与配置也不算麻烦curl https://rclone.org/install.sh | bash rclone configrclone config是交互式配置向导按提示选择对应的云存储类型填入 AccessKey 和 SecretKey创建一个 remote 名字。我用的是阿里云 OSS配置完后上传命令非常简单rclone copy /opt/mysql_backup/data/mysql/ remote:mysql-backup-bucket/ --transfers 4 --log-file/opt/mysql_backup/logs/rclone.log--transfers 4表示同时开启 4 个并发上传任务能显著加速大文件上传。rclone 还有--checkers参数控制并发检查文件差异的线程数一般保持默认就行。把这段逻辑加到 rsync 成功之后、脚本退出之前如果 rclone 传输失败也不要影响主流程的退出码用独立的日志记录即可——毕竟 rsync 已经保障了核心备份的异地落地对象存储是加分项不是必选项。5. crontab 定时任务配置与执行环境坑位排查5.1 crontab 配置时间窗口与频率的确定备份脚本全部就绪后需要把它挂到 crontab 里定时执行。我的建议是每天凌晨执行一次具体时间选在业务低峰期。电商类项目晚上 10 点后流量下降但有些夜间批处理任务会在凌晨 1~2 点跑所以我会避开这些时段选凌晨 3 点。金融类项目可能跨天结算凌晨 4~5 点才有空闲窗口这个要依据业务实际情况定。编辑当前用户的 crontabcrontab -e添加一行0 3 * * * /usr/bin/bash /opt/mysql_backup/bin/backup.sh /opt/mysql_backup/logs/cron.log 21这行的含义是每天凌晨 3 点整执行 backup.sh同时把标准输出和错误输出都追加到 cron.log 里。不建议写成/dev/null 21虽然这样不会收到 crontab 的系统邮件但遇到脚本静默失败时你根本没法排查。日志是排障的第一手材料宁可多占一点磁盘也要把所有输出都留下来。crontab 还需要注意一点如果服务器时间是 UTC那么0 3 * * *实际上是北京时间上午 11 点执行。很多云服务器默认时区是 UTC配置前先确认date timedatectl如果时区不对先设置timedatectl set-timezone Asia/Shanghai5.2 crontab 环境中 mysqldump 找不到的经典问题新手部署这个脚本最常遇到的坑手动执行一切正常挂到 crontab 后日志提示mysqldump: command not found。原因很简单crontab 执行任务时不加载用户的环境变量比如/etc/profile、~/.bashrcPATH 环境变量被重置为默认值PATH/usr/bin:/bin。如果你是用源码编译安装的 MySQLmysqldump 通常在/usr/local/mysql/bin这个路径不在 crontab 的默认 PATH 里自然找不到。解决思路有两个一是在 crontab 里显式指定 PATH0 3 * * * PATH/usr/local/mysql/bin:/usr/bin:/bin:/usr/local/bin /usr/bin/bash /opt/mysql_backup/bin/backup.sh /opt/mysql_backup/logs/cron.log 21二是在备份脚本开头引入环境变量我推荐这种脚本里加一行所有的执行环境都覆盖了source /etc/profile但这里有个前提/etc/profile里必须正确配置了 MySQL 的 PATH。如果之前是在命令行手动 export 的重启后 crontab 照样找不到。所以最稳妥的做法还是把 mysqldump 的绝对路径写死在脚本里或者用command -v mysqldump先在脚本里动态定位MYSQLDUMP_PATH$(command -v mysqldump) if [ -z ${MYSQLDUMP_PATH} ]; then MYSQLDUMP_PATH/usr/local/mysql/bin/mysqldump fi先动态探测探测不到再回退到默认路径这样既有兼容性也不会在环境异常时报出莫名奇妙的错误。5.3 定时任务日志监控与异常告警脚本挂到 crontab 只是开始真正的运维在后面。我强烈建议给备份任务加上健康检查至少做到每天看到备份成功的日志每周验证一次备份文件的大小是否在合理区间。日志监控我直接用最简单的 shell 方式每天早上看一眼backup.log里最新的几行。如果项目多、机器多可以写一个小脚本扫描当日日志里是否包含“成功”关键字没有就发告警通知。用企业微信机器人或者钉钉机器人发个 Webhook 请求就行。我实际用的告警逻辑大概长这样TODAY$(date %Y-%m-%d) SUCCESS_COUNT$(grep -c ${TODAY}.*成功 /opt/mysql_backup/logs/backup.log) if [ ${SUCCESS_COUNT} -lt 1 ]; then curl -s -X POST https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key你的key \ -H Content-Type: application/json \ -d {msgtype: text, text: {content: MySQL 备份疑似未成功执行请检查备份服务器}} fi这个逻辑我再解释一下SUCCESS_COUNT统计的是当天日志里含有“成功”字样的行数。由于备份脚本里有多处带“成功”字样的日志比如“备份并压缩成功”“传输成功”只要备份成功至少会出现一行所以统计到 0 行说明备份链路可能出了问题触发告警。逻辑不复杂但非常管用。5.4 定时任务执行时间过长的处理如果你的库比较大mysqldump 加 gzip 压缩可能不止跑几分钟可能跑半小时甚至更久。crontab 本身没有超时限制任务会一直执行到结束但要注意两个隐患一是脚本重入问题。如果上一个备份任务还没结束下一个时间点又到了crontab 会再起一个新进程两个 mysqldump 同时跑磁盘和 CPU 双重压力可能相互影响。解决方法是加一个 PID 锁脚本开头检查是否有同脚本实例在运行LOCK_FILE/tmp/mysql_backup.lock if [ -e ${LOCK_FILE} ]; then LOCK_PID$(cat ${LOCK_FILE}) if kill -0 ${LOCK_PID} 2/dev/null; then log 检测到备份脚本已在运行PID: ${LOCK_PID}本次执行跳过 exit 0 else rm -f ${LOCK_FILE} fi fi echo $$ ${LOCK_FILE} trap rm -f ${LOCK_FILE} EXITkill -0这个技巧很常用它不发送信号只检查进程是否存在。如果 PID 存在说明上一个任务还在跑直接退出本次执行如果进程不存在了说明上次执行是异常退出锁文件成了残留删掉重新获取锁。二是备份时长跨过本地清理窗口。比如备份在凌晨 3 点开始跑了 40 分钟而清理逻辑在备份结束之后才执行所以没问题。但如果你把清理命令也单独挂到 crontab 里就需要注意时间窗口不要造成边备份边清理的冲突。6. 异地恢复演练备份方案有效性的最终验证6.1 恢复演练为什么不能省很多人搭好了备份系统天天看到“备份成功”的日志就以为万事大吉。直到真出故障要恢复的时候才发现备份文件有问题——要么是 mysqldump 导出的 SQL 文件不完整要么是 gzip 解压报错要么是恢复目标环境的 MySQL 版本不兼容。这种“备份成功但没有恢复价值”的情况在业内并不少见。我见过最典型的一次某团队用 mysqldump 做备份但备份账号缺少 PROCESS 权限导致 mysqldump 导出时静默跳过了一些表日志里显示成功恢复时才发现少了好几个业务表。这种问题只有通过恢复演练才能暴露出来。所以我的习惯是每季度至少做一次恢复演练每次恢复演练用真实数据、真实流程完整走一遍从解压到导入再到校验的过程。演练环境用一台和生成环境版本一致的测试机器或者直接用 Docker 起一个临时 MySQL 实例。千万别在正式库上做恢复演练。6.2 从备份文件恢复到新库的完整过程假设有一个备份文件all_databases_20250105_030001.sql.gz现在要把数据恢复到一台全新的 MySQL 实例上。第一步解压备份文件zcat /data/mysql_backup/20250105_030001/all_databases_20250105_030001.sql.gz /tmp/restore.sql如果你确认解压后的 SQL 文件非常大比如 20GB建议不要直接解压到 /tmp而是解压到磁盘空间充足的目录。另外zcat解压需要可用的 gzip 版本如果遇到头部损坏的错误提示备份文件大概率已经损坏这时候要立刻检查异地备份的第二个副本。第二步导入 SQL 文件mysql -u root -p /tmp/restore.sql如果要指定字符集避免导入乱码可以加上mysql -u root -p --default-character-setutf8mb4 /tmp/restore.sql导入过程可能会比较长你可以用nohup放后台跑并记录日志nohup mysql -u root -p --default-character-setutf8mb4 /tmp/restore.sql /tmp/restore.log 21 第三步恢复后的数据校验这个步骤很多人会忽略但恰恰是恢复演练的核心。我通常做三件事检查库表数量是否与源库一致mysql -u root -p -e SELECT COUNT(*) FROM information_schema.tables WHERE table_schemayourdb;抽查几张核心业务表的行数与源库导出前的统计做对比。如果 mysqldump 导出时用了--single-transaction拿到的是某个时间点的一致性快照行数应该是确定的。跑一些关键查询比如导出某天订单的数量、用户总数等看结果是否符合预期。6.3 binlog 回放增量数据恢复的操作细节如果要恢复到某个更精确的时间点比如故障发生前的最后一秒就需要全量备份加 binlog 回放。这个流程我也走过操作如下假设全量备份是凌晨 0 点完成的故障发生在上午 10 点 15 分。先恢复凌晨 0 点的全量备份mysql -u root -p /backup/20250105_000001/all_databases_20250105_000001.sql.gz然后找到从凌晨 0 点到故障前的 binlog 文件。binlog 文件一般在数据目录下命名类似mysql-bin.000123。使用mysqlbinlog工具解析并回放mysqlbinlog \ --start-datetime2025-01-05 00:00:00 \ --stop-datetime2025-01-05 10:15:00 \ /var/lib/mysql/mysql-bin.000123 /var/lib/mysql/mysql-bin.000124 ... \ | mysql -u root -p如果 binlog 文件太多可以用通配符mysqlbinlog \ --start-datetime2025-01-05 00:00:00 \ --stop-datetime2025-01-05 10:15:00 \ /var/lib/mysql/mysql-bin.* \ | mysql -u root -p注意这里有个潜在问题如果某个 binlog 文件里包含了凌晨 0 点之前的事务直接全量回放会报错因为事务对应的数据可能不存在。--start-datetime和--stop-datetime可以帮我们过滤时间范围但严格来说datetime 过滤存在边界误差。更精确的做法是使用--start-position和--stop-position指定 binlog 事件的位置偏移量这需要先分析 binlog 找到准确的位点。对大多数恢复场景按时间过滤已经足够但要知道有这个误差存在。6.4 一份可供季度演练的检查清单演练不是走过场我给自己拟定了一份检查清单每次演练逐项打勾[ ] 异地的备份文件是否完整可解压解压后 SQL 文件不是 0 字节[ ] 恢复目标 MySQL 版本是否与源库一致或兼容[ ] 全部库表是否导入成功库表数量是否与源库一致[ ] 每张业务表行数是否与预期吻合抽查不低于 3 张表[ ] 核心业务查询是否正常返回无字符集乱码[ ] 应用的数据库连接指向恢复后的库能否正常启动和读写[ ] binlog 回放后数据是否追加到目标时间点事务是否一致每次演练结果记录下来有问题就尽快修正备份脚本或恢复流程。我在第一次演练时就发现 binlog 文件没有同步到异地机器导致时间点恢复只能恢复到全量备份那一刻后来专门加了一段 binlog 传输逻辑。如果当时没做演练真出事时只能干瞪眼。7. 方案扩展与实战经验补充7.1 多库多实例场景的备份策略调整如果你的服务器上跑了多个 MySQL 实例比如不同端口各跑一个那备份脚本里的MYSQL_HOST、MYSQL_PORT、MYSQL_SOCKET就不能只定义一份。我的做法是把实例信息写到一个配置文件数组里脚本遍历执行declare -A MYSQL_INSTANCES MYSQL_INSTANCES[instance1]3306:/var/run/mysqld/mysqld1.sock MYSQL_INSTANCES[instance2]3307:/var/run/mysqld/mysqld2.sock for INSTANCE_NAME in ${!MYSQL_INSTANCES[]}; do IFS: read -r PORT SOCKET ${MYSQL_INSTANCES[$INSTANCE_NAME]} mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ -h 127.0.0.1 \ -P ${PORT} \ -u ${MYSQL_USER} \ -p${MYSQL_PASS} \ --all-databases \ | gzip ${BACKUP_DIR}/${INSTANCE_NAME}_${DATE_TAG}.sql.gz done这种多实例备份方式每个实例导出的文件独立命名恢复时互不干扰。实测跑起来效果不错但要注意服务器的整体负载——多个 mysqldump 同时执行会显著增加 CPU 和磁盘 IO如果实例数太多建议每个实例的备份时间错开或者让它们串行执行。7.2 备份文件加密防止数据泄露的最后防线备份文件是纯 SQL 文本里面包含所有业务数据。如果异地备份文件被非法访问等于直接数据泄露。尤其备份目的地是对象存储时更要认真考虑加密。我用的方案是 GPG 对称加密在 gzip 压缩之后、rsync 传输之前做一层加密gpg --batch --yes \ --passphrase-file /opt/mysql_backup/.gpg_pass \ --symmetric \ --cipher-algo AES256 \ ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz加密后生成的是.sql.gz.gpg文件传输和存储都以加密文件为准。恢复时先解密再解压gpg --batch --yes \ --passphrase-file /opt/mysql_backup/.gpg_pass \ --decrypt \ ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz.gpg \ | zcat /tmp/restore.sql密钥文件的安全管理就很重要了。/opt/mysql_backup/.gpg_pass这个文件权限设为 600并且只在源备份机和解密恢复机上存在不要跟着备份文件一起传走。否则加密就失去了意义。7.3 备份成功的判定标准不能只看退出码脚本里我用了$?判断 mysqldump 是否成功但这是不够的。mysqldump 退出码为 0 只能说明命令正常结束并不代表数据完整。比如某个表在导出过程中被 DDL 变更或者磁盘空间刚好够 gzip 写入但文件不完整这些情况退出码未必是非 0。我在实际生产环境里增加了一重保险备份完成后检查文件大小和 SQL 文件尾部的完整性标记。SQL_FILE${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz MIN_SIZE1048576 # 1MB根据实际数据库大小调整 if [ ! -f ${SQL_FILE} ]; then log 备份文件不存在本次备份失败 exit 1 fi FILE_SIZE$(stat -c%s ${SQL_FILE}) if [ ${FILE_SIZE} -lt ${MIN_SIZE} ]; then log 备份文件异常偏小${FILE_SIZE} 字节可能是不完整备份请人工检查 exit 1 fi # 验证 gzip 文件完整性 if ! gzip -t ${SQL_FILE} 2/dev/null; then log gzip 完整性校验失败备份文件可能损坏 exit 1 figzip -t这个命令会测试压缩文件的完整性不实际解压内容速度很快。如果 gzip 头部或尾部 CRC 校验失败会立即返回非 0。这一层校验我强烈建议加上它能把“看似成功实则损坏”的情况拦截在本地。7.4 传输完成后的源端确认机制备份文件推到异地后如何确认远端真的写成功rsync 本身有校验机制但为了保险我还会在传输完成后再用 SSH 或 rsync daemon 检查一次远端文件大小和本地是否一致。如果走的是 rsync daemon 模式远端文件列表可以用rsync --list-only --password-file/etc/rsyncd.secrets.source \ ${RSYNC_USER}${REMOTE_HOST}::${REMOTE_MODULE}/${REMOTE_DIR}/对比输出中文件的字节数。也可以写个简单的对比脚本LOCAL_SIZE$(stat -c%s ${BACKUP_DIR}/all_databases_${DATE_TAG}.sql.gz) REMOTE_SIZE$(rsync --list-only --password-file/etc/rsyncd.secrets.source \ ${RSYNC_USER}${REMOTE_HOST}::${REMOTE_MODULE}/${REMOTE_DIR}/all_databases_${DATE_TAG}.sql.gz | awk {print $1}) if [ ${LOCAL_SIZE} ${REMOTE_SIZE} ]; then log 远端文件大小校验通过 else log 远端文件大小不一致本地 ${LOCAL_SIZE}远端 ${REMOTE_SIZE}请检查 fi这种显式校验比单纯依赖 rsync 的返回码更可靠因为即使 rsync 正常返回也可能因为某些网络代理或存储问题导致文件写不完整。多一层校验多一份安心。8. 整个方案落地后的运行效果与后续可扩展方向这套方案在我这边已经稳定跑了相当长一段时间。每天凌晨 3 点整备份脚本准时拉起mysqldump 导出 gzip 压缩rsync 推送到异地备份机rclone 同步到云对象存储全程大约 20 分钟跑完。我每天早上的例行公事就是看一眼日志里有没有红色警告再检查一下对象存储里今天的备份文件是不是按时出现了。到目前为止中途陆续发现并修复过几个问题账号权限不足、crontab 环境变量缺失、rsync 远端空间不够——每一个都是通过日志和告警发现的没有影响过真正的数据恢复。这套方案已经覆盖了从备份生成、压缩加密、异地传输、云上冗余、定时告警、恢复演练的完整闭环。如果你目前只有单机备份可以参考我在第 3 节的脚本先把本地备份做扎实如果你已经在做本地备份但还没异地传输重点看第 4 节的 rsync 配置如果你连恢复演练都没做过一定优先补上第 6 节的流程——再完美的备份方案恢复不了就等于没有备份。最后分享一个小技巧备份脚本里所有临时文件目录、日志目录、密码文件的权限统一设置为 700 或 600。别小看这一个权限设置备份文件里基本等于数据库全量数据如果目录权限放开任何能登录系统的用户都能把备份文件拷走你的数据保护就只剩一层窗户纸了。我用的目录权限是这样的/opt/mysql_backup目录设为 700/etc/rsyncd.secrets.source和 GPG 密码文件设为 600日志目录设为 750。细节做到位这套方案才算真正完整。