No one likes spam. Akismet has been doing a great job in keeping WordPress blogs clean from spam comments. Thousands of spam comments per hour can increase the overload in the database. You know what I mean, if your site is famous and yet if you are running on a server with limited resources. You may want to make sure your site’s database is as lean as possible. Ideally, you’d want to clean the spam from the WordPress as often as possible. An hourly / nightly / weekly cleaning is what you’d probably need. By default, Akismet purges old spam comments that are over 30 days old. Here’s a little script that is triggered by a cron job and runs on the server side, thus reducing the overhead on WordPress to make it faster. Since, this little script can increase the performance (to a tiniest extend), you may choose this, instead of waiting for Akismet to clear spam after 30 days.
Early Spam Purge Script
#!/bin/bash
# Author: Pothi Kalimuthu (@pothi)
# please change path to wp-config.php file in WP_CONFIG_PATH accordingly without trailing slash
WP_CONFIG_PATH="/home/username/public_html/wp"
# Find the credentials to use later
WPPASS=`/bin/sed "s/[()',;]/ /g" ${WP_CONFIG_PATH}/wp-config.php | /bin/grep DB_PASSWORD | /bin/awk '{print $3}'`
WPUSER=`/bin/sed "s/[()',;]/ /g" ${WP_CONFIG_PATH}/wp-config.php | /bin/grep DB_USER | /bin/awk '{print $3}'`
WPDB=`/bin/sed "s/[()',;]/ /g" ${WP_CONFIG_PATH}/wp-config.php | /bin/grep DB_NAME | /bin/awk '{print $3}'`
# Find the table prefix
WPPREFIX=`/bin/sed -n "/table_prefix/ s/'\(.*\)';/\1/p" ${WP_CONFIG_PATH}/wp-config.php | /usr/bin/awk '{print $3}'`
$(which mysql) -u${WPUSER} -p${WPPASS} ${WPDB} -e "DELETE FROM ${WPPREFIX}comments WHERE ${WPPREFIX}comments.comment_approved = 'spam';"
exit 0
Explanation
- You supply the path to wp-config.php file.
- The scripts extracts the name of the database, the database user, and the password from that file.
- It also extracts the table prefix used.
- Finally, it runs a simple SQL statement to remove the spam comments from the database.
There is hardly anything that can go wrong with this script. Yet, I recommend testing this script before running it as a cron job. You may test this script, if you have access to your server via SSH. Alternatively, please test it in a development environment. Never never never trust any script that you don’t understand.
Got any questions? Please feel free to comment or contact me.