README.md

mysql-backup-s3.sh

A tool to create mysql server backups and optionally compress them and save them on a remote S3 server.

It is built on top of mysqldump, gzip and aws-shell.

Run with -h for for help / usage information:

sh mysql-backup-s3.sh -h

Options

CLI options

The -h option displays a help message, which briefly describes usage.

The -v option will output extra information.

MySQL username and password

The -u option, which requires the mysql username parameter, allows to set the mysql user to use to connect to the database server. Default to root.

The -p option, which requires the mysql password parameter, allows to set the mysql password to connect to the database server. Is empty (unused) by default.

Split databases in separate files

The -s option can be used to save a different file for every different databases of the server.

If this option is not set, the file will be saved as mysql.sql. If it is set it will be multiple files instead, having the name of the database (dbname.sql).

Zip Compression

The -z option can be used to enabled gzip compression on the files. The resulting filenames will be mysql.sql.gz or dbname.sql.gz.

The gzip tool needs to be installed on the machine for compression to work.

Local path

The -l option can be used to specify a local path to create a local backup.

Remote path (S3)

The -r option can be used to specify a bucket/folder (on Amazon S3) to create a remote backup.

This feature uses the aws-shell utility (and its aws binary).

Install this tool:

pip install aws-shell

Configure your account with:

aws configure

Example

In your crontab, add the following line to run a full daily backup to S3 of your database:

# Run every 4 hours
0 0,4,8,12,16,20 * * * /bin/bash /root/tools/mysql-backup-s3.sh -s -z -r s3bucket/mysql/ -u backups -p password

mysql-backup-s3.sh

#!/bin/bash

# Initial terminal setup
if test -t 1; then

    # see if it supports colors...
    colorsSupport=$(tput colors)

    if test -n "$colorsSupport" && test $colorsSupport -ge 8; then
        bold="$(tput bold)"
        underline="$(tput smul)"
        standout="$(tput smso)"
        normal="$(tput sgr0)"
        black="$(tput setaf 0)"
        red="$(tput setaf 1)"
        green="$(tput setaf 2)"
        yellow="$(tput setaf 3)"
        blue="$(tput setaf 4)"
        magenta="$(tput setaf 5)"
        cyan="$(tput setaf 6)"
        white="$(tput setaf 7)"
    fi
fi

# Help
usage="
${underline}Backup a MySQL server to a local and optionally save to a remote S3 bucket${normal}

${standout}Usage:${normal}

$(basename "$0") [-h] [-v] [-u username] [-p password] [-l path] [-r bucket] [-s] [-z]

${standout}Options:${normal}

  ${bold}-h${normal}  Display this help message
  ${bold}-v${normal}  Vebose (display useful extra informations)

  ${bold}-u${normal}  MySQL username (default: root)
  ${bold}-p${normal}  MySQL password (default: \"\")

  ${bold}-i${normal}  Included databases list, comma-separated. CURRENTLY UNUSED.
  ${bold}-e${normal}  Excluded databases list, comma-separated. CURRENTLY UNUSED.

  ${bold}-l${normal}  Set the local backup path to create a local backup.
  ${bold}-r${normal}  Set the remote S3 bucket / path to upload to a remote backup.

  ${bold}-s${normal}  Split the table into multiple files for each databases.
  ${bold}-z${normal}  Compress (gzip) the file.
"

# Default configuration options
mysqlUsername="root"
mysqlPassword=""
localPath=""
remotePath=""
splitFiles=false
zipFiles=false
verbose=false

# Bin
mysqlCmd=`which mysql`
mysqldumpCmd=`which mysqldump`
awsCmd=`which aws`
gzipCmd=`which gzip`

# Timestamp
ts=`date +%Y-%m-%d-%H-%M-%S`


# Parse the options
while getopts 'hvu:p:i:e:l:r:sz' option; do
  case "$option" in
    h) printf "${usage}";exit;;
    v) verbose=true;;
    u) mysqlUsername="${OPTARG}";;
    i) includedDatabases="${OPTARG}";;
    e) excludedDatabases="${OPTARG}";;
    p) mysqlPassword="${OPTARG}";;
    l) localPath="${OPTARG}";;
    r) remotePath="${OPTARG}";;
    s) splitFiles=true;;
    z) zipFiles=true;;
  esac
