{"id":4081,"date":"2012-08-15T11:23:23","date_gmt":"2012-08-15T09:23:23","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4081"},"modified":"2012-08-15T11:23:23","modified_gmt":"2012-08-15T09:23:23","slug":"ways-to-export-mysql-result-set-to-file-on-client-side","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/ways-to-export-mysql-result-set-to-file-on-client-side","title":{"rendered":"Ways to export MySQL result set to file on client side"},"content":{"rendered":"

Problem<\/strong>: you wish to write table data to file, but you wish to do so on client side.<\/p>\n

SELECT … INTO OUTFILE<\/strong> writes the file on server. What are your options on client?<\/p>\n

1. mysql client<\/h4>\n

If you have direct access from your client machine to your DB server machine, and can connect via mysql<\/strong> client, you get a very customizable file write:<\/p>\n

\n
bash$ mysql<\/strong> -h db_host --execute \"SELECT * FROM my_table\" > \/tmp\/output.txt<\/pre>\n<\/blockquote>\n
The above writes fancy tables, so you probably want to:<\/p>\n
\n
bash$ mysql<\/strong> -h db_host --execute \"SELECT * FROM my_table\" --bat > \/tmp\/output.txt<\/pre>\n<\/blockquote>\n
Also try:<\/p>\n
\n
bash$ mysql<\/strong> -h db_host --execute \"SELECT * FROM my_table\" --silent --raw > \/tmp\/output.txt<\/pre>\n<\/blockquote>\n
To turn off headers.<\/p>\n
More options include the –xml<\/strong> or –html<\/strong> formats.<\/p>\n
2. tee<\/h4>\n
tee<\/strong> works similarly to a unix tee<\/em> command. From the mysql command line, issue:<\/p>\n
\n
mysql> tee<\/strong> file_name.txt<\/em><\/pre>\n<\/blockquote>\n
Anything you type from that moment on, and anything returned from the server, are written to given file, in the same format by which they are displayed on your client.<\/p>\n
This means result sets are written in table format (what with all the +—+—–+—+<\/strong> frames), which is not most convenient to parse later on.<\/p>\n
3. pager<\/h4>\n
If you’re on unix\/linux, you have a third option: use the pager<\/strong> command to write result sets to file. For example, use:<\/p>\n
\n
mysql> pager<\/strong> cat - >> \/tmp\/pager_output.txt<\/pre>\n<\/blockquote>\n
This will work similarly to the tee command, but will not verbose to screen. In the above we append<\/em> results to file.<\/p>\n
4. pager, Unix tee<\/h4>\n
So, while we’re at it, one can:<\/p>\n
\n
mysql> pager<\/strong> tee<\/strong> \/tmp\/tee_file.txt<\/pre>\n<\/blockquote>\n
The above will rewrite the file for every result set. Play at your own leisure to generate different files. For example, use<\/p>\n
\n
mysql> pager<\/strong> tee<\/strong> \/tmp\/tee_$(date +%M%H)_file.txt<\/pre>\n<\/blockquote>\n
for timestamp signature.<\/p>\n
pager<\/strong> + unix command open an endless gateway of opportunities. Use awk<\/strong> to get rid of table frames. Use grep<\/strong> to filter frame rows out. What have you.<\/p>\n
5. GUI Clients<\/h4>\n
I’m pretty sure your favorite GUI client does that, as well. Like the mysql<\/strong> client, you will need direct access to the DB server.<\/p>\n","protected":false},"excerpt":{"rendered":"
Problem: you wish to write table data to file, but you wish to do so on client side. SELECT … INTO OUTFILE writes the file on server. What are your options on client? 1. mysql client If you have direct access from your client machine to your DB server machine, and can connect via mysql […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[86,87],"class_list":["post-4081","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-client","tag-export"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-13P","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4081","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4081"}],"version-history":[{"count":23,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4081\/revisions"}],"predecessor-version":[{"id":5237,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4081\/revisions\/5237"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4081"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4081"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4081"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}