How to run MySQL query in a shell script

Last updated on July 23, 2020 by Dan Nanni

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.

If you find this tutorial helpful, I recommend you check out the series of bash shell scripting tutorials provided by Xmodulo.

Support Xmodulo

This website is made possible by minimal ads and your gracious donation via PayPal or credit card

Please note that this article is published by Xmodulo.com under a Creative Commons Attribution-ShareAlike 3.0 Unported License. If you would like to use the whole or any part of this article, you need to cite this web page at Xmodulo.com as the original source.