Solved

How to connect Mysql server with shell script in Linux

Posted on 2004-03-22
14
2,108 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:Avik77
  • 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:Avik77
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
 
LVL 44

Accepted Solution

by:
Karl Heinz Kremer earned 20 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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:Avik77
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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 demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now