How do you run a single query through mysql from the command line?

Essential tips and pro-level tricks

Running queries on remote databases

If the MySQL database is hosted remotely, include -h <hostname> in your command:

mysql -u user -p -h host -e "SELECT * FROM table;" db_name


Switch out the host with the IP address or hostname of your MySQL server.

Viewing outputs in table format

To receive the MySQL output in tabular format - because who doesn't like organized data - incorporate the -t flag:

mysql -u user -p -t -e "SELECT * FROM table;" db_name


You'll instantly fall in love with the carefully crafted ASCII tables!

Security is a priority

NEVER include passwords directly in the command line for the world to see. Let the MySQL client prompt for it - it's more secure and less error-prone.

Scripting for productivity

Save frequently used queries in a script file for greater efficiency:

# Let's create a file, SQL style!
echo "SELECT * FROM table;" > my_life_in_sql.sql
# And run it
mysql -u user -p db_name < my_life_in_sql.sql


Best practices for SQL rockstars

No more shell expansion

Mentioning -e in single quotes keeps unwanted shell expansions at bay, making your operation more secure.

Trial before you sail

Run a safety check with a simple query before deploying larger scripts:

mysql -u user -p -e "SELECT 'If this works, then I\'m a SQL genius!';" db_name


No interaction? No problem!

Execute your command without an interactive shell by piping the input to the MySQL client:

echo "UPDATE table SET column='value' WHERE id=1;" | mysql -u user -p db_name
# And like that, we've lit the spark for a revolution in the tables!


Always check the docs

The MySQL official documentation is your navigation guide for exploring command options and best practices. It's constantly updated and packed with details, much like this post!