List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:January 6 2010 8:36pm
Subject:Re: table export in cron
View as plain text  
As Oracle has SQL*Loader and MySQL has LOAD FROM INFILE/SELECT INTO
OUTFILE, your proposed approach is %100 sound.  Mysqldump will just
move you further from your particular objective.

The 'simple' alternate, if you would rather not have the file
generated on the server is to do something like

echo "select * from mytable" > mysql -u someone mydb >  mytable.tsv

which will output tab-delimited records, one per line, suitable for
feeding to SQL*Loader.

I would put the command into a shell script and have cron call that
shell script.

 - michael dykman


On Wed, Jan 6, 2010 at 10:48 AM,  <sureshkumarilu@stripped> wrote:
> Hi,
> You can use an external replication tool like Golden gate for replication between
> MySQL and Oracle.
> Thanks
> Suresh Kuna
> MySQL DBA
> Sent from BlackBerry® on Airtel
>
> -----Original Message-----
> From: Mikhail Berman <mikhailber@stripped>
> Date: Wed, 06 Jan 2010 10:42:14
> To: machiel.richards<machiel.richards@stripped>
> Cc: <mysql@stripped>
> Subject: Re: table export in cron
>
> Hi Machiel,
>
> As an alternative, you might consider use of mysqdump command,
> http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html,  in a KSH/BASH
> script running from cron
>
> The script might look like this:
>
> =====
>
> !#/bin/ksh
>
> mysqldump --password=yourpassword [more switches needed here]
> your_database your_table > /path/to/output/file
>
> ====
>
> mysqldump command has switches to accomplish fields termination as you
> need, plus it gives opportunity to specify target database via
> --compatible=name
>
> <http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_compatible>switch.
>
> Hoping this helps,
>
> Mikhail Berman
>
> machiel.richards wrote:
>> Hi all
>>
>>
>>
>>                 I have a question
> regarding exporting of tables to a file
>> from mysql.
>>
>>
>>
>>                 We need to export tables
> from mysql to a delimeted file
>> which will then be imported into another database (oracle).
>>
>>
>>
>>                 We can do this manually
> from within mysql using the
>> following command:
>>
>>
>>
>>                      
>           select * from <table> into outfile
>> '/path/to/output/file' fields terminated by '|';
>>
>>
>>
>>                 This needs to be
> configured though to be run in a cron once
>> every week at a specific time.
>>
>>
>>
>>                 How can we do this when
> running in a cron script?
>>
>>
>>
>>                 Your assistance is
> appreciated.
>>
>>
>>
>> Regards
>>
>> Machiel
>>
>>
>>
>>
>>
>>
>>
>
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
Thread
table export in cronmachiel.richards6 Jan
  • Re: table export in cronGary Smith6 Jan
  • Re: table export in cronMikhail Berman6 Jan
    • Re: table export in cronsureshkumarilu6 Jan
Re: table export in cronMichael Dykman6 Jan
Re: table export in cronNicola Salvemini7 Jan
  • RE: table export in cronmachiel.richards7 Jan
    • Re: table export in cronJoerg Bruehe7 Jan
    • RE: table export in cronDan8 Jan