mysql_base_del_old.sh

#!/bin/bash
#
# 2007/06/09 Created by [email protected]
#
# 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