MySQL数据库脚本备份

环境：centos7、MySQL 8.0

安装mysqldump命令

确认是否安装了 mysqldump
在终端运行：

which mysqldump

如果 没有任何输出，说明 MySQL 客户端未安装。

下载并安装 MySQL 8.0 官方 Yum 仓库

# 下载 MySQL 8.0 官方 repo（适用于 CentOS 7）
sudo wget https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm

# 安装 repo 包
sudo rpm -ivh mysql80-community-release-el7-11.noarch.rpm

# 安装 MySQL 8.0 客户端（仅客户端，不含服务端）
sudo yum install -y mysql-community-client

验证安装

which mysqldump
# 应输出：/usr/bin/mysqldump

mysqldump --version
# 应显示：mysqldump  Ver 8.0.xx for Linux on x86_64 (MySQL Community Server - GPL)

执行备份脚本

# === 配置区 ===
w="$(date "+%Y-%m-%d")"

# MySQL 配置
DB_USER="root"
DB_PASSWORD="123456"
DB_NAME="test"

# 备份目录
BACKUP_DIR="/mnt/sql"

# 创建备份目录（如果不存在）
mkdir -p "$BACKUP_DIR"

# 执行备份(本地数据库)
# 使用 --skip-extended-insert 避免大 INSERT 导致 max_allowed_packet 错误
# --complete-insert 确保 INSERT 包含列名，提高兼容性
/usr/bin/mysqldump -h 127.0.0.1 -P 3306 -u"$DB_USER" -p"$DB_PASSWORD" --skip-extended-insert --complete-insert "$DB_NAME" > "$BACKUP_DIR/backup_$w.sql"

# 检查是否成功
if [ $? -eq 0 ]; then
    echo "[$(date)] 备份成功：$BACKUP_DIR/backup_$w.sql"
else
    echo "[$(date)] 备份失败！" >&2
fi

# === 自动清理：删除 7 天前的 .sql 备份文件 ===
find "$BACKUP_DIR" -type f -name "backup_*.sql" -mtime +7 -delete

将执行脚本放到Linux的定时任务中执行

例：/mnt/sql_backup.sh
将备份脚本加入 Linux 定时任务（crontab），每天凌晨 2 点自动执行。
定时任务（cron）本身是系统服务，开机自动运行。

#第一步：给脚本添加执行权限
chmod +x /mnt/sql_backup.sh
#第二步：用 root 用户编辑 crontab
sudo crontab -e

#第三步：添加以下一行到 crontab
0 2 * * * /mnt/sql_backup.sh >> /var/log/sql_backup.log 2>&1

说明：
0 2 * * * → 每天 凌晨 2:00 执行
日志输出到 /var/log/sql_backup.log，方便你查看是否成功
2>&1 表示错误信息也写入日志

保存并退出编辑器
vim/vi：按 Esc → 输入 :wq → 回车
nano：按 Ctrl+O → 回车 → Ctrl+X
看到提示 crontab: installing new crontab 就表示成功了。

验证设置
查看当前 root 的定时任务：

sudo crontab -l

应该能看到你刚加的那一行。
检查日志（第二天 2 点后）：

tail -n 10 /var/log/sql_backup.log

将备份出的文件转移到另一台服务器

通过 sshpass 工具 实现“用账号密码自动登录 SSH 并同步文件

CentOS 7 安装 sshpass

# 启用 EPEL 仓库（如果未启用）
sudo yum install -y epel-release

# 安装 sshpass
sudo yum install -y sshpass

验证安装：

sshpass -V
# 应输出版本号

修改备份脚本 /mnt/sql_backup.sh

#!/bin/bash

# === 配置区 ===
w="$(date "+%Y-%m-%d")"

# MySQL 配置
DB_USER="root"
DB_PASSWORD="your_password"
DB_NAME="test"

# 备份目录
BACKUP_DIR="/mnt/sql"

# === 远程服务器配置 ===
REMOTE_HOST="192.168.1.20"     # ← 替换为你的目标服务器 IP
REMOTE_USER="root"             # ← 替换为目标服务器用户名
REMOTE_PASS="your_password"    # ← 替换为目标服务器密码
REMOTE_DIR="/mnt/sql"

mkdir -p "$BACKUP_DIR"

# 1. 本地备份
# 使用 --skip-extended-insert 避免大 INSERT 导致 max_allowed_packet 错误
# --complete-insert 确保 INSERT 包含列名，提高兼容性
/usr/bin/mysqldump -h 127.0.0.1 -P 3306 -u"$DB_USER" -p"$DB_PASSWORD" --skip-extended-insert --complete-insert "$DB_NAME" > "$BACKUP_DIR/backup_$w.sql"

if [ $? -ne 0 ]; then
    echo "[$(date)] 本地备份失败！" >&2
    exit 1
fi
echo "[$(date)] 本地备份成功: $BACKUP_DIR/backup_$w.sql"

# 2. 确保远程目录存在
sshpass -p "$REMOTE_PASS" ssh -o StrictHostKeyChecking=no "$REMOTE_USER@$REMOTE_HOST" "mkdir -p $REMOTE_DIR"

# 3. 同步当天的备份文件到远程
sshpass -p "$REMOTE_PASS" rsync -avz -e "ssh -o StrictHostKeyChecking=no" \
    "$BACKUP_DIR/backup_$w.sql" \
    "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"

if [ $? -eq 0 ]; then
    echo "[$(date)] 远程同步成功: $REMOTE_HOST:$REMOTE_DIR/backup_$w.sql"
else
    echo "[$(date)] 远程同步失败！" >&2
fi

# 4. 清理本地 7 天前的备份
find "$BACKUP_DIR" -type f -name "backup_*.sql" -mtime +7 -delete
echo "[$(date)] 本地 7 天前备份已清理"

# 5. 清理远程服务器 7 天前的备份
sshpass -p "$REMOTE_PASS" ssh -o StrictHostKeyChecking=no "$REMOTE_USER@$REMOTE_HOST" \
    "find '$REMOTE_DIR' -type f -name 'backup_*.sql' -mtime +7 -delete"

if [ $? -eq 0 ]; then
    echo "[$(date)] 远程 7 天前备份已清理"
else
    echo "[$(date)] 远程清理失败（可能无权限或路径错误）" >&2
fi