linux unix mysql 數據庫自動備份

文章給大家介紹在linux unix中利用shell命令實現mysql 數據庫自動備份腳本代碼，有需要了解的朋友只需簡單修改一下即可使用了。

文件名 mysql_data_backup.sh (需要執行權限)

mysql 數據庫表自動備份shell 腳本 (第二版)

調用示例:

 代碼如下 復制代碼

mysql_data_backup.sh

包含功能：

1.生成備份日志

2.自動檢測指定數據庫中的庫列表,單獨備份每個庫,最後打包為tar文件

3.可指定備份存檔文件(.tar) 保留的數目, 按時間正序清理以前的存檔

4. 恢復時可以登錄 mysql 以後直接用 source xxx.sql 自動刪除和創建數據庫,並還原數據.

需要修改的變量：

 代碼如下 復制代碼

db_host  //數據庫主機

db_user //數據庫帳號

db_passwd //數據庫密碼

backupFileNum //保留的備份存檔文件數目,超出該數目自動清理

backupDir //備份文件存放的目錄

logfile //日志文件路徑

----------------------------------------

如何恢復數據:

參見 mysql 數據庫表自動備份shell 腳本（第一版）

 代碼如下 復制代碼

#!/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