【Mysql】關於一個mysql的坑比時區問題

小亮520cl發表於2017-06-23
MySql

問題：mysqldump出來的檔案遷移到另外的庫，時間戳欄位總是少8個小時！



1.看到這個很快就想到時區問題，我們先看一下

  1. mysql時區
  2. mysql> show variables like '%zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | CST    |
    | time_zone        | SYSTEM |
    +------------------+--------+
  3. 系統時區
  4. [root@iZ2ze66bhrbxkc31nljgjnZ ~]# date -R
    Fri, 23 Jun 2017 16:06:46 +0800
  5. 很好沒毛病

2.檢視錶結構

  1. 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 檢視資料

  1. MariaDB [ecejmaster]> select * from svc_street_tmp_170623 where street_id=17615 limit 1;
  2. +-----------+---------+-------------+--------+-------------+---------------------+-------------+---------------------+----------+---------------------+---------------------+
  3. | street_id | city_id | street_name | status | create_user | create_time | update_user | update_time | del_flag | screate_time | supdate_time |
  4. +-----------+---------+-------------+--------+-------------+---------------------+-------------+---------------------+----------+---------------------+---------------------+
  5. | 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 |
  6. +-----------+---------+-------------+--------+-------------+---------------------+-------------+---------------------+----------+---------------------+---------------------+
  7. 1 row in set (0.00 sec)

4 備份

  1. [dbaadmin@YZ-PRO-DB-04 ~]$ mysqldump -udbmanager -p'12fAK1aR' -h 10.32.14.78 ecejmaster svc_street_tmp_170623 --where="street_id=17615" -t
  2. -- MySQL dump 10.15 Distrib 10.0.23-MariaDB, for Linux (x86_64)
  3. --
  4. -- Host: 10.32.14.78 Database: ecejmaster
  5. -- ------------------------------------------------------
  6. -- Server version 10.0.23-MariaDB-log

  8. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  9. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  10. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  11. /*!40101 SET NAMES utf8 */;
  12. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  13. /*!40103 SET TIME_ZONE='+00:00' */;
  14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

  18. --
  19. -- Dumping data for table `svc_street_tmp_170623`
  20. --
  21. -- WHERE: street_id=17615

  23. LOCK TABLES `svc_street_tmp_170623` WRITE;
  24. /*!40000 ALTER TABLE `svc_street_tmp_170623` DISABLE KEYS */;
  25. 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');
  26. /*!40000 ALTER TABLE `svc_street_tmp_170623` ENABLE KEYS */;
  27. UNLOCK TABLES;
  28. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */
----看到沒，時間少了8個小時，仔細觀察會發現timestamp時間戳會出現少8個小時的狀況，datetime時間型別不會！插入到另外的庫中，時間會再加8個小時，恢復正常

但是：如果dump時指定 -t  等引數（忽略了上面的set time_zone），再插回去就真的少8個小時了。剛好我們備份的全是insert語句，其它的資訊全部去掉了


5 看下mysqldump的說明

  1. [dbaadmin@YZ-PRO-DB-04 ~]$ mysqldump --help | grep -i zone
  2.   --tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
  3.                       zones or data is being moved between servers with
  4.                       different time zones.
  5. 預設是以0時區來匯出的，會對timestamp時間型別造成影響

6 解決辦法：

  1. 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章