How to Remove Spam Users from WordPress by Identifying Common Spam Email Domains with phpMyAdmin SQL

Step 1: Backup Your Database

#

Step 2: Open phpMyAdmin

#

Step 3: Find Users by most used domains in their emails
SELECT
  SUBSTRING_INDEX(user_email, '@', -1) AS domain,
  COUNT(*) AS count
FROM
  wp_users
WHERE
  SUBSTRING_INDEX(user_email, '@', -1) NOT IN ('gmail.com', 'outlook.com', 'hotmail.com')
GROUP BY
  domain
ORDER BY
  count DESC
LIMIT 100;

#

Step 4: Check These Users
SELECT * FROM `wp_users` WHERE `user_email` LIKE '%@tyypet.cn';

#

Step 5: Remove Users
DELETE FROM `wp_users` WHERE `user_email` LIKE '%@tyypet.cn';

#

Step 6: Cleanup Metadata
DELETE FROM `wp_usermeta` WHERE `user_id` NOT IN (SELECT `ID` FROM `wp_users`);