?
Solved

How do I launch Mysql from a BASH script

Posted on 2007-10-02
12
Medium Priority
?
8,182 Views
Last Modified: 2011-04-14
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
0
Comment
Question by:abrusko
  • 5
  • 5
  • 2
12 Comments
 
LVL 6

Assisted Solution

by:msklizmantas
msklizmantas earned 600 total points
ID: 19998232
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
 
LVL 3

Expert Comment

by:BioI
ID: 19998310
hi

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

regards
0
 
LVL 2

Author Comment

by:abrusko
ID: 19998468
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:BioI
ID: 19998592
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
 
LVL 2

Author Comment

by:abrusko
ID: 19998694
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
 
LVL 3

Expert Comment

by:BioI
ID: 19998701
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
 
LVL 2

Author Comment

by:abrusko
ID: 19998736
BioI,

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

Andy
0
 
LVL 3

Accepted Solution

by:
BioI earned 1400 total points
ID: 19998740
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
 
LVL 6

Expert Comment

by:msklizmantas
ID: 19998767
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
 
LVL 3

Expert Comment

by:BioI
ID: 19998787
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
 
LVL 2

Author Comment

by:abrusko
ID: 19998876
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
 
LVL 2

Author Comment

by:abrusko
ID: 19999168
Got it working...thanks very BioI & m.

Andy
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month17 days, 1 hour left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question