time_zone导致sys升高、时区的坑

http://mp.weixin.qq.com/s?__biz=MzA3NDcyMTQyNQ==&mid=2649257910&idx=1&sn=b661411f11fe7cb9b1669e34b3d2bd2f&chksm=8767a648b0102f5e3089fb5299e0faca9c75827394f3c723ec7001c0124068a036a26080991d&mpshare=1&scene=23&srcid=08012bgnM3a7LM0Nb6s1Spuz#rd

http://webcache.googleusercontent.com/search?q=cache:p_AeVu4QhL8J:glume.blog.chinaunix.net/uid-20708886-id-5105437.html+&cd=1&hl=zh-CN&ct=clnk&gl=hk

默认参数：

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

###################time_zone的坑

CREATE TABLE `t_zone` (
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from t_zone;
+---------------------+
| ctime |
+---------------------+
| 2017-08-02 11:58:32 |
| 2017-08-02 11:58:34 |
| 2017-08-02 11:58:34 |
+---------------------+
3 rows in set (0.00 sec)

 

备份出来的数据：

INSERT INTO `t_zone` VALUES ('2017-08-02 03:47:09'),('2017-08-02 03:47:10'),('2017-08-02 03:47:12');

mysqldump默认使用中时区

/*!40103 SET TIME_ZONE='+00:00' */;

######################

time_zone =SYSTEM和time_zone= '+08:00' 中时区的数据导入会加8，和原始的时间值相同，保证了数据一致性。

mysql> select * from t_zone;
+---------------------+
| ctime |
+---------------------+
| 2017-08-02 11:58:32 |
| 2017-08-02 11:58:34 |
| 2017-08-02 11:58:34 |
+---------------------+
3 rows in set (0.00 sec)

mysql> set time_zone='+7:00';
Query OK, 0 rows affected (0.00 sec)

mysql> set global time_zone='+7:00';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t_zone;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_zone;
+---------------------+
| ctime |
+---------------------+
| 2017-08-02 10:58:32 |
| 2017-08-02 10:58:34 |
| 2017-08-02 10:58:34 |
+---------------------+
3 rows in set (0.00 sec)

测试用的‘+7:00’时区，备份数据导入会少1个小时。

############################

--tz-utc=0 有国外业务，可以考虑使用这个参数备份。

/usr/local/mysql/bin/mysqldump -uroot -p -S /home/mysql3309/run/mysql.sock --set-gtid-purged=OFF --tz-utc=0  hd_test t_zone >t_zone_2.sql