linux mysql 数据库自动备份的设置方法

mysql_data_backup.bash 110 www_cas
mysql_data_backup.bash 112 www_qiche
mysql_data_backup.bash 112 www_health

 #!/bin/bash

# check host
if [ ! $1 ];then
echo 'usage: mysql_data_backup [hostID] dbname'
exit
else
if [ $1 -eq 110 ]; then
db_host=192.168.1.110
db_user=xxxx
db_passwd=xxx

elif [ $1 -eq 112 ]; then
db_host=192.168.1.112
db_user=xxx
db_passwd=xxx

else
echo 'Invalid Host ID'
exit
fi
fi

# check database name
if [ ! $2 ];then
echo 'usage: mysql_data_backup hostID [dbname]'
exit
else
echo 'Backup begin'
fi

begin_time=`date '+%s'`
db_name=$2

# get table info
tables_content=`mysqlshow -u $db_user -p$db_passwd -h $db_host $db_name | sed 's/|//g' | sed 's/ //g' | sed '1,4d' | sed '$d'`

# check content if emptyed
tables_content_check=`echo -n $tables_content > /tmp/mysql_backup_check.tmp`

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

# plan backup of sql tables
tables=(${tables_content})

# get tables length
tables_len=${#tables[*]}

# backup file(tar) number
backupFileNum=3

# backup of directory Do not bring /
backupDir="/backup1/sqldata/$db_name"

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

# log file
logfile='/backup1/sqldata/backup.log'

# 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 $tables_len tables."
for ((i=0;i<$tables_len;i++))
do
tableName="${tables[$i]}"
fileName="${tables[$i]}.sql"
filePath="$fileDir/$fileName"
echo "Table [${tables[$i]}] Backup ..."
mysqldump -e -h $db_host -u $db_user -p$db_passwd $db_name $tableName > $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 Database [${db_name}] : 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

@echo off
pause
echo 文件 %1 开始还原
mysql -h localhost -u root -t database1 --default-character-set=utf8 -e "source %1"
echo 文件 %1 完成还原
echo .
echo .
echo .