如何解決mysqldump時域問題

如何解決MySQLdump時域問題，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),灤南企業(yè)網(wǎng)站建設(shè),灤南品牌網(wǎng)站建設(shè),網(wǎng)站定制,灤南網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,灤南網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。

今天我們就再來討論一下mysqldump的時域問題。

問題介紹

日前，在客戶某系統(tǒng)部署了一個數(shù)據(jù)清理腳本，該腳本在對數(shù)據(jù)進行清理之前，首先會按照清理數(shù)據(jù)的條件先使用mysqldump將即將清理的數(shù)據(jù)導(dǎo)出，再進行清理。該腳本使用crontab定時任務(wù)在凌晨兩點執(zhí)行。但第二天查看腳本的執(zhí)行情況時，發(fā)現(xiàn)數(shù)據(jù)清理工作都順利的完成了，但mysqldump導(dǎo)出的SQL文件里卻只導(dǎo)出了表結(jié)構(gòu)，沒有導(dǎo)出數(shù)據(jù)?？吹竭@個現(xiàn)象甚是奇怪。

問題分析與排查

1.查看導(dǎo)出數(shù)據(jù)的where條件為"gmt_modified < date_sub(curdate(),interval 359 day)"，基于之前的理解，我們想過有可能是時域的問題，所以確認(rèn)了一下gmt_modified字段的數(shù)據(jù)類型，查看確認(rèn)gmt_modified的數(shù)據(jù)類型為datetime，由于datetime數(shù)據(jù)類型是與時域無關(guān)的，所以針對這一問題，排除了時域?qū)?dǎo)出數(shù)據(jù)的影響。

2.難道是這個腳本在當(dāng)前服務(wù)器的環(huán)境問題？將導(dǎo)出數(shù)據(jù)的條件改為"gmt_modified < date_sub(curdate(),interval 358 day)"，在這個條件下會查詢出一天的數(shù)據(jù)，將腳本當(dāng)中的數(shù)據(jù)刪除部分注釋掉，只執(zhí)行數(shù)據(jù)導(dǎo)出的部分，發(fā)現(xiàn)該腳本完整的導(dǎo)出了數(shù)據(jù)。實在讓人疑惑，為什么白天上班的時候數(shù)據(jù)能夠備份出來，然而凌晨的時候數(shù)據(jù)就備不出來？

3.這時候，懷疑是不是當(dāng)時數(shù)據(jù)庫處于某種狀態(tài)，阻止了mysqldump的備份。于是寫了一個腳本，每隔一秒去檢測當(dāng)前數(shù)據(jù)庫的連接狀態(tài)。加入crontab，與刪除數(shù)據(jù)的腳本在凌晨同時調(diào)起。數(shù)據(jù)清理腳本大約1分鐘執(zhí)行完成，于是設(shè)定數(shù)據(jù)庫連接監(jiān)控腳本執(zhí)行3分鐘。第二天觀察監(jiān)控的日志，也并未發(fā)現(xiàn)有任何異常的連接。

4.手動執(zhí)行腳本能夠備份成功，crontab就無法備份，難道真的有什么鬼故事？于是在凌晨2點手動執(zhí)行備份腳本，發(fā)現(xiàn)的確無法備份。將腳本當(dāng)中的mysqldump語句摘錄出來，單獨執(zhí)行，仍然沒有備份成功?？磥韨浞菔∨c腳本、與環(huán)境都沒有關(guān)系，就是mysqldump的問題。又回到問題的起點，難道真的是時域惹的禍？于是在mysqldump時加上--skip-tz-utc的參數(shù)。執(zhí)行備份，這次備份成功了。

--skip-tz-utc參數(shù)介紹

為什么--skip-tz-utc參數(shù)會影響mysqldump導(dǎo)出的時域呢，下面先簡要介紹一下--skip-tz-utc這個參數(shù)。 

