Howto: Use mysql or run mysql queries from shell script

Posted on 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 and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

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

    Reply Report comment

  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.

    Reply Report comment

  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

    Reply Report comment

  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

    Reply Report comment

  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

    Reply Report comment

  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.

    Reply Report comment

  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

    Reply Report comment

    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

      Reply Report comment

  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

    Reply Report comment

Leave a Comment