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