Skip to content

Instantly share code, notes, and snippets.

@gfnord
Created March 18, 2019 19:24
Show Gist options
  • Save gfnord/960794705b472d22bfbd334bbc85db12 to your computer and use it in GitHub Desktop.
Save gfnord/960794705b472d22bfbd334bbc85db12 to your computer and use it in GitHub Desktop.
#!/bin/bash
# Script will output dumps for all databases using seperate files
USER="root"
HOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
OUTPUT_DIR="/backup/MySQL_Backup"
# Parse options
while getopts ":u:h:o:" opt; do
case $opt in
u)
USER=$OPTARG
;;
h)
HOST=$OPTARG
;;
o)
OUTPUT_DIR=$OPTARG
;;
\?)
echo "Invalid option: -$OPTARG" >&2
exit 1
;;
:)
echo "Option -$OPTARG requires an argument." >&2
exit 1
;;
esac
done
VALIDATION_ERROR=false
if [ -z "$USER" ]; then
echo "User has not been specified" >&2
VALIDATION_ERROR=true
fi
if [ -z "$OUTPUT_DIR" ]; then
echo "Output dir has not been specified" >&2
VALIDATION_ERROR=true
fi
if $VALIDATION_ERROR ; then
exit 1
fi
dd=`date +%Y%m%d`
DBS="$($MYSQL -u $USER -h $HOST -Bse 'show databases')"
for db in $DBS
do
if [ $db != "information_schema" ]; then
FILE=$OUTPUT_DIR/$db$dd.sql
$MYSQLDUMP -u $USER -h $HOST --single-transaction --routines --triggers $db > $FILE
/bin/gzip $FILE &
fi
done
# Delete all backups older than 7 days
find $OUTPUT_DIR -mtime +7 -exec rm -f {} \;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment