Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to connect Mysql server with shell script in Linux

Posted on 2004-03-22
14
Medium Priority
?
2,163 Views
Last Modified: 2012-06-27
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
Comment
Question by:Avik Dasgupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
14 Comments
 
LVL 8

Expert Comment

by:da99rmd
ID: 10656198
Why not php i use it for chell scripting all the time.

/Rob
0
 
LVL 2

Author Comment

by:Avik Dasgupta
ID: 10659845
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
 
LVL 8

Expert Comment

by:da99rmd
ID: 10665199
Sry i have never seen it nevertheless, but i confident that some one has done it.

Happy hunting :)

/Rob
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 44

Accepted Solution

by:
Karl Heinz Kremer earned 60 total points
ID: 10673497
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
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
ID: 10673505
... 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
 
LVL 8

Expert Comment

by:da99rmd
ID: 10675236
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
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
ID: 10675700
... 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
 
LVL 8

Expert Comment

by:da99rmd
ID: 10675728
i vote for php :)
0
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
ID: 10675947
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
 
LVL 8

Expert Comment

by:da99rmd
ID: 10676046
Thats an official war declaration :-)
0
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
ID: 10678599
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
 
LVL 8

Expert Comment

by:da99rmd
ID: 10685079
It was only a joke :)
0
 
LVL 2

Author Comment

by:Avik Dasgupta
ID: 10695311
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
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
ID: 10695654
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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Have you ever been frustrated by having to click seven times in order to retrieve a small bit of information from the web, always the same seven clicks, scrolling down and down until you reach your target? When you know the benefits of the command l…
The purpose of this article is to fix the unknown display problem in Linux Mint operating system. After installing the OS if you see Display monitor is not recognized then we can install "MESA" utilities to fix this problem or we can install additio…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
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…

730 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