高级MySQL数据库备份脚本

最新推荐文章于 2024-08-28 19:48:03 发布

徐州蔡徐坤

最新推荐文章于 2024-08-28 19:48:03 发布

阅读量651

点赞数 17

分类专栏： MySQL 文章标签：数据库 mysql adb

本文链接：https://blog.csdn.net/qq_29864051/article/details/141624970

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

高级MySQL数据库备份脚本

主要功能
项目构成
SFTP备份配置：
邮件发送配置
如何执行备份
- 执行备份脚本
- 计划每日的定时任务
执行效果

主要功能

指定数据库连接中的数据库进行备份
可以将备份文件通过FTP或者SFTP传输到其他服务器进行异地备份
指定备份文件保留天数
邮件发送备份信息

项目构成

在这里插入图片描述

凭证文件：credentials.txt，用于存储mysql连接的账号密码
执行脚本：mysql-dump.sh，使用mysql的dump备份数据库
配置文件：settings.conf，配置mysql备份的ip，端口，目录，策略等

credentials.txt

# 使用凭证文件是为了避免执行时的警告信息,mysqldump: [Warning] Using a password on the command line interface can be insecure.
[client]
user=root
password=密码

settings.conf

# 该脚本为备份配置文件，根据需求更新配置
# 本脚本需要运行在mysql数据库运行所在的linux服务器上

#################### MySQL 数据库服务器配置 #####################

# MySQL 数据库服务器地址和端口
MYSQL_HOST="192.168.125.36" 
MYSQL_PORT="3306" 

# 保存MySQL账号密码的文件路径
CREDENTIAL_FILE="/mnt/sdb/credentials.txt" 

# 要备份的数据库名称使用空格分隔，输入"ALL"备份全部
DB_NAMES="test111" 

# 仅备份指定的数据库
# DB_NAMES="mydb testdb dbname2" 

# MySQL 数据库备份保留策略，选择本地备份副本保留的天数
BACKUP_RETAIN_DAYS=30

# 本地数据库备份文件存储路径
LOCAL_BACKUP_DIR="/mnt/sdb/dbbackup" 

# 日志文件存储路径
LOG_PATH="/mnt/sdb/logs"

# 临时文件路径
TMP_PATH="/tmp" 

###################### 启用 SFTP 备份 ############################

# 0=禁用, 1=启用
SFTP_ENABLE=1 
# SFTP 服务器地址host
SFTP_HOST="192.168.154.26" 
# SFTP/SSH 用户名
SFTP_USERNAME="root" 
# SFTP 服务器端口
SFTP_PORT="22" 
# SFTP 上传目录
SFTP_UPLOAD_DIR="/mnt/sdb1/test" 
# SCP 执行路径
SCP="/usr/bin/scp" 

###################### 启用邮件提醒 ##############################

# 0=不发送邮件, 1=发送邮件
SENDEMAIL=1  
# 接收邮件提醒的邮箱地址
EMAILTO='1254125@qq.com' 

###################### 启用 FTP 备份 ##############################

# 0=禁用, 1=启用
FTP_ENABLE=0 
# FTP 服务器地址
FTP_SERVER="ftp.tecadmin.net" 
# FTP 用户名
FTP_USERNAME="ftp 用户名" 
# FTP 密码
FTP_PASSWORD="密码" 
# FTP 上传目录
FTP_UPLOAD_DIR="/backup/dbbackup/" 

###################### 本地可执行文件路径 ##########################

GZIP="/bin/gzip" 
MYSQL="/usr/bin/mysql" 
MYSQLDUMP="/usr/bin/mysqldump"  
MKDIR="/bin/mkdir" 
MYSQLADMIN="/usr/bin/mysqladmin" 
GREP="/bin/grep"

mysql-dump.sh

#!/usr/bin/env bash

# 获取当前脚本的目录
SCRIPT_PATH="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
CONFIGFILE=$SCRIPT_PATH/settings.conf

source $CONFIGFILE
DATE_FORMAT='%Y%m%d'
CURRENT_DATE=$(date +"${DATE_FORMAT}")
CURRENT_TIME=$(date +"%H%M")
LOGFILENAME=$LOG_PATH/mydumpadmin-${CURRENT_DATE}-${CURRENT_TIME}.log
CREDENTIALS="--defaults-file=$CREDENTIAL_FILE"

# 如果日志路径不存在，创建该路径
[ ! -d $LOG_PATH ] && ${MKDIR} -p ${LOG_PATH}
echo "数据库备份报告——$(date +"%Y-%m-%d %H:%M:%S")" >> ${LOGFILENAME}
echo "" >> ${LOGFILENAME}


### 创建备份 ###
check_config() {
    ### 检查配置文件是否存在
    [ ! -f $CONFIGFILE ] && close_on_error "找不到配置文件，请确保配置文件正确"
}

db_backup() {
    ### 开始数据库备份
    if [ "$DB_NAMES" == "ALL" ]; then
        DATABASES=`$MYSQL $CREDENTIALS -h $MYSQL_HOST -P $MYSQL_PORT -Bse 'show databases' | grep -Ev "^(Database|mysql|performance_schema|information_schema)$"`
    else
        DATABASES=$DB_NAMES
    fi

    db=""
    # 如果备份路径不存在，创建该路径
    [ ! -d $BACKUPDIR ] && ${MKDIR} -p $BACKUPDIR
    echo "*** 正在导出 MySQL 数据库 ***"
    mkdir -p ${LOCAL_BACKUP_DIR}/${CURRENT_DATE}
    for db in $DATABASES; do
        FILE_NAME="${db}.${CURRENT_DATE}-${CURRENT_TIME}.sql.gz"
        FILE_PATH="${LOCAL_BACKUP_DIR}/${CURRENT_DATE}/"
        FILENAMEPATH="$FILE_PATH$FILE_NAME"
        echo -en "*** 数据库 > $db... ***\n"
        ${MYSQLDUMP} ${CREDENTIALS} --single-transaction -h ${MYSQL_HOST} -P $MYSQL_PORT $db | ${GZIP} -9 > $FILENAMEPATH
		
		 # 获取数据库备份文件大小
        DB_SIZE=$(du -sh ${FILENAMEPATH} | awk '{print $1}')
		
		echo "数据库名称：$db" >> ${LOGFILENAME}
        echo "数据库大小：$DB_SIZE" >> ${LOGFILENAME}
        echo "备份文件名：$FILE_NAME" >> ${LOGFILENAME}
		
        [ $FTP_ENABLE -eq 1 ] && ftp_backup
        [ $SFTP_ENABLE -eq 1 ] && sftp_backup
    done
    echo "*** 备份完成 ***"
    echo "*** 请检查 ${FILE_PATH} 中的备份文件 ***"
}

### 根据需求输出错误信息并退出 ###
close_on_error() {
    echo "$@"
    exit 99
}

### 确保可执行文件存在，否则退出 ###
check_cmds() {
    [ ! -x $GZIP ] && close_on_error "文件 $GZIP 不存在，请确保 $CONFIGFILE 中的路径正确。"
    [ ! -x $MYSQL ] && close_on_error "文件 $MYSQL 不存在，请确保 $CONFIGFILE 中的路径正确。"
    [ ! -x $MYSQLDUMP ] && close_on_error "文件 $MYSQLDUMP 不存在，请确保 $CONFIGFILE 中的路径正确。"
    [ ! -x $RM ] && close_on_error "文件 $RM 不存在，请确保 $CONFIGFILE 中的路径正确。"
    [ ! -x $MKDIR ] && close_on_error "文件 $MKDIR 不存在，请确保 $CONFIGFILE 中的路径正确。"
    [ ! -x $MYSQLADMIN ] && close_on_error "文件 $MYSQLADMIN 不存在，请确保 $CONFIGFILE 中的路径正确。"
    [ ! -x $GREP ] && close_on_error "文件 $GREP 不存在，请确保 $CONFIGFILE 中的路径正确。"
    if [ $SFTP_ENABLE -eq 1 ]; then
        [ ! -x $SCP ] && close_on_error "文件 $SCP 不存在，请确保 $CONFIGFILE 中的路径正确。"
    fi
}

### 检查数据库连接是否正常 ###
check_mysql_connection() {
    ${MYSQLADMIN} ${CREDENTIALS} -h ${MYSQL_HOST} -P ${MYSQL_PORT} ping | ${GREP} 'alive' > /dev/null
    [ $? -eq 0 ] || close_on_error "错误：无法连接到 MySQL 服务器。请确保 $CONFIGFILE 中的用户名和密码设置正确。"
}

### 将备份文件上传到 FTP 服务器 ###
ftp_backup() {
    echo "*** 正在将备份文件上传到 FTP ***"
    ftp -n $FTP_SERVER <<- EndFTP
        user "$FTP_USERNAME" "$FTP_PASSWORD"
        binary
        hash
        cd $FTP_UPLOAD_DIR
        lcd $FILE_PATH
        put "$FILE_NAME"
        bye
EndFTP
}


### 将备份文件上传到 SFTP 服务器 ###
sftp_backup() {
    echo "*** 正在将备份文件上传到 SFTP... ***"
    cd ${FILE_PATH}
    ${SCP} -P ${SFTP_PORT} "$FILE_NAME" ${SFTP_USERNAME}@${SFTP_HOST}:${SFTP_UPLOAD_DIR}/
	echo "异地备份传输服务器：${SFTP_HOST}" >> ${LOGFILENAME}
}

### 删除旧备份 ###
clean_old_backups() {
    echo "*** 检查是否存在需要删除旧备份... ***"
    DBDELDATE=`date +"${DATE_FORMAT}" --date="${BACKUP_RETAIN_DAYS} days ago"`
    
    if [ ! -z ${LOCAL_BACKUP_DIR} ]; then
        cd ${LOCAL_BACKUP_DIR}
        if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
            echo "***正在删除旧备份：${DBDELDATE}***"
			echo "删除旧备份：${DBDELDATE}" >> ${LOGFILENAME}
            rm -rf ${DBDELDATE}
        fi
    fi
	echo "*** 检查完毕 ***"
}

### 发送备份报告邮件 ###
send_report() {
    if [ $SENDEMAIL -eq 1 ]; then
        cat ${LOGFILENAME} | mail -vs "数据库备份报告 $(date +"%Y-%m-%d")" ${EMAILTO}
    fi
}

### 主流程 ###
check_config
check_cmds
check_mysql_connection
db_backup
clean_old_backups
send_report

SFTP备份配置：

设置 SFTP 无密码登录（SSH 密钥认证）

生成 SSH 密钥对

首先，需要在备份服务器上生成 SSH 密钥对：

在这里插入图片描述

ssh-keygen -t rsa

执行此命令后，密钥对将默认存储在 ~/.ssh/ 目录下。生成的文件包括：

id_rsa（私钥）
id_rsa.pub（公钥）

默认情况下，系统会提示选择是否设置密码短语。为了实现无密码登录，直接按下回车跳过此步骤

将公钥复制到 SFTP 服务器

在这里插入图片描述

生成密钥对后，需要将公钥复制到目标 SFTP 服务器，以便启用无密码登录。可以通过以下命令将公钥复制到远程服务器：

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.0.1

这条命令会将公钥添加到远程服务器的 ~/.ssh/authorized_keys 文件中。之后，备份服务器将可以无密码登录至远程 SFTP 服务器

测试无密码登录

完成上述步骤后，使用以下命令测试是否成功实现无密码登录：

ssh root@192.168.0.1

如果能够直接登录而不需要输入密码，则说明配置成功

通过此方式，备份脚本将能够全自动化执行 SFTP 文件传输，不再需要手动输入密码

邮件发送配置

安装插件sendmail、mailx

heyum install sendmail maix -y

修改mail配置

vim /etc/mail.rc,文件最后添加：

set from=邮箱@qq.com
set smtp=smtps://smtp.qq.com:465
set smtp-auth-user=邮箱@qq.com
set smtp-auth-password=授权码
set smtp-auth=login
set ssl-verify=ignore
set nss-config-dir=/etc/pki/nssdb

获取邮箱授权码

如何获取邮箱授权码，其他邮箱获取授权码方式大同小异

在这里插入图片描述

重启邮件服务

systemctl restart sendmail

如何执行备份

执行备份脚本

cd /script_path
# 赋予执行权限
chmod a+x mysql-dump.sh
# 手动执行一次备份脚本
./mysql-dump.sh

计划每日的定时任务

直接执行下面命令，将设置添加到 crontab，以便在每天凌晨 2:00 运行

(crontab -l ; echo "0 9 * * * cd /script_path && ./mysql-dump.sh") | crontab -

script_path替换为脚本路径

使用crontab -l可以查看定时任务列表

执行效果

生成备份文件路径和备份日志路径

在这里插入图片描述

邮件发送提醒

在这里插入图片描述

徐州蔡徐坤

关注

17
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
0
评论
高级MySQL数据库备份脚本

指定数据库连接中的数据库进行备份可以将备份文件通过FTP或者SFTP传输到其他服务器进行异地备份指定备份文件保留天数邮件发送备份信息
复制链接

扫一扫

专栏目录