linux unix 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