BASH script to perform database migration

sneakyimp · 11-18-2016, 08:15 PM

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 GB) 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.

Code:

#!/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.:

Code:

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:

Code:

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...

keefaz · 11-18-2016, 08:33 PM

For the first question, password value is stored only in memory, so I consider it's safe unless someone has the skill to log in the server and the ability to read/capture the memory at the correct address

Bash has a built-in variable $? that store the last command exit status, eg if the mysql command ends with error, then $? stores a non zero value, so you can check with

Code:

if [[ $? != 0 ]]; then
  echo "there was an error"
  # do appropriate thing, maybe stop the script

Although mysql doesn't have function to drop all tables in a database, the DROP command accepts a list of tables

Code:

DROP table1, table2, table3...

sneakyimp · 11-18-2016, 09:02 PM

Quote:

Originally Posted by keefaz

For the first question, password value is stored only in memory, so I consider it's safe unless someone has the skill to log in the server and the ability to read/capture the memory at the correct address

This was kind of my thinking, but within the BASH script, I'm calling a lot of other commands using the password...just want to be sure those don't end up in some BASH history file.

Quote:

Originally Posted by keefaz

Bash has a built-in variable $? that store the last command exit status, eg if the mysql command ends with error...

As you can see from the code in my post, I'm already making use of this variable. The problem I'm having specifically is that I want to:
1) execute a command
2) if the command fails (result is nonzero) then EXIT the script and stop all code execution
3) if it worked, I want to parse the output of the command line by line.
I'm trying all kinds of stuff more or less blindly.

Quote:

Originally Posted by keefaz

Although mysql doesn't have function to drop all tables in a database, the DROP command accepts a list of tables

Code:

DROP table1, table2, table3...

I am aware of this, but can't get past the need to check result & parse output from a single command. Once I get that sorted, I suppose my next question would be about string concatenation...

keefaz · 11-18-2016, 09:11 PM

Just saw your edit

Code:

[ IFS= read -r line ]

It isn't a correct condition test for Bash

I would just build the tables list, then delete them in one go
Something like

Code:

exitOnErr()
{
    echo "$@" 1>&2
    exit 1
}

DATABASE_NAME="my_database_name"

# sql command shortcut
alias theSql="mysql -u root -p$passvar --silent --skip-column-names $DATABASE_NAME"

# build the table list
tables=($(echo "SHOW TABLES" | theSql))
[[ $? != 0 ]] && exitOnErr "Can't build table list"

# build a comma separated list
tblist=$(IFS="," ;echo "${tables[*]}")

# check if it's correct
echo "DROP TABLE $tblist" 

# if yes, then uncomment next lines:

# echo "DROP TABLE $tblist" | theSql
# [[ $? != 0 ]] && exitOnErr "Can't drop tables"

keefaz · 11-18-2016, 09:16 PM

Quote:

Originally Posted by sneakyimp

This was kind of my thinking, but within the BASH script, I'm calling a lot of other commands using the password...just want to be sure those don't end up in some BASH history file.

AFAIK, bash script interactive questions / answers aren't stored anywhere

Quote:

Originally Posted by sneakyimp

As you can see from the code in my post, I'm already making use of this variable. The problem I'm having specifically is that I want to:
1) execute a command
2) if the command fails (result is nonzero) then EXIT the script and stop all code execution
3) if it worked, I want to parse the output of the command line by line.
I'm trying all kinds of stuff more or less blindly.

Your logic is fine but the conditions for while loop are not correct

sneakyimp · 11-18-2016, 11:06 PM

I appreciate your help. I'm making some progress I think but the alias just isn't working. I get error:

Code:

./foo.sh: line 32: theSql: command not found

My script is nearly identical to yours except for a few subtle tweaks:

Code:

#!/bin/bash

DATABASE_V3="my_v3_database"
DATABASE_V3_DUMP="/path/to/my_v3_database.sql"
DATABASE_V4="my_v4_database"

exitOnErr()
{
        # echo message
        echo "$1" 1>&2
        # exit using erro code
        exit $2
}


# prompt user for db password
read -sp "Please Enter Database Password: " passvar
echo

if [ ! $passvar ]; then
        exitOnErr "No db password provided. Exiting..." 1
fi

echo "starting db migration..."
echo


# sql command shortcut
alias theSql="mysql -u root -p$passvar --silent --skip-column-names $DATABASE_V3"

# build the table list
tables=($(echo "SHOW TABLES" | theSql))
[[ $? != 0 ]] && exitOnErr "Can't build table list" 2

# build a comma separated list
tblist=$(IFS="," ;echo "${tables[*]}")

# check if it's correct
echo "DROP $tblist" 

# if yes, then uncomment next lines:

# echo "DROP $tblist" | theSql
# [[ $? != 0 ]] && exitOnErr "Can't drop tables" 3

sneakyimp · 11-19-2016, 12:45 AM

Thank you for your help!

Ok so apparently we need some include/import stuff to get the alias working. I added some stuff after the opening bash bang and this is the script. I've shuffled things around a bit to delete tables from two databases and import various files. I still need to:
* alter the script to locate the import files relative to the bash script itself (e.g., ../sql/file.sql)
* add a 'time elapsed' output line when the script completes (it takes a long time!)

Any constructive criticism of the script is welcome.

Code:

#!/bin/bash
# alias.sh

# Must set this option, else script will not expand aliases.
shopt -s expand_aliases

