Backing up a MySQL server

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.

# 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
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
	echo $(date +"%d/%m/%Y %T") INFO: Emptying $DBDUMPDIR... #| tee -a $LOG
	rm -f $DBDUMPDIR/*

# Loop through all databases available and dump them (gzipped)
for DBNAME in $(echo "show databases;" | mysql --user=$DBUSER --password=$DBPASS -s)
	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

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/"  

Leave a Reply

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


This site uses Akismet to reduce spam. Learn how your comment data is processed.