0

I am not a mysql or sql expert by any means, and not too savy on FreeBSD, either. We have a server that regularly needs one table to have data deleted < a time frame that leaves only 1 month of data behind. Typical issue that the table becomes too big and then it can't write.

There are only two commands when I remote in:

mysql -h A.B.C.D -u USER -p radius

mysql> DELETE FROM calls WHERE event_date_time <  '2014-01-01 00:00:00';

mysql> optimize table calls;

I have to do this all from a jumpbox--I don't have access to the underlying os of this server. How would I set up a cronjob to do this every two months, for example?

Thanks

user202243
  • 13
  • 4
  • You want a cron job that does the job from the jumpbox, or directly on the mysql server itself? – NickW Feb 14 '14 at 15:43

1 Answers1

0

For the cron entry:

0 1 1 */2 * root /bin/optimize.sh

The above runs at 01:00 every 1st day of every second month

The script (/bin/optimize.sh):

#!/bin/bash
FIRST_DAY_OF_MONTH=$( date -d "`date`" +%Y-%m-01 )
DAY="$FIRST_DAY_OF_MONTH 00:00:00"
mysql -uUSER -pradius -h<host> -e "DELETE FROM calls WHERE event_date_time < '$DAY';"
mysql -uUSER -pradius -h<host> -e "optimize table calls;"

The above will take whatever the month is, find the first day, and delete up to that point. Obviously you can modify this to suit your needs.

NOTE: The < host > parameter needs to be filled in

Smoothie
  • 156
  • 1
  • I think I follow this, and I like it! And, I know this is stupid, but by adding the password -p radius etc... that gets around having to enter a password each time the script is run, correct? – user202243 Feb 14 '14 at 20:02