linux mysql 數(shù)據(jù)庫(kù)自動(dòng)備份的設(shè)置方法

2024-07-24 12:37:13

字體：大中小

供稿：網(wǎng)友

在linux中實(shí)現(xiàn)mysql自動(dòng)備份很簡(jiǎn)單,我們可以直接使用計(jì)劃任務(wù)與與相關(guān)的任務(wù)來(lái)實(shí)現(xiàn)自動(dòng)備份與恢復(fù)了,下面小編來(lái)給大家詳細(xì)介紹一下具體過(guò)程.

mysql 數(shù)據(jù)庫(kù)表自動(dòng)備份shell 腳本,調(diào)用示例,代碼如下:

mysql_data_backup.bash 110 www_cas

mysql_data_backup.bash 112 www_qiche

mysql_data_backup.bash 112 www_health

包含功能:

1.參數(shù)檢測(cè),2.生成備份日志,3.自動(dòng)檢測(cè)指定數(shù)據(jù)庫(kù)中的表,單獨(dú)備份每個(gè)表,最后打包為tar文件,4.單獨(dú)備份每個(gè)表,方便恢復(fù)用,5 支持多個(gè)數(shù)據(jù)主機(jī)判斷

以下為文件內(nèi)容,代碼如下:

#!/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  //Vevb.com

else

        echo "backup file number normal."

fi

數(shù)據(jù)恢復(fù)可以使用批處理來(lái)恢復(fù)數(shù)據(jù)表,G:database20110324 此目錄放置需要恢復(fù)的表sql文件,main.bat restore.bat 均放置在此目錄,點(diǎn)擊 main.bat 即可開始備份,每執(zhí)行完一個(gè)文件會(huì)暫停,按任意鍵可以繼續(xù).

綠色背景藍(lán)色文字部分需要根據(jù)需要修改.

共2處,第一處為 sql 文件路徑,第二處為數(shù)據(jù)庫(kù)名稱

main.bat內(nèi)容如下:

@echo off

for %%b IN (./*.sql) DO @restore.bat G:database20110324%%b

restore.bat 內(nèi)容如下:

@echo off

pause

echo 文件 %1 開始還原

mysql -h localhost -u root -t database1 --default-character-set=utf8 -e "source %1"

echo 文件 %1 完成還原

echo .

echo .

echo .