MySQL大表归档

BASH脚本定期处理

Posted by BY lingjun on April 25, 2019 本文总阅读量

Mysql大表归档

  • 归档脚本HisDataArch.sh
#!/bin/bash
. ~/.mpd
btime=`date +"%Y-%m-%d %H:%M:%S"`
dataDir='/data/backup/'
lastMonth=`date -d "1 month ago" +%m`
pathName=$1_$lastMonth
endDate="`date -d "3 month ago" +%Y-%m`-01"
tableList=("t_billc_bill_details" "t_billc_trade_info" "t_billc_his_bill" "t_billc_pay_wechat")
 
echo .................................... Start  $btime ....................................
 
freeDiskSize=$(df /| awk '/\//{print$3}')
if [[  $freeDiskSize -le 10240000 ]]; then
    echo '磁盘空间小于10G,无法运行'
    exit 1
else
    if echo "${tableList[@]}" | grep -w "$1" &>/dev/null; then
            if [[ ! -d ${dataDir}${pathName} ]]; then
                            mkdir -p ${dataDir}${pathName}
            else
                            mv ${dataDir}${pathName} ${dataDir}${pathName}_bak$btime
                            mkdir ${dataDir}${pathName}
            fi

            if [[ $1 = "t_billc_his_bill" ]]; then
                    echo $1
                    pt-archiver --source h=$dbhost,u=$dbuser,p=$password,D=hc_bill_center,t=$1,i=index_bill_date --file ${dataDir}${pathName}'/%D.%t-%Y-%m-%d' \
                            --no-version-check --charset=UTF8 --where="bill_date<'$endDate'" --ignore --txn-size=1000 --limit=1000 --bulk-delete \
                            --progress=5000 --statistics --why-quit
                    cd $dataDir
                    tar -cvf $pathName.tar $pathName
                    rm -rf $pathName
                    #scp $pathName.tar $tarUser@$tarHost:$tarPath
                    pt-online-schema-change --user=$dbuser --password=$password --defaults-file=/etc/my.cnf --set-vars='sql_log_bin=0' --charset=utf8 --alter "ENGINE=InnoDB" D=hc_bill_center,t=$1 --execute --print --no-check-alter
            else
                    echo $1
                    pt-archiver --source h=$dbhost,u=$dbuser,p=$password,D=hc_bill_center,t=$1,i=idx_bill_date --file ${dataDir}${pathName}'/%D.%t-%Y-%m-%d' \
                            --no-version-check --charset=UTF8 --where="bill_date<'$endDate'" --ignore --txn-size=1000 --limit=1000 --bulk-delete \
                            --progress=5000 --statistics --why-quit
                    cd $dataDir
                    tar -cvf $pathName.tar $pathName
                    rm -rf $pathName
                    #scp $pathName.tar $tarUser@$tarHost:$tarPath
                    pt-online-schema-change --user=$dbuser --password=$password --defaults-file=/etc/my.cnf --set-vars='sql_log_bin=0' --charset=utf8 --alter "ENGINE=InnoDB" D=hc_bill_center,t=$1 --execute --print --no-check-alter
            fi
    else
            echo "请输入正确的参数值"
            exit 1
    fi
fi
echo .................................... End  $btime ....................................
  • 部署crontab定时执行即可
    0 3 1 * * /bin/bash /data/scripts/HisDataArch.sh t_billc_bill_details >>/data/scripts/logs/HisDataArch.log
    0 3 2 * * /bin/bash /data/scripts/HisDataArch.sh t_billc_trade_info >>/data/scripts/logs/HisDataArch.log
    0 3 3 * * /bin/bash /data/scripts/HisDataArch.sh t_billc_his_bill >>/data/scripts/logs/HisDataArch.log
    0 3 4 * * /bin/bash /data/scripts/HisDataArch.sh t_billc_pay_wechat >>/data/scripts/logs/HisDataArch.log
    
  • 备注:脚本执行过程会产生大量binlog日志,需要确保有一定的磁盘空间