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