【沃趣科技】再述mysqldump時域問題

沃趣科技發表於2019-12-25
MySql

沃趣科技作為國內領先的資料庫雲平臺解決方案提供商，一直致力於企業級資料庫雲平臺產品的研發，為使用者提供高效能、高可用、可擴充套件的的資料庫雲環境及不同業務場景需求的資料庫平臺，滿足客戶對極致效能、資料安全、容災備份、業務永續等需求。沃趣科技憑藉專業的團隊，優質的產品，前沿的技術，貼心的服務贏得了客戶的信任與尊重，也獲得了市場的認同。

————————————————————————————————————————————

之前文章（ 引用沃趣技術--《當心！ 使用mysqldump備份可能會讓你欲哭無淚》 ）介紹了當時間欄位為timestamp時，使用mysqldump加where條件對時間欄位進行過濾匯出時，時域問題對匯出資料的影響。 今天我們就再來討論一下mysqldump的時域問題。

問題介紹

日前，在客戶某系統部署了一個資料清理指令碼，該指令碼在對資料進行清理之前，首先會按照清理資料的條件先使用mysqldump將即將清理的資料匯出，再進行清理。該指令碼使用crontab定時任務在凌晨兩點執行。但第二天檢視指令碼的執行情況時，發現資料清理工作都順利的完成了，但mysqldump匯出的SQL檔案裡卻只匯出了表結構，沒有匯出資料。看到這個現象甚是奇怪。

問題分析與排查

1.檢視匯出資料的where條件為"gmt_modified < date_sub(curdate(),interval 359 day)"，基於之前的理解，我們想過有可能是時域的問題，所以確認了一下gmt_modified欄位的資料型別，檢視確認gmt_modified的資料型別為datetime，由於datetime資料型別是與時域無關的，所以針對這一問題，排除了時域對匯出資料的影響。

2.難道是這個指令碼在當前伺服器的環境問題？ 將匯出資料的條件改為"gmt_modified < date_sub(curdate(),interval 358 day)"，在這個條件下會查詢出一天的資料，將指令碼當中的資料刪除部分註釋掉，只執行資料匯出的部分，發現該指令碼完整的匯出了資料。實在讓人疑惑，為什麼白天上班的時候資料能夠備份出來，然而凌 晨的時候資料就備不出來？

3.這時候，懷疑是不是當時資料庫處於某種狀態，阻止了mysqldump的備份。於是寫了一個指令碼，每隔一秒去檢測當前資料庫的連線狀態。加入crontab，與刪除資料的指令碼在凌晨同時調起。資料清理指令碼大約1分鐘執行完成，於是設定資料庫連線監控指令碼執行3分鐘。第二天觀察監控的日誌，也並未發現有任何異常的連線。

4.手動執行指令碼能夠備份成功，crontab就無法備份，難道真的有什麼鬼故事？於是在凌晨2點手動執行備份指令碼，發現的確無法備份。將指令碼當中的mysqldump語句摘錄出來，單獨執行，仍然沒有備份成功。看來備份失敗與指令碼、與環境都沒有關係，就是mysqldump的問題。又回到問題的起點，難道真的是時域惹的禍？於是在mysqldump時加上--skip-tz-utc的引數。執行備份，這次備份成功了。

--skip-tz-utc引數介紹

為什麼--skip-tz-utc引數會影響mysqldump匯出的時域呢，下面先簡要介紹一下--skip-tz-utc這個引數。  

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

--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'，這個時域是格林威治時間，這樣當匯出timestamp欄位時，會把在伺服器設定的當前時域下顯示的timestamp時間值轉化為在格林威治時間下顯示的時間。 如下圖所示，mysqldump匯出的檔案當中顯示的時間值相對於通過資料庫查詢顯示的時間倒退了8個小時。

