Basic Database Backup Script

backupdb.sh

#!/bin/bash

# This script backs-up a MySQL or MariaDB database, compresses it and uploads it to Amazon S3.
#
# MySQL 5.6+:
# Use `mysql_config_editor` to store authentication credentials in an encrypted login path file named .mylogin.cnf.
# `mysql_config_editor set --login-path=local --host=localhost --user=USERNAME --password`
#
# MariaDB and MySQL <= 5.5:
# Uncomment the following variable declarations and add the appropriate database credentials.
# DATABASE_USER=""
# DATABASE_PASSWORD=""
#
# S3 Setup:
# 1) Create a new IAM user and securely store the access credentials.
# 2) Install AWS CLI: `sudo pip install awscli`
# 3) Run AWS config: `aws configure` -> enter key ID and secret, region is probably us-east-1
# 4) Create a new S3 bucket.
# 5) Select the new user -> permissions -> inline policy -> create -> policy generator
# 6) Allow, Amazon S3, All Actions, ARN format: `arn:aws:s3:::bucket-name/*`
# 7) Go to AWS Console -> bucket -> Management -> Lifecycle -> Add lifecycle rule
# 8) Set the expiration for the current version of objects to an appropriate retention period
#
# Now just save this script as an executable in `/usr/local/bin` and schedule a cron job with `crontab -e`
# Sample crontab task: `0 0 * * * /usr/local/bin/backupdb.sh | mail -s "DB Backup Result" user@example.com`

DATABASE_NAME=""
S3_BUCKET_NAME=""

FILE=/tmp/`date +"%Y-%m-%d"`.${DATABASE_NAME}.sql.gz
if [[ -v DATABASE_USER && -v DATABASE_PASSWORD ]]; then
    mysqldump -u ${DATABASE_USER} -p"${DATABASE_PASSWORD}" ${DATABASE_NAME} | gzip -c > ${FILE} && /usr/local/bin/aws s3 cp ${FILE} s3://${S3_BUCKET_NAME}/
else
    mysqldump --login-path=local ${DATABASE_NAME} | gzip -c > ${FILE} && /usr/local/bin/aws s3 cp ${FILE} s3://${S3_BUCKET_NAME}/
fi