MySQL data export changes times

Question

I have some backup and restore scripts that I am using for my database. The table has a timestamp field. The backup script looks like this:

mysqldump -u user -ppass database --tab="../" --fields-terminated-by="|" --skip-comments table

It creates two files, table.sql and table.txt. The restore script looks like this:

mysql -u user -ppass database < "../table.sql"
mysqlimport -u user -ppass --local --fields-terminated-by="|" database "../table.txt"

However the backup script is outputting the wrong time - it's an hour behind what is in the database - but it doesn't correct it when importing.

For example the time on one row was 15:10:25 but when the backup script is run, 14:10:25 is listed in table.txt. When I run the restore script, the same row now has 14:10:25 as the time in the database. If I backup again, it says 13:10:25! And so on...

I can't figure out why this is. The time zone appears to be set to "SYSTEM" (I'm on GMT). The table.sql file has a few lines mentioning time zones, maybe something is wrong there? Here is the full file in question:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS `news_article`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `news_article` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `alias` varchar(65) NOT NULL,
  `author` tinyint(3) unsigned NOT NULL,
  `category` tinyint(3) unsigned NOT NULL,
  `posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `opening` text NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=MyISAM AUTO_INCREMENT=93 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Stealthy

Answer 1

Found a solution in the end: adding the --skip-tz-utc option to the export script.

This simply makes sure the exact date you export is what's imported in the second database. It works for me since the databases are the same time zone, but may not be ideal for others whose databases are different time zones.

Stealthy

I'm assuming that since I need to do this, I did something else wrong in the application. Using this option seems to do exactly the opposite of what it says in the man page. Could it be that I should have set the MySQL timezone in the app to be UTC instead of an offset? – Hyperparathyroidism

I think you should set timezone to UTC when importing data, not exporting. From documentation of '--tz-utc' option: "mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file." So, the dump is in UTC. But since you use tab dumps, the "SET TIME_ZONE='+00:00" instruction is not executed, so you have to set the timezone on the connection manually. – Overarm

Setting this flag when exporting worked in my situation, this saved a lot of trouble with reporting of date sensitive info. Thank you @DisgruntledGoat! – Bair