MySQL: Execute SQL Queries From The Linux Shell Execute SQL query from the Linux command-line: $ mysql -u USER -pPASSWORD -e ...

MySQL: Execute SQL Queries From The Linux Shell

Posted by Harry Posted on December 23, 2017 with No comments

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

Editor's Choice

MySQL: Execute SQL Queries From The Linux Shell