mysql_base_del_old.sh

作者: cross 日期: 2007-06-09 00:54

字體大小: 小中大

#!/bin/bash
#
# 2007/06/09 Created by <a href=\"mailto:cross@ssorc.tw\">cross@ssorc.tw</a>
#
# Purpose: 為了清除一或三個月前的舊資料
#
# How TO: 從 event 找 timestamp 小於自訂的日期，進而求得 sid 與 cid 兩值來刪除不要的資料
DB_NAME="base"
DB_USER="base"
DB_PASS="base123456"
TIME_DEL=`date --date="1 month ago" +%F`
echo $TIME_DEL
echo "select sid,cid from event where timestamp < '$TIME_DEL%';" | mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" | grep -v sid |
while read SIDCID; do
echo " sid and cid => $SIDCID"
set -- $SIDCID
echo " sid => $1"
echo " cid => $2"
for x in `echo data event iphdr opt tcphdr`
do
echo " delete from $x where sid='$1' and cid='$2';"
echo "delete from $x where sid='$1' and cid='$2';" | mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME"
done
done