/opt/shells/bak_db09.sh

#!/bin/bash
datastr=/nfs_nas/bakup/db09
DBHost="172.16.0.2"
DBUSER="test"
password="123456"
port="3306"
backupstr=mysqlbak$(date +%Y%m%d%H%M)
db_not_bak="information_schema performance_schema mysql test_xxl"
db_not_bak_except="test"

echo "这些表不备份" $db_not_bak
cd $datastr
mkdir $backupstr
cd $backupstr
totalDB=$(mysql -N -h $DBHost -u$DBUSER -p$password -P$port <<EOF
show databases;
EOF
)
###IFS
OLD_IFS="$IFS"
IFS=" "
arr=($totalDB)
IFS="$OLD_IFS"
for db in ${arr[@]}
do
        if [[ $db_not_bak =~ $db ]] && [[ ! $db_not_bak_except =~ $db ]]
then
    echo "$db" "不备份" 
else
    echo  "$db" "开始备份..."
    #mysqldump  -h $DBHost  -uroot  -p$password   -P$port  --default-character-set=utf8 --single-transaction --master-data=2 -R -E -dt $db   >$db.sql
    mysqldump  -h $DBHost  -u$DBUSER  -p$password   -P$port  --default-character-set=utf8 --single-transaction   $db   >$db.sql
    echo  "$db" "备份完成"
fi
done

# 压缩为tgz
cd $datastr
tar zcvf $backupstr".tgz" $backupstr
if [ "0" -eq $? ];then
    rm -rf $backupstr
fi

crontab

#echo "00 */3 * * * /opt/shells/bak_db09.sh" >> /var/spool/cron/crontabs/root
echo "00 */3 * * * /opt/shells/bak_db09.sh" >> /var/spool/cron/root