Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2181
  • Last Modified:

How to connect Mysql server with shell script in Linux

HI,
  I am having problems in connecting the Mysql server in Linux with a normal shell script (not PERL or PHP etc). I want to send querries to the database about various information like select * from my_tab; etc..
My current Linux version is Debian.

Thanks in advance.
Avik.
{{ moved to Linux section -- DanRollins / EE Page Editor }}
0
Avik Dasgupta
Asked:
Avik Dasgupta
  • 6
  • 6
  • 2
1 Solution
 
da99rmdCommented:
Why not php i use it for chell scripting all the time.

/Rob
0
 
Avik DasguptaAuthor Commented:
Actually I don't have any knowledge about php or PERL. Anyway I am looking forward for a mechanism that can connect the mysql database through a shell script in bash.

Avik.
0
 
da99rmdCommented:
Sry i have never seen it nevertheless, but i confident that some one has done it.

Happy hunting :)

/Rob
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Karl Heinz KremerCommented:
You can use the mysql program to get access to data from the database:

 echo "select * from user;" | mysql -u root --password=root_password mysql

This will print the result of the query to stdout. You need to use awk or a similar program to process this output.
0
 
Karl Heinz KremerCommented:
... and if you have a more complex query, write it to a file and use this syntax:

cat /tmp/sql.file | mysql -u root --password=root_password mysql

Replace root with the user id you want to use, root_password with the actual password for the account, and the last mysql in the command line with the database you want to open.
0
 
da99rmdCommented:
Then if you use the way khremar say you can add this to get it in to a variable:
POSTS = "`cat /tmp/sql.file | mysql -u root --password=root_password (database name here)`"

/Rob

0
 
Karl Heinz KremerCommented:
... but you don't have to assign the output to a variable, you can just pipe it to a program that can process it:

cat /tmp/sql.file | mysql -u root --password=root_password mysql | some_program

You really should look into Perl, even though you may not use Perl directly to access your database, but it would be very simple to use a Perl program to parse the output of the mysql program. In a second step you could then actually use the Perl DB connectivity.
0
 
da99rmdCommented:
i vote for php :)
0
 
Karl Heinz KremerCommented:
At the end it does probably not matter which scripting language you use, but I still think that Perl has the best string processing functions. Perl was made for this type of stuff :-)
0
 
da99rmdCommented:
Thats an official war declaration :-)
0
 
Karl Heinz KremerCommented:
Not really, just stating the facts. Perl is acctually an acronym, and stands for "Practical Extraction and Report Language". And this is exactly what the problem here requires: extraction of data from the mysql output, processing and reporting it in different form.
This is the reason why the string routines in Perl are so powerful. Sure, you can do most (all?) task in other languages as well, but you usually have to do more work. I'll give you that the program in any other language will look better :-), and is very likely easier to maintain in the long run.

Where PHP really shines is the database integration. If you have a direct connection to a DB, you probably will not need the parsing capabilities of Perl anymore, so for what I called the second step, PHP would be a good choice.
0
 
da99rmdCommented:
It was only a joke :)
0
 
Avik DasguptaAuthor Commented:
Thank you khkremer, for giving throwing some light into it.
Actually, I tried to follow ur command
>>echo "select * from user;" | mysql -u root --pasword=root_password mysql
But it was not working, I tried

echo "select * from table;"|mysql -D database
and it worked, I am logged in as the root hence user name and password is not required, i presume.I give u points for directing me in the direction from which I could make at least something.
Thank u.
0
 
Karl Heinz KremerCommented:
Actually, it's not because you are logged in as root, it's because the root user in mysql has no password assigned. In this case, you don't need the --password=... (or -p in interactive mode) switch.
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now