Bash Script not running MySQL Command

Question

I have a MySQL command in a bash script I'm running. I'm running other Linux command line commands before this one (in fact one is even a mysql command) but when it gets to this one, it doesn't work:

DEST_HOST="localhost"
DEST_DBNAME="db_name"
DEST_DBUSER="root"
DEST_DBPASS=""

# FILE NAME
MIGRATION_FILE="migration_database"

# Export/Import Commands
DO_DATABASE_UPDATE="mysql --host=$SOURCE_HOST --user=$DEST_DBUSER --password $DEST_DBNAME < $MIGRATION_FILE.sql"

echo 'Running local updates...'
$DO_DATABASE_UPDATE

echo "$DO_DATABASE_UPDATE"

# Done!
echo "Complete!"

I have the migration_database.sql file in the folder that I'm running it from. As you can see, I'm printing out the UPDATE command at the end of everything. What happens is that I run this and it just prints on the mysql menu like I ran mysql -I to get the help menu.

If I copy and paste the command that is printed out and run it, it works great. I'm not sure why bash doesn't execute this from within the script.

Any ideas?

New Bie · Accepted Answer · 2016-11-18 02:42:53Z

MySQL Command From a Bash Shell in One Line

Use the following command for quickly execution of MySQL query from a Linux Bash Shell : # mysql -u [user] -p[pass] -e "[mysql commands]"

Example :

# mysql -u root -pSeCrEt -e "show databases"

Run a MySQL Query From a Bash script using EOF Use the following syntax in your Bash scripts for running MySQL commands :

mysql -u [user] -p[pass] << EOF
[mysql commands]
EOF

Example :

#!/bin/bash
mysql -u root -pSeCrEt << EOF
use mysql;
show tables;
EOF

Execute a MySQL Command Remotely Use -h option to specify a MySQL server's IP address:

# mysql -h [ip] -u [user] -p[pass] -e "[mysql commands]"

Example :

# mysql -h 192.168.1.10 -u root -pSeCrEt -e "show databases"

Specify a Database to Use

Use -D option to specify the name of MySQL database :
# mysql -D [db name] -u [user] -p[pass] -e "[mysql commands]"

Example :

# mysql -D clients -u root -pSeCrEt -e "show tables"

Bill Karwin · Accepted Answer · 2016-12-13 22:37:03Z

1

... --password $DEST_DBNAME

You don't pass your database password. You just use the --password option with no argument, which means, "prompt me to type the password." If you run this script in a non-interactive shell, you'll never see the prompt.

You could set the password as an argument:

... --password='$DEST_DBPASS' $DEST_DBNAME

Or actually what I would recommend is that you do not put your username & password on the command-line at all, because that's not a good security habit.

Instead, put the username and password in a config file and tell your command to use that config file.

DO_DATABASE_UPDATE="mysql --defaults-extra-file=$HOME/migration.cnf $DEST_DBNAME < $MIGRATION_FILE.sql"

In your migration.cnf:

[client]
host = localhost
user = root
password = xxxxxxxx

If you're using MySQL 5.6 or later, you should also learn about mysql_config_editor so you can store those account credentials in an encrypted file.

Re your comment:

Is there some issue using a less than sign in a command in a bash variable?

Yes there is an issue. It seems the order of evaluation in shell processes redirection before variable expansion. My apologies for not noticing that before.

If I were writing your script I would write it either this way:

DO_DATABASE_UPDATE="mysql ...opts..."
echo 'Running local updates...'
$DO_DATABASE_UPDATE $DEST_DBNAME < $MIGRATION_FILE.sql

Or else this way:

DO_DATABASE_UPDATE="mysql ...opts... $DEST_DBNAME -e 'source $MIGRATION_FILE.sql'"
echo 'Running local updates...'
$DO_DATABASE_UPDATE

edited Dec 13, 2016 at 22:37

answered Nov 18, 2016 at 2:40

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

2 Comments

MillerMedia Over a year ago

I'm running it from Terminal on my Mac so I'm guessing that's why it's not prompting me? That would definitely be the problem, I didn't realize that was the case. I'll definitely try this. I like the conf file solution. I'll let you know what I find when I get back to my computer. Thanks for the quick response!

MillerMedia Over a year ago

I've been playing with this again and it doesn't want to run the update script. It will run up to the < character but then doesn't work. If I break it out of the variable declaration it works or if I stop the variable declaration at the < and do it like $DO_DATABASE_UPDATE < $MIGRATION_FILE.sql it works. Is there some issue using a less than sign in a command in a bash variable?

Collectives™ on Stack Overflow

Bash Script not running MySQL Command

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related