Delete all subscribers from wp_users and wp_usermeta a few thousand at a time

Question

I have a site with 70K spam subscribers, and I don't need any of them. So I need to delete all subscribers from wp_users and each user's associated meta from wp_usermeta using a query in adminer and/or phpmyadmin.

But to keep from crashing the server, so how can I delete a few thousand at a time?

This seems to be the basic query I need:

DELETE
FROM  wp_users 
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE meta_key = 'wp_capabilities'
AND meta_value  LIKE '%subscriber%
//except how do I select a few thousand at a time?

But how do I select a few thousand at a time?

Answer 1

My favorite way to do something like this is not via database hacking (which I am always concerned will have side effects), but via a command line tool.

For example, giving a functioning WP CLI install, you can simply do this:

wp user delete $(wp user list --role=subscriber --field=ID --number=10) --reassign=1

The reassign parameter is the user ID of the user to reassign content to. We can use "--number" (the limit), even though it is not well documented, because WP_User_Query supports it.

There you have it - a clean, one line command line utility to do operations like this.

I'd recommend in this case you try the subquery first "wp user list.." to see which users it will delete. After running the one-liner, you will see information like this:

... Success: Removed user 123 from https://example.com/. Success: Removed user 124 from https://example.com/. ...

Answer 2

I had a similar problem and after reading this question I made this plugin (adapted for your question).

Once installed it makes an admin menu under Users called Bulk User Delete when you go to this page, it deletes the users in batches, 20 at a time as per your sql query (my original query was slightly different).

You can make the batches smaller or bigger but this allows you to delete many users without server timeout, it may take a few minutes for lots of spam sign ups.

It uses jquery to reload the page after each batch is processed (this could be done with ajax I guess but I need something quickly).

Maybe someone else will find this helpful, you'll most likely need to adapt the sql query to your situation.

    <?php 
    /*
    Plugin Name: Bulk Delete Spam Users
    Description: Delete the spam sign ups
    Version: 0.1
    Author: Steven
    Text Domain: bulk-delete-spam-users
    */

    add_action('admin_menu', 'my_users_menu');
    function my_users_menu() {
        add_users_page('Bulk User Delete', 'Bulk User Delete', 'read', 'bulk-user-delete', 'active_users_nodel');
    }

    function active_users_nodel() {
        global $wpdb;
        $result = array();
        $result = $wpdb->get_results('
            SELECT wp_users.id, wp_users.user_login FROM wp_users 
            INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
            WHERE meta_key = "wp_capabilities"
            AND meta_value  LIKE "%subscriber%"
        ');

        $users = $result;
        $i = 0;
        echo '<div class="bud-box">';

        $users_remain = $users ? 'true' : 'false';

        $current_user = wp_get_current_user();
        echo '<strong>Orphaned content will be reassigned to:</strong><br />';
        echo 'Username: ' . $current_user->user_login . '<br />';
        echo 'User ID: ' . $current_user->ID . '<br />';

        <h3>currently deleting...</h3>

        foreach($users as $user) {
            $i++;
            if($i < 20) {
                wp_delete_user( $user->ID, $current_user->ID );
                var_dump($user->user_login);
            }
        }
        echo '</div>';
        ?>

        <script>
            (function($){
                $(document).ready(function(){
                    var uremain = <?php echo $users_remain; ?>;
                    if( uremain == true){
                        location.reload(); 
                    }
                });
            })(jQuery);
        </script>

    <?php } ?>

Answer 3

You can use Wp function for this. wp_delete_user(); this function work with user id. you can get all subscribers user id with this way: get_users() getting your filtered users.

$get_subscribers = get_users('role=subscriber');
foreach($get_subscribers as $user){
    wp_delete_user($user->ID);
}