I'm building a new site (v4). This is going to involve a database migration from the old (V3) to the new (V4) database. There are lots of tables and largish (half a GšŸ˜Ž SQL dump files involved. Because this takes awhile and involves many steps, I want to create a BASH script. Sadly, my BASH skills are sooo primitive.

Question 1: I want to prompt the user for their database password -- is this safe?
I want this script, when it first starts running, to prompt the user for a database password for a user that has full privileges on both databases. Is this safe? From what I can tell the password doesn't get stored in the bash history, but I'm wondering if it might get stored in some other place when I call a dozen MySQL commands inside my BASH script. Can someone clarify this for me? Chances are that I'll be using the root password for the db.

Question 2: How do I execute a command, check if it worked and, if so, parse the output line by line to formulate more commands?
Apparently there is no command with mysql to drop all the tables in a database. I don't want to drop and re-add the database because this would introduce the need to find the right credentials to grant permissions and stuff. Here's my script so far. I've tried numerous things trying to check the output of the mysql command but the way that it's piped is apparently a problem.

#!/bin/bash

read -sp "Please Enter Database Password: " passvar
echo
echo

if [ ! $passvar ]; then
        echo "No password provided. Exiting..." 1>&2
        echo
        exit 1
fi

echo "listing tables"
# HOW DO I CHECK IF THIS SUCCEEDS BEFORE LOOPING?
mysql -u root -p"$passvar" --silent --skip-column-names -e "SHOW TABLES" MY_DATABASE_NAME |
while IFS= read -r line
do
        # I will also be trying to formulate a DROP TABLE command here and want to check its success/failure
        echo "here is a line: $line"
done
echo "table list complete"
echo

EDIT: I have tried putting an if/fi block around the while loop (and various other things) but I can't get it to exit, e.g.:

echo "deleting tables"
mysql -u root -p"$passvar" --silent --skip-column-names -e "SHOW TABLES" MY_DATABASE_NAME |
while [ $? -eq 0 ] && [ IFS= read -r line ]
do
        echo "delete this table: $line"
done
if [ $? -ne 0 ]; then
        echo "table delete failed. Exiting..."
        exit 2
fi

echo "table delete complete"
echo

If I enter an incorrect password, I get output like this:

deleting tables
./db-migration.sh: line 19: [: too many arguments
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
table delete complete

Line 19 is the while command...

    Write a Reply...