public
Last active

Shell script to backup all mysql databases to seperate files

  • Download Gist
backup_mysql_databases.sh
Shell
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
#!/bin/bash
# Script will output dumps for all databases using seperate files
# Derived from this post: http://www.cyberciti.biz/faq/ubuntu-linux-mysql-nas-ftp-backup-script/
 
USER=""
PASSWORD=""
HOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
OUTPUT_DIR=""
 
# Parse options
while getopts ":u:p:h:o:" opt; do
case $opt in
u)
USER=$OPTARG
;;
p)
PASSWORD=$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 "$PASSWORD" ]; then
echo "Password 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
 
DBS="$($MYSQL -u $USER -h $HOST -p$PASSWORD -Bse 'show databases')"
for db in $DBS
do
if [ $db != "information_schema" ]; then
FILE=$OUTPUT_DIR/$db.sql
$MYSQLDUMP -u $USER -h $HOST -p$PASSWORD --ignore-table=mysql.event $db > $FILE
fi
done

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.