#!/bin/bash
nenv='pro数据库'
user='BakUser'
pwd="xxx"
dbname='vwork'
dbmagic='magicod'
dbfastdev='fast'
bakdir='/data/dbbak'
bakdate=`date +'%Y%m%d%H%M'`
#异机备份主机信息
bakhost="xxxx"
bakuser="xxx"
bakport=22
destdir="/data/dbbak"
test_func(){
if [ $? -ne 0 ];then
echo "`date +'%F %T'`: $1 失败" >> ${bakdir}/dbbak.log
exit 1
else
echo "`date +'%F %T'`: $1 成功" >> ${bakdir}/dbbak.log
fi
}
mkdir -p ${bakdir}
echo "`date +'%Y%m%d%H%M%S'`:开始备份数据库${dbname}" >> ${bakdir}/mysql_bak.err
##获取排除的表
tables=`mysql -u ${user} -p${pwd} -e "select ignore_table_name from ${dbname}.backup_ignore_table where in_use=1 AND database_name in ('standard',database());"`
if [[ $? -ne 0 ]];then
echo "`date +'%Y%m%d%H%M%S'`:获取ignore_table_name中的大表数据失败." >> ${bakdir}/mysql_bak.err
fi
ignore_table=''
if [[ ! -z ${tables} ]];then
for i in $tables;do
if [[ ${i} == 'ignore_table_name' ]];then
sleep 0.1
else
ignore_table="${ignore_table} --ignore-table=${dbname}.${i}"
fi
done
fi
# 备份ehr库
echo "正在备份数据库${dbname}"
rm -f ${bakdir}/res.log
mysqldump -u $user -p$pwd -f -R -E --triggers --single-transaction --master-data=2 --log-error=${bakdir}/res.log ${dbname} ${ignore_table} | gzip > ${bakdir}/${dbname}_${bakdate}.sql.gzip
grep -i error ${bakdir}/res.log > /dev/null
if [[ $? -eq 0 ]];then
echo "${nenv}备份失败"
else
echo "${nenv}备份成功"
fi
##备份magicapi库
echo "正在备份数据库${dbmagic}"
rm -f ${bakdir}/magicapi_res.log
mysqldump -u $user -p$pwd -f -R -E --triggers --single-transaction --master-data=2 --log-error=${bakdir}/magicapi_res.log ${dbmagic} | gzip > ${bakdir}/${dbmagic}_${bakdate}.sql.gzip
grep -i error ${bakdir}/magicapi_res.log > /dev/null
if [[ $? -eq 0 ]];then
echo "${dbmagic}备份失败"
else
echo "${dbmagic}备份成功"
fi
##备份fastdev库
echo "正在备份数据库${dbmagic}"
rm -f ${bakdir}/fastdev_res.log
mysqldump -u $user -p$pwd -f -R -E --triggers --single-transaction --master-data=2 --log-error=${bakdir}/fastdev_res.log ${dbfastdev} | gzip > ${bakdir}/${dbfastdev}_${bakdate}.sql.gzip
grep -i error ${bakdir}/fastdev_res.log > /dev/null
if [[ $? -eq 0 ]];then
echo "${dbfastdev}备份失败"
else
echo "${dbfastdev}备份成功"
fi
##刷新binlog
mysql -u ${user} -p${pwd} -e "flush logs;"
#异机备份
rsync -avz -e "ssh -p $bakport" ${bakdir}/*.sql.gzip $bakuser@$bakhost:$destdir
ssh -p $bakport $bakuser@$bakhost "find $destdir -name '*.sql.gzip' -mtime +30 -exec rm -f {} \; "
find ${bakdir}/ -mtime +4 -exec rm -f {} \;
Mysql 备份
最新推荐文章于 2023-03-01 11:01:38 发布