Tag Archives: loops

Clearing out a MySQL Database with One Long Bash Command

IMG_2196The need arose yesterday to drop all tables from a database so I could re-create a WordPress installation.  Sure, I could have dropped the database and re-created the entire thing, but that wouldn’t have been much of a challenge and I wanted to easily keep the same credentials and permissions on the database.

However, the number of tables exceeded my laziness.  There was a Drupal installation in addition to an old, unrelated WordPress site – perhaps around forty tables total.  I found a script that would do this, but it was also too involved.  I ended up with a fairly simple bash loop that did the trick. 

I’m using CentOS Linux and connecting to another CentOS Linux server running MySQL 5.1.69 for this article. Here’s how it worked in a not-so-brief explanation:

Overall, I simply needed to connect to the database and drop every table.  There isn’t any DROP-ALL-TABLES sort of command that I know of, so they must be dropped one-by-one.  Let’s get a list of tables to drop, shall we?

$ mysql -h db01 -u user -pP@ssword wordpress -e 'SHOW TABLES;'
+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+

There – that did it, but we don’t need all those pipe symbols in the output.  Pipe it through awk:

$ mysql -h db01 -u user -pP@ssword wordpress -e 'SHOW TABLES;' | awk '{ print $1}'
Tables_in_wordpress
wp_commentmeta
wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_term_relationships
wp_term_taxonomy
wp_terms
wp_usermeta
wp_users

And voila!  A simple list of all table names in this database.  You could easily filter this output to capture only a subset of table names with different prefixes (‘wp_’, ‘blog_’, etc..) if you didn’t need to list all of the tables.  The top of this list still includes the output column header, so we can just grep -v it right on out of there…

$ mysql -h db01 -u user -pP@ssword wordpress -e 'SHOW TABLES;' | awk '{ print $1}' \
| grep -v "^Tables_"
wp_commentmeta
wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_term_relationships
wp_term_taxonomy
wp_terms
wp_usermeta
wp_users

…and the list is clean.  Now we just have to drop each of those tables.  If we surround that entire command with tick marks (`) bash will run the command and capture the output.  We can use that output as an iterable list.  The For loop knows how to use this list – we just need to tell it what to do with each item.  So here we go:

$ for x in `mysql -h db01 -u user -pP@ssword wordpress -e 'SHOW TABLES;' \
| awk '{ print $1}' | grep -v "^Tables_"`
> do
> echo $x
> done
wp_commentmeta
wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_term_relationships
wp_term_taxonomy
wp_terms
wp_usermeta
wp_users

The previous example just prints each item in the list – this is useful to us in that we know exactly what’s in the list.  With this knowledge we can put each item (each table name, that is) in an SQL statement to drop the tables.  Let’s make sure we can do that by printing some SQL statements:

$ for x in `mysql -h db01 -u user -pP@ssword wordpress -e 'SHOW TABLES;' \
| awk '{ print $1}' | grep -v "^Tables_"`
> do
> echo 'DROP TABLE $x;'
> done
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;
DROP TABLE $x;

Uh-oh!  See what happened there?  The literal string ‘$x’ was printed instead of the variable’s value.  That’s an important difference between using single-quoted (‘) and double-quoted (“) strings.  Single-quoted strings will literally print what you put in them, and double-quoted strings will allow you to substitute variables.  Let’s change the single-quotes to double-quotes:

$ for x in `mysql -h db01 -u user -pP@ssword wordpress -e 'SHOW TABLES;' \
| awk '{ print $1}' | grep -v "^Tables_"`
> do
> echo "DROP TABLE $x;"
> done
DROP TABLE wp_commentmeta;
DROP TABLE wp_comments;
DROP TABLE wp_links;
DROP TABLE wp_options;
DROP TABLE wp_postmeta;
DROP TABLE wp_posts;
DROP TABLE wp_term_relationships;
DROP TABLE wp_term_taxonomy;
DROP TABLE wp_terms;
DROP TABLE wp_usermeta;
DROP TABLE wp_users;

Success!  We now have everything we need to drop the tables.  Just change the echo statement in the loop to a mysql statement with the proper credentials and bash will run those commands:

$ for x in `mysql -h db01 -u user -pP@ssword wordpress -e 'SHOW TABLES;' \
| awk '{ print $1}' | grep -v "^Tables_"`
> do
> mysql -h db01 -u user -pP@ssword wordpress -e "DROP TABLE $x;"
> done

There won’t be any output unless there is an error. For each of the table names, an SQL DROP statement will be issued to the database.

If you would like to read more on the commands I used, feel free to visit some of the documentation for them:

It goes without saying that you should be careful with these operations and most database maintenance should be handled by a knowledgeable database administrator – especially in a production environment. My purpose here was only to experiment with bash and do something I hadn’t done before, and the database I was working with wasn’t currently in production.  I highly recommend against trying these things on a database your organization depends on.