Purge spam comments in WordPress from the DOS command line

WordPress comment spam is a known issue and there is a stack of ways to identify and prevent that spam from appearing under an article. I, and millions of others, use Akismet to identify spam comments and it works great.

The problem is, what do I do with all the comments flagged as spam that still appear in the comments control panel?

  1. Use the Control Panel
In the WordPress control panel, go to Comments, click on Spam and then click Empty Spam

The problem with this method is that if you grow lax in your site maintenance, spam comments will add up and can potentially cause timeouts or web server instability when deleting hundreds or thousands of comments.

2. Use a Plugin

I hear that a plugin called Batch Comment Spam Deletion does a decent job of deleting plugins. I’ve never used it, but I include it as a reminder that “if it’s a thing, someone has probably written a WordPress plugin for it.”

3. Execute a script directly against the database

This method is my personal favorite, as it’s a set it and forget it task. It’s unsubtle, as it simply deletes any comment tagged “spam” from the back-end database regardless of it’s legitimately spam or not, but it works great in my case.

I have a utility server set up that has access to the AWS Aurora MySQL database powering this blog, so on that server I install the MySQL Command line utilities.

I create an answer file containing the following information:

host=<Aurora MySQL cluster name>.rds.amazonaws.com
user=<user with read/write privileges on the server>

and a batch file like this:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql" --defaults-extra-file=MyAnswerFile <mydatabase> -e "DELETE FROM wp_comments WHERE comment_approved = 'spam'"

I set up a scheduled task to run this script once a day, and never bother with comment spam again.

Leave a Comment

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