Re: table export in cron
am 06.01.2010 16:42:14 von Mikhail Berman
--------------040607050507070301010501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
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 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
>
>
>
>
>
>
>
--------------040607050507070301010501--
Re: table export in cron
am 06.01.2010 16:48:46 von sureshkumarilu
SGksDQpZb3UgY2FuIHVzZSBhbiBleHRlcm5hbCByZXBsaWNhdGlvbiB0b29s IGxpa2UgR29sZGVu
IGdhdGUgZm9yIHJlcGxpY2F0aW9uIGJldHdlZW4gTXlTUUwgYW5kIE9yYWNs ZS4NClRoYW5rcw0K
U3VyZXNoIEt1bmENCk15U1FMIERCQQ0KU2VudCBmcm9tIEJsYWNrQmVycnmu IG9uIEFpcnRlbA0K
DQotLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0tLQ0KRnJvbTogTWlraGFpbCBC ZXJtYW4gPG1pa2hh
aWxiZXJAZ21haWwuY29tPg0KRGF0ZTogV2VkLCAwNiBKYW4gMjAxMCAxMDo0 MjoxNCANClRvOiBt
YWNoaWVsLnJpY2hhcmRzPG1hY2hpZWwucmljaGFyZHNAZ21haWwuY29tPg0K Q2M6IDxteXNxbEBs
aXN0cy5teXNxbC5jb20+DQpTdWJqZWN0OiBSZTogdGFibGUgZXhwb3J0IGlu IGNyb24NCg0KSGkg
TWFjaGllbCwNCg0KQXMgYW4gYWx0ZXJuYXRpdmUsIHlvdSBtaWdodCBjb25z aWRlciB1c2Ugb2Yg
bXlzcWR1bXAgY29tbWFuZCwgDQpodHRwOi8vZGV2Lm15c3FsLmNvbS9kb2Mv cmVmbWFuLzUuMC9l
bi9teXNxbGR1bXAuaHRtbCwgIGluIGEgS1NIL0JBU0ggDQpzY3JpcHQgcnVu bmluZyBmcm9tIGNy
b24NCg0KVGhlIHNjcmlwdCBtaWdodCBsb29rIGxpa2UgdGhpczoNCg0KPT09 PT0NCg0KISMvYmlu
L2tzaA0KDQpteXNxbGR1bXAgLS1wYXNzd29yZD15b3VycGFzc3dvcmQgW21v cmUgc3dpdGNoZXMg
bmVlZGVkIGhlcmVdIA0KeW91cl9kYXRhYmFzZSB5b3VyX3RhYmxlID4gL3Bh dGgvdG8vb3V0cHV0
L2ZpbGUNCg0KPT09PQ0KDQpteXNxbGR1bXAgY29tbWFuZCBoYXMgc3dpdGNo ZXMgdG8gYWNjb21w
bGlzaCBmaWVsZHMgdGVybWluYXRpb24gYXMgeW91IA0KbmVlZCwgcGx1cyBp dCBnaXZlcyBvcHBv
cnR1bml0eSB0byBzcGVjaWZ5IHRhcmdldCBkYXRhYmFzZSB2aWEgDQotLWNv bXBhdGlibGU9bmFt
ZSANCjxodHRwOi8vZGV2Lm15c3FsLmNvbS9kb2MvcmVmbWFuLzUuMC9lbi9t eXNxbGR1bXAuaHRt
bCNvcHRpb25fbXlzcWxkdW1wX2NvbXBhdGlibGU+c3dpdGNoLg0KDQpIb3Bp bmcgdGhpcyBoZWxw
cywNCg0KTWlraGFpbCBCZXJtYW4NCg0KbWFjaGllbC5yaWNoYXJkcyB3cm90 ZToNCj4gSGkgYWxs
DQo+DQo+ICANCj4NCj4gICAgICAgICAgICAgICAgIEkgaGF2ZSBhIHF1ZXN0 aW9uIHJlZ2FyZGlu
ZyBleHBvcnRpbmcgb2YgdGFibGVzIHRvIGEgZmlsZQ0KPiBmcm9tIG15c3Fs Lg0KPg0KPiAgDQo+
DQo+ICAgICAgICAgICAgICAgICBXZSBuZWVkIHRvIGV4cG9ydCB0YWJsZXMg ZnJvbSBteXNxbCB0
byBhIGRlbGltZXRlZCBmaWxlDQo+IHdoaWNoIHdpbGwgdGhlbiBiZSBpbXBv cnRlZCBpbnRvIGFu
b3RoZXIgZGF0YWJhc2UgKG9yYWNsZSkuDQo+DQo+ICANCj4NCj4gICAgICAg ICAgICAgICAgIFdl
IGNhbiBkbyB0aGlzIG1hbnVhbGx5IGZyb20gd2l0aGluIG15c3FsIHVzaW5n IHRoZQ0KPiBmb2xs
b3dpbmcgY29tbWFuZDoNCj4NCj4gIA0KPg0KPiAgICAgICAgICAgICAgICAg ICAgICAgICAgICAg
ICAgIHNlbGVjdCAqIGZyb20gPHRhYmxlPiBpbnRvIG91dGZpbGUNCj4gJy9w YXRoL3RvL291dHB1
dC9maWxlJyBmaWVsZHMgdGVybWluYXRlZCBieSAnfCc7DQo+DQo+ICANCj4N Cj4gICAgICAgICAg
ICAgICAgIFRoaXMgbmVlZHMgdG8gYmUgY29uZmlndXJlZCB0aG91Z2ggdG8g YmUgcnVuIGluIGEg
Y3JvbiBvbmNlDQo+IGV2ZXJ5IHdlZWsgYXQgYSBzcGVjaWZpYyB0aW1lLg0K Pg0KPiAgDQo+DQo+
ICAgICAgICAgICAgICAgICBIb3cgY2FuIHdlIGRvIHRoaXMgd2hlbiBydW5u aW5nIGluIGEgY3Jv
biBzY3JpcHQ/IA0KPg0KPiAgDQo+DQo+ICAgICAgICAgICAgICAgICBZb3Vy IGFzc2lzdGFuY2Ug
aXMgYXBwcmVjaWF0ZWQuDQo+DQo+ICANCj4NCj4gUmVnYXJkcw0KPg0KPiBN YWNoaWVsDQo+DQo+
ICANCj4NCj4gIA0KPg0KPg0KPiAgIA0KDQo=
Re: table export in cron
am 06.01.2010 21:36:05 von Michael Dykman
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, 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=AE on Airtel
>
> -----Original Message-----
> From: Mikhail Berman
> Date: Wed, 06 Jan 2010 10:42:14
> To: machiel.richards
> Cc:
> 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, =A0in a KSH/BASH
> script running from cron
>
> The script might look like this:
>
> =====3D
>
> !#/bin/ksh
>
> mysqldump --password=3Dyourpassword [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=3Dname
>
ompatible>switch.
>
> Hoping this helps,
>
> Mikhail Berman
>
> machiel.richards wrote:
>> Hi all
>>
>>
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 I have a question regarding exporting of=
tables to a file
>> from mysql.
>>
>>
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 We need to export tables from mysql to a=
delimeted file
>> which will then be imported into another database (oracle).
>>
>>
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 We can do this manually from within mysq=
l using the
>> following command:
>>
>>
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 select *=
from into outfile
>> '/path/to/output/file' fields terminated by '|';
>>
>>
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 This needs to be configured though to be=
run in a cron once
>> every week at a specific time.
>>
>>
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 How can we do this when running in a cro=
n script?
>>
>>
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Your assistance is appreciated.
>>
>>
>>
>> Regards
>>
>> Machiel
>>
>>
>>
>>
>>
>>
>>
>
>
--=20
- michael dykman
- mdykman@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: table export in cron
am 07.01.2010 14:44:33 von Nicola Salvemini
----- machiel.richards wrote=0A>=0A> =
How can we do this when running in a cron script? =0A>
Hi!=0A=
=0AYou can use following command in a shell script running from cron:
=
echo "select * from into outfile '/path/to/output/file' fields ter=
minated by '|';" | mysql --user=3D --password=3D
ourdatabase>;
Bye!
=0A
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
RE: table export in cron
am 07.01.2010 14:48:24 von Machiel Richards
Hi All
Well I had some fun writing this cron , easy enough with all the help =
you guys gave me...
Just some interesting info I want to share, maybe someone came across =
this and can also give some thoughts...
What the Oracle guys found was that some of the fields were blank even =
though the fields were configured as not nullable.
I found that where this is the case, the users entered a blank space =
which is then counted as a character and is thus not null.
This is causing all kinds of havoc for them as Oracle apparently still =
sees this as nulls.
Regards
-----Original Message-----
From: Nicola Salvemini [mailto:nicolasalvemini@yahoo.it]=20
Sent: 07 January 2010 3:45 PM
To: sureshkumarilu@gmail.com; Mikhail Berman; machiel.richards
Cc: mysql@lists.mysql.com
Subject: Re: table export in cron
----- machiel.richards wrote
>
> How can we do this when running in a cron script?=20
>
Hi!
You can use following command in a shell script running from cron:
echo "select * from into outfile '/path/to/output/file' fields =
terminated by '|';" | mysql --user=3D =
--password=3D ;
Bye!
=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: table export in cron
am 07.01.2010 17:30:58 von Joerg Bruehe
Hi Machiel, all!
machiel.richards wrote:
> Hi All
>=20
> =09[[...]]
>=20
> =09What the Oracle guys found was that some of the fields were blan=
k even though the fields were configured as not nullable.
>=20
> =09I found that where this is the case, the users entered a blank s=
pace which is then counted as a character and is thus not null.
>=20
> =09This is causing all kinds of havoc for them as Oracle apparently=
still sees this as nulls.
In SQL, both an empty string "" and a string with just a blank " " ar=
e
well-defined values, different from the unknown NULL.
AFAIR, Oracle had a tradition of treating empty and/or blank strings =
as
NULL. I don't know whether that still holds, and whether it can be
influenced by some setting.
Which value would be used on the Oracle side for such fields? You cou=
ld
always import into a stage table on the Oracle side which allows NULL=
,
then update to some non-NULL default value, then transfer into the fi=
nal
one.
Jörg
--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
RE: table export in cron
am 08.01.2010 05:04:49 von dan
On Thu, 7 Jan 2010 15:48:24 +0200, "machiel.richards"
wrote:
> Hi All
>
> What the Oracle guys found was that some of the fields were blank even
> though the fields were configured as not nullable.
>
> I found that where this is the case, the users entered a blank space
> which is then counted as a character and is thus not null.
>
> This is causing all kinds of havoc for them as Oracle apparently still
> sees this as nulls.
I'm not sure what export method you're using, but you should be able to
fix this by specifying to quote fields.
Dan
--
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