Solved

How to connect Mysql server with shell script in Linux

Posted on 2004-03-22
14
2,117 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

770 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