Shell script Implementation Delete data in expired database __ Database

An application system every day to insert data to the database, and for very early data need to be cleaned from the database, to prevent unlimited data increase, the cost of storage and impact data query efficiency.

Implemented through two files, one file is a configuration file and one is a script file.

This example deletes data from the database 180 days before the time can be modified in the configuration file.

Note: The table name in the example is the time when the data was inserted in the Talarm,inserttime field (content format: 2016-11-06 19:36:00)

1) configuration file: dbinfo.conf

ndate=180   #设置天数
dbuser=root     #数据库账号
dbpassword=123456  #数据库密码
dbname=testdb  #数据库名称

2 script file: deletedbdata.sh

#!/bin/bash
. dbinfo.conf #读取配置文件
 
datestr= ' date-d '-$ndate day ' +%y-%m-%d ' #生成ndate天前的日期, such as: 2016-07-19 
Delsqlstr= "DELETE from Talarm WHERE inserttime < ' $datestr '" "#SQL语句
mysql-u$dbuser-p$dbpassword $dbname-E" $del Sqlstr "  #执行SQL语句

3 Run the script file

[Root@localhost tmp]#./deletedbdata.sh