mysql数据库异地备份脚本

心已赠人 2022-03-17 02:40 318阅读 0赞

mysql数据库存储在一个机器下，风险成本太高，需要做好异地备份，降低数据风险，以下是数据库异地备份的脚本

#!/bin/bash
    precheck () {
    n=0
    while [ $n -le 60 ]
    do
    ${mysqlhome}/bin/mysql -P ${myport} -u ${myuser} -p${mypass} -h 10.3.51.203 << EOF > In_use.tmp
    show OPEN TABLES where In_use > 0;
    exit
    EOF
    reto=$?
    if [ "`cat In_use.tmp`" != "" -a $reto -eq 0 ]
    then
    sleep 30
    if [ $n -lt 60 ]
    then
    let n=$n+1
    echo "retry $n"
    cat In_use.tmp
    continue
    else
    echo "give up after 60 times retry"
    exit
    fi
    else
    echo "clear, start backup now"
    break
    fi
    let n=$n+1
    sleep 30
    done
    }
    
    dat=`date +%Y-%m-%d`
    day=`date +%d`
    month=`date +%Y%m`
    datime=`date +'%Y-%m-%d %H:%M:%S'`
    bkhome=/home/mysqlbackup
    mysqlhome=/usr/local/mysql
    #!/bin/bash
    precheck () {
    n=0
    while [ $n -le 60 ]
    do
    ${mysqlhome}/bin/mysql -P ${myport} -u ${myuser} -p${mypass} -h 10.3.51.203 << EOF > In_use.tmp
    show OPEN TABLES where In_use > 0;
    exit
    EOF
    reto=$?
    if [ "`cat In_use.tmp`" != "" -a $reto -eq 0 ]
    then
    sleep 30
    if [ $n -lt 60 ]
    then
    let n=$n+1
    echo "retry $n"
    cat In_use.tmp
    continue
    else
    echo "give up after 60 times retry"
    exit
    fi
    else
    echo "clear, start backup now"
    break
    fi
    let n=$n+1
    sleep 30
    done
    }
    
    dat=`date +%Y-%m-%d`
    day=`date +%d`
    month=`date +%Y%m`
    datime=`date +'%Y-%m-%d %H:%M:%S'`
    bkhome=/home/mysqlbackup备份地址
    mysqlhome=/usr/local/mysql安装地址
    mybin=${mysqlhome}/bin
    bkdir=${bkhome}/mysql_${dat}_bk
    myuser=root用户名
    mypass=XXXXX密码
    myport=3306
    date
    rm -rf $bkdir
    mkdir $bkdir
    echo ${datime} > ${bkdir}/bktime.t
    
    while read db
    do
    mkdir ${bkdir}/${db}
    precheck
    ${mybin}/mysqldump -P ${myport} -u ${myuser} -p${mypass} -h 10.3.51.203 --opt --hex-blob --force --single-transaction --master-data=2 -R $db > $bkdir/${db}/${db}.sql
    grep "CHANGE MASTER TO" ${bkdir}/${db}/${db}.sql | sed 's/-- CHANGE MASTER TO //g' | sed 's/, /;\n/' > ${bkdir}/${db}/position
    /bin/gzip $bkdir/${db}/${db}.sql
    date
    echo "backup $db is done."
    done << Eaf
    数据库1
    数据库2
    数据库3
    Eaf
    
    if [ $day -eq 28 ]
    then
    cp -r ${bkhome}/mysql_${dat}_bk ${bkhome}/month_bk_${month}
    fi