Running MySQL commands from a shell script is a relatively simple task that has a lot of people baffled. Some say its too complicated and suggest using PHP or Perl, others claim doing so is a security risk (a favorite excuse of the ignorant), and some resort to using a shell script to writing SQL commands to a text file that MySQL would use as input. Below is a much more simple and direct way of generating and running complex SQL queries directly from a shell script without temporary files and without any security issues.

Let’s start with the basic idea:

echo "SELECT * FROM table_name" | mysql -u -p db_name

Using this method, you can pass any shell variables to MySQL. Aha, some will say, you have to put your password in the shell script and that is definitely not secure. You don’t have to: you can have your script prompt you for a password:

#!/bin/bash
echo -n "Enter username: " ; read db_user
echo -n "Enter $db_user password: " ; stty -echo ; read db_passwd ; stty echo ; echo ""
echo "SHOW DATABASES ;" | mysql --skip-column-names -u$db_user -p$db_passwd
echo -n "Enter database name: " ; read db_name
echo "SHOW TABLES ;" | mysql --skip-column-names -u$db_user -p$db_passwd $db_name
echo -n "Enter table name: " ; read table_name
echo "SELECT * FROM $table_name ;" | mysql -t -u$db_user -p$db_passwd $db_name

The script above will prompt your for the username and password (password will not be visible as you type it). It will then show you the list of available database and prompt you to select one. The script will then show you all the tables in that database and ask you to specify the name of the table you want to use. Finally, the script will select everything from that table. Everything is very simple, secure, and straightforward.