‘Polite’ deletion of older than xx days records in MySQL in bash

Sometimes deleting lots of records while having stuff coming in can cause problems due locks, following bash snippet deletes in chunks and sleeps between deletions allowing incoming data to be processed. Assumes that you have ts field as timestamp (Field: ts Type: timestamp Default: CURRENT_TIMESTAMP)

#!/bin/bash
#
# Variables here
SUCCESS=0
LOG_FILE=/var/log/db_cleaner.log
DB=my_db
COUNTER=0
CHUNK=750
TOT_RECS=0
DAYS=30
TABLE=my_table
DELAY=1m

echo "**************************************************" >> $LOG_FILE
echo -e "\nStarting cleanup: `date`\n" >> $LOG_FILE

# Find out number of records to be deleted
TOT_RECS=`echo "SELECT COUNT(*) FROM $TABLE WHERE DATE_SUB(CURDATE(), \
                INTERVAL $DAYS DAY) > ts;" | mysql | tail -1`
if [ $TOT_RECS -ne 0 ]
then
 echo "$TOT_RECS rows to be deleted in `expr $TOT_RECS / $CHUNK + 1` chunks,\
        chunk size: $CHUNK, delay: $DELAY" >> $LOG_FILE
 # Delete in chunks, COUNTER+1 to delete also last ones
 while [ `expr $TOT_RECS / $CHUNK` -ge $COUNTER  ]; do
   echo "DELETE FROM $TABLE WHERE DATE_SUB(CURDATE(),INTERVAL $DAYS DAY) >\
         ts LIMIT $CHUNK;" | mysql
   if [ $? != 0 ]
   then
     echo "DELETE failed: $? Exiting...." >> $LOG_FILE
     exit $?
   else
     echo "Chunk $COUNTER deleted" >> $LOG_FILE
 fi
 COUNTER=`expr $COUNTER + 1`
 sleep $DELAY
 done
else
 echo "Nothing to delete..." >> $LOG_FILE
fi
echo "Database cleaned...`date`" >> $LOG_FILE
exit $SUCCESS

Note that some lines are cut for layout (\)

Leave a Reply

Your email address will not be published. Required fields are marked *