shell定时清理数据表历史数据

实现代码

一天执行一次，直接拷贝到daily。

[wangzheng@wangzheng-centos ~]$ cat archive_cron.sh 
#!/bin/bash

# Unit of interval: daily, hourly, monthly, weekly, selfly.
interval="daily"

# If you want to define the crontab by yourself, you should use these params, and make sure:
# interval="selfly".
# cron_selfly="*/1 * * * * root "
#cron_selfly=""

# Witch scheduler
sched_name="cas"
# The expire time of job.(DAY)
expire_time="30"

# DB info.
db_name="scheduler"
db_host=""
db_port=""
db_user="slurm"
# Must configurate.
db_passwd="123456"

baseDir=$(cd `dirname $0`; pwd)
archive_script="archive_job.sh"

function writeArgs() {
    sed -i -e "s/__SCHED_NAME__/${sched_name}/g" \
            -e "s/__EXPIRE_TIME__/${expire_time}/g" \
            -e "s/__DB_NAME__/${db_name}/g" \
            -e "s/__DB_HOST__/${db_host}/g" \
            -e "s/__DB_PORT__/${db_port}/g" \
            -e "s/__DB_USER__/${db_user}/g" \
            -e "s/__DB_PASSWD__/${db_passwd}/g" \
            "$1"
}

function make_cron() {
    cron_dir="/etc/cron."$interval
    cp -f ${baseDir}/$archive_script $cron_dir
    writeArgs $cron_dir/$archive_script
    chmod 600 ${cron_dir}/$archive_script
}

function writeCrontab() {
cat << EOF >$1
# Run cron task of archive job.
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root

$cron_selfly sh $2
EOF
}

function make_cron_selfly() {
    cron_dir="/etc/cron.d/"
    cron_task=${cron_dir}/"archive_job.cron"
    if [ ! -f "${cron_task}" ]; then
        touch ${cron_task}
    else
        rm -f "${cron_task}"
        touch ${cron_task}
    fi
    cp -f ${baseDir}/$archive_script ${cron_dir}
    writeArgs $cron_dir/$archive_script
    writeCrontab ${cron_task} $cron_dir/$archive_script
    chmod 700 $cron_dir/$archive_script
    chmod 600 $cron_task
}

case "$interval" in
    daily|hourly|monthly|weekly)
        make_cron
    ;;
    *)
        make_cron_selfly
esac

[wangzheng@wangzheng-centos ~]$ cat archive_job.sh 
#!/bin/bash

# This script is used to archive job history records.
# Needs 7 Args:
# sched_name: the name of scheduer.
# expire_time: the expire time(DAY), default value:30.
# db_name: the name of mysql database, defalut value:scheduler.
# db_host: the ip of mysql db, default value:localhost.
# db_port: the port of mysql, default value:3306.
# db_user: the user of $3, default value:root.
# db_passwd: the password of $6.

# Witch scheduler
sched_name=__SCHED_NAME__
# The expire time of job.
expire_time=__EXPIRE_TIME__

# DB info.
db_name=__DB_NAME__
db_host=__DB_HOST__
db_port=__DB_PORT__
db_user=__DB_USER__
db_passwd=__DB_PASSWD__

# CAS table.
cas_job_table="cas_chess_job_table"

# Slurm table.
slurm_job_table="chess_job_table"
slurm_app_table="slurm_app_jobs"

# Default value.
[ -n "$expire_time" ] || expire_time="30"

[ -n "$db_name" ] || db_name="scheduler"

[ -n "$db_host" ] || db_host="localhost"

[ -n "$db_port" ] || db_port="3306"

[ -n "$db_user" ] || db_user="root"

# Archive cas history records.
function archive_cas_job(){
    expire_date=`date -d "$expire_time days ago" +"%Y-%m-%d %H:%M:%S"`
    status_clause=" WHERE JobStatus IN ('DONE', 'EXIT')"
    time_clause=" AND  EndTime <= '$expire_date'"
    archive_db `makeTableName $cas_job_table` "$status_clause" "$time_clause"
}

# Archive slurm history records.
function archive_slurm_job() {
    expire_unix_time=`date -d "1 days ago" +%s`
    state_clause="WHERE state IN ('3', '4', '5', '6', '7', '9', '10', '11')"
    time_clause=" AND time_end <= '$expire_unix_time'"
    archive_db `makeTableName $slurm_job_table` "$state_clause" "$time_clause"
    archive_db `makeTableName $slurm_app_table` "$state_clause" "$time_clause"
}

# Connect to mysql and exec sql.
function archive_db(){
mysql -h${db_host} -P${db_port} -u${db_user} -p${db_passwd} ${db_name} <<EOF >/dev/null 2>&1
INSERT INTO $1 SELECT * FROM $2 $3 $4;
DELETE FROM $2 $3 $4;
EOF
}

# Echo two table name. The table named *_history is used to archive history records.
function makeTableName() {
    echo $1"_history" $1
}

# Main
if [ "$sched_name" == "cas" ]; then
    archive_cas_job
elif [ "$sched_name" == "slurm" ]; then
    archive_slurm_job
fi