问题:mysqldump出来的文件迁移到另外的库,时间戳字段总是少8个小时!
1.看到这个很快就想到时区问题,我们先看一下
- mysql时区
- mysql> show variables like '%zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
- 系统时区
- [root@iZ2ze66bhrbxkc31nljgjnZ ~]# date -R
Fri, 23 Jun 2017 16:06:46 +0800
- 很好没毛病
2.查看表结构
- MariaDB [ecejmaster]> desc svc_street_tmp_170623;
+--------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+-----------------------------+
| street_id | int(11) | NO | | 0 | |
| city_id | int(11) | YES | | NULL | |
| street_name | varchar(50) | NO | | | |
| status | tinyint(4) | YES | | NULL | |
| create_user | int(11) | YES | | NULL | |
| create_time | datetime | NO | | CURRENT_TIMESTAMP | |
| update_user | int(11) | YES | | NULL | |
| update_time | datetime | YES | | NULL | |
| del_flag | tinyint(4) | NO | | 0 | |
| screate_time | timestamp | NO | | CURRENT_TIMESTAMP | |
| supdate_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+-------------+------+-----+-------------------+-----------------------------+
3 查看数据
- MariaDB [ecejmaster]> select * from svc_street_tmp_170623 where street_id=17615 limit 1;
- +-----------+---------+-------------+--------+-------------+---------------------+-------------+---------------------+----------+---------------------+---------------------+
- | street_id | city_id | street_name | status | create_user | create_time | update_user | update_time | del_flag | screate_time | supdate_time |
- +-----------+---------+-------------+--------+-------------+---------------------+-------------+---------------------+----------+---------------------+---------------------+
- | 17615 | 69 | 123 | 2 | 1 | 2017-06-23 15:37:24 | 1 | 2017-06-23 15:37:24 | 0 | 2017-06-23 15:37:24 | 2017-06-23 15:38:36 |
- +-----------+---------+-------------+--------+-------------+---------------------+-------------+---------------------+----------+---------------------+---------------------+
- 1 row in set (0.00 sec)
4 备份
- [dbaadmin@YZ-PRO-DB-04 ~]$ mysqldump -udbmanager -p'12fAK1aR' -h 10.32.14.78 ecejmaster svc_street_tmp_170623 --where="street_id=17615" -t
- -- MySQL dump 10.15 Distrib 10.0.23-MariaDB, for Linux (x86_64)
- --
- -- Host: 10.32.14.78 Database: ecejmaster
- -- ------------------------------------------------------
- -- Server version 10.0.23-MariaDB-log
-
- /*!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' */;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-
- --
- -- Dumping data for table `svc_street_tmp_170623`
- --
- -- WHERE: street_id=17615
-
- LOCK TABLES `svc_street_tmp_170623` WRITE;
- /*!40000 ALTER TABLE `svc_street_tmp_170623` DISABLE KEYS */;
- INSERT INTO `svc_street_tmp_170623` VALUES (17615,69,'123',2,1,'2017-06-23 15:37:24',1,'2017-06-23 15:37:24',0,'2017-06-23 07:37:24','2017-06-23 07:38:36');
- /*!40000 ALTER TABLE `svc_street_tmp_170623` ENABLE KEYS */;
- UNLOCK TABLES;
- /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */
----看到没,时间少了8个小时,仔细观察会发现timestamp时间戳会出现少8个小时的状况,datetime时间类型不会!插入到另外的库中,时间会再加8个小时,恢复正常
但是:如果dump时指定 -t 等参数(忽略了上面的set time_zone),再插回去就真的少8个小时了。刚好我们备份的全是insert语句,其它的信息全部去掉了
5 看下mysqldump的说明
- [dbaadmin@YZ-PRO-DB-04 ~]$ mysqldump --help | grep -i zone
- --tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
- zones or data is being moved between servers with
- different time zones.
- 默认是以0时区来导出的,会对timestamp时间类型造成影响
6 解决办法:
- mysqldump -uroot -S /data/3306/mysql.sock -pHP2T9wypjr6oEZRV ecejmaster3 $i --compact -c -t --skip-extended-insert --skip-tz-utc 跳过时区
总结:mysqldump会对 timestamp时间类型的字段造成8个小时的误差,存insert时使用skip-tz-utc跳过时区的方式导出解决
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-2141177/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29096438/viewspace-2141177/