Tag Archives: MySQL

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.

Installing and Using phpMyAdmin through YUM

Today is Stephen Hawking’s birthday. He turns 70.

I got PHPMyAdmin installed through YUM after enabling the EPEL (Extra Packages for Enterprise Linux) repository. Once I enabled EPEL, it was a simple

yum install phpMyAdmin

It installs to /usr/share/phpMyAdmin, so from there you just create a symbolic link in the web root to that directory, or change the httpd config to serve out that directory for a virtual host. Your choice. It’s just easier to run this and be done:

ln -s /usr/share/phpMyAdmin/ /var/www/html/mysql

So then http://mywebserver/mysql goes to my phpMyAdmin installation. However, if you’ve just set up MySQL, you will need to set the root user password by bringing up mysqld and running the following command as root:

mysqladmin -u root password P@55w0rd

You should, of course, replace ‘P@55w0rd’ with the password of your choice. At that time you can visit your phpMyAdmin installation through the web interface and log in with those credentials. You are encouraged to then create a user for yourself that does not have root privileges and use it instead, but I don’t see that practiced very often.

So there it is – phpMyAdmin installed through YUM.

I had another problem when I tried to get to phpMyAdmin on that server – none of the PHP code was run. It just showed the raw PHP files as if I’d asked for copies of them. PHP was installed, but I had never encountered an installation that wasn’t configured in apache.

Turns out it was a simple inclusion needed in httpd.conf of the PHP configurations. In /etc/httpd/conf.d/ there are some files that end in .conf that need to be included. So at the end of httpd.conf I put a line that says

Include conf.d/*.conf

I restarted Apache with that configuration and it worked. I can’t say if that’s the best way to do it, but for my test VM that’s fine with me.