溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
如何解決mysqldump時域問題，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
今天我們就再來討論一下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 -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) #導入不加--skip-tz-utc參數的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) #導入加上--skip-tz-utc參數的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)
經過上面的測試，我們了解到，是否加上--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 -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) #導入不加--skip-tz-utc參數的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) #導入加上--skip-tz-utc參數的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參數對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果然備份出了數據。
對于這個問題，如果不是在實際中碰到，單純憑我們自己的學習，很難注意這么細微的知識點。可見，實踐才是最好的老師。平時遇到什么問題，我們始終都要保持一個打破砂鍋問到底的心，這樣對于自己才會有所成長。再者，還要保持一個發散性的思維，碰到問題，多聯想，多問幾個為什么，然后再自己去尋求答案。主動的去尋找問題解決問題，而不是等問題主動找上門來。
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
