Backup Shell script: each Mysql database to a separated dump

MySql LogoAnother life-saver script for a sysadmin.
Run with root mysql user privileges, so.

Use this script at your own risk. The -h option provides a short usage line. If you notice any bug or if you have a useful addition please post your feedback as a comment.

#!/bin/bash

# Simple MySQL dump script which dumps each database to a compressed
# file with the date included in the file name

MYSQL='/usr/bin/mysql'

MYSQLDUMP='/usr/bin/mysqldump'
DUMPOPTS='--opt --hex-blob --skip-extended-insert'

DATEFORMAT='%Y%m%d-%H%M%S' # See man date

user=root
pass=
dir='.'
while getopts 'u:d:p:h' OPTION
do
case $OPTION in
u)
user="$OPTARG"
;;
d)
dir="$OPTARG"
;;
p)
pass="$OPTARG"
;;
h|?)
printf "Usage: %s: [-u USER] [-p PASSWORD] [-d DIRECTORY]\n" \
$(basename $0) >&2
exit 2
;;
esac
done
if [ -z "$pass" ]
then
read -s -p "password: " pass ; printf "%b" "\n"
fi

# Get the names of the database tables
databases=`$MYSQL -u$user -p$pass --skip-column-names -e'SHOW DATABASES'`

# Write the compressed dump for each table
for db in $databases; do
filename=`date +"$dir/$db-$DATEFORMAT.sql.gz"`
echo "creating $filename"
$MYSQLDUMP $DUMPOPTS -u$user -p$pass --database $db \
| gzip -9 > $filename
done

Example:

./mysqlbu.sh -d ~/backup

Hope this helps as much as helped me.
Ciao! 

Incoming search terms:

  • mail esac pt (48)
(Visited 2,191 times, 1 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *