#! /bin/bash

BAKPATH=/data/mysql-back

MYUSER=root

MYPASS="123456"

SOCKET=/var/lib/mysql/mysql.sock

MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"

MYDUMP="mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -F -R"

DBLIST=`$MYCMD -e "show databases;" |sed "1d" |egrep -v "_schema|test"`

[ ! -d $BAKPATH ] && mkdir -p $BAKPATH

for dbname in $DBLIST

do

    TLIST=`$MYCMD -e "show tables from $dbname;" |sed "1d"`

    for tname in $TLIST

    do

        mkdir -p $BAKPATH/$dbname

        $MYDUMP $dbname $tname |gzip > $BAKPATH/$dbname/${tname}_$(date +%F).sql.gz

    done

done