linux mysql数据库备份脚本代码

#!/bin/bash
#This is a ShellScript For Auto DB Backup
#Powered by aspbiz
#2004-09

#Setting
#设置数据库名，数据库登录名，密码，备份路径，日志路径，数据文件位置，
#以及备份方式
#默认情况下备份方式是tar，还可以是mysqldump,mysqldotcopy
#默认情况下，用root(空)登录mysql数据库，备份至/root/namexxxxx.tgz

DBName=mysql
DBUser=root
DBPasswd=password
BackupPath=/root/
LogFile=/root/db.log
DBPath=/var/local/mysql5/var/
#BackupMethod=mysqldump
#BackupMethod=mysqlhotcopy
#BackupMethod=tar
#Setting End

NewFile="$BackupPath""$DBName"$(date +%y%m%d).tgz
DumpFile="$BackupPath""$DBName"$(date +%y%m%d)
OldFile="$BackupPath""$DBName"$(date +%y%m%d –date=’5 days ago’).tgz

echo "——————————————-" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile
echo "————————–" >> $LogFile
#Delete Old File
if [ -f $OldFile ]
then
   rm -f $OldFile >> $LogFile 2>&1
   echo "[$OldFile]Delete Old File Success!" >> $LogFile
else
   echo "[$OldFile]No Old Backup File!" >> $LogFile
fi

if [ -f $NewFile ]
then
   echo "[$NewFile]The Backup File is exists,Can’t Backup!" >> $LogFile
else
   case $BackupMethod in
   mysqldump)
      if [ -z $DBPasswd ]
      then
         mysqldump -u $DBUser –opt $DBName > $DumpFile
      else
         mysqldump -u $DBUser -p$DBPasswd –opt $DBName > $DumpFile
      fi
      tar czvf $NewFile $DumpFile >> $LogFile 2>&1
      echo "[$NewFile]Backup Success!" >> $LogFile
      rm -rf $DumpFile
      ;;
   mysqlhotcopy)
      rm -rf $DumpFile
      mkdir $DumpFile
      if [ -z $DBPasswd ]
      then
         mysqlhotcopy -u $DBUser $DBName $DumpFile >> $LogFile 2>&1
      else
         mysqlhotcopy -u $DBUser -p $DBPasswd $DBName $DumpFile >>$LogFile 2>&1
      fi
      tar czvf $NewFile $DumpFile >> $LogFile 2>&1
      echo "[$NewFile]Backup Success!" >> $LogFile
      rm -rf $DumpFile
      ;;
   *)
      /etc/init.d/mysqld stop >/dev/null 2>&1
      tar czvf $NewFile $DBPath$DBName >> $LogFile 2>&1
      /etc/init.d/mysqld start >/dev/null 2>&1
      echo "[$NewFile]Backup Success!" >> $LogFile
      ;;
   esac
fi

echo "——————————————-" >> $LogFile

#!/bin/bash

#Mysql autobackup shell#write by tuspark.cn

#-------------------数据库相关的用户名、密码、需要备份的数据库名、备份目录等

dbuser=root

dbpasswd=XXXXX

dbserver=localhost

dbname=XXXXX

dbopt=--opt

backupdir=/dcbackup/

#-------------------是否开启FTP远程备份，0为否，1为是。

copytoftp=1

ftpserver=XXXXX

ftpuser=XXXXX

ftppasswd=XXXXX

#-------------------以下参数

fileprefix=dcradiusdump

filename=$backupdir`date +%F`.sql

newfile=$fileprefix-`date +%F`.tar.gz

keepdays=10

#-------------------以下为备份时的日志
logfile=/var/log/mysqlbackup.log
logtmp=/var/log/mybackup.tmp
#===============================================
if [ ! -d $backupdir ]
then
echo "$backupdir is not exist, then make ..." >> $logfile
mkdir -p $backupdir
fi
echo "start====================================>">>$logfile
echo "Beginning backup `date '+%F %T'`" >>$logfile
echo "Delete $keepdays days ago files ..." >>$logfile
find $backupdir -name $fileprefix* -mtime +$keepdays -fls $logtmp -exec rm {} ;
echo "Deleted Backup file is :">>$logfile
cat $logtmp >>$logfile
echo "Delete old file Success!" >>$logfile
if [ -f $backupdir$newfile ]
then
echo "$newfile backup exist, backup stop ..." >>$logfile
else
if [ -z $dbpasswd ]
then
mysqldump -u$dbuser -h$dbserver $dbopt $dbname >$dumpfilename
else
mysqldump -u$dbuser -p$dbpasswd -h$dbserver $dbopt $dbname >$dumpfilename
fi
tar czvf $backupdir$newfile $dumpfilename >>$logfile 2>&1
echo "$backupdir$newfile Backup Success!" >>$logfile
rm -fr $dumpfilename
if [ $copytoftp = 1 ]; then
if [ -z $ftpserver ];then
echo "Ftp Server not set,Copy to Ftp Failed ..." >>$logfile
exit 1
elif [ -z $ftpuser ];then
echo "Ftp user not set, Copy to Ftp Failed ..." >>$logfile
exit 2
elif [ -z $ftppasswd ]; then
echo "Ftp password not set, Copy to Ftp Failed ..." >>$logfile
exit 3
else
echo "Start copy to Ftp server ...." >> $logfile
ftp -n>$logfile
fi

第一步：在服务器上配置备份目录代码：
--------------------------------------------------------------------------------
mkdir /var/lib/mysqlbackup
cd /var/lib/mysqlbackup
--------------------------------------------------------------------------------
第二步：编写备份脚本代码：
--------------------------------------------------------------------------------
vi dbbackup.sh
--------------------------------------------------------------------------------
粘帖以下代码，务必更改其中的username,password和dbname。
代码：
--------------------------------------------------------------------------------
#!/bin/sh #Username to access the MySQL server USERNAME="username" # Username to access the MySQL server PASSWORD="password" # List of DBNAMES for Backup DBNAME="dbname" #date timestamp for log message DATE=`/bin/date +%Y-%m-%d_%Hh%Mm` #output file OUTDIR="/var/lib/mysqlbackup/" OUTFILE="ip_bindass".$DATE."sql.gz" #working directory DIR="/var/lib/mysqlbackup/"#cd $DIR # MySQL Backup /usr/bin/mysqldump --database $DBNAME --opt --single-transaction -u$USERNAME -p$PASSWORD | /usr/bin/gzip -9 > $OUTDIR$OUTFILE
--------------------------------------------------------------------------------
更改备份脚本权限
代码：
--------------------------------------------------------------------------------
chmod +x dbbackup.sh
--------------------------------------------------------------------------------
第三步：用crontab定时执行备份脚本代码：
--------------------------------------------------------------------------------
crontab -e
--------------------------------------------------------------------------------
若每天下午3点20备份，添加如下代码，
代码：
--------------------------------------------------------------------------------
20 15 * * * /var/lib/mysqlbackup/dbbackup.sh
--------------------------------------------------------------------------------
搞定！