MySQL备份脚本

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的备份数据,如果删除多个备份文件(不同名称)再复制一行修改下就行了