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.<\/p>\n
This one is a bash script. Hope someone else can get some use out of it. Obviously try it on\u00a0 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 …<\/p>\n
As always if you copy and paste it you'll need to make sure the double and single quotes are normal, and not curly.<\/p>\n
#!\/bin\/bash\r\n\r\nDATABASE=database_name\r\nLOGFILE=monitor.txt\r\nMYSQL_OPT=\"-u root -ppassword -sN -e \"\r\n\r\n# Clear logfile\r\necho \"\" > $LOGFILE\r\n\r\nfor BLOG_ID in 654 655 656 657 658 659 660 661 663 664 665 666 667 668 669 670\r\n\r\ndo \r\n\u00a0\u00a0 \u00a0echo blog id is $BLOG_ID >> $LOGFILE\r\n\u00a0\u00a0 \u00a0USER_EMAIL=$(mysql $MYSQL_OPT 'SELECT option_value FROM wp_'\"$BLOG_ID\"'_options WHERE option_name=\"admin_email\" LIMIT 0,1;' $DATABASE)\r\n\u00a0\u00a0 \u00a0echo user email is $USER_EMAIL >> $LOGFILE\r\n\u00a0\u00a0 \u00a0USER_ID=$(mysql $MYSQL_OPT 'SELECT user_id FROM wp_usermeta WHERE meta_key=\"primary_blog\" and meta_value='\"$BLOG_ID\"' ;' $DATABASE)\r\n\u00a0\u00a0 \u00a0if [ -z \"$USER_ID\" ]\r\n\u00a0\u00a0 \u00a0then\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0echo \"no user id. skipping user delete\"\u00a0 >> $LOGFILE\r\n\u00a0\u00a0 \u00a0else\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0echo user id is $USER_ID ... deleting user >> $LOGFILE\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DELETE FROM wp_usermeta WHERE user_id='\"$USER_ID\"' ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DELETE FROM wp_users WHERE ID='\"$USER_ID\"' LIMIT 1;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0fi\r\n\u00a0\u00a0 \u00a0echo \"Deleting Blog tables\" >> $LOGFILE \r\n\u00a0\u00a0\u00a0 mysql $MYSQL_OPT 'DELETE FROM wp_blogs WHERE blog_id='\"$BLOG_ID\"' LIMIT 1;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0\u00a0 mysql $MYSQL_OPT 'DELETE FROM wp_blog_versions WHERE blog_id='\"$BLOG_ID\"' LIMIT 1;' $DATABASE >> $LOGFILE\r\n\r\n\u00a0\u00a0\u00a0 mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_commentmeta ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_comments ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_links ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_options ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_postmeta ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_posts ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_terms ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_term_relationships ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0mysql $MYSQL_OPT 'DROP TABLE wp_'\"$BLOG_ID\"'_term_taxonomy ;' $DATABASE >> $LOGFILE\r\n\u00a0\u00a0 \u00a0echo \"------------------\" >> $LOGFILE\r\n\r\ndone\r\n\r\n# Debugging - opens logfile\r\nmore $LOGFILE\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[1,4],"tags":[],"class_list":["post-287","post","type-post","status-publish","format-standard","hentry","category-it","category-linux"],"_links":{"self":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts\/287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/comments?post=287"}],"version-history":[{"count":0,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts\/287\/revisions"}],"wp:attachment":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/media?parent=287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/categories?post=287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/tags?post=287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}