MySQL is a popular database used by many websites & organizations around the world. Typically, people log into MySQL console or GUI tool like Workbench to connect to MySQL database and execute SQL queries. But sometimes you may need to run MySQL queries directly from command prompt or terminal. Sometimes you may even need to add it to a shell script. In this article, we will learn how to run MySQL query from command line.
How to Run MySQL Query from Command Line
You can easily run MySQL query from command line using the MySQL command along with -e option.
$ mysql -h <host> -u <username> -p <password> <database> -e <SQL_query>
In the above command, you need to provide username, password, database name to connect with MySQL database. You need to specify the SQL query to be executed after -e option.
Here is a simple example to run SQL query by connecting to a local database.
$ mysql -utest_user -ptest_password -hlocalhost db -e "SELECT * FROM table;"
You can also omit the database name argument ‘db’ if you are referring to it in your SQL query.
$ mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table;"
Adding semicolon at the end of your SQL query is optional. But if you want to execute multiple SQL queries, you need to separate them using semi colons as shown in the following example.
$ mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table; SELECT * from db.table2;"
You can also execute separate MySQL commands with -e option if you want.
$ mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table;" $ mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table2;"
You can also store your SQL queries in a file and instruct MySQL to read the file. Let us say you have a file queries.txt
$ vi queries.txt
with the following queries.
SELECT * FROM db.table; SELECT * FROM db.table2;
Now if you want to execute the queries in your queries.txt file, you can do so using the following command. In this case, MySQL will read input from file queries.txt.
$ mysql -utest_user -ptest_password -hlocalhost < queries.txt
The key point to note is that you need to specify your SQL query within double quotes. If the above command syntax doesn’t work for you, try enclosing your SQL query within single quotes instead.
Also, if your SQL query itself contains double quotes, then you need to escape them by adding backslash before them.
The result of above query will be displayed on console. If you want to store this result in another file, you will need to use redirection operators. Here is an example to store the result of MySQL query in test.txt file.
$ mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table;" > /home/ubuntu/test.txt
One of the advantages of running MySQL queries in command line is that you can pipe the output to other Linux commands as per your requirement. Here is a simple example where we pass the output of MySQL query to grep command.
$ mysql -utest_user -ptest_password -hlocalhost -e "SELECT * FROM db.table;" | grep 'abc'
In this article, we have learnt how to execute MySQL queries from command line, store their output to files, and even pipe them to other Linux commands for further processing. You can use them effectively in a shell script to automatically fetch data, process it and take action depending on the retrieved information.
Also read:
How to Restore MongoDB Dump in Windows & Linux
Script to Keep Your Computer Awake
How to Extract Database from MySQL Dump File
How to Extract Table from MySQL Dump File
How to Extract Tables from PDF in Python