I used to make backup dumps from my webhosting MySQL databases (I’ve got remote access) via the mysqldump command. All commands that I need (including mysqlcheck to optimize and analyze all tables) are grouped together in batch files, so it works very conveniently. One more word to mysqlcheck – you should optimize and analyze your tables from time to time to defragment the tables and renew the index statistics. That makes sure that performence doesn’t suffer if many records are deleted and inserted.
However, after making new backup files today, I realized that the date/time data were one hour ahead of the original data. I was wondering how this could happen, analyzed the dump file and soon found the answer: the tz-utc option.
This option was by default set to true, so these lines were included in the dump file:
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */;
… and at the end:
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
That means, the time zone is set to UTC, but as my system time zone is one hour ahead (as we have CET in Austria), MySQL added one hour to each time relevant piece of data, when I imported the dump file. By the way, /*!40103 … */ means that this command is only being executed when you run it with MySQL version 4.1.3 or higher.
The solution is quite easy, though. Just add –tz-utc=false to your mysqldump command:
mysqldump --tz-utc=false [...further options] > dump_file.sql
Now, these three lines will be omitted and your time information will remain unchanged.
THANK YOU! I ran into this and this is exactly the explanation I was looking for!