DATABASE_V3="v3_database"
DATABASE_V3_DUMP="/home/sneakyimp/v3_dump.sql"
DATABASE_V4="v4_database"
DATABASE_V4_SCHEMA="/home/sneakyimp/v3_schema.sql"
DATABASE_V4_CONFIG="/home/sneakyimp/v3_config_data.sql"
DATABASE_V3_V4_MIGRATION="/home/sneakyimp/v3_v4_migration.sql"



exitOnErr()
{
	# echo message
	echo "$1" 1>&2
	# exit using erro code
	exit $2
}

# prompt user for db password
read -sp "Please Enter Database Password: " passvar
echo

if [ ! $passvar ]; then
        exitOnErr "No db password provided. Exiting..." 1
fi

# sql command shortcut
alias sqlCredentials="mysql -u root -p\"$passvar\"" 
deleteTables()
{
	# build the table list
	tables=($(sqlCredentials --silent --skip-column-names -e "SHOW TABLES" $1))
	[[ $? != 0 ]] && exitOnErr "Could not read table list for $1, response is $?" 2

	# build a comma separated list
	tblist=$(IFS="," ;echo "${tables[*]}")

	# check if it's correct
	echo "DROP TABLE $tblist;"

	# if yes, then uncomment next lines:

	sqlCredentials -e "DROP TABLE $tblist;" $1
	[[ $? != 0 ]] && exitOnErr "Can't drop tables in $1, response is $?" 3
}

importSql() {
	#$1 is db name
	#$2 is file to import

	echo "$1 < $2"

	sqlCredentials $1 < $2
	[[ $? != 0 ]] && exitOnErr "Can't import $2 into $1, response is $?" 4
}


echo "starting db migration..."
echo

echo "emptying $DATABASE_V3 ..."
deleteTables $DATABASE_V3
echo  "$DATABASE_V3 emptied"
echo

echo "importing V3 dump file from $DATABASE_V3_DUMP..."
importSql $DATABASE_V3 $DATABASE_V3_DUMP
echo "SQL import into $DATABASE_V3 complete"
echo

echo "emptying $DATABASE_V4 ..."
deleteTables $DATABASE_V4
echo  "$DATABASE_V4 emptied"
echo

echo "importing V4 schema file..."
importSql $DATABASE_V4 $DATABASE_V4_SCHEMA
echo "SQL import into $DATABASE_V3 complete"
echo

echo "importing V4 config file..."
importSql $DATABASE_V4 $DATABASE_V4_CONFIG
echo "SQL import into $DATABASE_V3 complete"
echo

echo "migrating data v3 -> v4"
importSql $DATABASE_V4 $DATABASE_V3_V4_MIGRATION
echo "SQL migration into $DATABASE_V4 complete"
echo


echo
echo "===DATA MIGRATION COMPLETE"
echo

grail · 11-19-2016, 03:11 AM

My only comments would be that you might want to look at heredocs instead of so many echoes and on a personal note, I am not a fan of things being messed up so I would have put all the functions together
instead of intermingled throughout the script (but that's probably just me

) and also maybe look at 'local' variables in your functions so when you look at this script in x months time
you don't have to find the call to the function to see what is being passed in.

sneakyimp · 11-19-2016, 01:45 PM

Can anyone suggest a good book or resource for BASH scripting? I obviously have a lot to learn.

astrogeek · 11-19-2016, 02:04 PM

These are all good places to start:

Bash Guide for Beginners
Advanced Bash Scripting Guide
Bash Reference Manual

I also like the O'Reilly Classic Shell Scripting book as a handy print reference.

ntubski · 11-19-2016, 02:13 PM

Quote:

Originally Posted by sneakyimp

Code:

alias sqlCredentials="mysql -u root -p\"$passvar\""

Quote:

Originally Posted by keefaz

AFAIK, bash script interactive questions / answers aren't stored anywhere

It's true that normal shell variables aren't accessible from outside the bash process, but passing the password as a command line parameter exposes it.

http://dev.mysql.com/doc/refman/5.5/...rity-user.html

Quote:

Code:

shell> mysql -u francis -pfrank db_name

This is convenient but insecure. On some systems, your password becomes visible to system status programs such as ps that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible.

grail · 11-19-2016, 02:14 PM

The links provided by astrogeek are good and once you have a handle on them I would suggest working your way through all of the following:

http://mywiki.wooledge.org/TitleIndex

sneakyimp · 11-19-2016, 06:32 PM

Quote:

Originally Posted by ntubski

It's true that normal shell variables aren't accessible from outside the bash process, but passing the password as a command line parameter exposes it.

http://dev.mysql.com/doc/refman/5.5/...rity-user.html

Thanks so much for this.

keefaz · 11-19-2016, 08:31 PM

Using mariadb server, it seems password is obfuscated on ps output

Code:

mysql --version
mysql  Ver 15.1 Distrib 5.5.43-MariaDB, for Linux (x86_64) using readline 5.1
echo 'SELECT SLEEP(10)' | mysql -u root --password=apass database_name &
ps aux | grep mysql
...
keefaz    8706  0.0  0.0 107144  4812 pts/6    S    12:29   0:00 mysql -u root --password=x xxxxxx database_name

ntubski · 11-20-2016, 07:52 AM

Quote:

Originally Posted by keefaz

Using mariadb server, it seems password is obfuscated on ps output

Yes, I believe that what's meant by

Quote:

MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible.

Note that Linux is not a real-time system, so the brief interval can become longer under some conditions.