Deleting old MySQL tables across blogs and databases in WPMu

OK, so this will probably be the final installment of my “pimp your WPMu databases” series 🙂 Part one was moving from WPMuDEV’s Multi-DB package to SharDB; part two dealt with optimizing tables across a number of databases in MySQL, and the final installation will share some SQL code, once again thanks to Gowtham, for deleting specific tables across various blogs and databases.

Ok, so here is the setup, when we started UMW Blogs we used the spam filter Spam Karma 2. It was a personal favorite of mine, and while a lot more work than Akismet, it did a pretty good job of shutting out spam. Spam karma 2 has since died, and we moved to Akismet well over a year ago now. However, for at least the first 500 or 600 blogs (maybe more) we had Spam karma 2 installed, which inserted two tables into each blog kinda like this:
wp_21_sk2_logs
wp_21_sk2_spams

Where 21 was the blog ID, so across numerous blogs and databases there are a number of blogs with two fields like those above (just a different blog ID number, say wp_581_sk2_logs) filled with spam. It would make sense to drop these tables out as part of an optimization of our databases, but the only question I had was how?

Well I went to Gowtham, the oracle, once again (I hope this well of riches doesn’t go dry for overuse 🙂 ) and he sent me the following script for deleting all tables with _sk2_ in the name. Now I imagine very few folks have this specific issue with Spam Karma 2, so the details of this script may seem irrelevant. But if you think about it, if there is a plugin that inserts tables on numerous blogs spread across various databases, and an uninstall of that plugin doesn’t delete the tables, then such a script would be quite useful to you. Just change the list of databases from my specific setup to yours and instead of the following bit of code:

$sql_q = "SHOW TABLES LIKE '%_sk2_%'";

Put a unique series of characters between the ‘%_uniquecharacters_%’ to delete a series of tables across blogs and databases.

How freaking cool is this? I tested the script on UMW Blogs, and it worked beautifully, UMW Blogs databases just lost another half a gig of spam fat, and I’m am happy to say it’s never been healthier. No heart disease in the future of this WPMu install—though we can never rule out plugin cancer 🙂

Here is a text version of the script.

And here it is sans the opening and closing PHP calls:

# PHP script to find all tables, whose name contains
# the string '_sk2', in a given set of MySQL databases
# and delete them. This is a 2-step process.
# Run this script as
#
# php mysql_skarma.php > mysql_skarma.sql
#
# Then, run the following command in a terminal
#
# mysql -u USERNAME -p < mysql_skarma.sql # # and enter PASSWORD when prompted. # # First written: Gowtham, Mon, 25 Jan 2010 08:01:48 -0500 # Last modified: Gowtham, Mon, 25 Jan 2010 09:20:22 -0500 # Connect to the database # It is expected that you will be able to connect # to all your databases [listed below] with this # set of credentials $host = 'localhost'; $dbuser = 'username'; $dbpasswd = 'password'; # The act of connecting to the MySQL server $connect = mysql_connect($host, $dbuser, $dbpasswd) or die('MySQL Connection Error: ' .
mysql_errno() . ': ' .
mysql_error());

# List of databases
$databases = array(
"umwblogs_wpmu_0",
"umwblogs_wpmu_1",
"umwblogs_wpmu_2",
"umwblogs_wpmu_3",
"umwblogs_wpmu_4",
"umwblogs_wpmu_5",
"umwblogs_wpmu_6",
"umwblogs_wpmu_7",
"umwblogs_wpmu_8",
"umwblogs_wpmu_9",
"umwblogs_wpmu_a",
"umwblogs_wpmu_b",
"umwblogs_wpmu_c",
"umwblogs_wpmu_d",
"umwblogs_wpmu_e",
"umwblogs_wpmu_f",
"umwblogs_wpmu_global",
"umwblogs_wpmu_vip1"
);

# FOREACH_LOOP_DATABASES BEGINS
# Loop through the array, 'databases'
# Each array element will be stored in the variable 'database'
foreach ($databases as $database) {

# Connect to the database using details specified
# above - it expects that there is one username-password
# combination that can connect to all your databases
mysql_select_db($database, $connect) or
die('Database Connection Error: ' .
mysql_errno() . ': ' .
mysql_error());

# Select all tables from 'database' that have '_sk2_'
# in their name
$sql_q = "SHOW TABLES LIKE '%_sk2_%'";
$result = mysql_query($sql_q) or
die('Invalid Query: ' .
mysql_errno() . ': ' .
mysql_error());

# WHILE_LOOP BEGINS
# Loop through the list of tables from 'database'
while ($table_details = mysql_fetch_row($result)) {
$table_name = $table_details[0];

# SQL query to DROP the table
# This will be written out to a flat text [SQL] file
# to be imported into MySQL. This is necessary because
# tables with an underscore in their names cannot be
# directly deleted from PHP [to my limited knowledge]
print "DROP TABLE `$database`.`$table_name`; \n";
}
# WHILE_LOOP ENDS

}
# FOREACH_LOOP_DATABASES ENDS

# Close the connection
mysql_close($connect);

This entry was posted in UMW Blogs, wordpress multi-user, wpmu and tagged , , , , . Bookmark the permalink.

3 Responses to Deleting old MySQL tables across blogs and databases in WPMu

  1. Martha says:

    Wow. The only way UMW Blogs could be in better shape is if it enrolled in a spinning class.

  2. Reverend says:

    And if Andy Rush was leading that spinning class in HD underwear.

  3. miklb says:

    Just a heads up, your link to the text file uses the .php extension, but I found it’s .txt. Thanks for sharing the script. I’m looking to modify it to be able to parse through all my sites and set comments to open, due to some configuration errors when migrating.

Leave a Reply to Reverend Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.