MySQL定时异机备份

场景：将A机器MySQL数据库部分表每日定时备份到B机器上 （只适用于Linux）
实现方式算是比简单了，就是用mysqldump生成文件，使用scp命令传输到另一台机器上。

1. 编写备份shell脚本

在A机器新建脚本
(当然没有vim的话vi命令也可以）

vim backupAndUpload.sh

#!/bin/bash

# 源数据库主机地址
SOURCE_DB_HOST="localhost"
# 源数据库端口号
SOURCE_DB_PORT="3306"
# 源数据库用户名
SOURCE_DB_USER="root"
# 源数据库密码
SOURCE_DB_PASSWORD="xxx"
# 源数据库名称
SOURCE_DB_NAME="db1"

# 需要备份的表列表
TABLES=(
    "tb1"
    "tb2"
    "tb3"
    "tb4"
)

# 本地备份文件存储路径
BACKUP_FILE="/home/app/data-backup/db1.sql"

# 远程服务器登录用户名
REMOTE_USER="root"
# 远程服务器 SSH 端口号
REMOTE_PORT="22"
# 远程服务器 IP 地址
REMOTE_HOST="192.168.2.222"
# 远程服务器备份文件存放目录
REMOTE_PATH="/home/app/data-backup"

# 日志文件路径
LOG_FILE="/home/app/data-backup/out.log"

# 记录日志函数
# 参数: $1 - 日志消息内容
log() {
    local message="$1"
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $message" | tee -a "$LOG_FILE"
}

# 生成备份文件
log "开始备份数据库..."

# 使用 mysqldump 备份指定表
# --single-transaction: 在备份开始时启动一个事务，确保备份数据一致性（适用于 InnoDB 引擎）
mysqldump \
    -h"${SOURCE_DB_HOST}" \
    -P"${SOURCE_DB_PORT}" \
    -u"${SOURCE_DB_USER}" \
    -p"${SOURCE_DB_PASSWORD}" \
    "${SOURCE_DB_NAME}" \
    "${TABLES[@]}" \
    --single-transaction > "${BACKUP_FILE}"

# 检查备份是否成功
if [ $? -eq 0 ]; then
    # 备份成功，开始上传文件
    log "备份成功，开始上传文件..."

    # 使用 scp 上传备份文件到远程服务器
    scp -P "${REMOTE_PORT}" "${BACKUP_FILE}" "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}"

    # 检查上传是否成功
    if [ $? -eq 0 ]; then
        # 文件上传成功
        log "文件上传成功"
    else
        # 文件上传失败
        log "文件上传失败"
    fi
else
    # 备份失败
    log "备份失败"
fi

解析：

1. 定义变量：脚本中定义了数据库用户、密码、数据库名称、表名列表、备份文件名、远程用户、远程主机和远程路径。
2. 生成备份文件：使用 mysqldump 命令生成备份文件，并将指定的表导出到 BACKUP_FILE 文件中。
3. 检查备份是否成功：通过检查 mysqldump 命令的返回值来判断备份是否成功。
4. 上传备份文件：如果备份成功，则使用 scp 命令将备份文件上传到远程服务器的指定路径。
5. 检查上传是否成功：通过检查 scp 命令的返回值来判断文件上传是否成功。

2.生成ssh密钥

由于scp需要交互输入密码才能传输，所以下面使用SSH 密钥认证方式

在A机器上执行以下命令生成密钥

ssh-keygen -t rsa

三次回车

密钥会生成在/root/.ssh/目录下（以下都是以root用户操作的所以家目录就是/root)

将A机器上的公钥id_rsa.pub复制到B机器/root/.ssh目录

在B机器执行以下命令将公钥追加到authorized_keys文件

cat id_rsa.pub >> ~/.ssh/authorized_keys

如果提示没有该文件，就在家目录建一个.ssh目录

mkdir -p ~/.ssh

或者

cat ~/.ssh/id_rsa.pub | ssh -p 22 root@192.168.2.222 "mkdir -p ~/.ssh && cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys"

3. 手动执行

上面设置好之后可以手动执行脚本看是否成功
在A机器执行

赋予脚本执行权限

chmod +x backupAndUpload.sh

./backupAndUpload.sh

4.设置定时执行

当然还是在A机器执行，输入下面命令进入crontab编辑

crontab -e

最后一行加入以下内容，每日凌晨01:20执行一次
（写给小白：其实就是vi编辑器 按i键输入后在按ESC键，最后后输入:wq保存）

20 1 * * * /home/app/backupAndUpload.sh

最后用下面命令查询是否保存成功

crontab -l

然后就坐等自动备份了