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

Uptime:                 4 hours 59 min 56 sec

You can add code as follows from shell script:

#!/bin/bash
mysql dbnane<<EOFMYSQL
SELECT * from table;
EOFMYSQL

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

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

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 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.)

Reply

2 rocky March 16, 2007 at 8:35 am

Thanks for sharing this script with us!

Reply

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

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

Reply

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

Reply

5 RajaRajan July 6, 2011 at 2:24 pm

Great help. Thanks a lot Liju.

Reply

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.

Reply

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

Thanks for sharing script.. worked perfectly

Reply

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.

Reply

9 israel December 28, 2008 at 3:09 pm

Great job!!
Simple & Functional.
Igs

Reply

10 rahul February 7, 2009 at 6:39 am

thnx Liju mathew…

good one…

Reply

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

Reply

12 Jake March 21, 2009 at 8:00 am

How can i flush tables ?

Reply

13 Jason April 4, 2009 at 5:47 pm

Jake

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

Reply

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

Reply

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

Reply

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

Reply

17 Rara June 11, 2010 at 6:24 am

Nice mysql queries in shell tutorial, thank you.

Reply

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’);
EOFMYSQL

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?

Thanks

Reply

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.

Reply

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.

Reply

21 andy February 2, 2012 at 2:12 am

mh? how do I upload an sql file

Reply

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

relly helpful commands

Reply

23 Nithya March 29, 2012 at 10:07 am

nice post its really helpful

Reply

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'”)

Thanks

Reply

25 sachin August 7, 2012 at 6:01 am

FLUSH TABLES WITH READ LOCK;
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
UNLOCK TABLES;

any one send the procedure of rum above command using script

Reply

Leave a Comment

Tagged as:

Previous Faq:

Next Faq: