LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 11-18-2016, 08:15 PM   #1
sneakyimp
Senior Member
 
Registered: Dec 2004
Posts: 1,056

Rep: Reputation: 78
BASH script to perform database migration


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

Last edited by sneakyimp; 11-18-2016 at 08:37 PM.
 
Old 11-18-2016, 08:33 PM   #2
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,325

Rep: Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757
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...
 
Old 11-18-2016, 09:02 PM   #3
sneakyimp
Senior Member
 
Registered: Dec 2004
Posts: 1,056

Original Poster
Rep: Reputation: 78
Quote:
Originally Posted by keefaz View Post
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 View Post
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 View Post
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...
 
Old 11-18-2016, 09:11 PM   #4
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,325

Rep: Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757
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"

Last edited by keefaz; 11-18-2016 at 09:28 PM. Reason: missing TABLE, from DROP TABLE mysql command and DATABASE NAME
 
1 members found this post helpful.
Old 11-18-2016, 09:16 PM   #5
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,325

Rep: Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757
Quote:
Originally Posted by sneakyimp View Post
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 View Post
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
 
Old 11-18-2016, 11:06 PM   #6
sneakyimp
Senior Member
 
Registered: Dec 2004
Posts: 1,056

Original Poster
Rep: Reputation: 78
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
 
Old 11-19-2016, 12:45 AM   #7
sneakyimp
Senior Member
 
Registered: Dec 2004
Posts: 1,056

Original Poster
Rep: Reputation: 78
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
 
Old 11-19-2016, 03:11 AM   #8
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
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.
 
1 members found this post helpful.
Old 11-19-2016, 01:45 PM   #9
sneakyimp
Senior Member
 
Registered: Dec 2004
Posts: 1,056

Original Poster
Rep: Reputation: 78
Can anyone suggest a good book or resource for BASH scripting? I obviously have a lot to learn.
 
Old 11-19-2016, 02:04 PM   #10
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_12{.0|.1}
Posts: 5,584
Blog Entries: 11

Rep: Reputation: 3606Reputation: 3606Reputation: 3606Reputation: 3606Reputation: 3606Reputation: 3606Reputation: 3606Reputation: 3606Reputation: 3606Reputation: 3606Reputation: 3606
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.
 
1 members found this post helpful.
Old 11-19-2016, 02:13 PM   #11
ntubski
Senior Member
 
Registered: Nov 2005
Distribution: Debian, Arch
Posts: 3,590

Rep: Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908
Quote:
Originally Posted by sneakyimp View Post
Code:
alias sqlCredentials="mysql -u root -p\"$passvar\""
Quote:
Originally Posted by keefaz View Post
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.
 
1 members found this post helpful.
Old 11-19-2016, 02:14 PM   #12
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,816

Rep: Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071Reputation: 3071
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
 
Old 11-19-2016, 06:32 PM   #13
sneakyimp
Senior Member
 
Registered: Dec 2004
Posts: 1,056

Original Poster
Rep: Reputation: 78
Quote:
Originally Posted by ntubski View Post
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.
 
Old 11-19-2016, 08:31 PM   #14
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,325

Rep: Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757Reputation: 757
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

Last edited by keefaz; 11-19-2016 at 08:37 PM.
 
Old 11-20-2016, 07:52 AM   #15
ntubski
Senior Member
 
Registered: Nov 2005
Distribution: Debian, Arch
Posts: 3,590

Rep: Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908Reputation: 1908
Quote:
Originally Posted by keefaz View Post
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: Bash script to Redis SCAN e.g. for pruning, migration and archiving LXer Syndicated Linux News 0 11-08-2015 07:20 AM
How do I perform commands in a bash script as a different user? theonislair Programming 5 06-10-2012 01:10 PM
[SOLVED] Failed miserably to execute bash script via PATH variable after FS migration. kopatops Linux - Software 21 08-18-2010 06:34 AM
script Q : how do I force bash to perform the math? kevinyeandel Linux - Newbie 4 02-20-2009 02:35 AM
helping me in a bash script that perform a "select" menus Task adam_blackice Programming 5 09-15-2007 01:09 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 08:42 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration