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
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.
Hello. You should use double quotes:
Thanks.. And what this option means «-B -e»? You can reply russian language
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.
Nice Article to Read and Reply.
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
If the query is using backsticks then EOF at the beginning of query should be ‘EOF’
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??
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
Best summary ever. Thanks for shering.
Please, if you publish it (no need. Only writing to thank you), edit the typo in the comment. Should be “sharing” :_-(
This content was very helpful. Thanks a lot for sharing and the effort taken.
A. Angayarkanni