Blog

How to Run SQL Query Using Bash Script and Command-Line?

While working as a developer, one needs to automate some existing Structured Query Language(SQL) Queries using bash script without accessing the interactive MySQL prompt. In this blog post, I will show the different ways to run SQL queries using Bash Script or using a command line. I will be using the MySQL database in this blog.

Before following this blog post, please make sure that you have a MySQL client and server installed. Use the below command to check if MySQL is installed on your machine.

which mysql

mysql --help

[maria_dev@sandbox-hdp root]$ which mysql
/bin/mysql
[maria_dev@sandbox-hdp root]$ mysql --help
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  --auto-vertical-output
                      Automatically switch to vertical output mode if the
                      result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)
  --bind-address=name IP address to bind to.

Execute SQL query from the Linux command-line

mysql -u USER_NAME -pPASS_WORD -h hostname -D database -e "<SQL_QUERY>"

The below table shows the different options we can have while running Queries.

OptionDescription
–user, -uMySQL user name account that is used to connect SQL Database Server.
–password, -pThe password that is used to connect to SQL Database.
–database, -DName of the Database that we need to connect.
–host, -hName of the host where the Database is Installed.
–skip-column-names, -NIt makes sure that Column names are not written in
–batch, -BIt is used to print results using a tab as the column separator, with each row on a new line.

Example To Show all Tables from Customer Tables

mysql -u root -padmin -e "USE customer;SHOW TABLES"

-pPASS_WORD When passing the PASS_WORD through the command line, we should not have a space between password and -p

Note: Here I am passing the password in the command Line for demonstration purposes. It is not recommended when running Queries in a Production environment.

Run SQL query on the explicitly specified host

To run a SQL query on an explicitly specified host, we use the -h option and specified hostname as HOSTNAME.

mysql -u USER -pPASSWORD -h HOSTNAME -e "SQL_QUERY"

Use the Below Link as an Example to connect to the localhost Host.

mysql -u retail_dba -phadoop -h localhost -e "USE world;SELECT * FROM CITY LIMT 20;"

Run SQL query on the specified database:

It will run the SQL query using the hostname defined by the -h parameter for specified HOSTNAME and database identified by DATABASE option with D parameter.

mysql -u USER -pPASSWORD -h <HOSTNAME> -D <DATABASE> -e "SQL_QUERY"

Example:

~/tutorials/rdbms$ mysql -u root -padmin -D employees -h localhost -e "SELECT  * FROM dept_emp LIMIT 20; "
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d005    | 1986-06-26 | 9999-01-01 |
|  10002 | d007    | 1996-08-03 | 9999-01-01 |
|  10003 | d004    | 1995-12-03 | 9999-01-01 |
|  10004 | d004    | 1986-12-01 | 9999-01-01 |
|  10005 | d003    | 1989-09-12 | 9999-01-01 |
|  10006 | d005    | 1990-08-05 | 9999-01-01 |
|  10007 | d008    | 1989-02-10 | 9999-01-01 |
|  10008 | d005    | 1998-03-11 | 2000-07-31 |
|  10009 | d006    | 1985-02-18 | 9999-01-01 |
|  10010 | d004    | 1996-11-24 | 2000-06-26 |
|  10010 | d006    | 2000-06-26 | 9999-01-01 |
|  10011 | d009    | 1990-01-22 | 1996-11-09 |
|  10012 | d005    | 1992-12-18 | 9999-01-01 |
|  10013 | d003    | 1985-10-20 | 9999-01-01 |
|  10014 | d005    | 1993-12-29 | 9999-01-01 |
|  10015 | d008    | 1992-09-19 | 1993-08-22 |
|  10016 | d007    | 1998-02-11 | 9999-01-01 |
|  10017 | d001    | 1993-08-03 | 9999-01-01 |
|  10018 | d004    | 1992-07-29 | 9999-01-01 |
|  10018 | d005    | 1987-04-03 | 1992-07-29 |
+--------+---------+------------+------------+

Suppressing column headings:

We use the -N option to Suppress the Column heading.

mysql -u USER -pPASSWORD -N -e "SQL_QUERY"

Save the output to a file

We can redirect the result to a certain location to save the output of a Query in the File using the > sign and the file name.

mysql -u USER -pPASSWORD -e "SQL_QUERY" > FILE

Example:

Here we are trying to save the result of the query in the /home/hduser/tutorials/rdbms/result.txt location.

