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 SQL queries to a database 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

2 Replies to “MySQL: Run Query from Bash Script or Linux Command Line”

  1. Hi I am running a query using -e. I am trying to run multiple queries like this

    mysql -h localhost -usupport -pabc123 -B -e ‘use test; select * from price.sku where dispsku=’test01′ ;’
    This throws me an error that unknown column in test01 in where clause

    However i can run
    mysql -h localhost -usupport -pabc123 -B -e ‘show databases; show databases;’
    and I get two outputs of both ‘show databases ;” statement.

    I have sucessfully executed my first query through bash but I am not able to run with -e as of now.

    1. Hello. You should use double quotes:

      mysql -h localhost -usupport -pabc123 -B -e "use test; select * from price.sku where dispsku='test01';"

Leave a Reply