Remove Spammers from WordPress Database

Oct 212014
 Posted by on 21/10/2014 How-To's  Add comments

remove-spammers-from-databaseFew weeks ago I was called to clean a server for my former employer. For that company I made almost 50 WordPress sites based on various themes. Some of them with open registration, open comments or multisites which permit registration of users or sub-sites. All these sites was hosted on a VPS with a generous configuration (100 GB disk space, 2 GB Real Memory, 4 GB Virtual Memory), self-managed with the help of Virtualmin+Webmin.

After a year when nobody watch these sites, the server was flooded by spammers, thousands of users was registered and published sub-sites, comments or spammy posts. The space on disk was filled, databases becomes huge and the server was down for weeks. The hosting company has disabled even the scheduled back-ups because the backups don’t had space to run.

In short I was called to clean the server, to remove the spammers and their posts. After restoring the basic functionality of the server by deleting some old unfinished backups I started to remove users from WordPress Dashboard. What a nightmare!!! For about 33000 spammers probably it take a life to delete them from Users menu… It take about 30 minutes to remove 20 users and I saw WSOD at each click…

So, I start thinking to remove the users directly from database. But how to remove users and their associated posts? Searching the internet I found this solution from Tripwire Magazine which seems to help in my case. But I don’t need to remove posts from specific categories, but for specific users. Which users? Looking in the users list I saw that spammers used specific emails – like,,  etc. So, if I can select users with “spamming” emails and remove them and their posts I solve the problem. 

Using WordPress Database ER Diagram I found the relations between users and posts and I found the answer for my issue. Here are the steps:

  • make a backup of your database – if something goes wrong you can restore the database and start over.
  • log into PhpMyAdmin and select desired database from the left panel
  • select SQL tab and paste the next script in the textarea. Run the query by pressing “Go” button. Re-run the query multiple times replacing  with each offending email address. Don’t remove the wildcard “%” which stay for “any string”.


After running the script the database was clean, with only few hundreds of spammers left because they used common addresses like yahoo or gmail, used also by “good” users. But now is simple to delete the spammers manually from Users panel.

Finally I removed the pictures left unattached (uploaded by spammers to their posts which not exist anymore) and I optimized the database (there are a lot of plugins for that).

I did this for all spammed sites. Now the server is up and running, with only 36 GB filled, with scheduled backups and all functionalities back URI:  


Experienced web developer and graphic designer. Former consultant for Suffusion theme and former moderator on Suffusion Support forum. I teach others while learning myself.

Google+ Profile

    Google+ Page

 Leave a Reply

(Your comment will be published. To contact me privately, use the contact form.)

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  One Response to “Remove Spammers from WordPress Database”

Comments (1)
  1. Wow! Thanks … saved me from a lot of work! I was looking for a solution to remove spam users for quite some time. Thanks