mysqldump备份数据后,新插入数据的datetime出错的问题

公司开发了一套数据库中间件系统,这两天发现写入到datetime或timestamp字段时会出现写入时间比真实时间大8个小时的问题.

经过排查,发现是mysqldump搞得鬼.

因为我们使用的是maya维护长连接,而且长连接是复用的,当执行mysqldump时,mysqldump默认启用--tz-utc参数,将本连接的时区设为UTC时区(而北京的timezone是+08:00)，这样后面某个业务使用这个数据库连接的,都会出现datetime/timestamp时间+8个小时.

官方文档解释:

--tz-utc

This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones. --tz-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc. This option was added in MySQL 5.1.2.