全库

#!/bin/bash
datastr=/nas/bakup/mysql_polardb
DBHost="192.16.8.0.3"
DBUSER="root"
password="123456"
port="3306"
backupstr=mysqlbak$(date +%Y%m%d%H%M)
db_not_bak="information_schema performance_schema mysql sys"
db_table_not_bak="d1.t1 d2.t2"


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 ]]
then
    echo "不备份" "$db"
else
    #mysqldump  -h $DBHost  -u$DBUSER  -p$password   -P$port  --default-character-set=utf8 --single-transaction --master-data=2 -R -E -dt $db   >$db.sql
totalTABLE=$(mysql -N -h $DBHost -u$DBUSER -p$password -P$port <<EOF
use $db;
show tables;
EOF
)
    OLD1_IFS="$IFS"
    IFS=" "
    arr1=($totalTABLE)
    IFS="$OLD1_IFS"
    mkdir -p $backupstr"/"$db
    for table in ${arr1[@]}
    do 
        if [[ $db_table_not_bak =~ $db"."$table ]];then
            echo "不备份" $db"."$table
        else
            echo $db"."$table" dumping..."
            mysqldump  -h $DBHost  -u$DBUSER  -p$password   -P$port  --default-character-set=utf8 --single-transaction   $db $table  >$backupstr"/"$db"/"$db.$table.sql
        fi
    done

fi
done

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

单库 带模糊匹配的名单 适合分表情况

#!/bin/bash
datastr=/data/mysql_bak
DBHost="127.0.01"
DB="mydb"
user="root"
password="123456"
port="3306"
backupstr=mysqlbak$(date +%Y%m%d%H%M)
#不备份表名，按最后一个下划线截取，匹配所有分表，如log_0 log_1
db_table_not_bak="log imei"

cd $datastr
mkdir $backupstr
cd $backupstr


#遍历表名称
#mysqldump  -h $DBHost  -u$user  -p$password   -P$port  --default-character-set=utf8 --single-transaction --master-data=2 -R -E -dt $db   >$db.sql
totalTABLE=$(mysql -N -h $DBHost -u$user -p$password -P$port <<EOF
use $DB;
show tables;
EOF
)

###IFS
OLD1_IFS="$IFS"
IFS=" "
arr1=($totalTABLE)
IFS="$OLD1_IFS"
mkdir -p $backupstr"/"$DB
for table in ${arr1[@]}
do 

    #table名称去掉最后一个下划线以后在$db_table_not_bak中
    if [[ $db_table_not_bak =~ ${table%_*} ]];then
        echo "不备份" $DB"."$table
    else
        echo $DB"."$table" dumping..."
        mysqldump  -h $DBHost  -u$user  -p$password   -P$port  --default-character-set=utf8 --single-transaction   $DB $table  >$backupstr"/"$DB"/"$DB.$table.sql
    fi
done