remote mysqldump to csv (--tab)

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: remote mysqldump to csv (--tab)

--_88db054c-3b43-4e9c-88cf-3a743ecd54e3_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi

You can use on shell=2C connect at the client mysq -h (host or IP) -p(passw=
ord)

and run the query:

SELECT a=2C b=2C c INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY '=3B' 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=2C 28 Apr 2010 14:11:14 +0200
> From: mavoo@psb.vib-ugent.be
> To: mysql@lists.mysql.com
> Subject: remote mysqldump to csv (--tab)
>=20
> Hi all=2C
>=20
> posted this in the backup list=2C but that one seems to be pretty dead=2C=
so i'll try my luck again here:
>=20
>=20
> We're currently looking for a way to backup a pretty big mysql table to=20
> a csv file. However=2C we don't want to allow the user ssh or file access=
=20
> to the server=2C so it'd have to happen remotely. the --tab/-T option=20
> allows exporting to csv=2C 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=2C only to find bad news=2C so I'm pretty s=
ure=20
> it's just not possible. I know there are ways to do this (little script=20
> to convert the sql to csv=2C or pipe it all through sed)=2C but we were j=
ust=20
> wondering if we're really not overlooking a nifty option in mysqldump :-)
>=20
> Thanks in advance
>=20
> mavoo
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dce_caldi@hotmail.=
com
>=20
=20
____________________________________________________________ _____
O Internet Explorer 8 quer te ajudar a navegar seguro. Entre aqui para ler =
as dicas.
http://www.microsoft.com/brasil/windows/internet-explorer/?W T.mc_id=3D1500=

--_88db054c-3b43-4e9c-88cf-3a743ecd54e3_--

Re: remote mysqldump to csv (--tab)

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

Carlos Eduardo Caldi wrote:
> 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
> > From: mavoo@psb.vib-ugent.be
> > To: mysql@lists.mysql.com
> > Subject: remote mysqldump to csv (--tab)
> >
> > 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
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=ce_caldi@hotmail.com
> >
>
> ------------------------------------------------------------ ------------
> Cansado de entrar em todas as suas diferentes contas de email? Veja
> como juntar todas
>

--
Marijn Vandevoorde

Tel:+32 (0)9 331 36 95 fax:+32 (0)9 3313809
VIB Department of Plant Systems Biology, Ghent University
Technologiepark 927, 9052 Gent, BELGIUM
marijn.vandevoorde@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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: remote mysqldump to csv (--tab)

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 -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.

Marijn Vandevoorde schrieb:
> 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
>
> Carlos Eduardo Caldi wrote:
>> 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
>> > From: mavoo@psb.vib-ugent.be
>> > To: mysql@lists.mysql.com
>> > Subject: remote mysqldump to csv (--tab)
>> >
>> > 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
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: http://lists.mysql.com/mysql?unsub=ce_caldi@hotmail.com
>> >
>>
>> ------------------------------------------------------------ ------------
>> Cansado de entrar em todas as suas diferentes contas de email? Veja
>> como juntar todas
>>
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org