WordPress Multisite Blog Mass Removal Script

by

I was moving a WPMU site to a new server and saw that the database had grown to about 250Mb, and there were about 700 blogs running on the server. I used a few database queries to pull out a list of which blogs I wanted to delete — basically any which had a creation date and last update date the same, and any with only one post etc — at which point I had a list of 300 blog IDs. So was I going to manually delete them all? Nope, I did what any sensible person did, and wrote a script to do it.

This one is a bash script. Hope someone else can get some use out of it. Obviously try it on  a test database first. You'll need to change the database name, and the MYSQL_OPT line to reflect your user and password, natch, and then fill in your list of blog IDS …

As always if you copy and paste it you'll need to make sure the double and single quotes are normal, and not curly.

#!/bin/bash

DATABASE=database_name
LOGFILE=monitor.txt
MYSQL_OPT="-u root -ppassword -sN -e "

# Clear logfile
echo "" > $LOGFILE

for BLOG_ID in 654 655 656 657 658 659 660 661 663 664 665 666 667 668 669 670

do 
    echo blog id is $BLOG_ID >> $LOGFILE
    USER_EMAIL=$(mysql $MYSQL_OPT 'SELECT option_value FROM wp_'"$BLOG_ID"'_options WHERE option_name="admin_email" LIMIT 0,1;' $DATABASE)
    echo user email is $USER_EMAIL >> $LOGFILE
    USER_ID=$(mysql $MYSQL_OPT 'SELECT user_id FROM wp_usermeta WHERE meta_key="primary_blog" and meta_value='"$BLOG_ID"' ;' $DATABASE)
    if [ -z "$USER_ID" ]
    then
        echo "no user id. skipping user delete"  >> $LOGFILE
    else
        echo user id is $USER_ID ... deleting user >> $LOGFILE
        mysql $MYSQL_OPT 'DELETE FROM wp_usermeta WHERE user_id='"$USER_ID"' ;' $DATABASE >> $LOGFILE
        mysql $MYSQL_OPT 'DELETE FROM wp_users WHERE ID='"$USER_ID"' LIMIT 1;' $DATABASE >> $LOGFILE
    fi
    echo "Deleting Blog tables" >> $LOGFILE 
    mysql $MYSQL_OPT 'DELETE FROM wp_blogs WHERE blog_id='"$BLOG_ID"' LIMIT 1;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DELETE FROM wp_blog_versions WHERE blog_id='"$BLOG_ID"' LIMIT 1;' $DATABASE >> $LOGFILE

    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_commentmeta ;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_comments ;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_links ;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_options ;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_postmeta ;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_posts ;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_terms ;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_term_relationships ;' $DATABASE >> $LOGFILE
    mysql $MYSQL_OPT 'DROP TABLE wp_'"$BLOG_ID"'_term_taxonomy ;' $DATABASE >> $LOGFILE
    echo "------------------" >> $LOGFILE

done

# Debugging - opens logfile
more $LOGFILE

Leave a Comment