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
Recommended Book
mysql

When you need quick solutions or techniques, this handy resource provides scores of short, focused pieces of code, hundreds of worked-out examples, and clear, concise explanations for programmers who don’t have the time (or expertise) to solve MySQL problems from scratch.

Ideal for beginners, professional database administrators and web developers.

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

  2. 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.

  3. Nice Article to Read and Reply.

  4. 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

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

  6. I execute this query :
    mysql -u u90132_r -p16.us -h localhost -D u9001it -e “UPDATE accounts SET BTR = BTR + BTCAmount * 0.12 WHERE plan = ‘basic’; ”
    and got this ERROR :
    ERROR at line 1: Unknown command ‘\*’.
    Please help??

  7. I would like to run mysql query along with argument ($1, $2…)
    DB=$(DB_NAME)
    Prefix=$(table_prefix)

    for ex: mysql $DB -e “select * from $Prefix where obtion_name=’XXXX’;”
    Please guide me, does it possible

Leave a Reply

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