> Hey folks,
Hi.
> I am having some trouble backing up a database from a server in NY to a
> server in California.
>
> When I do this:
>
> mysqldump myDatabase | mysql --host=otherTimeZone.com [etc]
>
> the data gets there but the timestamp columns (timestamp NOT NULL
> default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP) end up three
> hours later. I have learned about the --tz-utc option to mysqldump and
> that would be lovely, but it's only available in MySQL 5.1.2 and I
> would rather not get into upgrading (from 5.0.45-log). I thought it
> would be clever to write a shell script that says echo 'SET
> time_zone="US/Eastern";' > $DUMP; and then concatenates the rest of the
> dump to the file which is then fed into the mysql client -- but it
> doesn't work.
--tz-utc should be available it seems. From my mysqldump --help
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping
of
TIMESTAMP data when a server has data in different
time
zones or data is being moved between servers with
different time zones.
Which is version 5.0.67
But you can also set the timezone of mysql itself:
http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
Then again, I always set the OS to the same timezone, regardless of where
they are in the world. Some people set to UTC - I set to EST :)
Another approach, depending on exactly the scenario, is to just dump the
text file to Cali server. Then when you do a restore, none of this would be
a problem.
H
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
