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

2024-07-24 12:37:13

字体：大中小

来源：转载

供稿：网友

在linux中实现mysql自动备份很简单,我们可以直接使用计划任务与与相关的任务来实现自动备份与恢复了,下面小编来给大家详细介绍一下具体过程.

mysql 数据库表自动备份shell 脚本,调用示例,代码如下:

mysql_data_backup.bash 110 www_cas

mysql_data_backup.bash 112 www_qiche

mysql_data_backup.bash 112 www_health

包含功能:

1.参数检测,2.生成备份日志,3.自动检测指定数据库中的表,单独备份每个表,最后打包为tar文件,4.单独备份每个表,方便恢复用,5 支持多个数据主机判断

以下为文件内容,代码如下:

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

数据恢复可以使用批处理来恢复数据表,G:database20110324 此目录放置需要恢复的表sql文件,main.bat restore.bat 均放置在此目录,点击 main.bat 即可开始备份,每执行完一个文件会暂停,按任意键可以继续.

绿色背景蓝色文字部分需要根据需要修改.

共2处,第一处为 sql 文件路径,第二处为数据库名称

main.bat内容如下:

@echo off

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

restore.bat 内容如下:

@echo off

pause

echo 文件 %1 开始还原

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

echo 文件 %1 完成还原

echo .

echo .

echo .