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:
||The MySQL user name to use when connecting to the server.|
||The password to use when connecting to the server.|
||The database to use.|
||Connect to the MySQL server on the given host.|
||Do not write column names in results.|
||Print results using tab as the column separator, with each row on a new line.|
MySQL: Run SQL Queries From A Bash ScriptTo run multiple SQL queries or a single multi-line SQL query i use the
<<EOF part tells the shell that i am going to enter multi-lines until the
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.
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