mysql按表备份

  sre

全库

#!/bin/bash
datastr=/opt/db_bak/mysql_bak
DBHost="127.0.0.1"
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="tt.t1 tt.t2"


cddatastr
mkdir backupstr
cdbackupstr
totalDB=(mysql -N -hDBHost -uroot -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  -hDBHost  -uroot  -ppassword   -Pport  --default-character-set=utf8 --single-transaction --master-data=2 -R -E -dt db   >db.sql
totalTABLE=(mysql -N -hDBHost -uroot -ppassword -Pport <<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  -uroot  -ppassword   -Pport  --default-character-set=utf8 --single-transactiondb table  >backupstr"/"db"/"db.$table.sql
        fi
    done

fi
done


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

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

LEAVE A COMMENT

Captcha Code