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.
1 2 3 4 | mkdir /temptouch /temp/conntrack_trim.shchmod +x /temp/conntrack_trim.shnano /temp/conntrack_trim.sh |
Now paste following data, and modify accordingly
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | #!/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 DETAILSSQLUSER="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 belowexport MYSQL_PWD=$SQLPASSCMD="mysql -u$SQLUSER --skip-column-names -s -e"logger $DB Trimmer Script started $DATE , IT WILL DELETE tables from $DB older then $DAYSecho "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 Formulanum=0cat $TMPFILE | while read tablesdonum=$[$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 plzdoneecho "$DB TRIMMING script ended $DATE " |
You can now schedule it to run daily in night at 00:00 hours by editing CRONTAB
1 | crontab -e |
& add following entry
1 | /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
1 2 3 4 | mkdir /temptouch /temp/syslog_trim.shchmod +x /temp/syslog_trim.shnano /temp/syslog_trim.sh |
Now paste following data, and modify accordingly
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | #!/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 DETAILSSQLUSER="sql_user"SQLPASS="sql_password"DB="syslog"DAYS=30################################### Don't modify belowexport MYSQL_PWD=$SQLPASSCMD="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" |
