Ubuntu或Linux系统定时发送数据库备份邮件脚本配置。

1. 安装msmtp

安装 msmtp
sudo apt-get update
sudo apt-get install -y msmtp msmtp-mta

2. 创建配置文件sudo nano /etc/msmtprc （重要提示：脚本里面不要有任何备注，以下配置需要在邮箱后台开放SMTP权限 并获取授权码:MDe4sdXSEL3LR）

defaults
tls           on
tls_trust_file /etc/ssl/certs/ca-certificates.crt
logfile       /var/log/msmtp.log

account 163
host    smtp.163.com
port    465
auth    on
user    wdlzhy@163.com
password MDe4sdXSEL3LR
from    wdlzhy@163.com

 3. 

测试发送命令

printf 'From: 备份脚本 <wdlzhy@163.com>\nSubject: ry_vue 备份完成\n\n数据库 ry_vue 已成功备份。\n' \
| msmtp -t 112050107@qq.com

4. 查看日志确认

sudo tail -n 20 /var/log/msmtp.log

以上是测试部分讲解，下面是正式脚本编写：

1. 创建脚本文件

sudo nano /usr/local/bin/ry_vue_backup.sh

sudo chmod +x /usr/local/bin/ry_vue_backup.sh

#!/usr/bin/env bash
set -euo pipefail

CONTAINER_NAME="mysql-db"
BACKUP_DIR="/media/jinsuona/sda2_data/mysql_backups"
MYSQL_USER="jsnbakuser"
MYSQL_PASS="JinSa!951"

LOGFILE="/var/log/ry_vue_backup.log"    # Backup log file
TS=$(date +"%Y%m%d_%H%M%S")            # Timestamp
BACKUP_FILE="$BACKUP_DIR/ry_vue_backup_${TS}.sql.gz"
RECIPIENT="112050107@qq.com"    # Replace with your email

# 2. Ensure backup directory exists
mkdir -p "$BACKUP_DIR"

# 3. Perform mysqldump and gzip
{
  echo "[$(date +"%Y-%m-%d %H:%M:%S")] Starting backup..."
        docker exec "$CONTAINER_NAME" \
          sh -c "exec mysqldump --single-transaction --quick --no-tablespaces ry_vue -u$MYSQL_USER -p$MYSQL_PASS" \
          | gzip > "$BACKUP_FILE"
  echo "[$(date +"%Y-%m-%d %H:%M:%S")] Backup saved to $BACKUP_FILE"
} >> "$LOGFILE" 2>&1

# 4. Send backup file as email attachment via msmtp
# Construct MIME email
BOUNDARY="====$(date +%s)===="
EMAIL_SUBJECT="ry_vue Backup ${TS}"
{
    printf 'From: Backup Script <wdlzhy@163.com>\n'
    printf 'To: %s\n' "$RECIPIENT"
    printf 'Subject: %s\n' "$EMAIL_SUBJECT"
    printf 'MIME-Version: 1.0\n'
    printf 'Content-Type: multipart/mixed; boundary="%s"\n' "$BOUNDARY"
    printf '\n'
    printf '%s\n' "--$BOUNDARY"
    printf 'Content-Type: text/plain; charset="utf-8"\n'
    printf '\n'
    printf 'Database ry_vue has been backed up successfully.\nBackup file attached: %s\n' "$(basename "$BACKUP_FILE")"
    printf '\n'
    printf '%s\n' "--$BOUNDARY"
    printf 'Content-Type: application/gzip; name="%s"\n' "$(basename "$BACKUP_FILE")"
    printf 'Content-Transfer-Encoding: base64\n'
    printf 'Content-Disposition: attachment; filename="%s"\n' "$(basename "$BACKUP_FILE")"
    printf '\n'
    base64 "$BACKUP_FILE"
    printf '\n'
    printf '%s--\n' "--$BOUNDARY"
} | msmtp -t

# 5. Clean old backups (keep 7 days)
find "$BACKUP_DIR" -type f -name "ry_vue_backup_*.sql.gz" -mtime +7 -delete

# 4. Send notification email via msmtp
# Prepare email headers and body
# EMAIL_SUBJECT="ry_vue Backup Completed: $TS"
# EMAIL_BODY="Database ry_vue has been backed up successfully.\nBackup file: $(basename "$BACKUP_FILE")"

# printf 'From: Backup Script <wdlzhy@163.com>\nTo: wdlzhy@163.com\nSubject: %s\n\n%s\n' \
#  "$EMAIL_SUBJECT" "$EMAIL_BODY" \
#  | msmtp -t wdlzhy@163.com

# 5. Clean old backups (keep 7 days)
# find "$BACKUP_DIR" -type f -name "ry_vue_backup_*.sql.gz" -mtime +7 -delete

2. 上面的脚本中用到的数据库用户和密码，需要通过下面的方式先创建

mysql -h127.0.0.1 -uroot -p
CREATE USER 'jsnbakuser'@'%' IDENTIFIED BY 'JinSa!951';
GRANT SELECT, LOCK TABLES, SHOW VIEW ON ry_vue.* TO 'jsnbakuser'@'%';
FLUSH PRIVILEGES;

3. 

配置 crontab（每小时在整点执行）
sudo crontab -e
在打开的编辑器末尾添加（以下配置为每隔1小时发送一次邮件）
0 * * * * /usr/local/bin/ry_vue_backup.sh >> /var/log/ry_vue_backup.log 2>&1