mysql> show variables like "time_zone";
 +---------------+--------+
 | Variable_name | Value |
 +---------------+--------+
 | time_zone | +08:00 |
 +---------------+--------+
 1 row in set (0.01 sec)
 mysql> show create table t_timestamp;
 +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table | Create Table |
 +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | t_timestamp | CREATE TABLE `t_timestamp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
 +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 mysql> select * from t_timestamp;
 +----+-----------+---------------------+
 | id | name | create_time |
 +----+-----------+---------------------+
 | 1 | messi | 2019-12-07 13:27:55 |
 | 3 | xavi | 2019-12-07 13:28:01 |
 | 5 | xsh | 2019-12-07 13:28:08 |
 | 7 | cr7 | 2019-12-08 14:24:18 |
 | 9 | ozil | 2019-12-08 14:24:26 |
 | 11 | ramos | 2019-12-08 14:24:33 |
 | 13 | pique | 2019-12-09 08:24:24 |
 | 15 | henry | 2019-12-09 08:24:34 |
 | 17 | lukaku | 2019-12-10 12:00:58 |
 | 19 | rakitici | 2019-12-10 12:01:12 |
 | 21 | van dijk | 2019-12-11 22:00:46 |
 | 23 | mane | 2019-12-11 22:00:57 |
 | 25 | suarez | 2019-12-11 22:01:34 |
 | 27 | Ronaldol | 2019-12-11 22:01:55 |
 | 29 | Ronaldiho | 2019-12-12 18:00:20 |
 | 31 | Deco | 2019-12-12 18:00:28 |
 +----+-----------+---------------------+
 16 rows in set (0.00 sec)
[root@rhel74 timestamp]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_timestamp > full_timestamp.sql
 mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 timestamp]# vim full_timestamp.sql
/*!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_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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 */;
...
 LOCK TABLES `t_timestamp` WRITE;
/*!40000 ALTER TABLE `t_timestamp` DISABLE KEYS */;
 INSERT INTO `t_timestamp` VALUES (1,'messi','2019-12-07 05:27:55'),(3,'xavi','2019-12-07 05:28:01'),(5,'xsh','2019-12-07 05:28:08'),(7,'cr7','2019-12-08 06:24:18'),(9,'ozil','2019-12-08 06:24:26'),(11,'ramos','2019-12-08 06:24:33'),(13,'pique','2019-12-09 00:24:24'),(15,'henry','2019-12-09 00:24:34'),(17,'lukaku','2019-12-10 04:00:58'),(19,'rakitici','2019-12-10 04:01:12'),(21,'van dijk','2019-12-11 14:00:46'),(23,'mane','2019-12-11 14:00:57'),(25,'suarez','2019-12-11 14:01:34'),(27,'Ronaldol','2019-12-11 14:01:55'),(29,'Ronaldiho','2019-12-12 10:00:20'),(31,'Deco','2019-12-12 10:00:28');
/*!40000 ALTER TABLE `t_timestamp` ENABLE KEYS */;
 UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

知道了--tz-utc,那麼--skip-tz-utc的含義就是當mysqldump匯出資料時，不使用格林威治時間，而使用當前mysql伺服器的時域進行匯出。 如下列程式碼所示，這次備份使用了--skip-tz-utc，匯出檔案的語句中並沒有設定時域，匯出的資料中顯示的時間值也和表中查詢出來的時間值相同。

[root@rhel74 timestamp]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF --skip-tz-utc xshtest t_timestamp > full_timestamp_without_tz_utc.sql
 mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 timestamp]# vim full_timestamp_without_tz_utc.sql
/*!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 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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 */;
... LOCK TABLES `t_timestamp` WRITE;
/*!40000 ALTER TABLE `t_timestamp` DISABLE KEYS */;
 INSERT INTO `t_timestamp` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08'),(7,'cr7','2019-12-08 14:24:18'),(9,'ozil','2019-12-08 14:24:26'),(11,'ramos','2019-12-08 14:24:33'),(13,'pique','2019-12-09 08:24:24'),(15,'henry','2019-12-09 08:24:34'),(17,'lukaku','2019-12-10 12:00:58'),(19,'rakitici','2019-12-10 12:01:12'),(21,'van dijk','2019-12-11 22:00:46'),(23,'mane','2019-12-11 22:00:57'),(25,'suarez','2019-12-11 22:01:34'),(27,'Ronaldol','2019-12-11 22:01:55'),(29,'Ronaldiho','2019-12-12 18:00:20'),(31,'Deco','2019-12-12 18:00:28');
/*!40000 ALTER TABLE `t_timestamp` ENABLE KEYS */;
 UNLOCK TABLES;

那麼這個引數的意義何在呢？ 當一些公司具有跨國業務時，需要在兩個時域部署兩臺mysql伺服器，這兩臺伺服器都按照各自的時區設定伺服器的時域。 假設一個伺服器在北京（東八區），一個伺服器在東京（東九區），現在需要將北京伺服器裡的資料匯入至東京伺服器。 如下列程式碼所示，當匯入不加--skip-tz-utc引數的dump檔案，查詢的t_timestamp表的資料相對於在之前的東八區伺服器的時間值多了一個小時，但由於東八區伺服器裡的13點和東九區伺服器裡的14點代表的是同一時刻，所以，在東九區的伺服器裡顯示的多出的一個小時，這樣顯示是正確的。 而如果不加--skip-tz-utc引數，dump檔案匯入東九區伺服器後，儘管顯示的時間值和之前東八區伺服器顯示的時間值相同，但兩者代表的時刻卻已經不同，東九區的13點相對東八區的13點是要慢一個小時的。

[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
 mysql> show variables like "time_zone";
 +---------------+--------+
 | Variable_name | Value |
 +---------------+--------+
 | time_zone | +09:00 |
 +---------------+--------+
 1 row in set (0.02 sec)
#匯入不加--skip-tz-utc引數的dump檔案
[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest < full_timestamp.sql
 mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
 mysql> select * from t_timestamp;
 +----+-----------+---------------------+
 | id | name | create_time |
 +----+-----------+---------------------+
 | 1 | messi | 2019-12-07 14:27:55 |
 | 3 | xavi | 2019-12-07 14:28:01 |
 | 5 | xsh | 2019-12-07 14:28:08 |
 | 7 | cr7 | 2019-12-08 15:24:18 |
 | 9 | ozil | 2019-12-08 15:24:26 |
 | 11 | ramos | 2019-12-08 15:24:33 |
 | 13 | pique | 2019-12-09 09:24:24 |
 | 15 | henry | 2019-12-09 09:24:34 |
 | 17 | lukaku | 2019-12-10 13:00:58 |
 | 19 | rakitici | 2019-12-10 13:01:12 |
 | 21 | van dijk | 2019-12-11 23:00:46 |
 | 23 | mane | 2019-12-11 23:00:57 |
 | 25 | suarez | 2019-12-11 23:01:34 |
 | 27 | Ronaldol | 2019-12-11 23:01:55 |
 | 29 | Ronaldiho | 2019-12-12 19:00:20 |
 | 31 | Deco | 2019-12-12 19:00:28 |
 +----+-----------+---------------------+
 16 rows in set (0.01 sec)
#匯入加上--skip-tz-utc引數的dump檔案
[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest < full_timestamp_without_tz_utc.sql 
 mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
 mysql> select * from t_timestamp;
 +----+-----------+---------------------+
 | id | name | create_time |
 +----+-----------+---------------------+
 | 1 | messi | 2019-12-07 13:27:55 |
 | 3 | xavi | 2019-12-07 13:28:01 |
 | 5 | xsh | 2019-12-07 13:28:08 |
 | 7 | cr7 | 2019-12-08 14:24:18 |
 | 9 | ozil | 2019-12-08 14:24:26 |
 | 11 | ramos | 2019-12-08 14:24:33 |
 | 13 | pique | 2019-12-09 08:24:24 |
 | 15 | henry | 2019-12-09 08:24:34 |
 | 17 | lukaku | 2019-12-10 12:00:58 |
 | 19 | rakitici | 2019-12-10 12:01:12 |
 | 21 | van dijk | 2019-12-11 22:00:46 |
 | 23 | mane | 2019-12-11 22:00:57 |
 | 25 | suarez | 2019-12-11 22:01:34 |
 | 27 | Ronaldol | 2019-12-11 22:01:55 |
 | 29 | Ronaldiho | 2019-12-12 18:00:20 |
 | 31 | Deco | 2019-12-12 18:00:28 |
 +----+-----------+---------------------+
 16 rows in set (0.01 sec)

經過上面的測試，我們瞭解到，是否加上--skip-tz-utc引數，會影響timastamp欄位的匯入匯出，那麼對datetime時間欄位會不會有影響呢？ 我們又進行了如下測試，測試顯示不加--skip-tz-utc，dump檔案頂部會有一個SET TIME_ZONE='+00:00'的設定時域的語句，加上--skip-tz-utc，則沒有這條語句，因此使用當前伺服器的時域。 但兩個dump檔案匯出的資料顯示都和資料庫裡的查詢的時間值是相同的。

#資料在東八區伺服器裡的查詢情況
 mysql> show variables like "time_zone";
 +---------------+--------+
 | Variable_name | Value |
 +---------------+--------+
 | time_zone | +08:00 |
 +---------------+--------+
 1 row in set (0.00 sec)
 mysql> show create table t_datetime;
 +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table | Create Table |
 +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | t_datetime | CREATE TABLE `t_datetime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
 +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.06 sec)
 mysql> select * from t_datetime;
 +----+-----------+---------------------+
 | id | name | create_time |
 +----+-----------+---------------------+
 | 1 | messi | 2019-12-07 13:27:55 |
 | 3 | xavi | 2019-12-07 13:28:01 |
 | 5 | xsh | 2019-12-07 13:28:08 |
 | 7 | cr7 | 2019-12-08 14:24:18 |
 | 9 | ozil | 2019-12-08 14:24:26 |
 | 11 | ramos | 2019-12-08 14:24:33 |
 | 13 | pique | 2019-12-09 08:24:24 |
 | 15 | henry | 2019-12-09 08:24:34 |
 | 17 | lukaku | 2019-12-10 12:00:58 |
 | 19 | rakitici | 2019-12-10 12:01:12 |
 | 21 | van dijk | 2019-12-11 22:00:46 |
 | 23 | mane | 2019-12-11 22:00:57 |
 | 25 | suarez | 2019-12-11 22:01:34 |
 | 27 | Ronaldol | 2019-12-11 22:01:55 |
 | 29 | Ronaldiho | 2019-12-12 18:00:20 |
 | 31 | Deco | 2019-12-12 18:00:28 |
 +----+-----------+---------------------+
 16 rows in set (0.00 sec)
