brock

Delete Comment Spam using a Cron Job

23 May 2009

WordPress catches all of the comment spam, but it doesn't seem to delete it often enough. If you have a high traffic site, this can become megabytes in your database. Bleh. I don't have a high traffic site, but I sync my databases off-site everyday and spam wastes my bandwidth. So I delete all the comments marked as spam automatically each day using a scheduled cron job so that I never even have to see it. Could I possibly delete a real comment? Yes. Does the convenience of never even knowing about comment spam outweigh the risk? You bet.

Create a bash script in your scripts folder by typing this at the command prompt:

sudo pico /home/brockangelo/scripts/del_spam.sh

sudo: elevate privileges
pico: use your editor of choice
/home.../scripts/: wherever you keep all your bash scripts
del_spam.sh: name it something obvious

By the way, bash scripts are as easily made as windows batch files. You just put in the code and put a .sh at the end. Inside "del_spam.sh" add the following:

mysql -u username -ppassword -e "delete from wp_comments where comment_approved='spam';" brockangelo

Where "brockangelo" is the name of the database. Repeat this line for each WordPress site you host. Then you need to set this up as a cron job and schedule it for once a night. Or, you can download my plugin that deletes spam daily. Delete Spam Daily plugin.

By the way, the username and password format in that command you see above looks wrong, but it is typed correctly. You put "-u (space) username" then you squish the "-p" and "password" together like this "-u username -ppassword".