手把手教你写一个带压缩、清理和日志的MySQL自动备份Shell脚本（基于Percona XtraBackup 8.0与Cron）

手把手教你写一个带压缩、清理和日志的MySQL自动备份Shell脚本基于Percona XtraBackup 8.0与Cron在数据库运维中备份是保障数据安全的最后一道防线。一个健壮的备份方案不仅能应对硬件故障还能在人为误操作时快速恢复业务。本文将带你从零开始构建一个生产级MySQL自动备份脚本整合Percona XtraBackup的高效热备份能力、自动化压缩归档、智能清理策略和完备的日志记录系统。1. 环境准备与工具选型1.1 为什么选择Percona XtraBackup传统mysqldump在备份大型数据库时存在明显瓶颈锁表时间长、恢复速度慢。Percona XtraBackup作为业界公认的物理备份工具具有三大核心优势热备份机制在不阻塞正常DML操作的情况下获取一致性备份增量备份支持仅备份变化的数据页节省存储空间快速恢复直接替换数据文件比逻辑导入快数倍最新8.0版本完美兼容MySQL 8.0的诸多新特性包括# 验证安装版本 xtrabackup --version # 预期输出示例xtrabackup version 8.0.35-311.2 系统环境配置在Ubuntu 22.04上配置备份环境需要特别注意权限控制。以下是推荐目录结构/var/backups/mysql/ ├── full/ # 完整备份存储 ├── incremental/ # 增量备份存储 └── logs/ # 备份日志使用以下命令创建并设置权限sudo mkdir -p /var/backups/mysql/{full,incremental,logs} sudo chown -R mysql:mysql /var/backups/mysql sudo chmod 750 /var/backups/mysql2. 备份脚本核心架构设计2.1 脚本基础框架一个健壮的备份脚本应该包含以下模块#!/bin/bash # 定义全局配置 CONFIG_FILE/etc/mysql/backup.conf # 加载配置文件 [ -f $CONFIG_FILE ] source $CONFIG_FILE # 初始化日志系统 init_logging() { LOG_DIR/var/backups/mysql/logs LOG_FILE${LOG_DIR}/backup_$(date %Y%m%d).log exec 31 42 exec (tee -a $LOG_FILE) 21 } # 主备份流程 run_backup() { # 具体实现 } # 错误处理 handle_error() { # 错误处理逻辑 } # 主执行流程 main() { init_logging run_backup } main $2.2 配置文件分离设计将敏感信息与脚本分离是安全最佳实践。创建/etc/mysql/backup.conf# MySQL连接配置 MYSQL_USERbackup_user MYSQL_PASSWORDcomplex_password_123 MYSQL_SOCKET/var/run/mysqld/mysqld.sock # 备份策略 FULL_BACKUP_DAYSunday # 每周日全量备份 KEEP_DAYS30 # 保留最近30天备份 COMPRESS_LEVEL6 # gzip压缩级别(1-9)3. 高级备份功能实现3.1 智能备份策略结合全量和增量备份的混合策略能显著节省存储空间。以下是核心判断逻辑get_backup_type() { local current_day$(date %A) local last_backup$(ls -t /var/backups/mysql/full/*.qp 2/dev/null | head -1) if [ $current_day $FULL_BACKUP_DAY ] || [ -z $last_backup ]; then echo full else echo incremental fi }对应的备份执行命令run_xtrabackup() { local backup_type$1 local target_dir/var/backups/mysql/${backup_type}/$(date %Y%m%d_%H%M%S) mkdir -p $target_dir case $backup_type in full) xtrabackup --backup --target-dir$target_dir \ --user$MYSQL_USER --password$MYSQL_PASSWORD \ --socket$MYSQL_SOCKET ;; incremental) local base_dir$(ls -td /var/backups/mysql/full/* | head -1) xtrabackup --backup --target-dir$target_dir \ --incremental-basedir$base_dir \ --user$MYSQL_USER --password$MYSQL_PASSWORD \ --socket$MYSQL_SOCKET ;; esac }3.2 并行压缩优化使用pigz工具进行多线程压缩可以显著加快处理速度compress_backup() { local backup_dir$1 local compression_threads$(nproc) if command -v pigz /dev/null; then tar -cf - -C $backup_dir . | pigz -c -p $compression_threads -$COMPRESS_LEVEL ${backup_dir}.tar.gz else tar -czf ${backup_dir}.tar.gz -C $backup_dir . fi rm -rf $backup_dir }4. 系统集成与监控4.1 Cron定时任务配置使用systemd timer比传统cron更灵活可靠。创建/etc/systemd/system/mysql-backup.service[Unit] DescriptionMySQL Automated Backup Afternetwork.target [Service] Typeoneshot ExecStart/usr/local/bin/mysql_backup.sh对应的timer单元文件/etc/systemd/system/mysql-backup.timer[Unit] DescriptionRun MySQL backup daily at 2AM [Timer] OnCalendar*-*-* 02:00:00 Persistenttrue [Install] WantedBytimers.target启用服务sudo systemctl daemon-reload sudo systemctl enable --now mysql-backup.timer4.2 监控与告警集成将备份状态接入Prometheus监控系统export_backup_metrics() { local status$1 local duration$2 local backup_size$(du -sh ${backup_dir}.tar.gz | cut -f1) cat EOF /var/lib/node_exporter/mysql_backup.prom # HELP mysql_backup_status Last backup status (0success, 1failure) # TYPE mysql_backup_status gauge mysql_backup_status $status # HELP mysql_backup_duration_seconds Last backup duration in seconds # TYPE mysql_backup_duration_seconds gauge mysql_backup_duration_seconds $duration # HELP mysql_backup_size_bytes Last backup size in bytes # TYPE mysql_backup_size_bytes gauge mysql_backup_size_bytes $(stat -c%s ${backup_dir}.tar.gz) EOF }5. 恢复流程与实战技巧5.1 数据恢复标准化流程完整恢复流程应包含三个关键阶段准备阶段# 解压备份文件 mkdir /var/backups/restore tar -xzf full_backup.tar.gz -C /var/backups/restore # 应用redo日志 xtrabackup --prepare --target-dir/var/backups/restore停止MySQL服务sudo systemctl stop mysql数据替换与权限修复# 清空数据目录 rm -rf /var/lib/mysql/* # 复制备份数据 xtrabackup --copy-back --target-dir/var/backups/restore # 修复权限 chown -R mysql:mysql /var/lib/mysql5.2 性能优化参数在大型数据库场景下调整以下参数可提升备份效率xtrabackup --backup --target-dir/path/to/backup \ --parallel4 \ # 并行线程数 --compress \ # 启用压缩 --compress-threads4 \ # 压缩线程数 --throttle100 \ # 限制IOPS --use-memory2G # 用于prepare阶段的内存6. 异常处理与日志分析6.1 错误捕获机制实现分级错误处理策略handle_error() { local exit_code$1 local message$2 case $exit_code in 0) return ;; 1) echo [WARNING] $message 2 send_alert warning $message ;; *) echo [ERROR] $message 2 send_alert critical $message exit $exit_code ;; esac } # 在关键操作后调用 run_xtrabackup || handle_error $? XtraBackup执行失败6.2 日志分析技巧使用logrotate管理日志文件配置/etc/logrotate.d/mysql-backup/var/backups/mysql/logs/*.log { daily missingok rotate 30 compress delaycompress notifempty create 640 mysql mysql }关键日志分析命令示例# 检查最近备份成功率 grep -c Backup completed successfully /var/backups/mysql/logs/* # 统计备份耗时分布 awk /Backup completed/ {print $NF} /var/backups/mysql/logs/* | sort -n | uniq -c # 查找所有失败记录 grep -i error /var/backups/mysql/logs/* --coloralways