Mysql 备份

最新推荐文章于 2023-03-01 11:01:38 发布

东方-phantom

最新推荐文章于 2023-03-01 11:01:38 发布

阅读量453

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/hkyw000/article/details/127534124

版权

mysql 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

#!/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 {} \;