Howto: Use mysql or run mysql queries from shell script

by on January 29, 2007 · 25 comments· LAST UPDATED January 29, 2007

in , ,

Q. I have job (executed from shell script) where I need to run mysql query from a shell script. I want to run mysql from within a shell script rather than use it interactively by typing mysql -u user -p dbnane command.

How do I use mysql in Shell Scripts?

A. I recommend using Perl or php as it is easier to get data from mysql. You can easily use APIs. BASH/KSH Shell does not provide access to mysql directly.

However you can pass sql command using mysql -e swich. Following query connect as root and print mysql server uptime:
$ mysql -u root -p -e STATUS | grep -i uptime

Uptime:                 4 hours 59 min 56 sec

You can add code as follows from shell script:

mysql dbnane<<EOFMYSQL
SELECT * from table;

Save and execute script as follows:
$ chmod +x
$ ./

I strongly recommend using perl or php as shell provides limited access.

Tweet itFacebook itG+ itDownload PDF versionFound an error/typo on this page?

{ 25 comments… read them below or add one }

1 Yassen March 15, 2007 at 10:11 am

Executing mysql queries that result in a single variable or a single column of values is very useful and straightforward:

result=$(mysql -u $user $db -sN -e “select distinct ttuState from path where state = ‘active'”)

(Note the options that suppress column names and pseudo-graphic characters.)


2 rocky March 16, 2007 at 8:35 am

Thanks for sharing this script with us!


3 C.LoS March 26, 2008 at 4:56 pm

Yassen.. that line right there is perfectt, thanks for that…


4 Liju mathew September 10, 2008 at 3:57 am

This vl more easier than all of above. Try this

mysql -uuser -hhost -ppassword -e “use mydb; show tables;” > /tmp/logs/querry-output.log


5 RajaRajan July 6, 2011 at 2:24 pm

Great help. Thanks a lot Liju.


6 Sreeram September 24, 2008 at 10:03 am

Thanks Liju

Tried out all options but only this worked:

mysql -uuser -hhost -p’password’ -e “use mydb; show tables;”

Please note the change for password: -p’pwd123′
This command would become part of a larger shell script I am working on.


7 krishna s gudi October 10, 2008 at 4:37 am

Thanks for sharing script.. worked perfectly


8 prajesh November 18, 2008 at 11:06 am

Thanks for sharing this script this is working perfectly, but I think it execute only one query at a time.


9 israel December 28, 2008 at 3:09 pm

Great job!!
Simple & Functional.


10 rahul February 7, 2009 at 6:39 am

thnx Liju mathew…

good one…


11 Alice Gheorghiu March 11, 2009 at 4:30 pm

Hi, I am working on automating a process of delivering some monthly reports by e-mail to a group of users. I implemented successfully multiple queries in one mysql call to the database, however I want to make it more actively such as to accept a date and a file name parameter. After I get these I programmatically do different things depending on the file name. Do I need to call mysql multiple times for each condition, or I can embed shell conditionals within mysql part? Also how would I exit a mysql process within the shell ? As of now it simply terminate the session to the server instead of getting back to the command line after execution.
Any tip would be really appreciated.
Thanks, — Alice


12 Jake March 21, 2009 at 8:00 am

How can i flush tables ?


13 Jason April 4, 2009 at 5:47 pm


truncate table is the fastest way to do it in mysql.


14 suhas August 26, 2009 at 11:00 am

hi All,
i need to send one report on daily basis to client by executing below command

zgrep “\*xxx\*xxx\#” XXX.log.2009-05-15 |cut -d “,” -f5|sort|uniq >111_15.txt
so i am planning to make this automated process
please help for this, requirement is as fallows

if date is 15 I need to take 14th data and need to send result to client automatically , how to right the shell script for this


15 atul October 9, 2009 at 7:13 pm

hi buddies i have a pro…..
my db name is atul
and table name is explem.i wanna access my table value from the shell script
plz help me


16 anant April 6, 2010 at 7:38 pm

i want to access a routing table and accsess a data given by bandwidth monitoring tool using shell scripting


17 Rara June 11, 2010 at 6:24 am

Nice mysql queries in shell tutorial, thank you.


18 Joan June 28, 2010 at 3:45 am

Hi everyone,

I’m trying to insert in one MySQL table the date of the server. I get the date of the server with this shell command:

date +%Y-%m-%d–%H:%M:%S

An this is my script:

fecha= date +%Y-%m-%d–%H:%M:%S
mysql rsync<Slave’);

when i check the database, i don’t get the real date of the insert. instead of that i get “fecha”.

How i can fix this?



19 noone July 6, 2011 at 10:17 am

Hi joan,

when you define your Variable in a script using the date command you need to add back ticks. (`)

so it should look like
fecha=`date +%Y-%m-%d–%H:%M:%S`

that should fix the problem.


20 Praveen October 3, 2011 at 10:30 am

I need to create an automated shell script which can execute the sql query and get the data from sql database.


21 andy February 2, 2012 at 2:12 am

mh? how do I upload an sql file


22 Balaswamy vaddeman February 6, 2012 at 11:02 am

relly helpful commands


23 Nithya March 29, 2012 at 10:07 am

nice post its really helpful


24 Nithya March 29, 2012 at 10:10 am

if you want to get connect another host db table values you can use it :

query=$(mysql -h hostname -u username dbName -sN -e “select bldurl from productdetails where productname=’MAIL'”)



25 sachin August 7, 2012 at 6:01 am

lvcreate -L4G -s -n dbbackup /dev/mynew_vg/data01
mkdir -p /mnt/dbbackup
mount /dev/mynew_vg/data01 /mnt/dbbackup/
tar -cf /home/db.tar /mnt/dbbackup/mysql
Unlock the tables

any one send the procedure of rum above command using script


Leave a Comment

Tagged as:

Previous Faq:

Next Faq: