How to bulk delete users directly from phpMyAdmin/MySQL databases

If you’re good with MYSQL quries then you’ll have to export all the user’s IDs from users table that you’re going to delete.
then using those IDs you’ll have to delete records from usermeta adding a condition where user_id IN (101,102....)

101,102.... will be your user IDs with comma separation that you’re going to delete.

Note:
1. Create a full backup before deleting anything.
2. To find the ids, you can either use user_registered column to find the users from the above date or you can use ID column and make a greater than condition. but in this process, if in between any real user registered then you’ll have to manually find that user or exclude that specifying another condition.

My site has exactly 364,235 unwanted users:

That’s a lot of users, and listing IDs of this many users may not be feasible ??

You need a way to isolate these undesired users from the other users… eg all unconfirmed users, all users with “subscriber” role, all users who have not made any post, etc.

Once you define a criteria to isolate these users, then you can look for a plugin, PHP code snippet, or an SQL query snippet you can run to automatically clean up the database.

(Sorry, I’m responding from my cellphone sitting in a moving bus, so I’m unable to give you specific instructions. But the above should be the strategy to use.)

Good luck!

If you get this squared away, one thing you might want to do is go into General Settings and uncheck the option that Anyone can register.

Mine is set that way. But, I do have in the Discussion Settings that users do not have to be registered and logged in to comment.

How many users do you want to retain? If it’s less than the number you want to delete, then it will be faster and safer to create separate DB tables of “good” users, and do some table renaming to make them the active tables.

Thanks for the advice!

I did not specify that:
– there are only three real users, and
– all unwanted users have the same role, while real users have different roles

Therefore, of is easy to separate real ones from fake ones.

@markrh : Yes, thank you. That was already done, but now I have to get rid of all those fake users.

@vijayhardaha : Thank you. For adding the IDs of the users I want to delete, can it be done using their role? Or I can simply do it for all and leave out the three real users…
I’m not good with MySQL queries, but I’m not so bad at programming in general, so I believe that I can manage. I’ll try to follow the steps you outline.

@gappiah : Thank you. The criteria is found, because I want to delete all “subscribers”, or I want to delete all users but three, which I can select manually.
Exactly what I’m looking for is the query that will allow me to delete them.

If you only want to retain three out of 364K users, you DO NOT want to use DELETE queries!

Instead, use the phpMyAdmin “Copy Table” feature (accessed from the Operations tab when viewing the table contents), and select the Structure only option. You’ll need to do this with the wp_users and wp_usermeta tables, and give the new tables names of, say, wp_users2 and wp_usermeta2. Change the table prefix from wp_ to the one in use on your site.

Now find the IDs of the three users you want to retain. Let’s say the IDs are 1, 8, and 44. Then run the following two queries (using the correct table prefix):

INSERT INTO wp_usermeta2 SELECT * FROM wp_usermeta WHERE user_id IN (1,8,44);
INSERT INTO wp_users2 SELECT * FROM wp_users WHERE ID IN (1,8,44);

You now have tables with only the users you want, but please inspect both before proceeding. If the new tables look OK, use phpMyAdmin to rename wp_users and wp_usermeta to something else (rename wp_users first), and afterwards, rename wp_users2 and wp_usermeta2 to wp_users and wp_usermeta (rename wp_usermeta2 first). Your site will throw errors during the minute or so you do the renaming. If there are any issues with the new tables, revert the rename process to restore the original tables.

After you verify that your site is running fine with the two new tables, you can drop the two original tables since they are no longer needed.

Note: if you are using any type of cache plugin that caches DB queries, you should deactivate it before starting this process, and then re-enable it afterwards. This is called an object cache, and if the plugin has an option to disable its object cache, use that option as opposed to deactivating the plugin.

@diondesigns : Thank you! The solution you provided was effective, fast, and easy to implement. Now my problem is solved.