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