再述mysqldump时域问题

沃趣技术 2019-12-25

265

作者许升辉 · 沃趣科技数据库技术专家

出品沃趣科技

之前文章（引用沃趣技术--《当心！使用mysqldump备份可能会让你欲哭无泪》）介绍了当时间字段为timestamp时，使用mysqldump加where条件对时间字段进行过滤导出时，时域问题对导出数据的影响。今天我们就再来讨论一下mysqldump的时域问题。

问题介绍

日前，在客户某系统部署了一个数据清理脚本，该脚本在对数据进行清理之前，首先会按照清理数据的条件先使用mysqldump将即将清理的数据导出，再进行清理。该脚本使用crontab定时任务在凌晨两点执行。但第二天查看脚本的执行情况时，发现数据清理工作都顺利的完成了，但mysqldump导出的SQL文件里却只导出了表结构，没有导出数据。看到这个现象甚是奇怪。

问题分析与排查

查看导出数据的where条件为"gmt_modified < date_sub(curdate(),interval 359 day)"，基于之前的理解，我们想过有可能是时域的问题，所以确认了一下gmt_modified字段的数据类型，查看确认gmt_modified的数据类型为datetime，由于datetime数据类型是与时域无关的，所以针对这一问题，排除了时域对导出数据的影响。
难道是这个脚本在当前服务器的环境问题？将导出数据的条件改为"gmt_modified < date_sub(curdate(),interval 358 day)"，在这个条件下会查询出一天的数据，将脚本当中的数据删除部分注释掉，只执行数据导出的部分，发现该脚本完整的导出了数据。实在让人疑惑，为什么白天上班的时候数据能够备份出来，然而凌晨的时候数据就备不出来？
这时候，怀疑是不是当时数据库处于某种状态，阻止了mysqldump的备份。于是写了一个脚本，每隔一秒去检测当前数据库的连接状态。加入crontab，与删除数据的脚本在凌晨同时调起。数据清理脚本大约1分钟执行完成，于是设定数据库连接监控脚本执行3分钟。第二天观察监控的日志，也并未发现有任何异常的连接。
手动执行脚本能够备份成功，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备份恢复、数据迁移有丰富的实践。

再述mysqldump时域问题

为什么--skip-tz-utc参数会影响mysqldump导出的时域呢，下面先简要介绍一下--skip-tz-utc这个参数。

评论