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
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
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
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
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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
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
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'
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'
ASKER
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
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
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
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
ASKER
BioI,
Is mysqlimport only used for importing data? Would I execute mysqlimport if I just want to do a simple "select"?
Andy
Is mysqlimport only used for importing data? Would I execute mysqlimport if I just want to do a simple "select"?
Andy
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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
afterwards create shell script, which will execute such commands:
cat my.sql | mysql -h dmysql1 -u zz99 -p zz99123 serversdb
m
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
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
ASKER
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
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
ASKER
Got it working...thanks very BioI & m.
Andy
Andy
It is not allowed to use a space between -p and your password.
Try this: mysql -h dmysql1 -u zz99 -pzz99123
regards