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

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

  1. If the query is using backsticks then EOF at the beginning of query should be ‘EOF’

  2. Hi
    Can we use bash variables for username, dbname. Like
    dbuser=us
    dbname=mydb
    And use them in mysql command like
    Mysql -u$dbuser.
    If so it can be used please provide an example

  3. Nice Article to Read and Reply.

  4. Hello
    Is that popssible to pass parameters to a SQL file called in script bash ?
    Like ; “mysql -h localhost -usupport -pabc123 -B -e SqlDir/sqlFile.sql $param1 ?
    Inside sqlFile i would have :
    “select * from orders where ordernumber=$param1”
    t.

  5. 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';"
      1. Thanks.. And what this option means «-B -e»? You can reply russian language

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.