Asterisk database housekeeping

A simple script of asterisk database cleanup.

!! Be careful you have free disk space equal to the size of the database. That need for defragmentation of the database.

 UPD.  I added a check of free disk space

Create a script file in the folder /home/asterisk/

touch /home/asterisk/housekeeping.sh

#!/bin/bash

MYSQL_USER="user" # MySQL user
MYSQL_PASSWORD="password" # MySQL password
KEEP_DAYS="180" # Days
EMAIL="test@shrainer.me" # email to send notification
EMAILFROM="pbx@shrainer.me" # sender email
DFUSE=`df | grep /dev/sda1 | awk '{print $5}' | sed '{s/.$//;}'`


if [ $DFUSE -lt 47 ]
then

if ps aux | grep "[m]ysql" > /dev/null
then
 mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e"delete from asteriskcdrdb.cdr where datediff(now(), calldate) > $KEEP_DAYS;"
 mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e"delete from asteriskcdrdb.cel where datediff(now(), eventtime) > $KEEP_DAYS;"

 if ! mysqlcheck -u$MYSQL_USER -p$MYSQL_PASSWORD --optimize asteriskcdrdb; then
  /usr/local/bin/sendEmail -f $EMAILFROM -t $EMAIL -o message-charset=utf-8 -u "Housekeeping DB" -m "Error housekeeping asteriskcdrdb" -s smtp.example.com
  exit 0
 fi
fi
fi
else
/usr/local/bin/sendEmail -f $EMAILFROM -t $EMAIL -o message-charset=utf-8 -u "Housekeeping DB" -m "No free space to housekeeping asteriskcdrdb" -s smtp.example.com
fi

Do not forget set access rights to the file

chmod +x /home/asterisk/housekeeping.sh

And add it to the cron on a daily execution

17 01 * * * root /bin/bash /home/asterisk/housekeeping.sh > /dev/null 2>&1

Ваш дедушка/