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'`

# 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++))

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

补充：数据库,mysql教程