Create a backup of all your MySQL databases

dbbackup

#!/bin/sh

# dbbackup - Create a backup of all your MySQL databases
#
# Copyright 2011 Rene Kreijveld - [email protected]
#
# This program is free software; you may redistribute it and/or modify it.

NOW=$(date +"%Y%m%d-%H%M%S")
DBCONF=/usr/local/directadmin/conf/mysql.conf
DBUSER=`grep 'user=' $DBCONF | cut -d = -f 2`
DBHOST=localhost
DBPASS=`grep 'passwd=' $DBCONF | cut -d = -f 2`
BACKUPDIR=/backups/mysql

# Find MySQL Socket
if [ -S /var/lib/mysql/mysql.sock ]
then
        MYSOCK=/var/lib/mysql/mysql.sock
elif [ -S /var/run/mysqld/mysqld.sock ]
then
        MYSOCK=/var/run/mysqld/mysqld.sock
elif [ -S /tmp/mysql.sock ]
then
        MYSOCK=/tmp/mysql.sock
fi

#MYSOCK=/path/to/your/socket/file/here

if [ -z MYSOCK ]
then
	echo "No valid MySQL socket file found!"
	echo "Either MySQL is not running or it is installed in a custom location."
	echo "Manually add it's path in this script on line 28."
	exit 1
fi

DBS=`mysql -u$DBUSER -h$DBHOST -p$DBPASS -S$MYSOCK -e"show databases"`

for DATABASE in $DBS
do
case "$DATABASE" in
'Database')
        ;;
'information_schema')
        ;;
'performance_schema')
        ;;
'da_roundcube')
        ;;
'mysql')
        ;;
*)
        FILENAME="$DATABASE.$NOW.gz"
        mysqldump -u$DBUSER -h$DBHOST -p$DBPASS -S$MYSOCK $DATABASE | gzip --best > $BACKUPDIR/$FILENAME
        ;;
esac
done