RDS有一個數據庫表tbl_online表，有6千萬數據，需要清理，保留3個月。

步驟如下:創建月表

將需要刪除的數據寫入月表

刪除數據

優化表

shell如下:

#!/bin/bash

base="rdsxx.mysql.rds.aliyuncs.com"

#前3個月的日期

months_ago=`date -d "-3 month" +"%Y-%m-%d"`

#前3個月的月份,去掉前面的0

months_ago_mon=`date -d "-3 month" +"%-m"`

#前3個月年份

years_ago_year=`date -d "-3 month" +"%-Y"`

#+++++++++++++++++++++++++++++++++++++

statime=`date +%Y-%m-%d" "%H:%M:%S`

echo "############################" >> /opt/tbl_online.log

#創建月表

/usr/local/mysql/bin/mysql -u root -h $base -e "use kd_shop;create table if not exists tbl_online_bak_"$years_ago_year"_$months_ago_mon like tbl_online;"

echo "$statime開始插入"$months_ago"數據" >> /opt/tbl_online.log

/usr/local/mysql/bin/mysql -u root -h $base -e ?"use kd_shop;insert into tbl_online_bak_"$years_ago_year"_$months_ago_mon select * from tbl_online where create_time >= '$months_ago 00:00:00' and create_time <= '$months_ago 23:59:59';"

statime=`date +%Y-%m-%d" "%H:%M:%S`

echo "$statime插入數據完成" >> /opt/tbl_online.log

#++++++++++++++++++++++++++++++++++++++

statime=`date +%Y-%m-%d" "%H:%M:%S`

echo "$statime開始刪除數據以及優化表" >> /opt/tbl_online.log

/usr/local/mysql/bin/mysql -u root -h $base -e "use kd_shop;delete from tbl_online where create_time >= '$months_ago 00:00:00' and create_time <= '$months_ago 23:59:59';optimize table tbl_online;"

statime=`date +%Y-%m-%d" "%H:%M:%S`

echo "$statime刪除以及優化表完成" >> /opt/tbl_online.log

最后任務計劃條用即可