done

# The mysql commands w
if [ "$mysqlPassword" != "" ]; then
  passwordPrompt="-p${mysqlPassword}"
else
  passwordPrompt=""
fi

# Nothing to do?
if [ "$localPath" == "" ] && [ "$remotePath" == "" ]; then
  printf "${red}Nothing to do: no local path and no remote location specified.${normal}\n"
  exit 1;
fi


if [ "$localPath" != "" ]; then
  # Create local path, if needed
  localPath="${localPath}/${ts}"
  mkdir -p ${localPath}
fi

# Ensure remote path starts with s3:// URL
if [ "$remotePath" != "" ]; then
  if [ ! "$remotePath" =~ "s3://*" ]; then
    remotePath="s3://${remotePath}/${ts}"
  fi
  if [ "$localPath" == "" ]; then
    localPath="/tmp"
    deleteLocal=true
  fi
fi

# Output extra information
if [ "$verbose" == true ]; then
  splitStr=$([ "$splitFiles" == true ] && printf "${green}Yes${normal}" || printf "${red}No${normal}")
  zipStr=$([ "$zipFiles" == true ] && printf "${green}Yes${normal}" || printf "${red}No${normal}")
  remotePathStr=$([ "$remotePath" != "" ] && printf "$remotePath" || printf "${red}No${normal}")
  printf "
  ${standout}Summary of the operations:${normal}
  Split databases into files?....${bold}${splitStr}${normal}
  Compress backup files?.........${bold}${zipStr}${normal}
  Local path.....................${bold}${localPath}${normal}
  Remote S3 path.................${bold}${remotePathStr}${normal}
  "
fi

# Run the script (dump)
if [ "$splitFiles" != false ]; then
  databases=`${mysqlCmd} --user=${mysqlUsername} ${passwordPrompt} -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
  for db in $databases; do
    if [ "$zipFiles" != false ]; then
      filename="${localPath}/${db}.sql.gz"
      ${mysqldumpCmd} --force --opt --user=${mysqlUsername} ${passwordPrompt} --databases $db | "${gzipCmd}" > "${filename}"
    else
      filename="${localPath}/${db}.sql"
      ${mysqldumpCmd} --force --opt --user=${mysqlUsername} ${passwordPrompt} --databases $db  > "${filename}"
    fi
    if [ "$verbose" == true ]; then
      printf "${green}Database \"${db}\" backup saved to \"${filename}\".${normal}\n"
    fi
    if [ "$remotePath" != "" ]; then
      if [ "$verbose" == true ]; then
        printf "${yellow}Uploading $(basename "$filename") to S3...${normal}\n"
      fi
      ${awsCmd} s3 cp ${filename} ${remotePath}/$ts/
      if [ "$deleteLocal" == true ]; then
        printf "${yellow}Deleting local file...${normal}\n"
        rm $filename
      fi
    fi
  done
else
  if [ "$zipFiles" != false ]; then
    filename="${localPath}/mysql.sql.gz"
    ${mysqldumpCmd} --all-databases --user=${mysqlUsername} ${passwordPrompt} | "${gzipCmd}" > "${filename}"
  else
    filename="${localPath}/mysql.sql"
    ${mysqldumpCmd} --all-databases --user=${mysqlUsername} ${passwordPrompt} > "${filename}"
  fi
  if [ "$verbose" == true ]; then
    printf "${green}Full database backup saved to \"${filename}\".${normal}\n"
  fi
  if [ "$remotePath" != "" ]; then
    if [ "$verbose" == true ]; then
      printf "${yellow}Uploading $(basename "$filename") to S3...${normal}\n"
    fi
    ${awsCmd} s3 cp ${filename} ${remotePath}/$ts/
    if [ "$deleteLocal" == true ]; then
      printf "${yellow}Deleting local file...${normal}\n"
      rm $filename
    fi
  fi
fi

printf "\n"