Tag Archives: bash

xargs Reminder

I remember using xargs at a previous workplace for pasting a multi-line text list (say, from a text file on my desktop) to generate a space-delimited line of text containing the items in the pasted list. For the life of me this morning I couldn’t remember how to do it, and most examples don’t use xargs in this way. Here’s my reminder that I hope might help others.

Say I had this list of students in a text file that looked like:

mary
bob
susan
jim
jennifer
xavier

and I needed to generate a quick Bash one-liner to process something for them on the command line.  If I wanted to use a for loop on the command line, I’d supply the for loop with a list of things separated by a space.  For example:

for x in bob sue larry
> do
> echo "Hi $x"
> done

and the output will be:

Hi bob
Hi sue
Hi larry

On to xargs: say those names are in a text file on my desktop, with one name per line:

mary
bob
susan
jim
jennifer
xavier

If I copy the text from the file and paste it into the Linux command window, each name will be on a separate line and be interpreted as a command:

$ mary
-bash: mary: command not found
$ bob
-bash: bob: command not found
$ susan
-bash: susan: command not found
$ jim
-bash: jim: command not found
$ jennifer
-bash: jennifer: command not found
$ xavier
-bash: xavier: command not found

Using xargs, I can paste the list into the window and get all the names on one line, each separated by a space.  I will type xargs <<EOF and press enter, then paste my text.  The ‘EOF’ part of the command specifies the end-of-file string, which is needed to tell xargs when I’m finished with the input.  After I paste the list, I enter EOF to finish the command:

$ xargs <<EOF
> mary
> bob
> susan
> jim
> jennifer
> xavier
> EOF

And the output:

mary bob susan jim jennifer xavier

I can then copy this single line and paste it in the for loop.  Sure, it’s not really efficient with three items, but it sure makes things easier when you’re working with 100 server names.

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.