creative commons licensed ( BY-NC-SA ) flickr photo shared by maol
Frequent words I heard as a kid was reminding me of my household chore– “TAKE OUT THE TRASH!!!”
I’m finally getting around to doing it regularly.
Even with Akismet running on this blog, I have seen it accumulate more than 1000 trapped spams per day, and by the time the count hit 10k, the WordPress interface hung on how long it too to empty the spam odored trash. The only way to clean out was to jump into the database via phpMyAdmin, and manually run an SQL command.
I finally decided to set up a cron script to do this, and here is how I did it via my Reclaim Hosting cpanel (I await some technowiz to tell me how bad my approach is). I am essentially doing a command line database query to empty the comments marked as spam (the ones that Akismet IDs as spam).
To do this you will need info that is stored in your wp-config.php file on your server- they will look something like:
|
1 2 3 4 5 6 7 8 9 |
// ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define('DB_NAME', 'mygroovydatabase'); /** MySQL database username */ define('DB_USER', 'trickyusername'); /** MySQL database password */ define('DB_PASSWORD', 'X4somehiglhycryptic8password'); |
- the name of your wordpress database (below referred to as DB_NAME)
- the user name that logs into your wordpress database (DB_USER)
- a password for that database user (DB_PASSWORD)
The thing you are looking for in your cpanel is Cron Jobs down in the Advanced Section
Finding the Cron Jobs
When you add a new cron job, you need to choose how often it will run. Mine is every day at 4am; but a more reasonanle setting might be as shown below, every other day at 3am
The command is the big deal here. Mine looks like this, you will have to fill in your own values (no quotes) for DB_NAME, DB_NAME, and DB_PASSWORD (and make it all one one line, I broke mine up so you can see it)
|
1 2 3 |
mysql -u DB_USER -pDB_PASSWORD --database=DB_NAME -Bse "DELETE FROM wp_comments WHERE comment_approved = 'spam'" |
Note that there is no space after “-p” and your database password, so for my fake example above, it would look like
|
1 2 3 |
mysql -u trickyusername -pX4somehiglhycryptic8password --database=mygroovydatabase -Bse "DELETE FROM wp_comments WHERE comment_approved = 'spam'" |
So far, I am finding it is keeping the number of flagged comments to a more reasonable number.
I did get more powerful solution from Brad Emerson that essentially blacklists IPs at the server level, and am hoping to sort that out eventually.
For now, I just wanted something to keep the database from bloating from all the trash that piles up.
I do not fault anyone else for making the choice, but I refuse to turn off comments. Call me a masochist. And sometimes… sometimes.. it is mildly entertaining to see what floats through. It’s kind of like occasionally lifting your floor to scan the sludge for curiosity of how life can exist down there,
I like visiting your sight recently and many times since first finding it and the reading of all your very excellent good postings here. You typically know what you are talking about many times and others would fined this sight interesting for the reading. I am bookmark this for now and will return here in future for more of your good posting. It is hard to come by educated ppoeple in your subject area but you appear to know what you are talking about. I’m gonna watch out For brussel s Proust. and the borsch. I was at first excited and then Kitty as I went and started to catch the hoppers. As they are huge, RVs have a large and varied checklist of componentsthat you need to inspect and repair on an annual basis to make sure it is safe for the road. Share we be in contact approximately your article on AOL?
You cannot fool me with your generic spam comments, I know you are real.
Not automated, but if you’re looking for something that will work in multisite, I’ve had good luck with this script from Boone: http://teleogistic.net/2013/02/safely-delete-spam-comments-across-a-large-wp-network/
Thanks Joseph. I can see the Great One has done this more thoroughly than I (deleting comment meta is nice as is the optimize step. It would not take much to adopt this for a single site