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.

ADVERTISEMENTS

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.

🐧 If you liked this page, please support my work on Patreon or with a donation.
🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
28 comments… add one
  • Yassen Mar 15, 2007 @ 10:11

    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 Mar 16, 2007 @ 8:35

    Thanks for sharing this script with us!

  • C.LoS Mar 26, 2008 @ 16:56

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

  • Liju mathew Sep 10, 2008 @ 3:57

    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 Jul 6, 2011 @ 14:24

      Great help. Thanks a lot Liju.

  • Sreeram Sep 24, 2008 @ 10:03

    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 Oct 10, 2008 @ 4:37

    Thanks for sharing script.. worked perfectly

  • prajesh Nov 18, 2008 @ 11:06

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

  • israel Dec 28, 2008 @ 15:09

    Great job!!
    Simple & Functional.
    Igs

  • rahul Feb 7, 2009 @ 6:39

    thnx Liju mathew…

    good one…

  • Alice Gheorghiu Mar 11, 2009 @ 16:30

    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

    • Alvaro Pereyra Apr 30, 2016 @ 10:53

      I am wondering the same thing I guess it automatically exits. Did you ever figure it out?

  • Jake Mar 21, 2009 @ 8:00

    How can i flush tables ?

  • Jason Apr 4, 2009 @ 17:47

    Jake

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

  • suhas Aug 26, 2009 @ 11:00

    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

    • kino Mar 10, 2016 @ 1:52

      Hi Sir,

      You will need to use a crontab/cron if you are planning to automate your script.

  • atul Oct 9, 2009 @ 19:13

    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 Apr 6, 2010 @ 19:38

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

  • Rara Jun 11, 2010 @ 6:24

    Nice mysql queries in shell tutorial, thank you.

  • Joan Jun 28, 2010 @ 3:45

    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

  • noone Jul 6, 2011 @ 10:17

    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 Oct 3, 2011 @ 10:30

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

  • andy Feb 2, 2012 @ 2:12

    mh? how do I upload an sql file

  • Balaswamy vaddeman Feb 6, 2012 @ 11:02

    relly helpful commands

  • Nithya Mar 29, 2012 @ 10:07

    nice post its really helpful

  • Nithya Mar 29, 2012 @ 10:10

    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

    • sachin Aug 7, 2012 @ 6:01

      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

  • Shilpa Jan 8, 2016 @ 5:29

    When i try to put statement as “user dbname; select * from table where id=’123′;”

    I am getting warning as :
    Warning: Using a password on the command line interface can be insecure.

    Unable to get output as well.

    Whereas if I use the condition without where clause i am able to get data. please suggest

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.