Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
You want a cron job that does the job from the jumpbox, or directly on the mysql server itself? –  NickW Feb 14 at 15:43

1 Answer 1

up vote 0 down vote accepted

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.