MySQL 数据库本地及异地备份：全面指南

---

MySQL 数据库本地及异地备份：全面指南

在Linux环境下，mysqldump工具是进行MySQL数据库备份的常见方式。本文将分享一份改进版的数据库备份脚本，并结合实际案例，阐述如何定期备份MySQL数据库，并实现本地和异地备份。通过本文，您将掌握一个高效、安全的备份方案，确保数据的安全性和可恢复性。

---

一定要全部看完，再操作，因为数据库密码我没有再脚本中体现，当然也可以体现，看具体需求

---

备份脚本概述

我们创建一个改进版的备份脚本，命名为mysql_backup.sh。该脚本的目标是：

• 自动备份MySQL数据库，并排除系统数据库（如mysql、information_schema等）。
• 将备份数据打包并进行本地压缩。
• 同步备份到异地备份服务器（通过rsync）。
• 自动清理30天前的备份文件，节省存储空间。
• 记录详细的备份日志，便于排查问题。

---

编写备份脚本

脚本内容

#!/bin/bash
set -eo pipefail
trap 'echo "错误发生在行 $LINENO"; exit 1' ERR# 配置区
USER="root"                           # MySQL用户名
BACKUP_DIR="/data/backup"             # 备份存储目录
EXCLUDE_DBS="mysql|information_schema|performance_schema|sys"  # 排除的系统数据库
RSYNC_TARGET="rsync_backup@192.168.1.2::mysqlbackup/192.168.1.3/"     # 异地备份目标
LOGFILE="$BACKUP_DIR/data_backup.log" # 日志文件路径
DATE=$(date +%Y%m%d_%H%M)             # 当前日期时间# 创建备份目录
mkdir -p "$BACKUP_DIR" || exit 1
cd "$BACKUP_DIR" || exit 1# 记录开始时间
echo "==== 备份开始 $(date +"%Y-%m-%d %H:%M:%S") ====" >> "$LOGFILE"# 获取数据库列表并备份
DATABASES=$(mysql -e "SHOW DATABASES;" | grep -Ev "$EXCLUDE_DBS" | tr '\n' ' ')
echo "备份数据库: $DATABASES" >> "$LOGFILE"
mysqldump --routines --single-transaction --master-data=2 --databases $DATABASES > "wzqmysql_bak${DATE}.sql"# 压缩备份
if [ -s "wzqmysql_bak${DATE}.sql" ]; thentar -czf "wzqmysql_bak${DATE}.tar.gz" "wzqmysql_bak${DATE}.sql"rm -f "wzqmysql_bak${DATE}.sql"echo "压缩完成，文件大小: $(du -sh wzqmysql_bak${DATE}.tar.gz)" >> "$LOGFILE"
elseecho "备份文件为空，终止流程！" >> "$LOGFILE"exit 1
fi# 同步到异地
rsync -avz --password-file=/etc/rsync.password "wzqmysql_bak${DATE}.tar.gz" "$RSYNC_TARGET"# 清理30天前备份
find "$BACKUP_DIR" -type f -mtime +30 -name "*.gz" -deleteecho "==== 备份完成 $(date +"%Y-%m-%d %H:%M:%S") ====" >> "$LOGFILE"

---

主要参数解释

• --single-transaction：对于InnoDB表，确保备份的一致性。
• --master-data=2：将主服务器的二进制日志位置写入备份文件，适用于主从复制环境。
• --routines：备份存储过程和函数。
• rsync：用于将备份文件同步到异地服务器。
• find -mtime +30：清理30天前的备份文件。

---

设置脚本权限

在Linux系统中，为脚本添加执行权限：

chmod +x /data/mysql_backup.sh

---

定期执行备份

为了实现自动备份，可以将该脚本添加到定时任务中（例如每天凌晨1点执行）。

设置crontab任务

编辑crontab配置文件：

crontab -e

添加如下行，设定每天凌晨1点执行备份脚本：

0 1 * * * /data/mysql_backup.sh

保存退出后，crontab会自动更新。

---

安全性建议

避免明文密码

将MySQL密码存储在~/.my.cnf配置文件中，并设置严格权限：

[client]
user = root
password = 123456

设置配置文件权限：

chmod 600 ~/.my.cnf

rsync密码文件安全

确保/etc/rsync.password权限为600：

chmod 600 /etc/rsync.password

---

进阶功能和优化

增量备份

mysqldump不支持增量备份，建议使用二进制日志或Percona XtraBackup。以下是简要步骤：

1. 查看二进制日志位置：

   head -n 30 wzqmysql_bak${DATE}.sql | grep "CHANGE MASTER TO"
   

2. 使用mysqlbinlog导出增量日志：

   mysqlbinlog --start-datetime="2025-02-21 00:00:00" /var/lib/mysql/binlog.000001 > incremental.sql
   

备份验证

定期验证备份文件是否可以成功恢复：

# 解压备份
tar -wzqf wzqmysql_bak20250221_0100.tar.gz
# 恢复数据库
mysql -e "CREATE DATABASE test_restore;"
mysql test_restore < wzqmysql_bak20250221_0100.sql

日志轮转

使用logrotate管理日志文件：

/data/backup/data_backup.log {dailyrotate 30compressmissingok
}

详细解释

• daily: 这表示日志文件将每天进行轮换。如果日志文件每天更新或者你希望每次备份后都生成一个新的日志文件，可以使用这个选项。

• rotate 30: 这表示系统将保留最近30个轮换过的日志文件，其余的将被删除。比如，你的日志文件每天生成一个新备份，当系统积累到30个日志文件时，最旧的日志文件将会被删除，确保只保留最新的30个日志备份。

• compress: 这个选项告诉logrotate在轮换日志时，对旧的日志文件进行压缩（通常是.gz格式），从而节省磁盘空间。压缩文件的名字一般会附加.gz扩展名，例如data_backup.log.1.gz、data_backup.log.2.gz等。

• missingok: 如果指定的日志文件不存在，不会抛出错误，而是跳过处理。例如，如果/data/backup/data_backup.log文件在某一天没有生成，logrotate会忽略这个情况，不会报错或中止执行。

---

总结

通过本文的改进版脚本，您可以实现MySQL数据库的定期备份、本地压缩、异地同步以及旧备份文件的自动清理。结合安全性建议和进阶功能，您可以构建一个高效、可靠的数据库备份方案，确保数据的安全性和可恢复性。

---

附录：脚本依赖

• MySQL客户端和mysqldump工具。
• rsync用于异地备份。
• cron用于定时任务调度。
• mailutils（可选）用于邮件通知。