hduser@hmaster:~/tutorials/rdbms$ mysql -u root -padmin -e "USE employees;SELECT * FROM departments LIMIT 200;" >/home/hduser/tutorials/rdbms/result.txt

hduser@hmaster:~/tutorials/rdbms$ cat result.txt
dept_no dept_name
d009    Customer Service
d005    Development
d002    Finance
d003    Human Resources
d001    Marketing
d004    Production
d006    Quality Management
d008    Research
d007    Sales

Run SQL Queries From A Bash Script

Example: Create a Shell script named shell_sql_single_query.sh

#!/bin/bash
mysql -u root -padmin -e "USE employees;SELECT * FROM departments LIMIT 200;"

Give proper permission to the shell script and run the script.

~$/tutorials/rdbms$ chmod a+x shell_sql_single_query.sh

~$/tutorials/rdbms$ ./shell_sql_single_query.sh
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+

Running Multiple SQL Queries using Shell Script

We can use the <<EOF...EOF based construction to run multiple SQL queries using Shell Script.

The <<EOF part tells the shell that it will be multi-lines SQL Query until the EOF tag.

We need to make sure that there are no spaces before the second EOF Tag. If there is any space, it will be considered as part of the SQL Query.

Instead of EOF we can use names like <<BASH_QUERY ... BASH_QUERY

mysql -u USER -pPASSWORD <<BASH_QUERY
QUERY 1
QUERY 2
QUERY N
BASH_QUERY

Passing Variable to SQL Query from Bash Script

Here we are passing the Database from the bash script to the SQL query.

#!/bin/bash
mysql -u root -psecret <<BASH_QUERY
USE $<DATABASE>;
SHOW tables;
BASH_QUERY

Below is an example script that shows how to run multiple SQL scripts with your own variable defined. We are defining two-variable called DATABASE_NAME and TABLE_NAME that are being used in the SQL query.

#!/bin/bash
#Example Script that shows passing of Variable
DATABASE_NAME=employees
TABLE_NAME=employees
mysql -u root -padmin <<SQL_QUERY
 USE $DATABASE_NAME;
SELECT * FROM departments LIMIT 200;
SELECT COUNT(*) FROM $TABLE_NAME;
SQL_QUERY

When we run the above query, we get the below results.

~$/tutorials/rdbms$ ./shell_sql_multiple_query.sh

dept_no dept_name
d009    Customer Service
d005    Development
d002    Finance
d003    Human Resources
d001    Marketing
d004    Production
d006    Quality Management
d008    Research
d007    Sales
COUNT(*)
300024

Running a .sql file

I created a simple SQL file in this location /home/hduser/tutorials/rdbms

USE employees;
SELECT * FROM salaries LIMIT 20;

To run this SQL file through the command line or Bash script, we need to use the below syntax.

msyql -u <UserName> -p<PassWord> -h <HostName> < <SQL_File_Location>

hduser@hmaster:~/tutorials/rdbms$ mysql -u root -padmin < /home/hduser/tutorials/rdbms/sample_script.sql
emp_no  salary  from_date       to_date
10001   60117   1986-06-26      1987-06-26
10001   62102   1987-06-26      1988-06-25
10001   66074   1988-06-25      1989-06-25
10001   66596   1989-06-25      1990-06-25
10001   66961   1990-06-25      1991-06-25
10001   71046   1991-06-25      1992-06-24
10001   74333   1992-06-24      1993-06-24
10001   75286   1993-06-24      1994-06-24
10001   75994   1994-06-24      1995-06-24
10001   76884   1995-06-24      1996-06-23
10001   80013   1996-06-23      1997-06-23
10001   81025   1997-06-23      1998-06-23
10001   81097   1998-06-23      1999-06-23
10001   84917   1999-06-23      2000-06-22
10001   85112   2000-06-22      2001-06-22
10001   85097   2001-06-22      2002-06-22
10001   88958   2002-06-22      9999-01-01
10002   65828   1996-08-03      1997-08-03
10002   65909   1997-08-03      1998-08-03
10002   67534   1998-08-03      1999-08-03

Conclusion

In this blog post, we have read how to use a Unix shell script to connect to the database and execute a SQL query. We also learn how to pass variables to SQL queries using a bash script. We also learn about how to run multiple SQL at once.

References

Run MySQL Query through Bash Script

Tags: , ,