#匯出時不加--skip-tz-utc引數
[root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_datetime > full_t_datetime.sql
 mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 datetime]# vim full_t_datetime.sql
/*!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_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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 */;
...
 LOCK TABLES `t_datetime` WRITE;
/*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
 INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08'),(7,'cr7','2019-12-08 14:24:18'),(9,'ozil','2019-12-08 14:24:26'),(11,'ramos','2019-12-08 14:24:33'),(13,'pique','2019-12-09 08:24:24'),(15,'henry','2019-12-09 08:24:34'),(17,'lukaku','2019-12-10 12:00:58'),(19,'rakitici','2019-12-10 12:01:12'),(21,'van dijk','2019-12-11 22:00:46'),(23,'mane','2019-12-11 22:00:57'),(25,'suarez','2019-12-11 22:01:34'),(27,'Ronaldol','2019-12-11 22:01:55'),(29,'Ronaldiho','2019-12-12 18:00:20'),(31,'Deco','2019-12-12 18:00:28');
/*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
 UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
#匯出時加上--skip-tz-utc引數
[root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF --skip-tz-utc xshtest t_datetime > full_t_datetime_without_tz_utc.sql
 mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 datetime]# vim full_t_datetime_without_tz_utc.sql
/*!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 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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 */;
...
 LOCK TABLES `t_datetime` WRITE;
