Use SSH to export query results from MySql

Steve McDaniel
Steve McDaniel used Ask the Experts™
I would like to run a query using SSH and export those results to a csv file.

I can connect to the DB using SSH and run the query, but how do I get it to output the results to a file vs screen output?


Connect to MySql vis SSH
Query select first,phone from tablename where active='y'
output to leads.csv

Thanks in advance
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Easiest way is to tunnel MySQL over SSH. Then you're working locally and can do pretty much whatever you want, including exporting to csv.

ssh -fNg -L 3307: user@

where is the IP of the MySQL server and 'user' is the user you're using to authenticate over ssh.

Once the tunnel is up, simply use:
mysql -u<username> -p -h -P 3307 <dbname>


BTW, as a direct answer: if you can connect to MySQL, you only need the "-e" flag (run query) and ">" (redirect):

mysql -u<username> -p -h -P 3307 <dbname> -e "select first,phone from tablename where active='y'" > result.txt

Open in new window

David FavorFractional CTO
Distinguished Expert 2018

You may also prefer using -Bse over -e as the output will be more... .csv + more scriptable or programmatically useful.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial