-
-
Save henke-andersson/4136709 to your computer and use it in GitHub Desktop.
Backup each MySQL database on a MySQL server into a separate file.
This file contains 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
#!/bin/bash | |
# dump-all-databases.sh | |
# | |
# | |
# Backup each MySQL database on a MySQL server into a separate file. | |
# Optionally files can be gzipped (dbname.sql.gz) | |
# | |
# Usage: dump_all_databases [ -u username -o output_dir -h hostname -x exclude -z ] | |
# | |
# -u username to connect MySQL server | |
# -o [output_dir] optionally specify the output directory where to put the files | |
# -h [hostname] optionally specify the hostname of the target MySQL database. | |
# -x [exclude] optionally specify a regex for databases that should be excluded. | |
# -z enable gzip | |
# | |
# Note: The script will prompt for a password, you cannot specify it as command line argument for security reasons. | |
# | |
# | |
# | |
# Based on a script of the same name by Daniel Verner [1] which was based on a script | |
# from [2]. I have made some modifications; namely adding a hostname option and an option | |
# to exclude databases that match a given regex. Verner's script contained no copyright | |
# or license. I credit his work and place the remainder of this script in the public domain. | |
# - Sam Parsons, 2012 | |
# | |
# [1] http://carrotplant.com/en/blog/how-to-dump-all-mysql-databases-into-separate-files | |
# [2] http://soniahamilton.wordpress.com/2005/11/16/backup-multiple-databases-into-separate-files/ | |
# | |
PROG_NAME=$(basename $0) | |
USER="" | |
PASSWORD="" | |
OUTPUTDIR=${PWD} | |
HOST="" | |
HOST_STR="" | |
EXCLUDE="" | |
GZIP_ENABLED=0 | |
GZIP="" | |
MYSQLDUMP="/usr/local/mysql/bin/mysqldump" | |
MYSQL="/usr/local/mysql/bin/mysql" | |
while getopts u:o:h:x:z OPTION | |
do | |
case ${OPTION} in | |
u) USER=${OPTARG};; | |
o) OUTPUTDIR=${OPTARG};; | |
h) HOST=${OPTARG};; | |
x) EXCLUDE="|$OPTARG";; | |
z) GZIP_ENABLED=1;; | |
?) echo "Usage: ${PROG_NAME} [ -u username -o output_dir -h hostname -x exclude -z ]" | |
exit 2;; | |
esac | |
done | |
if [ "$USER" != '' ]; then | |
echo -ne "Enter password for" $USER": " | |
oldmodes=`stty -g` | |
stty -echo | |
read PASSWORD | |
stty $oldmodes | |
echo ""; | |
fi | |
if [ ! -d "$OUTPUTDIR" ]; then | |
mkdir -p $OUTPUTDIR | |
fi | |
if [ "$HOST" != '' ]; then | |
HOST_STR=" -h $HOST " | |
else | |
HOST_STR="" | |
fi | |
# get a list of databases | |
databases=`$MYSQL $HOST_STR --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema$EXCLUDE)"` | |
# dump each database in turn | |
for db in $databases; do | |
echo $db | |
if [ $GZIP_ENABLED == 1 ]; then | |
$MYSQLDUMP $HOST_STR --force --opt --user=$USER --password=$PASSWORD --databases $db | gzip > "$OUTPUTDIR/$db.sql.gz" | |
else | |
$MYSQLDUMP $HOST_STR --force --opt --user=$USER --password=$PASSWORD --databases $db > "$OUTPUTDIR/$db.sql" | |
fi | |
done |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment