mysql-backup-s3.sh

db_backup.sh

#!/bin/bash

# Replace the placeholders with your MySQL server details
MYSQL_HOST="localhost"
MYSQL_USER="xxx"
MYSQL_PASSWORD="xxx"
DATABASE_NAME="xxx"

# Set your Amazon S3 bucket name
S3_BUCKET="xxx"
AWS_PROFILE="xxx"

# ----------------------------------------

# ref: https://simplebackups.com/blog/the-ultimate-mysql-database-backup-script

# Set the backup directory with the current date as the subfolder name
DIR=$(date +%Y-%m-%d_%H%M)
DEST=~/db_backups/$DIR
DEST_FULLPATH=$DEST/$DATABASE_NAME.sql
mkdir -p $DEST

# Use mysqldump to create a SQL backup file
mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE_NAME > $DEST_FULLPATH

# Upload the backup file to Amazon S3
aws --profile $AWS_PROFILE sts get-caller-identity
aws --profile $AWS_PROFILE s3 cp $DEST_FULLPATH s3://$S3_BUCKET/$DIR/$DATABASE_NAME.sql

# Remove local backups older than 3 days
find ~/db_backups -type d -mtime +3 -exec rm -rf {} \;