Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1275671
  • 博文数量: 842
  • 博客积分: 12411
  • 博客等级: 上将
  • 技术积分: 5772
  • 用 户 组: 普通用户
  • 注册时间: 2011-06-14 14:43
文章分类

全部博文(842)

文章存档

2013年(157)

2012年(685)

分类:

2012-04-14 17:24:59

原文地址:MySQL备份脚本 作者:hnkeyang

MySQL备份脚本,支持通过ssh(已建立双机互信)备份到多个服务器,备份多个库,备份时指定要排除的表(如log表或测试表)
#!/bin/bash
#

dbuser=root  ##MySQL用户名
dbpasswd=123456 ##MySQL密码
dbserver=localhost
dbopt=--opt
backupdir=/home/dbbak
if [ ! -d $backupdir ];then
  mkdir -p $backupdir
fi

#set store backup file server
dbbakserver=()
dbbakserver[0]=a.51netinfo.net:222  ##要备份到的远程第一台服务器 ip:端口
dbbakserver[1]=b.51netinfo.net:22   ##要备份到的远程第二台服务器 ip:端口
##如果还有第三个第四个复制 dbbakserver[1]=b.51netinfo.net:22 改下下标及服务器ip端口就行了
keepdays=3 ##本地保存备份数据天数

logfile=/var/log/mysqlbackup.log
logtmp=/tmp/dbbak_del.log

backup(){
#backup {table_name} [ -e exclude_table1].. [-e exclude_table4]
if [ -z $1 ];then
 echo Usage: backup {table_name} [ -e exclude_table1].. [-e exclude_table4]
 exit 1
fi
dbname=$1
fileprefix=${dbname}.sql
dumpfilename=$fileprefix-`date +%F`.sql
newtarfile=$fileprefix-`date +%F`.tar.gz

exclude_table=()
index=0
while [ $# -gt 0 ];do
  case $1 in
   -e) shift;exclude_table[$index]=$1;((index++)) ;;
    *) shift ;;
  esac
done

echo "start====================================>">>$logfile
echo "`date '+%F %T'` Beginning backup" >>$logfile

echo "`date '+%F %T'` Delete $keepdays days ago files ..." >>$logfile

find $backupdir -name $fileprefix* -mtime +$keepdays -fls $logtmp -exec rm {} ;
echo "`date '+%F %T'` Deleted Backup file is :$(cat $logtmp|awk '{print $NF}')">>$logfile

if [ -f $backupdir/$newtarfile ];then
  echo "`date '+%F %T'` $backupdir/$newtarfile backup exist, backup stop ..." >>$logfile
  echo "`date '+%F %T'` $backupdir/$newtarfile backup exist, backup stop ..."
  exit 1
fi
exc_dump(){
for j in $(mysql -u$dbuser -p$dbpasswd -e "use $dbname;show tables" | grep -v Tables);do
 for k in ${exclude_table[@]};do
   if [ $k == $j ];then
     continue 2
   fi
 done
 mysqldump -u$dbuser -p$dbpasswd -h$dbserver $dbopt $dbname $j >> $backupdir/$dumpfilename
done
}

if [ ${#exclude_table[@]} -gt 0 ];then
 exc_dump
else
 mysqldump -u$dbuser -p$dbpasswd -h$dbserver $dbopt $dbname >$backupdir/$dumpfilename
fi

##################tar bck file
cd $backupdir
tar czf $newtarfile $dumpfilename >>$logfile 2>&1
echo "$backupdir/$newtarfile Backup Success!" >>$logfile
rm -fr $dumpfilename
if [ ${#dbbakserver[@]} -eq 0 ];  then
  echo "Dbbak Server not set,Copy Stop!" >>$logfile
fi
echo "Start copy to dbbak server ...." >> $logfile
for bckserv in ${dbbakserver[@]};do
  eval $(echo $bckserv|awk -F":" '{print "bckserv_ip="$1;print "bckserv_port="$2}')
  if [ -z $bckserv_port ];then
    bckserv_port=22
  fi
  /usr/bin/scp -rP $bckserv_port $backupdir/$newtarfile root@$bckserv_ip:$backupdir
done

echo "End=======================================">>$logfile
}

backup netinfo -e pa1 -e pay #如果有要排除的表加-e参数空格跟表名,没有不用加-e
backup netinfo1  ##备份第二个数据库
##远端服务器删除旧的备份文件脚本
#!/bin/bash
#delold delfilename filedir keepdays
log_File=/var/log/backup.log

delold(){
if [ -z $1 ] || [ -z $2 ] || [ -z $3 ];then
echo Usage: delold filename filedir keepdays
exit 1
fi
fileprefix1=$1
backupdir1=$2
keepdays=$3
find $backupdir1 -name $fileprefix1* -mtime +$keepdays|xargs rm -fr {}
echo "Deleted Backup file is :" $fileprefix1 >>$log_File
echo "Delete old file Success!" >>$log_File
}

delold netinfo /home/dbbak/ 5    ##delold 备份文件名(去除日期及后缀后的)  备份目录 删除几天前的数据

#本例删除 /home/dbbak 中5天前的netinfo的备份数据,如果删除多个备份文件(不同名称)再复制一行修改下就行了
阅读(313) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~