Linux shell scripts automatically delete data for the first 3 months of MySQL tables

RDS has a database table Tbl_online table with 60 million data that needs to be cleaned up and retained for 3 months.

The steps are as follows:

1. Create a monthly table

2. Write data that needs to be deleted to the monthly table

3. Delete data

4. Optimizing tables
   

The shell is as follows:

#!/bin/bash

Base= "Rdsxx.mysql.rds.aliyuncs.com"

#前3个月的日期

months_ago= ' date-d '-3 month "+"%y-%m-%d "'

#前3个月的月份, remove the previous 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 "_$m Onths_ago_mon like Tbl_online; "

echo "$statime start inserting" $months _ago "data" >>/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 Insert data Complete" >>/opt/tbl_online.log

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

Statime= ' Date +%y-%m-%d ' "%h:%m:%s '

echo "$statime start deleting data and Tuning tables" >>/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 Delete and optimize table completion" >>/opt/tbl_online.log

You can use the final task plan bar

This article is from the "Falling Star" blog, make sure to keep this source http://xiao987334176.blog.51cto.com/2202382/1703300

Linux shell scripts automatically delete data for the first 3 months of MySQL tables