mysqldump.sh

#!/bin/bash
source /etc/environment

DEST_DUMP_PATH="/opt/backups/mysql"
DAYS=3
DUMP_NAME="$(hostname)_$(date +"%d-%m-%Y_%s")" # Get date in dd-mm-yyyy_s format

S3CMD=$(which s3cmd)
AWS=$(which aws)
AWS_S3_BUCKET="mysql-backups"
DO_S3_BUCKET="db.backup/mysqldump"

# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Create Backup sub-directories
MBD="${DEST_DUMP_PATH}/${DUMP_NAME}/mysql"
install -d "${MBD}"

SKIP="information_schema sys performance_schema" # DB skip list
DBS="$(${MYSQL} -Bse 'show databases')"          # Get all databases

#=====================================#
# Remove old files
find ${DEST_DUMP_PATH} -type f -mtime +${DAYS} -delete

mkdir -p ${DEST_DUMP_PATH}

# Archive database dumps
for db in ${DBS}; do
    skipdb=-1
    if [ "${SKIP}" != "" ]; then
        for i in ${SKIP}; do
            [ "${db}" == "${i}" ] && skipdb=1 || :
        done
    fi

    if [ "${skipdb}" == "-1" ]; then
        mkdir -p "${MBD}/${db}"
        for dump_table in $(${MYSQL} -Bse "show tables from ${db}"); do
            FILE_TABLE_DATA="${MBD}/${db}/${db}-${dump_table}.sql"
            FILE_TABLE_SCHEMA="${MBD}/${db}/${db}-${dump_table}-schema.sql"
            ${MYSQLDUMP} --set-gtid-purged=OFF --no-data --single-transaction "${db}" "${dump_table}" | ${GZIP} -9 >"${FILE_TABLE_SCHEMA}.gz"
            ${MYSQLDUMP} --set-gtid-purged=OFF --single-transaction "${db}" "${dump_table}" | ${GZIP} -9 >"${FILE_TABLE_DATA}.gz"
        done
        FILE="${MBD}/${db}.sql"
        ${MYSQLDUMP} --set-gtid-purged=OFF --single-transaction "${db}" | ${GZIP} -9 >${FILE}.gz
    fi
done

# Archive the directory, send mail and cleanup
cd ${DEST_DUMP_PATH} || exit
tar -cf ${DUMP_NAME}.tar "${DUMP_NAME}"

# Copy to S3 bucket
${S3CMD} sync ${DUMP_NAME}.tar s3://${DO_S3_BUCKET}/${DUMP_NAME}.tar &>/dev/null
${AWS} s3 cp ${DUMP_NAME}.tar s3://${AWS_S3_BUCKET}/${DUMP_NAME}.tar &>/dev/null

rm -rf "${DUMP_NAME}"