remote mysqldump to csv (--tab)

[Marijn Vandevoorde]

Hi all,

posted this in the backup list, but that one seems to be pretty dead, so i'll try my luck again here:


We're currently looking for a way to backup a pretty big mysql table to
a csv file. However, we don't want to allow the user ssh or file access
to the server, so it'd have to happen remotely. the --tab/-T option
allows exporting to csv, but not remotely.
So we're looking for a way to have mysqldump store these files remotely,
on the client where mysqldump is executed.
I've been looking up and down, only to find bad news, so I'm pretty sure
it's just not possible. I know there are ways to do this (little script
to convert the sql to csv, or pipe it all through sed), but we were just
wondering if we're really not overlooking a nifty option in mysqldump :-)

Thanks in advance

mavoo

[Carlos Eduardo Caldi]

Hi

You can use on shell, connect at the client mysq -h (host or IP) -p(password)

and run the query:

SELECT a, b, c INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table

more info see the link
http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html


Carlos Caldi

Date: Wed, 28 Apr 2010 14:11:14 +0200
Subject: remote mysqldump to csv (--tab)
Hi all,
We're currently looking for a way to backup a pretty big mysql table to
a csv file. However, we don't want to allow the user ssh or file access
to the server, so it'd have to happen remotely. the --tab/-T option
allows exporting to csv, but not remotely.
So we're looking for a way to have mysqldump store these files remotely,
on the client where mysqldump is executed.
I've been looking up and down, only to find bad news, so I'm pretty sure
it's just not possible. I know there are ways to do this (little script
to convert the sql to csv, or pipe it all through sed), but we were just
wondering if we're really not overlooking a nifty option in mysqldump :-)
Thanks in advance
mavoo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

_________________________________________________________________
O Internet Explorer 8 quer te ajudar a navegar seguro. Entre aqui para ler as dicas.
http://www.microsoft.com/brasil/windows/internet-explorer/?WT.mc_id=1500

[Marijn Vandevoorde]

Thank you for replying Carlos, but I'm under the impression that this
will also put the file on the server. Acutally, mysqldump --tab uses
INTO OUTFILE to generate the dump if i'm not mistaken

Post by Carlos Eduardo Caldi
Hi
You can use on shell, connect at the client mysq -h (host or IP) -p(password)
SELECT a, b, c INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table
more info see the link
http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html
Carlos Caldi

Date: Wed, 28 Apr 2010 14:11:14 +0200
Subject: remote mysqldump to csv (--tab)
Hi all,
posted this in the backup list, but that one seems to be pretty
We're currently looking for a way to backup a pretty big mysql table to
a csv file. However, we don't want to allow the user ssh or file access
to the server, so it'd have to happen remotely. the --tab/-T option
allows exporting to csv, but not remotely.
So we're looking for a way to have mysqldump store these files

remotely,

on the client where mysqldump is executed.
I've been looking up and down, only to find bad news, so I'm pretty

sure

it's just not possible. I know there are ways to do this (little script
to convert the sql to csv, or pipe it all through sed), but we were

just

wondering if we're really not overlooking a nifty option in

mysqldump :-)

Thanks in advance
mavoo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

------------------------------------------------------------------------
Cansado de entrar em todas as suas diferentes contas de email? Veja
como juntar todas
<http://www.windowslive.com.br/public/tip.aspx/view/16?product=1&ocid=Hotmail:MSN:Hotmail:Tagline:1x1:semLinha>

--
Marijn Vandevoorde

Tel:[phone] fax:[phone]
VIB Department of Plant Systems Biology, Ghent University
Technologiepark 927, 9052 Gent, BELGIUM
***@psb.vib-ugent.be http://www.psb.vib-ugent.be
==================================================================
"You're so beautiful.You could be a part time model.
But you'd probably still have to keep your normal job"
--J. Clement

[walter harms]

you can circumvent the problem by using stdout.
just drop the "INTO OUTFILE '/tmp/result.txt'".
the result should look something like that:

mysql -BAN <database> -e "select ..." >filename

you can also pipe truh gzip to compress the file and save
a lot of space.
re,
wh


note: i found ";" is not a good separator since people
may use ; in comments, i prefer "|" for that reason.

Post by Marijn Vandevoorde
Thank you for replying Carlos, but I'm under the impression that this
will also put the file on the server. Acutally, mysqldump --tab uses
INTO OUTFILE to generate the dump if i'm not mistaken

Post by Carlos Eduardo Caldi
Hi
You can use on shell, connect at the client mysq -h (host or IP) -p(password)
SELECT a, b, c INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table
more info see the link
http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html
Carlos Caldi

Date: Wed, 28 Apr 2010 14:11:14 +0200
Subject: remote mysqldump to csv (--tab)
Hi all,
posted this in the backup list, but that one seems to be pretty
We're currently looking for a way to backup a pretty big mysql table to
a csv file. However, we don't want to allow the user ssh or file access
to the server, so it'd have to happen remotely. the --tab/-T option
allows exporting to csv, but not remotely.
So we're looking for a way to have mysqldump store these files

remotely,

on the client where mysqldump is executed.
I've been looking up and down, only to find bad news, so I'm pretty

sure

it's just not possible. I know there are ways to do this (little script
to convert the sql to csv, or pipe it all through sed), but we were

just

wondering if we're really not overlooking a nifty option in

mysqldump :-)

Thanks in advance
mavoo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

------------------------------------------------------------------------
Cansado de entrar em todas as suas diferentes contas de email? Veja
como juntar todas
<http://www.windowslive.com.br/public/tip.aspx/view/16?product=1&ocid=Hotmail:MSN:Hotmail:Tagline:1x1:semLinha>