How do I launch Mysql from a BASH script

Hi,

I am new to MySQL and Linux (BASH).  I am attempting to run a SQL query from BASH.  I often do similar things using Oracle and KShell, but am new to the MySQL/Linux/BASH environment.

When I enter the following on the shell command line everything works fine:

mysql -h dmysql1 -u zz99 -p
Enter Password:
use serversdb
select * from table;
exit

However I need to execute this from a script.  So I have created a script called SelectTestdata.sh which contains the following:

mysql -h dmysql1 -u zz99 -p zz99123
mysql use serversdb
mysql < selectdata   (selectdata is a file with select * from testdata; in it.)

I then do the following:
zz99@dmysql1:~/batch/MySQL> bash SelectTestdata.sh

And it returns:
Enter password:

After entering my password it returns:
ERROR 1049 (42000): Unknown database 'zz99123'

So I have 2 questions:

1) Why is it asking for my password when I passed it the password in the SelectTestdata.sh script?
2) after keying in zz99123, why does it think zz99123 is the database name?

Any guidance will be appreciated (and rewarded!!!)

Thx,
Andy
LVL 2
abruskoAsked:
Who is Participating?
 
BioIConnect With a Mentor Commented:
sorry for crossposting...
The way I solve these kind of issues (but I am not sure whether this is the most efficient), is by writing all my mysql-command to 1 text file and use this as in input in the msyql-command.

e.g. you create a file "sqlcommands.sql" containing the command:

mysqlimport -h dmysql1 -u zz99 -pzz99123 serversdb --local selectdata
(off course, you can add some more sql-commands).

Next, you run all  these sql-commands in the .sql-file by using the shell command:
mysql -h dmysql1 -u zz99 -pzz99123 serversdb < sqlcommands.sql
0
 
msklizmantasConnect With a Mentor Commented:
hi,

try this:

mysql -h dmysql1 -u zz99 -p zz99123 serversdb

from batch script you should provide database name, afterwards you can pass sql queries like you do :)

regards,

m
0
 
BioICommented:
hi

It is not allowed to use a space between -p and your password.
Try this: mysql -h dmysql1 -u zz99 -pzz99123

regards
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
abruskoAuthor Commented:
Thanks alot guys...both of your answers were needed and both worked.

Except I still don't seem to be executing all of the lines of my SelectTestdat.sh script (which now looks like this):

mysql -h dmysql1 -u zz99 -pzz99123 serversdb
mysql < selectdata   (selectdata is a file with select * from testdata; in it.)

Why is line 2 (mysql < selectdata ) not executing?

Thanks again,
Andy
0
 
BioICommented:
abrusko,

It does not work because your are first starting op mysql, and then pass a command "mysql < selectdata" that needs to be executed in the shell prompt, not whithin the mysql prompt!

Therefore some additional questtions:
=> does the table where you want to insert your data in, already exists?
In that case, you can consider using mysqlimport:
mysqlimport -h dmysql1 -u zz99 -pzz99123 serversdb selectdata
however, your textfile should have the same name as the tablename e.g. "testdata"
this way, you can execute everything in one shell-command

=> the other option, if you want to use the mysql-prompt-variant, is "load data infile"
LOAD DATA INFILE 'selectdata' INTO TABLE 'testdata'
0
 
abruskoAuthor Commented:
BioI,

Thank you very much for the info.

Here is my goal.  I need to learn how to execute batch SQL scripts (of any and all kind) against a MySQL DB from Linux using BASH.

I do this using UNIX, K Shell, PL/SQL and Oracle all the time, but am attempting to do it under the Linux/Bash/MySQL world.

I am working on developing a new system.  This new system will require some batch SQL to run at night to do various things to the tables in the system.

So I thought as long as I know how to access MySQL from BASH, then I can write whatever SQL statements are needed.

The technique I have always used in the UNIX/K Shell/Oracle world was to write a K Shell script that called a PL/SQL script.  The PL/SQL script contained whatever SQL was needed.

Does this same approach work with Linux/Bash/MySQL or do I need to use a whole different technique?

Thanks again for your time and effort!

Andy
0
 
BioICommented:
some extra hints:

for mysqlimport: if you do not have access to the server, you should add the parameter --local to your shell-command, and run the mysqlimport command from the directory where the text-file ("selectdata")  is stored:
mysqlimport -h dmysql1 -u zz99 -pzz99123 serversdb --local selectdata

Same goes up for the myslq-prompt command:
LOAD DATA LOCAL INFILE 'selectdata' INTO TABLE 'testdata'
where testdata is a file stored in the directory from which you started the mysql-client
0
 
abruskoAuthor Commented:
BioI,

Is mysqlimport only used for importing data?  Would I execute mysqlimport if I just want to do a simple "select"?

Andy
0
 
msklizmantasCommented:
put your sql statements in file, let's say: my.sql
afterwards create shell script, which will execute such commands:

cat my.sql | mysql -h dmysql1 -u zz99 -p zz99123 serversdb

m
0
 
BioICommented:
yes, you can also execute some select commands using the method described in my previous post and write them to a file.

e.g. you can create a .sql-file containing:
LOAD DATA LOCAL INFILE 'selectdata' INTO TABLE testdata
SELECT * FROM test testdata WHERE status = 1 into outfile;

(!! you need 'file'-privileges to execute this last command)

PS in my previous example, you need off course to paste the line:
LOAD DATA LOCAL INFILE 'selectdata' INTO TABLE testdata
in your .sql-file
not the mysql-import

0
 
abruskoAuthor Commented:
Thanks, BioI and m,

I will mess around with your suggestions and see if my feeble brain can get my simple "select * from testdata" to run and produce some output somewhere.

I'll either be back later crying or happy!

Stay tuned...thanks.

Andy
0
 
abruskoAuthor Commented:
Got it working...thanks very BioI & m.

Andy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.