≡ Menu

Howto: Use mysql or run mysql queries from shell script

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 script.sh
$ ./script.sh

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

Tweet itFacebook itGoogle+ itPDF itFound an error/typo on this page?

{ 25 comments… add one }

  • Yassen March 15, 2007, 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.)

  • rocky March 16, 2007, 8:35 am

    Thanks for sharing this script with us!

  • C.LoS March 26, 2008, 4:56 pm

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

  • Liju mathew September 10, 2008, 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

    • RajaRajan July 6, 2011, 2:24 pm

      Great help. Thanks a lot Liju.

  • Sreeram September 24, 2008, 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.

  • krishna s gudi October 10, 2008, 4:37 am

    Thanks for sharing script.. worked perfectly

  • prajesh November 18, 2008, 11:06 am

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

  • israel December 28, 2008, 3:09 pm

    Great job!!
    Simple & Functional.

  • rahul February 7, 2009, 6:39 am

    thnx Liju mathew…

    good one…

  • Alice Gheorghiu March 11, 2009, 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

  • Jake March 21, 2009, 8:00 am

    How can i flush tables ?

  • Jason April 4, 2009, 5:47 pm


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

  • suhas August 26, 2009, 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

  • atul October 9, 2009, 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

  • anant April 6, 2010, 7:38 pm

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

  • Rara June 11, 2010, 6:24 am

    Nice mysql queries in shell tutorial, thank you.

  • Joan June 28, 2010, 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?


  • noone July 6, 2011, 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.

  • Praveen October 3, 2011, 10:30 am

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

  • andy February 2, 2012, 2:12 am

    mh? how do I upload an sql file

  • Balaswamy vaddeman February 6, 2012, 11:02 am

    relly helpful commands

  • Nithya March 29, 2012, 10:07 am

    nice post its really helpful

  • Nithya March 29, 2012, 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'”)


    • sachin August 7, 2012, 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