Last Updated: February 25, 2016
·
627
· phaus

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