Bookmark and Share
how to archive history data in mysql database
(Publish Date: 2009-6-10 1:14pm, Total Visits: 1942, Today: 1, This Week: 1, This Month: 1)

1. crontab job

#####archive two tables every two months######
0 0 1 2,4,6,8,10,12 * /root/scripts/archive/archive.sh 2>&1

2. the content of archive.sh

#!/bin/bash

y=`/bin/date +%Y`
m=`date +%m`
let "m=10#$m"
let "m -=1"
if [ $m -ge 10 ]; then
        DATE=$y"_"$m
        KEEP_DATE=$y"_"$m"_26"
else
        DATE=$y"_0"$m
        KEEP_DATE=$y"_0"$m"_26"
fi
mysql -usupport -pxxxxx -e "use test; create table search_terms_new like search_terms; rename table search_terms to search_ter
ms_$DATE; rename table search_terms_new to search_terms; insert into search_terms (creation_date, search_terms, site_id, ur_id, repo
rt_date) select creation_date, search_terms, site_id, ur_id, report_date from search_terms_$DATE where creation_date >= '$KEEP_DATE'
; "
mysql -usupport -pxxxxx -e "use test; create table cross_sell_transactions_new like cross_sell_transactions; rename table cros
s_sell_transactions to cross_sell_transactions_$DATE; rename table cross_sell_transactions_new to cross_sell_transactions;"