How to run MySQL query in a shell script

When you are writing a shell script as part of some batch processing, there are cases when the script needs to process data stored in a separate MySQL server. The general-purpose script languages such as Perl and Python have separate MySQL modules or interfaces to use, but shell script languages do not have such an interface for MySQL. However, there are ways to run some simple MySQL queries, and process the result via a shell script.

The following is the guide on how to run a MySQL query in a shell script.

First, you need to install MySQL client on the host where you will be running your shell script.

Assuming that is the case, you can run the following shell script. The code example presented here runs a simple MySQL SELECT call, and processes the result which contains potentially multiple rows.

#!/bin/bash

result=`mysql -h $dbHost --user=$dbUser --password=$dbPassword --skip-column-names -e "select id from mydb.mytable where myattribute = 3"`

items=$(echo $result | tr " " "\n")

for item in $items
do
    echo "$item"
done

In the code snippet, the variable "result" will store multiple table entries concatenated by white space. So you need to split the result string into individual rows which are stored in "items" as an array. Finally, loop through individual rows to process each row as you wish.

Subscribe to Xmodulo

Do you want to receive Linux FAQs, detailed tutorials and tips published at Xmodulo? Enter your email address below, and we will deliver our Linux posts straight to your email box, for free. Delivery powered by Google Feedburner.


Support Xmodulo

Did you find this tutorial helpful? Then please be generous and support Xmodulo!

The following two tabs change content below.
Dan Nanni is the founder and also a regular contributor of Xmodulo.com. He is a Linux/FOSS enthusiast who loves to get his hands dirty with his Linux box. He likes to procrastinate when he is supposed to be busy and productive. When he is otherwise free, he likes to watch movies and shop for the coolest gadgets.

2 thoughts on “How to run MySQL query in a shell script

Leave a comment

Your email address will not be published. Required fields are marked *