-6aa0819f295951c2.gif

全库

#!/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"


cddatastr
mkdir backupstr
cdbackupstr
totalDB=(mysql -N -hDBHost -uDBUSER -ppassword -Pport <<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  -uDBUSER  -ppassword   -Pport  --default-character-set=utf8 --single-transaction --master-data=2 -R -E -dt db   >db.sql
totalTABLE=(mysql -N -hDBHost -uDBUSER -ppassword -Pport <<EOF
usedb;
show tables;
EOF
)
    OLD1_IFS="IFS"
    IFS=" "
    arr1=(totalTABLE)
    IFS="OLD1_IFS"
    mkdir -pbackupstr"/"db
    for table in{arr1[@]}
    do 
        if [[ db_table_not_bak =~db"."table ]];then
            echo "不备份"db"."table
        else
            echodb"."table" dumping..."
            mysqldump  -hDBHost  -uDBUSER  -ppassword   -Pport  --default-character-set=utf8 --single-transactiondb table  >backupstr"/"db"/"db.table.sql
        fi
    done

fi
done

# 压缩为tgz
cddatastr
tar zcvf backupstr".tgz"backupstr
if [ "0" -eq ? ];then
    rm -rfbackupstr
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"

cddatastr
mkdir backupstr
cdbackupstr


#遍历表名称
#mysqldump  -h DBHost  -uuser  -ppassword   -Pport  --default-character-set=utf8 --single-transaction --master-data=2 -R -E -dt db   >db.sql
totalTABLE=(mysql -N -hDBHost -uuser -ppassword -Pport <<EOF
useDB;
show tables;
EOF
)

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

    #table名称去掉最后一个下划线以后在db_table_not_bak中
    if [[db_table_not_bak =~ {table%_*} ]];then
        echo "不备份"DB"."table
    else
        echoDB"."table" dumping..."
        mysqldump  -hDBHost  -uuser  -ppassword   -Pport  --default-character-set=utf8 --single-transactionDB table  >backupstr"/"DB"/"DB.$table.sql
    fi
done

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

Captcha Code