linux unix mysql 数据库自动备份

mysql_data_backup.sh

db_host  //数据库主机

db_user //数据库帐号

db_passwd //数据库密码

backupFileNum //保留的备份存档文件数目,超出该数目自动清理

backupDir //备份文件存放的目录

logfile //日志文件路径

#!/bin/bash

# mysq link user config
db_host=192.168.0.222
db_user=admin
db_passwd=123456
# backup File number
backupFileNum=4

# log file
logfile='/sqlbackup/backup.log'

# backup of directory Do not bring /
backupDir="/sqlbackup"

# check database name
echo 'Backup begin'

begin_time=`date '+%s'`

# get databases list
databases_content=`mysqlshow -u $db_user -p$db_passwd -h $db_host | sed 's/|//g' | sed 's/ //g' | sed '1,4d' | sed '$d'`

# check databases if emptyed then exit
databases_content_check=`echo -n $databases_content > /tmp/mysql_backup_check.tmp`

if [ ! -s /tmp/mysql_backup_check.tmp ];then
    echo 'Database Emptyed Backup stop.'
    exit
fi

# plan backup of sql databases
databases=(${databases_content})

# get databases length
databases_len=${#databases[*]}

# backup of directory for date
datestamp=$(date "+%Y%m%d")

# final backup directroy
fileDir="$backupDir/$datestamp"

# auto create target directory
if [ ! -d $backupDir ];then
 mkdir $backupDir
fi

if [ ! -d $fileDir ];then
 mkdir $fileDir
fi

if [ ! -d $logfile ];then
 touch $logfile
fi

echo "total $databases_len databases."

for ((i=0;i<$databases_len;i++))

do

  dbName="${databases[$i]}"

  fileName="${databases[$i]}.sql"

  filePath="$fileDir/$fileName"

  echo "Database [$dbName] Backup ..."

  echo "DROP DATABASE IF EXISTS `$dbName`;"  > $filePath

  echo "CREATE DATABASE `$dbName` CHARACTER SET `utf8`;" >> $filePath

  echo "use $dbName;" >> $filePath

  mysqldump -h $db_host  -u $db_user -p$db_passwd $dbName >> $filePath

done

# tar files
cd $backupDir
tar cvf ${datestamp}.tar ./${datestamp}

# delete source archive
rm -rf ./${datestamp}

end_time=`date '+%s'`
total_time=$[end_time-begin_time]
echo 'Backup Done'
echo "Total time: ${total_time} second"

now_date=`date '+%Y-%m-%d %k:%M:%S'`
# save log
echo "${now_date} : Backup Databases : Total time [${total_time}s]" >> $logfile

# begin clean excess of backup file

# count backup dir name of length;
backupDir_length=`expr length "$backupDir"`

# get tar file list
backupFile_list=` find $backupDir -name "*.tar" | sort -n -r -k 1.$backupDir_length`

# set array
backupFiles=(${backupFile_list})

backupFile_length=${#backupFiles[*]}

if [ $backupFile_length -gt $backupFileNum ];then
        for((i=$backupFile_length;i>$backupFileNum;i--))
        do
                fileName="${backupFiles[$i-1]}"
                rm -rf $fileName
                #save log
                echo "file: $fileName deleted"
                echo "file: $fileName deleted" >> $logfile
        done
else
        echo "backup file number normal."
fi