WordPress – Bulk Remove all Users

As many WordPress administrators will know, spam users signing up to your blogs is a real pain and hard to administer. We had over 70,000 junk spam user accounts on our site and we decided it was time to have a clear out.

But where to start?

We started by going into the Users section of our WordPress dashboard and extended the amount of users lists to 999 per page. Yes, it would take forever still, but we wanted to give it a try and maybe clear them all out over the space of a few weeks. However, highlighting 999 user accounts and then selecting Delete from the Bulk Action drop down just caused the site to crash out as it made the URL to delete the account too long.

So, database amendments then?

The next obvious step was to look into the database and see if we could delete all users without deleting our main admin account. Before we start, the first thing to always do when messing around with your database is to take a backup, so if it all goes horribly wrong, you can go back and put it right again. So – go ahead and take a backup of your WordPress database using the CPanel Backup utility. Note: if you do not take a backup and you balls it all up – don’t come crying to us that your site is screwed!

SQL Querying

Once you are happy that you have a full backup of your WordPress database, you can now go ahead and load up the phpMyAdmin utility from your CPanel: Once loaded, you need to click on the SQL button at the top of the screen: You can now run SQL queries against your WordPress database using the query window. The below SQL script will show all the users apart from your account with ID 1 – which should be your main admin account:

SELECT * FROM $users WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM $posts) AND ID NOT IN (SELECT DISTINCT user_id FROM $comments)

where:

• $users = the name of your users table
• $posts = the name of your posts table
• $comments = the name of your comments table

You can go ahead and check that you are happy with what you are going to delete. So check the output of the script very carefully. Once you are happy, you can continue to the deleting phase. This script will then go ahead and delete all the users apart from your ID=1 user account.

DELETE FROM $users WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM $posts) AND ID NOT IN (SELECT DISTINCT user_id FROM $comments)

where:

• $users = the name of your users table
• $posts = the name of your posts table
• $comments = the name of your comments table

You will then want to tidy up your user meta data, so now go ahead and run this query:

DELETE FROM $usersmeta WHERE user_id > 1 AND user_id NOT IN (SELECT DISTINCT post_author FROM $posts) AND user_id NOT IN (SELECT DISTINCT user_id FROM $comments)

where:

• $usermeta = the name of your users table
• $posts = the name of your posts table
• $comments = the name of your comments table

You will then finally want to remove all the links that may have been created by spam users. You can run this query to carry this out:

DELETE FROM $links WHERE LINK_OWNER > 1 AND LINK_OWNER NOT IN (SELECT DISTINCT post_author FROM $posts) AND LINK_OWNER NOT IN (SELECT DISTINCT user_id FROM $comments)

where:

• $links = the name of your links table
• $posts = the name of your posts table
• $comments = the name of your comments table

Now time to check your WordPress Users…

When you have completed these queries, you should log back into your WordPress dashboard and click on Users – you should now notice that only your admin account is left!

Variations

If you want to keep the first, let’s say 500, user accounts then you can change the WHERE ID > 1 and change the number to suit your needs. So to keep the first 500 user accounts we would use WHERE ID > 500.

Comments

If you have any questions or feedback on this guide, please feel free to leave us a message below in the comments section.

PDF eBook Download

You can download this guide by clicking the button below: WordPress – Bulk Remove all Users – Free eBook