数据库备份并分离日志表（按月）sh 脚本

#!/bin/sh
year=`date +%Y`  
month=`date +%m`  
day=`date +%d`  
hour=`date +%H`  
dir="/data/dbbackup/file" #备份路径  
#db数据备份
db_sqlname="db_bk.sql"  #备份成的文件名  
db_sqlnamelog="db_bklog.sql"  #备份成的文件名  
db_host="127.0.0.1" #数据库主机  
db_user="" #数据库用户名  
db_passwd="" #数据库密码  
db_dbname="threecards" #数据库名


mkdir $dir/$db_dbname"-"$year$month$day$hour #创建备份路径  
bkdir=$dir"/"$db_dbname"-"$year$month$day$hour

ignore="--ignore-table="$db_dbname"."  
ignore_log_credit=$ignore"log_credit" #忽略两个按月份拆表的log  
ignore_log_table=$ignore"log_table"

db_log="log_credit log_table" #忽略两个按月份拆表的log表名  
count=$((($year-2015)*12+$month-4))  
for i in  $(seq $count)  
do  
syear=$((($i+3)/12+2015))  
smonth=$(($i+4-($syear-2015)*12))  
if [ $smonth -ge 10 ]  
then  
smonth=$smonth  
else  
smonth=0$smonth  
fi  
log_credit=$ignore"log_credit_"$syear$smonth  
log_table=$ignore"log_table_"$syear$smonth  
ignore_log_credit=$ignore_log_credit" "$log_credit  
ignore_log_table=$ignore_log_table" "$log_table  
db_log=$db_log" log_credit_"$syear$smonth" log_table_"$syear$smonth" "  
done  
ignore_log=$ignore_log_table" "$ignore_log_credit  
echo "main db backup start"  
/usr/bin/mysqldump $ignore_log -h$db_host -u$db_user -p$db_passwd $db_dbname $db_tbname > $bkdir/$db_sqlname
echo "log db backup start"  
echo $db_log  
/usr/bin/mysqldump  -h$db_host -u$db_user -p$db_passwd $db_dbname $db_tbname $db_log> $bkdir/$db_sqlnamelog

echo "Delete expired file"  
find $dir -type f -mtime +5 -exec rm -f {} \;  #清除5天前过期备份  
echo "tar"  
cd $bkdir  
tar -zcvf $bkdir".tar.gz" ./  
echo "delete"  
rm -rf $bkdir