Skip to content

# Ways to export MySQL result set to file on client side

MySQL client export August 15, 2012

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 client, you get a very customizable file write:

>     bash$ mysql -h db_host --execute "SELECT * FROM my_table" > /tmp/output.txt

The above writes fancy tables, so you probably want to:

>     bash$ mysql -h db_host --execute "SELECT * FROM my_table" --bat > /tmp/output.txt

Also try:

>     bash$ mysql -h db_host --execute "SELECT * FROM my_table" --silent --raw > /tmp/output.txt

To turn off headers.

More options include the –xml or –html formats.

#### 2. tee

tee works similarly to a unix tee command. From the mysql command line, issue:

>     mysql> tee file_name.txt

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.

This means result sets are written in table format (what with all the +—+—–+—+ frames), which is not most convenient to parse later on.

#### 3. pager

If you’re on unix/linux, you have a third option: use the pager command to write result sets to file. For example, use:

>     mysql> pager cat - >> /tmp/pager_output.txt

This will work similarly to the tee command, but will not verbose to screen. In the above we append results to file.

#### 4. pager, Unix tee

So, while we’re at it, one can:

>     mysql> pager tee /tmp/tee_file.txt

The above will rewrite the file for every result set. Play at your own leisure to generate different files. For example, use

>     mysql> pager tee /tmp/tee_$(date +%M%H)_file.txt

for timestamp signature.

pager + unix command open an endless gateway of opportunities. Use awk to get rid of table frames. Use grep to filter frame rows out. What have you.

#### 5. GUI Clients

I’m pretty sure your favorite GUI client does that, as well. Like the mysql client, you will need direct access to the DB server.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
8
Leave a Reply

8 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
7 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Daniël van Eeden

mysqldump can be used to generate output in CSV (comma or tab separated), XML and SQL format.

Guest
Peter Laursen

But it would be nice to have “SELECT .. INTO [LOCAL] OUTFILE [CHARACTER SET ..] .. ;”.

The option for XML export by ‘mysqldump’ was added in 5.5 (ported from 6.0). Not sure about CSV. Maybe a 5.6 feature?

Author
shlomi

@Daniel,
Thanks!

@Peter:
Seems like –xml is already present in 5.1 (tested with 5.1.51)

mysqldump –tab=/path/to/files is very old, as well, and have been used by mk-parallel-dump for CSV export.

Guest
Sheeri

–xml and –html have been in mysqldump for a long time, I think even 4.1 had it.

As for character set, you set the character set you want in your client.

Also, many GUIs don’t need a direct connection, they’ll do an ssh login/tunnel for you.

Guest
Anders Karlsson

Also try my mysqljsonexport that allows you to export MySQL data in JSON format, and you can pass an ad-hoc SQL statement to export, in addition to exporting complete tables. Mysqljsonexport is available on sourceforge

Guest
Peter Laursen

@Sheeri ..

I requested the CHARACTER SET option for (not now existing) LOCAL OUTFILE option. If makes perfectly sense to SELECT INTO OUTFILE and specify the encoding of the file different from the client character set.

If I am issuing the commant from a utf8 client I could still want the file encoded in cp1251 or big5, for instance.

Guest
Shantanu Oak

It is also possible to start mysql with –tee option.
I have saved it as an alias to ~/.bashrc file
alias mysql_new=’mysql –tee=”/home/sqltee.txt”‘

Guest
Kishor Pawar

select into outfile is useful when you may want to take incremental backup of a single table.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.