How to Run MySQL/MariaDB Queries Directly from the Linux Command Line

If you are in charge of managing a database server, from time to time you may need to run a query and inspect it carefully. While you can do that from the MySQL / MariaDB shell, but this tip will allow you to execute the MySQL/MariaDB Queries directly using the Linux command line AND save the output to a file for later inspection (this is particularly useful if the query return lots of records).

Let us look at some simple examples of running queries directly from the command line before we can move to a more advanced query.

To view all the databases on your server, you can issue the following command:

# mysql -u root -p -e "show databases;"

Next, to create a database table named tutorials in the database tecmintdb, run the command below:

$ mysql -u root -p -e "USE tecmintdb; CREATE TABLE tutorials(tut_id INT NOT NULL AUTO_INCREMENT, tut_title VARCHAR(100) NOT NULL, tut_author VARCHAR(40) NOT NULL, submissoin_date DATE, PRIMARY KEY (tut_id));"

We will use the following command and pipe the output to the tee command followed by the filename where we want to store the output.

Suggested Read: 20 MySQL/MariaDB Commands for Database Administration in Linux

For illustration, we will use a database named employees and a simple join between the employees and salaries tables. In your own case, just type the SQL query between the quotes and hit Enter.

Note that you will be prompted to enter the password for the database user:

# mysql -u root -p -e "USE employees; SELECT DISTINCT A.first_name, A.last_name FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE hire_date < '1985-01-31';" | tee queryresults.txt

View the query results with the help of cat command.

# cat queryresults.txt
Run MySQL/MariaDB Queries from Commandline
Run MySQL/MariaDB Queries from Commandline

With the query results in a plain text files, you can process the records more easily using other command-line utilities.

Summary

We have shared several Linux tips that you, as a system administrator, may find useful when it comes to automating your daily Linux tasks or performing them more easily.

Suggested Read: How to Backup and Restore MySQL/MariaDB Databases

Do you have any other tips that you would like to share with the rest of the community? If so, please do so using the comment form below.

Otherwise, feel free to let us your thoughts about the assortment of tips that we have looked at, or what we can add or possibly do to improve each of them. We look forward to hearing from you!

Hey TecMint readers,

Exciting news! Every month, our top blog commenters will have the chance to win fantastic rewards, like free Linux eBooks such as RHCE, RHCSA, LFCS, Learn Linux, and Awk, each worth $20!

Learn more about the contest and stand a chance to win by sharing your thoughts below!

Gabriel Cánepa
Gabriel Cánepa is a GNU/Linux sysadmin and web developer from Villa Mercedes, San Luis, Argentina. He works for a worldwide leading consumer product company and takes great pleasure in using FOSS tools to increase productivity in all areas of his daily work.

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

3 Comments

Leave a Reply
  1. Search for how to use the mysql_config_editor command. It stores the data encrypted.

    You set it up with:

    # mysql_config_editor set --login-path=myserver --host=dbserver.foo.com --user=adam -p
    

    Then you can do:

    alias sqlcmd='mysql --login-path=myserver  -Dmydbname  --execute'
    

    And finally:

    sqlcmd 'DESC table'
    
    Reply
  2. Maybe we can create a file name ‘.my.cnf’ under user home directory, add mysql user, password in it, like this
    [client]
    user=USERNAME
    password=USERPASSWD

    then change its mode as 600, so we can use command ‘mysql’ directly without set parameter ‘-p’, and password prompt would not appear.

    Reply
  3. Wow , someone should make a tutorial on how to make a READ ONLY user for that tutorial. Even if the password is prompted, newcomers would easily add the root password to a script and pat themselves on the back.

    Reply

Got Something to Say? Join the Discussion...

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.