**前言：**

在前面文章中，有提到過 mysqldump 備份檔案中記錄的時間戳資料都是以 UTC 時區為基礎的，在篩選恢復單庫或單表時要注意時區差別。後來再次檢視文件，發現 tz-utc、skip-tz-utc 引數與此有關，本篇文章我們一起來看下此引數的作用吧。

#### 1.tz-utc與skip-tz-utc引數介紹

這兩個引數可以作用於 mysqldump 備份過程中，互為相反引數。顧名思義可以看出，一個引數是將時間戳改為 UTC 時區，另一個是跳過時區變動。

在 mysql 伺服器上執行 mysqldump --help 的命令，可以看到下面一段話。

```sql
[root@host ~]# mysqldump --help
mysqldump  Ver 10.13 Distrib 5.7.23, for Linux (x86_64)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
...省略很多內容
  --tz-utc            SET TIME_ZONE='+00:00' at top of dump to allow dumping of
                      TIMESTAMP data when a server has data in different time
                      zones or data is being moved between servers with
                      different time zones.
                      (Defaults to on; use --skip-tz-utc to disable.)
```

--tz-utc 引數是 mysqldump 的預設引數，會使得 mysqldump 的匯出檔案的頂部加上一個設定時區的語句 SET TIME_ZONE='+00:00' ，這個時區是格林威治時間，也就是0時區。這樣當匯出 timestamp 時間戳欄位時，會把在伺服器設定的當前時區下顯示的 timestamp 時間值轉化為在格林威治時間下顯示的時間。比如我們資料庫採用北京時間東八區，mysqldump 匯出的檔案當中顯示的 timestamp 時間值相對於透過資料庫查詢顯示的時間倒退了8個小時。

知道了 --tz-utc ，那麼 --skip-tz-utc 的含義就是當 mysqldump 匯出資料時，不使用格林威治時間，而使用當前 mysql 伺服器的時區進行匯出，這樣匯出的資料中顯示的 timestamp 時間值也和表中查詢出來的時間值相同。

#### 2.實驗引數具體作用

為了更清楚瞭解這對引數的作用，下面我們來具體測試下，我們知道 mysqldump 後可以跟 where 條件來備份部分資料，若根據 timestamp 欄位來備份部分資料，這對引數是否有影響呢？我們一併來驗證下：

先來看下我的環境設定及測試資料：

# 測試表 有datetime欄位和timestamp欄位 共10條資料 兩個時間顯示是相同的
mysql> show create table test_tb\G
*************************** 1. row ***************************
       Table: test_tb