在mysql服務(wù)器上執(zhí)行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參數(shù)是mysqldump的默認(rèn)參數(shù)，會使得mysqldump的導(dǎo)出文件的頂部加上一個設(shè)置時域的語句SET TIME_ZONE='+00:00'，這個時域是格林威治時間，這樣當(dāng)導(dǎo)出timestamp字段時，會把在服務(wù)器設(shè)置的當(dāng)前時域下顯示的timestamp時間值轉(zhuǎn)化為在格林威治時間下顯示的時間。如下圖所示，mysqldump導(dǎo)出的文件當(dāng)中顯示的時間值相對于通過數(shù)據(jù)庫查詢顯示的時間倒退了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的含義就是當(dāng)mysqldump導(dǎo)出數(shù)據(jù)時，不使用格林威治時間，而使用當(dāng)前mysql服務(wù)器的時域進行導(dǎo)出。如下列代碼所示，這次備份使用了--skip-tz-utc，導(dǎo)出文件的語句中并沒有設(shè)置時域，導(dǎo)出的數(shù)據(jù)中顯示的時間值也和表中查詢出來的時間值相同。

[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;

那么這個參數(shù)的意義何在呢？當(dāng)一些公司具有跨國業(yè)務(wù)時，需要在兩個時域部署兩臺mysql服務(wù)器，這兩臺服務(wù)器都按照各自的時區(qū)設(shè)置服務(wù)器的時域。假設(shè)一個服務(wù)器在北京（東八區(qū)），一個服務(wù)器在東京（東九區(qū)），現(xiàn)在需要將成都服務(wù)器里的數(shù)據(jù)導(dǎo)入至東京服務(wù)器。如下列代碼所示，當(dāng)導(dǎo)入不加--skip-tz-utc參數(shù)的dump文件，查詢的t_timestamp表的數(shù)據(jù)相對于在之前的東八區(qū)服務(wù)器的時間值多了一個小時，但由于東八區(qū)服務(wù)器里的13點和東九區(qū)服務(wù)器里的14點代表的是同一時刻，所以，在東九區(qū)的服務(wù)器里顯示的多出的一個小時，這樣顯示是正確的。而如果不加--skip-tz-utc參數(shù)，dump文件導(dǎo)入東九區(qū)服務(wù)器后，盡管顯示的時間值和之前東八區(qū)服務(wù)器顯示的時間值相同，但兩者代表的時刻卻已經(jīng)不同，東九區(qū)的13點相對東八區(qū)的13點是要慢一個小時的。

[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h292.168.239.31 tokyotest
 mysql> show variables like "time_zone";
 +---------------+--------+
 | Variable_name | Value |
 +---------------+--------+
 | time_zone | +09:00 |
 +---------------+--------+
 1 row in set (0.02 sec)
#導(dǎo)入不加--skip-tz-utc參數(shù)的dump文件
[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h292.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 -h292.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)
#導(dǎo)入加上--skip-tz-utc參數(shù)的dump文件
[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h292.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 -h292.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)

經(jīng)過上面的測試，我們了解到，是否加上--skip-tz-utc參數(shù)，會影響timastamp字段的導(dǎo)入導(dǎo)出，那么對datetime時間字段會不會有影響呢？我們又進行了如下測試，測試顯示不加--skip-tz-utc，dump文件頂部會有一個SET TIME_ZONE='+00:00'的設(shè)置時域的語句，加上--skip-tz-utc，則沒有這條語句，因此使用當(dāng)前服務(wù)器的時域。但兩個dump文件導(dǎo)出的數(shù)據(jù)顯示都和數(shù)據(jù)庫里的查詢的時間值是相同的。

#數(shù)據(jù)在東八區(qū)服務(wù)器里的查詢情況
 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)
#導(dǎo)出時不加--skip-tz-utc參數(shù)
[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 */;
#導(dǎo)出時加上--skip-tz-utc參數(shù)
[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文件導(dǎo)入至東九區(qū)服務(wù)器,從下面的測試當(dāng)中可以看到，導(dǎo)入之后，兩種dump文件在東九區(qū)服務(wù)器里顯示的時間值是相同的，且這個時間值和在東八區(qū)服務(wù)器里顯示的時間值也相同。但這個和timestamp字段加--skip-tz-utc的導(dǎo)出方式產(chǎn)生的問題也是相同的。在不同時域服務(wù)器里顯示相同的時間值，但這相同的時間值在不同時域服務(wù)器里代表的并不是同一時刻。所以這也就是當(dāng)具有跨國跨時區(qū)的業(yè)務(wù)時，使用timestamp字段比較好的原因。

#東九區(qū)服務(wù)器
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h292.168.239.31 tokyotest
 mysql> show variables like "time_zone";
 +---------------+--------+
 | Variable_name | Value |
 +---------------+--------+
 | time_zone | +09:00 |
 +---------------+--------+
 1 row in set (0.02 sec)
#導(dǎo)入不加--skip-tz-utc參數(shù)的dump文件
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h292.168.239.31 tokyotest <full_t_datetime.sql
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h292.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)
#導(dǎo)入加上--skip-tz-utc參數(shù)的dump文件
[root@rhel74 datetime]# mysql -utokyo -ptokyo -h292.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 -h292.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參數(shù)對mysqldump導(dǎo)出timestamp字段是會有影響的，但不會影響datetime字段。但對于最開始我們在生產(chǎn)上遇到的問題，這樣的理解反而使我們更加疑惑。時域問題不是不會影響datetime字段的導(dǎo)出嗎？那為什么在mysqldump中以datetime字段作為where條件判斷的字段導(dǎo)出數(shù)據(jù)時，在凌晨的時候會導(dǎo)不出來，而在白天的時候卻可以正常導(dǎo)出呢？對于這些問題，我們又進行了下面的測試。

實驗驗證

1. 環(huán)境介紹

本次測試采用的數(shù)據(jù)庫版本為mysql5.7.22

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

當(dāng)前mysql服務(wù)器設(shè)置的時域為東八區(qū)的時域。

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

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

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

用于測試的t_datetime表的表結(jié)構(gòu)如下。

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天的數(shù)據(jù)。

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天以前的數(shù)據(jù)

根據(jù)5天以前的查詢條件，可以看到在該條件下可以從該表中查到3條數(shù)據(jù)。那么按照我們的要求，mysqldump也應(yīng)該備份下列的3條數(shù)據(jù)。

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沒有備份出任何的數(shù)據(jù)。

[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天以前的數(shù)據(jù)

在mysqldump的命令加上了--skip-tz-utc的參數(shù)，再次查看備份文件，可以看到這次備份出了我們想要的數(shù)據(jù)。

[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，我們的備份需求是達到了。但是這種結(jié)果仍然得不到一種很好的解釋。因為按照我們的理解，datetime數(shù)據(jù)類型是和時域無關(guān)的，然而在我們的實踐中，時域卻影響了數(shù)據(jù)備份。帶著這個疑問，我們在mysql的官方文檔找到了相關(guān)的答案。

如何解決mysqldump時域問題

第一段的前面兩句找到了我們想要的答案：會話時域的設(shè)置會影響具有時域敏感性的時間值的顯示。包括NOW()、CURDATE()函數(shù)，和用timestamp數(shù)據(jù)類型存儲的字段。

看到這里，突然有點豁然開朗，我們之前的理解沒有錯，datetime數(shù)據(jù)類型的確是不受時域影響，然而使用create_time < date_sub(curdate(), interval 5 day)條件進行備份時，影響備份結(jié)果的，并非是datetime數(shù)據(jù)類型本身，而是條件表達式中curdate()函數(shù)。

由于使用mysqldump進行備份時，會設(shè)置當(dāng)前會話的時域為+0:00，即使用格林威治時間。那么會話中的curdate()函數(shù)，會按照當(dāng)前服務(wù)器時間減8個小時來進行計算。當(dāng)前時間為2019-12-13的凌晨2點，那么減8個小時之后，通過格林威治時間計算的curdate()即為2019-12-12，然而datetime中的數(shù)值不變，那么根據(jù)2019-12-12計算出的5天以前便沒有數(shù)據(jù)。

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

5. 不帶skip-tz-utc，且用當(dāng)前的真實時間代替?zhèn)浞輻l件中curdate()函數(shù)

[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 */;

結(jié)果不出所料，mysqldump果然備份出了數(shù)據(jù)。

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

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識有進一步的了解或閱讀更多相關(guān)文章，請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對創(chuàng)新互聯(lián)的支持。

新聞標(biāo)題：如何解決mysqldump時域問題
標(biāo)題來源：http://bm7419.com/article10/igcido.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、面包屑導(dǎo)航、微信小程序、App設(shè)計、動態(tài)網(wǎng)站、軟件開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

網(wǎng)站托管運營