Last active
October 22, 2019 07:55
-
-
Save ioagel/d3d5687bac6fa964db24d868d3189d25 to your computer and use it in GitHub Desktop.
Backup script for mysql/mariadb databases with optional encryption optimized for docker containers.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env bash | |
| # Author: Ioannis Angelakopoulos <[email protected]> | |
| # Date: 18/01/2019 | |
| ########################### | |
| # REQUIREMENTS | |
| ########################### | |
| # | |
| # * Required commands: | |
| # + mysqldump | |
| # + mysql | |
| # + bzip2 or gzip or xz # for compression | |
| # + openssl # for encryption | |
| # + msmtp # for mail functionality | |
| # | |
| ########################### | |
| # USAGE | |
| ########################### | |
| # * Set correct values for below variables: | |
| # | |
| # MYSQL_HOST REQUIRED | |
| # MYSQL_PASSWD REQUIRED | |
| # BACKUP_ROOTDIR default = /backup (if using the script in a docker container do not change this) | |
| # MYSQL_PORT default = 3306 | |
| # MYSQL_PROTO default = TCP | |
| # MYSQL_USER default = root | |
| # MYSQL_OPTIONS default = --single-transaction | |
| # DATABASES default = all (or 'db1 db2 db3' multiple databases separated by SPACES) | |
| # COMPRESS default = YES | |
| # CMD_COMPRESS default = bzip2 -9 | |
| # | |
| # SLEEP_SCHEDULE default = 0 (run once and exit) if you want to use SLEEP, supports docker and as standalone script | |
| # OR | |
| # CRON_SCHEDULE default = 0 (run once and exit) if you want to use CRON in DOCKER image: ioagel/mysql-backup:[alpine|5|8] | |
| # | |
| # ENC default = NO | |
| # CERT_LOC default = /run/secrets/mysql_backup_cert | |
| # | |
| # MAILTO REQUIRED for the mail functionality | |
| # MSMTPRC default = /etc/msmtprc # where msmtp gets the mail settings | |
| # MSMTP_ACCOUNT_NAME default = sql-dump # account identity in msmtprc | |
| # | |
| # Backup script suitable to run as a command in a mysql docker container | |
| # to backup databases running in mysql/mariadb servers, containerized or not. | |
| # It is optimized for docker that's why ENV vars are preferred | |
| # instead of commandline args. | |
| # SUPPORTS Encryption of backups: just use ENV=YES and mount the certificate to default or other dir inside the container | |
| # | |
| # To use it check: | |
| # - DockerHub https://cloud.docker.com/repository/docker/ioagel/mysql-backup | |
| # - Github https://github.com/ioagel/mysql-backup | |
| # - Gist for encryption HOWTO: https://gist.github.com/ioagel/2432fabb8b128f0ea16cb0408310d050 | |
| # EXAMPLES: | |
| # | |
| # run once and exit - mysql-prod is the container running the db we want to backup | |
| # $ docker run --rm --link mysql-prod -e MYSQL_PASSWD=password -e MYSQL_HOST=mysql-prod ioagel/mysql-backup | |
| # | |
| # Use SLEEP | |
| # run as a daemon and backup every 1 hour | |
| # $ docker run -d --link mysql-prod -e MYSQL_PASSWD=password -e MYSQL_HOST=mysql-prod -e SLEEP_SCHEDULE=3600 -v db_backups:/backup ioagel/mysql-backup | |
| # or backup every 3h (10800 seconds) | |
| # $ docker run -d --link mysql-prod -e MYSQL_PASSWD=password -e MYSQL_HOST=mysql-prod -e SLEEP_SCHEDULE=10800 -v db_backups:/backup ioagel/mysql-backup | |
| # or backup every 3h (only supported in LINUX) | |
| # $ docker run -d --link mysql-prod -e MYSQL_PASSWD=password -e MYSQL_HOST=mysql-prod -e SLEEP_SCHEDULE=3h -v db_backups:/backup ioagel/mysql-backup | |
| # | |
| # OR | |
| # | |
| # Use CRON (only with docker image: ioagel/mysql-backup) | |
| # run as a daemon and backup every 1 hour | |
| # $ docker run -d --link mysql-prod -e MYSQL_PASSWD=password -e MYSQL_HOST=mysql-prod -e CRON_SCHEDULE='0 * * * *' -v db_backups:/backup ioagel/mysql-backup | |
| # or backup once every day at 12:00am | |
| # $ docker run -d --link mysql-prod -e MYSQL_PASSWD=password -e MYSQL_HOST=mysql-prod -e CRON_SCHEDULE='0 0 * * *' -v db_backups:/backup ioagel/mysql-backup | |
| # | |
| # Encryption | |
| # $ docker run --rm --link mysql-prod -e MYSQL_PASSWD=password -e MYSQL_HOST=mysql-prod -e ENC=YES -v $PWD/cert.pem:/run/secrets/mysql_backup_cert -v db_backups:/backup ioagel/mysql-backup | |
| # | |
| # backup remote docker container or standalone mysql/mariadb server | |
| # $ docker run --rm -e MYSQL_HOST=mysql.example.org -e MYSQL_PASSWD=password -v db_backups:/backup ioagel/mysql-backup | |
| # | |
| # run as a standalone script | |
| # $ MYSQL_PASSWD=password MYSQL_HOST=mysql.example.org bash mysql_backup.sh | |
| ######################################################### | |
| # Modify below variables to fit your need ---- | |
| ######################################################### | |
| # Where to store backup copies. | |
| BACKUP_ROOTDIR=${BACKUP_ROOTDIR:-/backup} # for docker, mount a volume here | |
| # if you want the name of the service in the name of the BACKUP_DIR | |
| SWARM_SERVICE=${SWARM_SERVICE} | |
| # Required | |
| MYSQL_HOST=${MYSQL_HOST} | |
| MYSQL_PASSWD=${MYSQL_PASSWD} | |
| MYSQL_PORT=${MYSQL_PORT:-3306} | |
| MYSQL_PROTO=${MYSQL_PROTO:-TCP} # either TCP or SOCKET | |
| MYSQL_USER=${MYSQL_USER:-root} | |
| # You can override it and provide any option you need | |
| MYSQL_OPTIONS=${MYSQL_OPTIONS:---single-transaction} | |
| # Databases we should backup. | |
| # Multiple databases MUST be separated by SPACE. | |
| # eg. 'db1 db2 db3' | |
| DATABASES=${DATABASES:---all-databases} | |
| # Compress plain SQL file: YES, NO. | |
| # If Encryption is chosen then this var is not considered, the file will be compressed and encrypted! | |
| COMPRESS=${COMPRESS:-YES} | |
| # Compression program: bzip2, gzip, xz | |
| CMD_COMPRESS=${CMD_COMPRESS:-bzip2 -9} | |
| # Encryption: YES or NO | |
| ENC=${ENC:-NO} | |
| # x509 certificate file in pem format | |
| # Mount in container or use secrets with swarm. | |
| # Secrets are mounted by default under: /run/secrets dir. | |
| CERT_LOC=${CERT_LOC:-/run/secrets/mysql_backup_cert} | |
| # Cipher | |
| CIPHER=${CIPHER:--aes-256-cbc} | |
| # Takes a NUMBER for time in seconds or | |
| # for LINUX ONLY: NUMBER[s|m|h|d] for seconds, minutes, hours and days respectively. | |
| # Default value is 0 which means run once and exit. | |
| SLEEP_SCHEDULE=${SLEEP_SCHEDULE:-0} | |
| # OR | |
| # Use Cron schedule like: '0 0 * * *' backup every day at 12am | |
| CRON_SCHEDULE=${CRON_SCHEDULE:-0} | |
| # MAIL Settings | |
| MAILTO=${MAILTO} | |
| MSMTPRC=${MSMTPRC:-/etc/msmtprc} | |
| MSMTP_ACCOUNT_NAME=${MSMTP_ACCOUNT_NAME:-sql-dump} | |
| ####### example of msmtprc ######## | |
| # defaults | |
| # auth on | |
| # tls on | |
| # tls_starttls on | |
| # tls_trust_file /etc/ssl/certs/ca-certificates.crt | |
| # logfile /var/log/msmtp.log | |
| # | |
| # account sql-dump [$MSMTP_ACCOUNT_NAME] | |
| # host smtp.example.org | |
| # port 587 | |
| # from [email protected] | |
| # user [email protected] | |
| # password my_secure_password | |
| ######################################################### | |
| # You do *NOT* need to modify below lines. | |
| ######################################################### | |
| PATH='/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/sbin' | |
| # Commands. | |
| CMD_DATE='/bin/date' | |
| CMD_DU='du -sh' | |
| CMD_MYSQLDUMP='mysqldump' | |
| CMD_MYSQL='mysql' | |
| CMD_MAIL='msmtp' | |
| # if we run inside docker | |
| DOCKER=${DOCKER:-NO} | |
| if [[ ${DATABASES} == 'all' ]]; then | |
| DATABASES='--all-databases' | |
| fi | |
| # declare some vars we will use later | |
| LOGFILE= | |
| TIMESTAMP= | |
| BACKUP_DIR= | |
| # handle docker stop signal properly | |
| cleanup() { | |
| echo "Shutting down ..." | |
| kill -s SIGTERM $! | |
| exit 0 | |
| } | |
| trap cleanup SIGINT SIGTERM | |
| # Verify MYSQL_HOST is set | |
| if [[ -z "$MYSQL_HOST" ]]; then | |
| echo "[ERROR] Environment variable MYSQL_HOST is required." 1>&2 | |
| exit 255 | |
| fi | |
| # Verify MYSQL_PASSWD is set | |
| if [[ -z "$MYSQL_PASSWD" ]]; then | |
| echo "[ERROR] Environment variable MYSQL_PASSWD is required." 1>&2 | |
| exit 255 | |
| fi | |
| # Verify compress program is either bzip2 or gzip or xz | |
| if [[ ! "$CMD_COMPRESS" =~ ^bzip2.*|^gzip.*|^xz.* ]]; then | |
| echo "[ERROR] Environment variable CMD_COMPRESS can either be 'bzip2', 'gzip' or 'xz' with/without options like 'bzip2 -9'." 1>&2 | |
| exit 255 | |
| fi | |
| # VERIFY ENC is either YES or NO | |
| if [[ ! "$ENC" =~ ^YES$|^NO$ ]]; then | |
| echo "[ERROR] Environment variable ENC can be either YES or NO." 1>&2 | |
| exit 255 | |
| fi | |
| # Verify openssl program is available if we are encrypting | |
| if [[ "$ENC" == "YES" ]]; then | |
| if [[ ! "$(openssl version 2>/dev/null)" ]]; then | |
| echo "[ERROR] For Encryption 'openssl' program is required." 1>&2 | |
| exit 255 | |
| fi | |
| if [[ ! -f "$CERT_LOC" ]]; then | |
| echo "[ERROR] For encryption you need to mount the certificate in: $CERT_LOC" 1>&2 | |
| echo "[ERROR] or your own location using the Environment variable CERT_LOC." 1>&2 | |
| exit 255 | |
| fi | |
| fi | |
| # Use this var to filter this warning from the logs. | |
| SUPPRESS_MYSQL_ERROR="Using a password on the command line interface can be insecure." | |
| CON_SETTINGS="-u $MYSQL_USER --password=$MYSQL_PASSWD --host=$MYSQL_HOST --port=$MYSQL_PORT --protocol=$MYSQL_PROTO" | |
| # Verify MySQL connection. | |
| ${CMD_MYSQL} ${CON_SETTINGS} -e "show databases" >/dev/null 2>&1 | |
| if [[ "$?" != '0' ]]; then | |
| echo "[ERROR] MySQL username or password or host or protocol is incorrect in file ${0}." 1>&2 | |
| echo "Please fix them first." 1>&2 | |
| exit 255 | |
| fi | |
| BACKUP_SUCCESS='YES' | |
| sleep_linux_regex='^[0-9]+[s|m|h|d]{0,1}$' | |
| sleep_unix_regex='^[0-9]+$' | |
| backup_db() | |
| { | |
| db="${1}" | |
| if [[ "$db" == '--all-databases' ]]; then | |
| output_sql="${BACKUP_DIR}/all-${TIMESTAMP}.sql" | |
| else | |
| output_sql="${BACKUP_DIR}/${db}-${TIMESTAMP}.sql" | |
| # Check whether database exists or not | |
| ${CMD_MYSQL} ${CON_SETTINGS} -e "use ${db}" 2>&1 | grep -v "${SUPPRESS_MYSQL_ERROR}" >> ${LOGFILE} | |
| [[ "${PIPESTATUS[0]}" != '0' ]] && return 1 | |
| fi | |
| if [[ "$ENC" == 'NO' ]]; then | |
| ${CMD_MYSQLDUMP} \ | |
| ${CON_SETTINGS} \ | |
| ${MYSQL_OPTIONS} \ | |
| "$db" > "$output_sql" 2>&1 | grep -v "${SUPPRESS_MYSQL_ERROR}" >> ${LOGFILE} | |
| if [[ "${PIPESTATUS[0]}" != '0' ]]; then | |
| echo "[ERROR] MYSQL_OPTIONS environment variable value: '${MYSQL_OPTIONS}' is INVALID!" | |
| rm -f "$output_sql" | |
| return 1 | |
| fi | |
| # Compress | |
| if [[ "${COMPRESS}" == 'YES' ]]; then | |
| ${CMD_COMPRESS} "${output_sql}" >> ${LOGFILE} | |
| fi | |
| else # we are encrypting | |
| if [[ "$CMD_COMPRESS" =~ ^bzip2.* ]]; then | |
| suffix='bz2' | |
| elif [[ "$CMD_COMPRESS" =~ ^gzip.* ]]; then | |
| suffix='gz' | |
| else | |
| suffix='xz' | |
| fi | |
| ${CMD_MYSQLDUMP} \ | |
| ${CON_SETTINGS} \ | |
| ${MYSQL_OPTIONS} \ | |
| "$db" 2>&1 | grep -v "${SUPPRESS_MYSQL_ERROR}" | ${CMD_COMPRESS} | \ | |
| openssl smime -encrypt -binary ${CIPHER} -out "$output_sql"."$suffix".enc -outform DER "$CERT_LOC" >> ${LOGFILE} | |
| if [[ "${PIPESTATUS[0]}" != '0' ]]; then | |
| echo "[ERROR] MYSQL_OPTIONS environment variable value: '${MYSQL_OPTIONS}' is INVALID!" | |
| rm -f "$output_sql"."$suffix".enc | |
| return 1 | |
| fi | |
| fi | |
| } | |
| execute_backup() { | |
| # Date. | |
| YEAR="$(${CMD_DATE} +%Y)" | |
| MONTH="$(${CMD_DATE} +%m)" | |
| DAY="$(${CMD_DATE} +%d)" | |
| TIME="$(${CMD_DATE} +%H.%M.%S)" | |
| TIMESTAMP="${YEAR}.${MONTH}.${DAY}.${TIME}" | |
| # Define, check, create directories. | |
| if [[ -z ${SWARM_SERVICE} ]]; then | |
| BACKUP_DIR="${BACKUP_ROOTDIR}/${MYSQL_HOST}/${YEAR}/${MONTH}/${DAY}" | |
| else | |
| BACKUP_DIR="${BACKUP_ROOTDIR}/${SWARM_SERVICE}_${MYSQL_HOST}/${YEAR}/${MONTH}/${DAY}" | |
| fi | |
| # Log file | |
| LOGFILE="${BACKUP_DIR}/${TIMESTAMP}.log" | |
| # Check and create directories. | |
| [[ ! -d "${BACKUP_DIR}" ]] && mkdir -p "${BACKUP_DIR}" 2>/dev/null | |
| # Initialize log file. | |
| echo "==========================================================" > "${LOGFILE}" | |
| { echo "* Starting backup: ${TIMESTAMP}"; echo "* Backup directory: ${BACKUP_DIR}"; } >> "${LOGFILE}" | |
| # Backup. | |
| echo "* Backing up databases ..." >> "${LOGFILE}" | |
| sed -n 1,4p "${LOGFILE}" | |
| for db in ${DATABASES}; do | |
| backup_db "${db}" >> "${LOGFILE}" | |
| if [[ "$?" == '0' ]]; then | |
| if [[ "$db" == '--all-databases' ]]; then | |
| echo " - ALL [DONE]" >> "${LOGFILE}" | |
| else | |
| echo " - ${db} [DONE]" >> "${LOGFILE}" | |
| fi | |
| else | |
| BACKUP_SUCCESS='NO' | |
| fi | |
| done | |
| # Append file size of backup files. | |
| { echo -e "* File size:\n----"; ${CMD_DU} "${BACKUP_DIR}"/*"${TIMESTAMP}"*sql*; echo "----"; } >> "${LOGFILE}" | |
| echo "* Backup completed (Success? ${BACKUP_SUCCESS})." >> "${LOGFILE}" | |
| } | |
| backup_interval_format_error() { | |
| echo "[ERROR] SLEEP_SCHEDULE supports 0 for running the script once and exit (the default value)" 1>&2 | |
| echo "[ERROR] or a NUMBER for time in seconds or for LINUX ONLY: NUMBER[s|m|h|d] for seconds, minutes, hours and days respectively." 1>&2 | |
| exit 255 | |
| } | |
| send_mail() { | |
| cp ${LOGFILE} /tmp/mail_file | |
| MAIL_FILE=/tmp/mail_file | |
| sed -i '1d;$d' ${MAIL_FILE} | |
| sed -i "1s/^/To: ${MAILTO}\n/" ${MAIL_FILE} | |
| if [[ -z ${SWARM_SERVICE} ]]; then | |
| sed -i "2s/^/Subject: SQL Dump - HOST: ${MYSQL_HOST}\n/" ${MAIL_FILE} | |
| else | |
| sed -i "2s/^/Subject: SQL Dump - HOST: ${MYSQL_HOST} - SERVICE: ${SWARM_SERVICE}\n/" ${MAIL_FILE} | |
| fi | |
| sed -i "3s/^/*** Backup completed (Success? ${BACKUP_SUCCESS})\n/" ${MAIL_FILE} | |
| sed -i "4s/^/***\n/" ${MAIL_FILE} | |
| sed -i "5s/^/-----------DETAILED REPORT-----------\n/" ${MAIL_FILE} | |
| cat ${MAIL_FILE} | ${CMD_MAIL} -C ${MSMTPRC} -a ${MSMTP_ACCOUNT_NAME} "${MAILTO}" | |
| rm -f ${MAIL_FILE} | |
| } | |
| if [[ ${CRON_SCHEDULE} != '0' ]]; then | |
| if [[ "${DOCKER}" == 'NO' ]]; then | |
| echo "[ERROR] CRON scheduling can be used only with the docker image: ioagel/mysql-backup" | |
| exit 255 | |
| fi | |
| execute_backup | |
| sed -n '5,$p' "$LOGFILE" | |
| if [[ ! -z "${MAILTO}" && -f "${MSMTPRC}" ]]; then | |
| send_mail | |
| fi | |
| exit 0 | |
| fi | |
| if [[ "$SLEEP_SCHEDULE" == '0' ]]; then | |
| execute_backup | |
| if [[ "${DOCKER}" == 'YES' ]]; then | |
| echo "Override SLEEP_SCHEDULE environment variable if you want to run non-stop as a daemon" >> ${LOGFILE} | |
| fi | |
| sed -n '5,$p' "$LOGFILE" | |
| elif [[ "$SLEEP_SCHEDULE" =~ $sleep_linux_regex ]]; then | |
| [[ $(uname -s) != 'Linux' ]] && [[ ! "$SLEEP_SCHEDULE" =~ $sleep_unix_regex ]] && backup_interval_format_error | |
| while true; do | |
| execute_backup | |
| sed -n '5,$p' "$LOGFILE" | |
| if [[ ! -z "${MAILTO}" && -f "${MSMTPRC}" ]]; then | |
| send_mail | |
| fi | |
| sleep "$SLEEP_SCHEDULE" & | |
| wait $! | |
| done | |
| else | |
| backup_interval_format_error | |
| fi | |
| exit 0 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment