Backup all MySQL Databases on one Host

#!/bin/sh
PWD=`which pwd`
MYSQLDUMP=`which mysqldump`
MYSQL=`which mysql`
TAR=`which tar`

HOST=localhost
USER=root
PASS=password

TARGET=/home/backup/data/mysql
rm -R $TARGET/*
ROOT=/home/backup
CMD_FILE="cmd.sql"

echo "show databases;" > $CMD_FILE 
DBS=`$MYSQL --user=$USER --password=$PASS < $CMD_FILE`
DATE=`eval date +%Y-%m-%d`
echo "ROOT: $ROOT"

I=0
echo "saved $DATE"
for DB in $DBS; do
 if [ ! $I = 0 ]; then
 $MYSQLDUMP $DB --user=$USER --password=$PASS --add-locks --flush-logs > $TARGET/$DB.sql
 echo "$DB saved"
 cd $TARGET
 $TAR cfj $DB.tar.bz2 $DB.sql
 rm $DB.sql
 cd $ROOT
 echo ""
 fi
 I=$(( $I + 1 ))
done
echo "$I DBs saved"
echo "done"
rm $ROOT/$CMD_FILE

Related protips

Have a fresh tip? Share with Coderwall community!