Create Table: CREATE TABLE `test_tb` (
  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `stu_id` int(11) NOT NULL COMMENT '學號',
  `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
  `dt_time` datetime NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='測試表'
1 row in set (0.00 sec)

mysql> select * from test_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | dt_time             | create_time         |
+--------------+--------+----------+---------------------+---------------------+
|            1 |   1001 | fgds     | 2020-07-10 09:43:28 | 2020-07-10 09:43:28 |
|            2 |   1002 | fgsw     | 2020-10-10 09:43:28 | 2020-10-10 09:43:28 |
|            3 |   1003 | vffg     | 2020-10-10 02:00:00 | 2020-10-10 02:00:00 |
|            4 |   1004 | wdsd     | 2020-10-31 23:43:28 | 2020-10-31 23:43:28 |
|            5 |   1005 | grdb     | 2020-11-01 00:00:00 | 2020-11-01 00:00:00 |
|            6 |   1006 | sdfv     | 2020-11-01 02:00:00 | 2020-11-01 02:00:00 |
|            7 |   1007 | fgfg     | 2020-11-06 02:00:00 | 2020-11-06 02:00:00 |
|            8 |   1008 | tyth     | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
|            9 |   1009 | ewer     | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
|           10 |   1010 | erre     | 2020-11-11 15:17:03 | 2020-11-11 15:17:03 |
+--------------+--------+----------+---------------------+---------------------+
```

mysqldump 預設開啟 tz-utc ，先來看下預設情況下的備份結果：

```sql
# 為更明顯看出結果 我們使用skip-extended-insert來一行行展現資料
# 全庫備份
[root@host ~]# mysqldump -uroot -pxxxx  --skip-extended-insert --databases testdb > utc_testdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more utc_testdb.sql 
-- MySQL dump 10.13  Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version       5.7.23-log

...省略
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
# 先儲存老時區 然後將此會話時區改為0時區
...省略
--
-- Dumping data for table `test_tb`
--

LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (1,1001,'fgds','2020-07-10 09:43:28','2020-07-10 01:43:28');
INSERT INTO `test_tb` VALUES (2,1002,'fgsw','2020-10-10 09:43:28','2020-10-10 01:43:28');
INSERT INTO `test_tb` VALUES (3,1003,'vffg','2020-10-10 02:00:00','2020-10-09 18:00:00');
INSERT INTO `test_tb` VALUES (4,1004,'wdsd','2020-10-31 23:43:28','2020-10-31 15:43:28');
INSERT INTO `test_tb` VALUES (5,1005,'grdb','2020-11-01 00:00:00','2020-10-31 16:00:00');
INSERT INTO `test_tb` VALUES (6,1006,'sdfv','2020-11-01 02:00:00','2020-10-31 18:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-05 18:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 07:17:03');
# 可以看出timestamp時間值減去了8小時 而datetime時間值不變
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
# 再將時區改為原時區
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- Dump completed on 2020-11-11 15:34:21

# 使用where條件備份單表部分資料 備份11月份以來的資料
# 資料庫中查詢
mysql> select * from test_tb where create_time >= '2020-11-01 00:00:00';
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | dt_time             | create_time         |
+--------------+--------+----------+---------------------+---------------------+
|            5 |   1005 | grdb     | 2020-11-01 00:00:00 | 2020-11-01 00:00:00 |
|            6 |   1006 | sdfv     | 2020-11-01 02:00:00 | 2020-11-01 02:00:00 |
|            7 |   1007 | fgfg     | 2020-11-06 02:00:00 | 2020-11-06 02:00:00 |
|            8 |   1008 | tyth     | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
|            9 |   1009 | ewer     | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
|           10 |   1010 | erre     | 2020-11-11 15:17:03 | 2020-11-11 15:17:03 |
+--------------+--------+----------+---------------------+---------------------+
6 rows in set (0.00 sec)
# mysqldump匯出
[root@host ~]# mysqldump -uroot -pxxxx  --skip-extended-insert testdb test_tb --where "create_time >= '2020-11-01 00:00:00' " > utc_testdb2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more utc_testdb2.sql 
-- MySQL dump 10.13  Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version       5.7.23-log
...
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
...省略
--
-- Dumping data for table `test_tb`
--
-- WHERE:  create_time >= '2020-11-01 00:00:00'

LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-05 18:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 07:17:03');
# 發現只匯出4條
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

-- Dump completed on 2020-11-11 15:58:56
```

建議各位仔細看下上面匯出結果，說實話，筆者原來也沒做過詳細測試，現在看到結果也是稍微有點吃驚的。預設情況下，全備出來的資料是沒問題的，雖然將 timestamp 時間值轉為0時區顯示，但當你匯入資料庫時還會以你的資料庫時區來展示 timestamp 時間。但使用 where 條件匯出部分資料時，卻出現了資料庫中查詢得出的結果與dump匯出的結果不同的情況，這個時候 mysqldump 只匯出了轉化成0時區後的時間值符合 where 條件的資料，與直接查詢出的結果有出入，這是我原來沒注意到的。

再來看下使用 --skip-tz-utc 引數，看下這個引數是否符合我們的預期：

```sql
# 使用skip-tz-utc全備
[root@host ~]# mysqldump -uroot -pxxxx  --skip-extended-insert --skip-tz-utc --databases testdb > skiputc_testdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more skiputc_testdb.sql 
-- MySQL dump 10.13  Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version       5.7.23-log
..省略 未見時區更改語句
--
-- Dumping data for table `test_tb`
--

LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (1,1001,'fgds','2020-07-10 09:43:28','2020-07-10 09:43:28');
INSERT INTO `test_tb` VALUES (2,1002,'fgsw','2020-10-10 09:43:28','2020-10-10 09:43:28');
INSERT INTO `test_tb` VALUES (3,1003,'vffg','2020-10-10 02:00:00','2020-10-10 02:00:00');
INSERT INTO `test_tb` VALUES (4,1004,'wdsd','2020-10-31 23:43:28','2020-10-31 23:43:28');
INSERT INTO `test_tb` VALUES (5,1005,'grdb','2020-11-01 00:00:00','2020-11-01 00:00:00');
INSERT INTO `test_tb` VALUES (6,1006,'sdfv','2020-11-01 02:00:00','2020-11-01 02:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-06 02:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 15:17:03');
# timestamp時間值顯示與datetime顯示一樣 未做轉換
UNLOCK TABLES;
-- Dump completed on 2020-11-11 16:23:32

# 使用skip-tz-utc備份部分資料
[root@host ~]# mysqldump -uroot -pxxxx  --skip-extended-insert --skip-tz-utc testdb test_tb --where "create_time >= '2020-11-01 00:00:00' " > skiputc_testdb2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more skiputc_testdb2.sql 
-- MySQL dump 10.13  Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version       5.7.23-log
.. 省略
--
-- Dumping data for table `test_tb`
--
-- WHERE:  create_time >= '2020-11-01 00:00:00'

LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (5,1005,'grdb','2020-11-01 00:00:00','2020-11-01 00:00:00');
INSERT INTO `test_tb` VALUES (6,1006,'sdfv','2020-11-01 02:00:00','2020-11-01 02:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-06 02:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 15:17:03');
# 6條資料 和資料庫中查詢一致
UNLOCK TABLES;
-- Dump completed on 2020-11-11 16:28:39
```

從上面結果可以看出，使用 --skip-tz-utc 引數後，timestamp 時間戳欄位值不會轉換，匯出部分資料也符合預期。

#### 3.一些小建議

那麼這個引數的意義何在呢？當你的資料庫伺服器處於不同時區時。假設一個伺服器在北京（東八區），一個伺服器在東京（東九區），現在需要將北京伺服器裡的資料匯入至東京伺服器。當匯入按照預設不加 --skip-tz-utc 引數的dump檔案，查詢的 timestamp 時間資料相對於在之前的東八區伺服器的時間值多了一個小時，但由於東八區伺服器裡的13點和東九區伺服器裡的14點代表的是同一時刻，所以，在東九區的伺服器裡顯示的多出的一個小時，這樣顯示是正確的。而如果增加 --skip-tz-utc 引數，dump檔案匯入東九區伺服器後，儘管顯示的時間值和之前東八區伺服器顯示的時間值相同，但兩者代表的時刻卻已經不同。

關於這個引數應該如何使用，我們首先應該明白，是否加上 --skip-tz-utc 引數，只會影響 timestamp 欄位的匯入匯出，對 datetime 時間欄位不會影響。

這裡筆者建議首先對 timestamp 欄位使用作出規範。比如 timestamp 欄位只用於建立時間和更新時間需求，只代表該行資料的建立及更新時間，做到與業務弱相關，其他時間欄位儘量使用 datetime 。這樣即使 mysqldump 採用不同引數，實際產生影響也不大。

如果你的伺服器處於不同時區，那建議還是按照預設來，這樣匯入匯出的資料都是正確的。如果你的伺服器都是處於同一時區，那麼是否使用 --skip-tz-utc 引數區別不大，我們只需知道預設情況 mysqldump 會將 timestamp 時間值轉為0時區儲存即可。當備份部分資料且以 timestamp 欄位來篩選時，這時候建議增加 --skip-tz-utc 引數。這裡再次提醒下，從全備中篩選單庫或單表的備份時，也要注意下 timestamp 欄位資料。

**參考：**

- [https://zhuanlan.zhihu.com/p/99395517](https://zhuanlan.zhihu.com/p/99395517)

![wx_blog.png](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvMTE5NTM3LzE1ODk0NDUxNTUwNjgtNjE3N2NiYjQtNzkyMS00YzIzLWIwNDAtN2Y3NjhlYzNhZTA3LnBuZw?x-oss-process=image/format,png)

mysqldump中skip-tz-utc引數介紹

相關文章