Quick Links

You can make your bash scripts run differently based on the contents of a MySQL database by connecting to it from the command line and passing a query, which you can use in if blocks to branch based on a value.

Note: Our examples show using the root user on MySQL, but you would want to substitute a different user account there.

Run SQL Queries from a Bash Script

The syntax for running inside a bash script is the same as accessing a database from the command line:

mysql -u root -pPassword -h hostname -D dbname -e 'query'

If your database is running locally, you can omit the -h flag. If your running a development database, you can omit the -p flag if your database has no password.

This will output the results of the query to STDOUT, which you can pipe to a file:

mysql -u root -D dbname -e 'SELECT * FROM table' > file

...or store in a variable with the $( ) construct:

variable=$(mysql -u root -D dbname -e 'SELECT * FROM table')

But, the output won't be very pretty by default, so you can make it easier to work with by using the -B flag to print in Tab Separated Values (TSV) format and the -N flag to omit column headers.

Use -B flag to print in Tab Separated Values (TSV) format and -N flag to omit column headers.

TSV files are the lesser-used cousin of CSV files, which you can convert from the command line.

Checking a Specific Value and Branching

If you want to branch based on a value in your database, you can query for the specific row and column, and store the response in a variable. You can then use a bash if block to branch based on the contents of that variable.

field=$(mysql -u root -BNe 'USE test; SELECT label FROM test WHERE id=1')

if [ $field == 'TEST' ]; then

//do stuff

fi

This only works when you're requesting a specific value and performing a simple comparison. If you need more manipulation than bash provides, you can use awk, which can select out individual columns from text and perform comparisons:

if [ $(echo $field | awk '{if ($1 == value && $2 == value2) print 1; else print 0}') == "1" ]; then

//do stuff

fi

Here, the awk command prints out true or false (1 or 0), which the bash if can use to compare and branch.

Both of these examples assume your SQL query is only returning a single row, which you can assure by selecting based on ID. But if you're running a more complex query, you might want to limit the response to the first row with the SQL command LIMIT 1.

Error Checking: Verify If a Database or Table Exists

While you're probably writing this script specifically for your server, a bit of error checking never hurt anyone. You'll want to verify that the database and tables you'll be working with actually exist before you go messing with them. Also, if the connection to the database isn't working, it will error out here as well.

The SQL command 'USE dbname' will produce an error if dbname doesn't exist (return code > 0). You can use this in an if block to check if the database is properly configured:

if mysql -u root -e 'USE mydbname'; then

//database exists, do stuff

fi

You can invert the if block by starting the boolean with an !, which will run the code only when the database isn't configured, which is useful for doing first time setup.

Similarly, you can check if tables exist by trying to access the first row:

if mysql -u root -e 'USE mydbname; SELECT * FROM tablename LIMIT 1'; then

//database and table exist, do more stuff

fi

Because the if block checks the return code of whatever command you chuck into it, and MySQL commands will return an error code if they run into problems, you can use any command in an if block to account for errors.