#!/bin/bash

NOW=`date +”%Y-%m”`;
BACKUPDIR=”location/of/your/backup/dir/$NOW”;

### Server Setup ###
#* MySQL login user name *#
MUSER=”user”;

#* MySQL login PASSWORD name *#
MPASS=”pass”;

#* MySQL login HOST name *#
MHOST=”your-mysql-ip”;
MPORT=”your-mysql-port”;

# DO NOT BACKUP these databases
IGNOREDB=”
information_schema
mysql
test
”

#* MySQL binaries *#
MYSQL=`which mysql`;
MYSQLDUMP=`which mysqldump`;
GZIP=`which gzip`;

# assuming that /nas is mounted via /etc/fstab
if [ ! -d $BACKUPDIR ]; then
mkdir -p $BACKUPDIR
else
:
fi

# get all database listing
DBS=”$(mysql -u $MUSER -p$MPASS -h $MHOST -P $MPORT -Bse ‘show databases’)”

# SET DATE AND TIME FOR THE FILE
NOW=`date +”d%dh%Hm%Ms%S”`; # day-hour-minute-sec format
# start to dump database one by one
for db in $DBS
do
DUMP=”yes”;
if [ "$IGNOREDB" != "" ]; then
for i in $IGNOREDB # Store all value of $IGNOREDB ON i
do
if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
DUMP=”NO”; # SET value of DUMP to “no”
#echo “$i database is being ignored!”;
fi
done
fi

if [ "$DUMP" == "yes" ]; then # If value of DUMP is “yes” then backup database
FILE=”$BACKUPDIR/$NOW-$db.gz”;
echo “BACKING UP $db”;
$MYSQLDUMP –add-drop-database –opt –lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE
fi
done

V2.0

#!/bin/sh

USER=”mysql user”
PASS=”mysql password”
HOME=”/home/backup/dbs”
DATE=”`date +”%d%b”`”
MYSQL=”/usr/local/bin/mysql”
MYSQLDUMP=”/usr/local/bin/mysqldump”
BZIP2=”/usr/bin/bzip2″

cd $HOME && mkdir $DATE && cd $DATE
$MYSQL -u $USER –password=$PASS -Bse ‘show databases’ |while read m; \
do $MYSQLDUMP -u $USER –password=$PASS `echo $m` > `echo $m`.sql;done
$BZIP2 *sql

echo “done”