Database backup script

backup.sh

#!/bin/bash
# Based on https://gist.github.com/2206527
echo -e "Backup MySQL Databases to S3"

# Basic variables
host="localhost"
mysqlpass="rootpass"
bucket="s3://bucketname"
accesskey="awsaccesskey"
secretkey="awssecretkey"

# Timestamp (sortable AND readable)
stamp=`date +"%Y-%m-%dT%H:%M:%SZ"`

# List all the databases
databases=`docker exec mariadb mysql -u root -p$mysqlpass -h "$host" -e "SHOW DATABASES;" | tr -d "| " | grep -v "\(Database\|information_schema\|performance_schema\|mysql\|test\)"`

# Feedback
echo -e "Dumping to $bucket/$stamp/"

# Loop the databases
for db in $databases; do

  # Define our filenames
  filename="$db.sql.gz"
  tmpfile="/tmp/$filename"
  object="$bucket/$stamp/$filename"

  # Feedback
  echo -e "Begin with backup for $db"

  # Dump and zip
  echo -e "  creating $tmpfile"
  docker exec mariadb mysqldump -u root -p$mysqlpass -h "$host" --force --opt --databases "$db" | gzip -c > "$tmpfile"

  # Upload
  echo -e "  uploading..."
  s3cmd --access_key="$accesskey" --secret_key="$secretkey" put "$tmpfile" "$object"

  # Delete
  rm -f "$tmpfile"

done;

echo -e "Successfully completed backup"