Syed Jahanzaib Personal Blog to Share Knowledge !

July 29, 2021

MySQL: DROP tables older than X Period using BASH Script



Notes for Self:

Following script can delete single or multiple table older than X time from the mysql DB. It was pretty useful for DMASOFTLAB RADIUS MANAGER CONNTRACK table OR customized  SYSLOG-NG logging system, where table is daily created automagically in database using current date using YYYY_MM_DD format (dates have underscore sign). There are other solutions as well like creating procedure etc, but since this was older MySQL version , therefore I took this route.


1# FOR DMASOFTLAB RADIUS MANAGER

DMASOFTLAB Radius manager have its own connection tracking module which stores date wise table to store data. (YYYY-M-DD format for table), & deleting it using bash script is not possible because older versions gives syntax error, therefore we had to wrap the table name in BACKQUOTE.

Also most importantly, if we delete tables older then x period, then there is a table that dma creates to hold the conntrack details called tabxid, & eventually with date criteria, it will be deleted too, therefore I EXEMPTED in the mysql statement so that it should remain safe else conntrack table will not work.

mkdir /temp
touch /temp/conntrack_trim.sh
chmod +x /temp/conntrack_trim.sh
nano /temp/conntrack_trim.sh

Now paste following data, and modify accordingly

#!/usr/bin/env bash
####!/bin/sh
#set -x
#################
# Script to delete TABLES OLDER THEN X period from particular DB
# Made for syslog-ng to save disk space
# Created on: 2019
# Last Modified: 10-SEP-2021
# Syed Jahanzaib / aacable at hotmail dot com / aacable.wordpress.com
#################
#### MODIFY BELOW
#MYSQL DETAILS
SQLUSER="SQL_ID"
SQLPASS="SQL_PASSWORD"
DB="conntrack"
DATE=`date +'%Y-%m-%d'`
# You can change the months to days also by
#DAYS="30 DAYS" (or maybe syntax is DAY)
DAYS="3 MONTH"
TMPFILE="/tmp/conntrack_tables_list-$DATE"
#################
#################
# Don't modify below
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
logger $DB Trimmer Script started $DATE , IT WILL DELETE tables from $DB older then $DAYS
echo "syslog_ng script started $DATE , IT WILL DELETE tables from $DB older then $DAYS"
# This is one time step.
echo " Script Started @ $DATE "
# --- Now Delete $DEL_TABLE TABLE from $DB table ...
$CMD "SELECT create_time FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '$DB' AND table_name NOT LIKE '%tabidx%' AND create_time < NOW() - INTERVAL $DAYS;" | awk '{print $1}' > $TMPFILE
# Apply Count Loop Formula
num=0
cat $TMPFILE | while read tables
do
num=$[$num+1]
TABLE_TO_DELETE=`echo $tables`
# In below CMD , I wrapped the backquote, it took an hour to sort this issue that DASH - sign is not supported in DB/table for older mysql version)
$CMD "use $DB; DROP TABLE \`$TABLE_TO_DELETE\`;"
DATE=`date +'%Y-%m-%d'`
echo "$DB_TRIMMING script deleted TABLE $TABLE_TO_DELETE FROM $DB , confirm it Please. Jz"
logger $DB TRIMMING script ENDED $DATE , $TABLE_TO_DELETE got deleted $DB , confirm it plz
done
echo "$DB TRIMMING script ended $DATE "

You can now schedule it to run daily in night at 00:00 hours by editing CRONTAB

crontab -e

& add following entry

/temp/conntrack_trim.sh

Save & Exit.


2# FOR SYSLOG-NG, to delete SINGLE table created 30 days before – JUNK TEST CODE

Syslog-NG generally creates tables with underscore _ sign, therefore I modified the script as per below

mkdir /temp
touch /temp/syslog_trim.sh
chmod +x /temp/syslog_trim.sh
nano /temp/syslog_trim.sh

Now paste following data, and modify accordingly

#!/usr/bin/env bash
####!/bin/sh
#set -x
# Script to delete XX days older single table from particular DB
# Made for syslog-ng to save disk space
# Syed Jahanzaib / aacable at hotmail dot com / aacable.wordpress.com
#################
#################
#### MODIFY BELOW
#MYSQL DETAILS
SQLUSER="sql_user"
SQLPASS="sql_password"
DB="syslog"
DAYS=30
#################
#################

# Don't modify below
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
DATE=`date +'%Y-%m-%d'`
DEL_TABLE=`date +'%Y_%m_%d' -d "$DAYS day ago"`
logger syslog_ng script started $DATE , IT WILL DELETE $DEL_TABLE TABLE FROM $DB
# This is one time step.
echo " Script Started @ $DATE "
# --- Now Delete $DEL_TABLE TABLE from $DB table ...
$CMD "use $DB; SHOW TABLES;"
$CMD "use $DB; DROP TABLE $DEL_TABLE;"
DATE=`date +'%Y-%m-%d'`
logger TABLE_TRIMMING script ENDED $DATE , $DEL_TABLE TABLE FROM $DB deleted, confirm it plz
$CMD "use $DB; SHOW TABLES;"
echo "TABLE_TRIMMING  script ENDED at $DATE , $DEL_TABLE TABLE FROM $DB deleted, confirm it Please. Jz"

Regard’s
Syed JAHANZAIB

1 Comment »

  1. Hi need scripting service.

    Like

    Comment by Baba-Musah Mohammed — August 16, 2021 @ 5:45 PM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: