centos 数据库定期备份

备份脚本 week-mysql-backup.sh

#! /bin/bash
#dump命令路径
dump=/usr/bin/mysqldump
logFile=/data/crontab-mysql/week-bak.log
# MySQL用户
user="root"
# MySQL密码
userPWD="mysql"
# host
host="172.200.147.41"
#port
port="3301"
# 需要定时备份的数据表列表
#dbNames=(dtsx_core dtsx_file dtsx_smp dtsx_smp_egov dtsx_ucap dtsx_unid egov_core hoox_console hoox_demo hoox_evaluation hoox_general hoox_model_foshan hoox_mtc hoox_smp hoox_smp_operation)
# 每次的备份数据以日期创建文件夹存放，同时删除过期备份
# 此处以保留30天的备份数据
DATE=`date -d "now" +%Y%m%d%H`
ODATE=`date -d "-30 day" +%Y%m%d%H`
newdir=/data/crontab-mysql/week/$DATE
olddir=/data/crontab-mysql/week/$ODATE
echo `date +"%Y年%m月%d日 %H:%M:%S"`"开始备份数据:172.200.147.41-表单中心-MySQL" >> $logFile
# 创建新备份文件夹
mkdir -p $newdir
# 对备份数据库列表的所有数据库备份
#for dbName in ${dbNames[*]}
#do
#  dumpFile=$dbName-$DATE.sql.gz
#  $dump -h$host -P$port -u$user -p$userPWD $dbName | gzip > $newdir/$dumpFile
#done

# 整库备份
dumpFile=wform_schema-$DATE.sql.gz
$dump -h$host -P$port -u$user -p$userPWD --databases wform_schema -c --hex-blob --default-character-set=utf8 | gzip > $newdir/$dumpFile

#开始远程备份172.200.147.58:/data/bakup/mongodb_hoox-smp/week
sourcepath=/data/crontab-mysql/week
targetpath=/data/backup/mysql_well-form/week
logpath=/data/jobLOG/job.log
themename=well-form:172.200.147.41
echo "`date +%Y%m%d` start $themename upload......"
echo "`date +%Y%m%d` start $themename upload......" >> $logpath

sleep 10

password='1qaz@WSX'

echo "start copy datafile from remote path..."
echo "start copy datafile from remote path..." >> $logpath

for file in $sourcepath/$DATE/*
do
    if test -f $file
    then
        arr=$file
/usr/bin/expect <<EOF
        set timeout 5
        spawn scp -P 22 -r $arr root@172.200.147.42:$targetpath
        send "yes\r"
        expect "*assword:"
        send "$password\r"
        send "exit\r"
        expect eof
EOF
    fi
done

if [ $? = 0 ]; then
        echo "datafile copy successful!"
        echo "datafile copy successful!" >> $logpath
else
        echo "datafile copy failed!"
        echo "datafile copy failed!" >> $logpath
fi
#结束远程备份

# 删除前30天过期备份数据
if [ -d $olddir ];
  then
    rm -rf $olddir
fi
echo `date +"%Y年%m月%d日 %H:%M:%S"`"结束备份数据:172.200.147.41-表单中心-MySQL" >> $logFile
echo `date +"%Y年%m月%d日 %H:%M:%S"`"Bakup succ!" >> $logFile
exit

注意事项

1. yum install expect

2.先创建指定登入 备份目录，远程传输目录

3. 修改文件里传输IP 地址， 数据库指定 data 地址

4.说明

周备份修改，执行整库备份（默认脚本执行 指定表备份）

5. 日备份为测试