Use SSH to export query results from MySql

Steve McDaniel
Steve McDaniel used Ask the Experts™
on
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?

Basically

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

Thanks in advance
Comment
Watch Question

Do more with

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

Commented:
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:127.0.0.1:3306 user@11.22.33.44

where 11.22.33.44 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 127.0.0.1 -P 3307 <dbname>

HTH,
Dan

Commented:
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 127.0.0.1 -P 3307 <dbname> -e "select first,phone from tablename where active='y'" > result.txt

Open in new window

David FavorFractional CTO
Distinguished Expert 2018

Commented:
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