Just wanted to paste a small script here, which dumps and gzips all databases hosted on a MySQL instance.
Make sure, that this script is not readable for everybody, as it contains credentials.
dumpdbs.sh
#!/bin/bash
#
# MySQL database dump
#
# - Takes MySQL Dumps of all available databases
# - Only keeps one backup in the dumpfolder
# -> uncomment the LOG variable and the pipes to the tee command for logging to a file
#
# 2011, Looke
#
# Setup
DBUSER=root
DBPASS=xxx
DBDUMPDIR=/dbdumps
DBDUMPDATE=$(date '+%d-%m-%Y')
# LOG=/var/log/dumpdbs
# Create/Empty DBDUMPDIR
if [ ! -d $DBDUMPDIR ]; then
echo $(date +"%d/%m/%Y %T") INFO: $DBDUMPDIR not found, will create it... #| tee -a $LOG
mkdir $DBDUMPDIR
else
echo $(date +"%d/%m/%Y %T") INFO: Emptying $DBDUMPDIR... #| tee -a $LOG
rm -f $DBDUMPDIR/*
fi
# Loop through all databases available and dump them (gzipped)
for DBNAME in $(echo "show databases;" | mysql --user=$DBUSER --password=$DBPASS -s)
do
echo $(date +"%d/%m/%Y %T") INFO: Dumping $DBNAME as ${DBNAME}_${DBDUMPDATE}.sql.gz... # | tee -a $LOG
mysqldump --user=$DBUSER --password=$DBPASS $DBNAME | gzip -c > $DBDUMPDIR/${DBNAME}_${DBDUMPDATE}.sql.gz
done
This script does a good job together with Bacula. Here the Job resource in Bacula
Job {
Name = "Backup MySQL DBs"
...
Client Run Before Job = "/opt/bacula/scripts/dumpdbs.sh"
...
}