MySQL: Run Query from Bash Script or Linux Command Line

Sometimes it is needed to run some MySQL queries from the Linux command-line interface without accessing the interactive MySQL prompt.

For example, when it is required to schedule a backup of MySQL database or to automate execution of some SQL queries with a Bash script.

In this article i will show the most useful, from my point of view, options of the MySQL command-line client and show how to run multiple queries from a Bash script.

Cool Tip: List MySQL users, their passwords and granted privileges from the command-line prompt! Read more →

MySQL: Execute SQL Queries From The Linux Shell

Execute SQL query from the Linux command-line:

$ mysql -u USER -pPASSWORD -e "SQL QUERY"

-pPASSWORD: This is not a typo! There should not be a space between your password and the -p when you pass it on the command-line!

Run SQL query on the explicitly specified database:

$ mysql -u USER -pPASSWORD -D DATABASE -e "SQL QUERY"

Run SQL query on the explicitly specified host:

$ mysql -u USER -pPASSWORD -h HOSTNAME -e "SQL QUERY"

Suppressing column headings:

$ mysql -u USER -pPASSWORD -N -e "SQL QUERY"

Suppress table borders:

$ mysql -u USER -pPASSWORD -B -e "SQL QUERY"

Save the output to a file:

$ mysql -u USER -pPASSWORD -e "SQL QUERY" > FILE

Cool Tip: Create a MySQL database and GRANT ALL PRIVILEGES on it to a user! Simple and clear MySQL tutorial with good examples! Read more →

The most useful MySQL options when executing SQL queries from the Linux command-line or a Bash script:

Option Description
--user, -u The MySQL user name to use when connecting to the server.
--password, -p The password to use when connecting to the server.
--database, -D The database to use.
--host, -h Connect to the MySQL server on the given host.
--skip-column-names, -N Do not write column names in results.
--batch, -B Print results using tab as the column separator, with each row on a new line.

MySQL: Run SQL Queries From A Bash Script

To run multiple SQL queries or a single multi-line SQL query i use the <<EOF...EOF construction.

The <<EOF part tells the shell that i am going to enter multi-lines until the EOF tag.

Note that here shouldn’t be any spaces just before the second EOF tag, otherwise it will be considered as a part of the SQL query.

By the way, you can rename the EOF to anything you want, e.g. <<MY_QUERY...MY_QUERY.

Cool Tip: Do you have a backup? You MUST have it! Backup MySQL databases from the command-line! This is really easy! Read more →

Use the following construction if you need to run multiple SQL queries from a Bash script:

mysql -u USER -pPASSWORD <<EOF
SQL QUERY 1
SQL QUERY 2
SQL QUERY N
EOF

Bash script example:

#!/bin/bash
mysql -u root -psecret <<MY_QUERY
USE mysql
SHOW tables
MY_QUERY