One of my other sites, Vision of Earth, has done well enough over the years that it has attracted attention from spammers. The problem I've been facing recently was that tens of thousands of "people" were subscribing to the site, receiving welcome emails, and attempting to post comments.
I use Akismet, so these comments weren't actually getting through to the blog itself, but their sheer number made them a problem.
First, auto-delete spam comments
This section assumes that you have the Akismet WordPress Plugin installed. Since it comes by default with most installations of WordPress, I think this is the easiest approach that is also very effective.
To make Akismet automatically delete (most) spam comments, go to Settings -> Akismet and click the option that says "Silently discard the worst and most pervasive spam so I never see it." Once you do that, Akismet will start deleting the vast majority of the spam you get. In my case, the number appears to be over 90%.
If 90% isn't good enough for you, or you'd like assurance that every spam comment is deleted, I suggest that you use Spam Comments Cleaner. The setup is ridiculously simple and intuitive, and I can verify that it indeed works.
Second, stop spammers from registering
There are a variety of plugins out there that will help you with this. The one I decided to go with today was Stop Spammers. When you install it, you can optionally set it up to link up with StopForumSpam, Project Honey Pot, and BotScout via API keys (free). This was a pretty easy process, and one that I'd recommend for the future health of the Internet. By linking up with these tools, we both empower them and empower ourselves.
Third, clean spammers/sploggers out of the database
Warning: I didn't find an easy way to do this. The plugins WangGuard and Inactive User Deleter purport to be able to automatically delete these accounts. I tried both of them and they both failed. For my tests, I had about 46 thousand inactive/spam accounts on my server.
WangGuard can't batch delete more users than you can view at one time on the "Users" menu in WordPress. If I raise the number of viewable Users (by clicking "Screen Options" in the top-right) to more than a few hundred, the plugin fails with an out-of-memory problem.
Inactive User Deleter purports to be able to filter users, but it's selective power is very weak and it also crashes with an out-of-memory error when I try to run it.
I, unfortunately, had to do this manually. Luckily I know what I'm doing in a MySQL database and was able to find enough help online to finish the task. If you want to follow the rest of this guide, you'll need to have command-line access to your server. Similar actions might be possible on various server management systems, but I won't deal with them here.
Before I could start deleting accounts, I needed to be absolutely sure that I was going to be able to:
- recover from this effort in case it fails and
- delete only those Users that I specifically want to delete.
Make a backup of certain database tables
On the command line, I ran:
mysqldump <wordpress_database_name> wp_users wp_usermeta -u <wordpress_username> -p > ~/2014-10-05-database_cleanup_backup_of_users_and_usermeta.sql
The intent of this command is to backup the wp_users and wp_usermeta tables from the <wordpress_database_name> database. For me this created a 28 megabyte file. Note that the '<' and '>' symbols shouldn't be present in your command (except for the final one after '-p').
If you don't know your <wordpress_database_name> or <wordpress_username> (or its associated password, which you'll need to type in after you run the command), you can find them in the wp-config.php file in the base directory of your WordPress installation.
You will see lines like:
define('DB_NAME', 'mydatabasename'); // The name of the database define('DB_USER', 'mydbusername'); // Your MySQL username define('DB_PASSWORD', 'mypassword123abc'); // ...and password
You'll need all of these pieces of information in the next section as well.
Note that you can restore these tables by running:
mysql <wordpress_database_name> -u <wordpress_username> -p < ~/2014-10-05-database_cleanup_backup_of_users_and_usermeta.sql
Beware, if you do this at a later date, you'll destroy any information that was added to these tables since you made the backup. This is a backup that you don't want to have to use in the distant future. You can use more advanced MySQL queries to load these things without losing what you have, but that is far beyond the scope of this post.
Manually delete the bad user accounts
I log into mysql using this command:
mysql <wordpress_database_name> -u <wordpress_username> -p
You'll need to enter the password to get in. Once in, I tested a bunch of queries to ensure that I understood how many users existed and how many I wanted to delete, and of what types. In my case, I wanted to delete everyone who was a "Subscriber" who had not created a comment or a post. These are pretty intense criteria. Don't blindly apply what I did here unless you want to delete exactly that set of people.
I deleted the user entries:
delete from wp_users WHERE ID NOT IN (SELECT post_author FROM wp_posts UNION SELECT user_id FROM wp_comments UNION select distinct user_id from wp_usermeta where meta_key = 'wp_capabilities' and meta_value not like '%subscriber%' );
When the command is successfully completed, it will tell you something like:
Query OK, 45421 rows affected (8 min 36.24 sec)
Now to get rid of the records in wp_usermeta, we run:
delete FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users);
When it is successful, it will show you the number of rows deleted, like so:
Query OK, 547468 rows affected (20.43 sec)
Note that wp_usermeta will probably contain more than ten times as many entries as wp-users.
And, you're done. Your WordPress interface should update with these new numbers. Congrats, you've cleaned out all the inactive / spammer user accounts on your server.