/*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
 INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08'),(7,'cr7','2019-12-08 14:24:18'),(9,'ozil','2019-12-08 14:24:26'),(11,'ramos','2019-12-08 14:24:33'),(13,'pique','2019-12-09 08:24:24'),(15,'henry','2019-12-09 08:24:34'),(17,'lukaku','2019-12-10 12:00:58'),(19,'rakitici','2019-12-10 12:01:12'),(21,'van dijk','2019-12-11 22:00:46'),(23,'mane','2019-12-11 22:00:57'),(25,'suarez','2019-12-11 22:01:34'),(27,'Ronaldol','2019-12-11 22:01:55'),(29,'Ronaldiho','2019-12-12 18:00:20'),(31,'Deco','2019-12-12 18:00:28');
/*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
 UNLOCK TABLES;

我們再把這兩種dump檔案匯入至東九區伺服器,從下面的測試當中可以看到，匯入之後，兩種dump檔案在東九區伺服器裡顯示的時間值是相同的，且這個時間值和在東八區伺服器裡顯示的時間值也相同。 但這個和timestamp欄位加--skip-tz-utc的匯出方式產生的問題也是相同的。 在不同時域伺服器裡顯示相同的時間值，但這相同的時間值在不同時域伺服器裡代表的並不是同一時刻。 所以這也就是當具有跨國跨時區的業務時，使用timestamp欄位比較好的原因。

#東九區伺服器
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
 mysql> show variables like "time_zone";
 +---------------+--------+
 | Variable_name | Value |
 +---------------+--------+
 | time_zone | +09:00 |
 +---------------+--------+
 1 row in set (0.02 sec)
#匯入不加--skip-tz-utc引數的dump檔案
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest <full_t_datetime.sql
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
 mysql> select * from t_datetime;
 +----+-----------+---------------------+
 | id | name | create_time |
 +----+-----------+---------------------+
 | 1 | messi | 2019-12-07 13:27:55 |
 | 3 | xavi | 2019-12-07 13:28:01 |
 | 5 | xsh | 2019-12-07 13:28:08 |
 | 7 | cr7 | 2019-12-08 14:24:18 |
 | 9 | ozil | 2019-12-08 14:24:26 |
 | 11 | ramos | 2019-12-08 14:24:33 |
 | 13 | pique | 2019-12-09 08:24:24 |
 | 15 | henry | 2019-12-09 08:24:34 |
 | 17 | lukaku | 2019-12-10 12:00:58 |
 | 19 | rakitici | 2019-12-10 12:01:12 |
 | 21 | van dijk | 2019-12-11 22:00:46 |
 | 23 | mane | 2019-12-11 22:00:57 |
 | 25 | suarez | 2019-12-11 22:01:34 |
 | 27 | Ronaldol | 2019-12-11 22:01:55 |
 | 29 | Ronaldiho | 2019-12-12 18:00:20 |
 | 31 | Deco | 2019-12-12 18:00:28 |
 +----+-----------+---------------------+
 16 rows in set (0.00 sec)
#匯入加上--skip-tz-utc引數的dump檔案
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest < full_t_datetime_without_tz_utc.sql
 mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h192.168.239.31 tokyotest
 mysql> select * from t_datetime;
 +----+-----------+---------------------+
 | id | name | create_time |
 +----+-----------+---------------------+
 | 1 | messi | 2019-12-07 13:27:55 |
 | 3 | xavi | 2019-12-07 13:28:01 |
 | 5 | xsh | 2019-12-07 13:28:08 |
 | 7 | cr7 | 2019-12-08 14:24:18 |
 | 9 | ozil | 2019-12-08 14:24:26 |
 | 11 | ramos | 2019-12-08 14:24:33 |
 | 13 | pique | 2019-12-09 08:24:24 |
 | 15 | henry | 2019-12-09 08:24:34 |
 | 17 | lukaku | 2019-12-10 12:00:58 |
 | 19 | rakitici | 2019-12-10 12:01:12 |
 | 21 | van dijk | 2019-12-11 22:00:46 |
 | 23 | mane | 2019-12-11 22:00:57 |
 | 25 | suarez | 2019-12-11 22:01:34 |
 | 27 | Ronaldol | 2019-12-11 22:01:55 |
 | 29 | Ronaldiho | 2019-12-12 18:00:20 |
 | 31 | Deco | 2019-12-12 18:00:28 |
 +----+-----------+---------------------+
 16 rows in set (0.01 sec)

通過上面的測試，我們瞭解了--skip-tz-utc引數對mysqldump匯出timestamp欄位是會有影響的，但不會影響datetime欄位。 但對於最開始我們在生產上遇到的問題，這樣的理解反而使我們更加疑惑。 時域問題不是不會影響datetime欄位的匯出嗎？ 那為什麼在mysqldump中以datetime欄位作為where條件判斷的欄位匯出資料時，在凌晨的時候會導不出來，而在白天的時候卻可以正常匯出呢？ 對於這些問題，我們又進行了下面的測試。

實驗驗證

1. 環境介紹

本次測試採用的資料庫版本為mysql5.7.22

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.22-log |
+------------+

當前mysql伺服器設定的時域為東八區的時域。

mysql> show variables like "time_zone";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +08:00 |
+---------------+--------+

當前系統時間為北京時間2019-12-13的凌晨兩點多，若推算成格林尼治時間，此時為2019-12-12的下午18點多。

mysql> select now();
 +---------------------+
 | now() |
 +---------------------+
 | 2019-12-13 02:17:36 |
 +---------------------+

用於測試的t_datetime表的表結構如下。

CREATE TABLE `t_datetime` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_bin NOT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

表中模擬了從2019-12-07到2019-12-12之間6天的資料。

mysql> select * from t_datetime;
 +----+-----------+---------------------+
 | id | name | create_time |
 +----+-----------+---------------------+
 | 1 | messi | 2019-12-07 13:27:55 |
 | 3 | xavi | 2019-12-07 13:28:01 |
 | 5 | xsh | 2019-12-07 13:28:08 |
 | 7 | cr7 | 2019-12-08 14:24:18 |
 | 9 | ozil | 2019-12-08 14:24:26 |
 | 11 | ramos | 2019-12-08 14:24:33 |
 | 13 | pique | 2019-12-09 08:24:24 |
 | 15 | henry | 2019-12-09 08:24:34 |
 | 17 | lukaku | 2019-12-10 12:00:58 |
 | 19 | rakitici | 2019-12-10 12:01:12 |
 | 21 | van dijk | 2019-12-11 22:00:46 |
 | 23 | mane | 2019-12-11 22:00:57 |
 | 25 | suarez | 2019-12-11 22:01:34 |
 | 27 | Ronaldol | 2019-12-11 22:01:55 |
 | 29 | Ronaldiho | 2019-12-12 18:00:20 |
 | 31 | Deco | 2019-12-12 18:00:28 |
 +----+-----------+---------------------+

2. 不帶skip-tz-utc備份t_datetime表5天以前的資料

根據5天以前的查詢條件，可以看到在該條件下可以從該表中查到3條資料。 那麼按照我們的要求，mysqldump也應該備份下列的3條資料。

mysql> select * from t_datetime where create_time <  date_sub(curdate(), interval 5 day);
 +----+-------+---------------------+
 | id | name | create_time |
 +----+-------+---------------------+
 | 1 | messi | 2019-12-07 13:27:55 |
 | 3 | xavi | 2019-12-07 13:28:01 |
 | 5 | xsh | 2019-12-07 13:28:08 |
 +----+-------+---------------------+

然而，事實上，按照create_time < date_sub(curdate(), interval 5 day)的條件，mysqldump沒有備份出任何的資料。

[root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_datetime --where="create_time  < date_sub(curdate(), interval 5 day)" >  5_day_ago_without_skip_tz_utc.sql
 mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 datetime]# vim  5_day_ago_without_skip_tz_utc.sql
--
-- Dumping data for table `t_datetime`
--
-- WHERE: create_time < date_sub(curdate(), interval 5 day)
 LOCK TABLES `t_datetime` WRITE;
/*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
 UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

3. 帶skip-tz-utc備份t_datetime表5天以前的資料

在mysqldump的命令加上了--skip-tz-utc的引數，再次檢視備份檔案，可以看到這次備份出了我們想要的資料。

[root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF --skip-tz-utc xshtest t_datetime --where="create_time < date_sub(curdate(), interval 5 day)" > 5_day_ago_with_skip_tz_utc.sql
 mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 datetime]# vim 5_day_ago_with_skip_tz_utc.sql
--
-- Dumping data for table `t_datetime`
--
-- WHERE: create_time < date_sub(curdate(), interval 5 day)
 LOCK TABLES `t_datetime` WRITE;
/*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
 INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08');
/*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
 UNLOCK TABLES;

4. 查閱官方文件

雖然加上--skip-tz-utc，我們的備份需求是達到了。 但是這種結果仍然得不到一種很好的解釋。 因為按照我們的理解，datetime資料型別是和時域無關的，然而在我們的實踐中，時域卻影響了資料備份。 帶著這個疑問，我們在mysql的官方文件找到了相關的答案。

第一段的前面兩句找到了我們想要的答案： 會話時域的設定會影響具有時域敏感性的時間值的顯示。 包括NOW()、CURDATE()函式，和用timestamp資料型別儲存的欄位。

看到這裡，突然有點豁然開朗，我們之前的理解沒有錯，datetime資料型別的確是不受時域影響，然而使用create_time < date_sub(curdate(), interval 5 day)條件進行備份時，影響備份結果的，並非是datetime資料型別本身，而是條件表示式中curdate()函式。

由於使用mysqldump進行備份時，會設定當前會話的時域為+0:00，即使用格林威治時間。 那麼會話中的curdate()函式，會按照當前伺服器時間減8個小時來進行計算。 當前時間為2019-12-13的凌晨2點，那麼減8個小時之後，通過格林威治時間計算的curdate()即為2019-12-12，然而datetime中的數值不變，那麼根據2019-12-12計算出的5天以前便沒有資料。

按照上面的結論，我們可以進行一個猜想，由於影響mysqldump備份結果集的是curdate()函式，那麼我們將條件表示式中的curdate()函式替換成真實的時間字串，這樣就不會受時域的影響，而能夠正常備份出資料來。 按照這個猜想，我們又進行了如下的測試。

5. 不帶skip-tz-utc，且用當前的真實時間代替備份條件中curdate()函式

[root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_datetime --where="create_time < date_sub('2019-12-13', interval 5 day)" > 5_day_ago_without_curdate.sql
 mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rhel74 datetime]# vim 5_day_ago_without_curdate.sql
--
-- Dumping data for table `t_datetime`
--
-- WHERE: create_time < date_sub('2019-12-13', interval 5 day)
 LOCK TABLES `t_datetime` WRITE;
/*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;
 INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08');
/*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */;
 UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

結果不出所料，mysqldump果然備份出了資料。

結  論

對於這個問題，如果不是在實際中碰到，單純憑我們自己的學習，很難注意這麼細微的知識點。 可見，實踐才是最好的老師。 平時遇到什麼問題，我們始終都要保持一個打破砂鍋問到底的心，這樣對於自己才會有所成長。 再者，還要保持一個發散性的思維，碰到問題，多聯想，多問幾個為什麼，然後再自己去尋求答案。 主動的去尋找問題解決問題，而不是等問題主動找上門來。

| 作者簡介

許升輝·沃趣科技資料庫工程師

熟悉MySQL體系結構和innodb儲存引擎工作原理；擅長資料庫問題分析，效能調優；對mysql備份恢復、資料遷移有豐富的實踐。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2670592/，如需轉載，請註明出處，否則將追究法律責任。

相關文章