Howto: Use mysql or run mysql queries from shell script

in Categories , , , , , , , , last updated January 29, 2007

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.

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.

Share this on (or read 28 comments/add one below):

28 comment

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

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

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

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

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

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

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

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

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

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

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

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

    Have a question? Post it on our forum!