mysqldump时域问题

最新推荐文章于 2024-01-01 21:02:20 发布
最新推荐文章于 2024-01-01 21:02:20 发布
阅读量264 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xsh5161/article/details/108995849
版权

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果然备份出了数据。

奶牛问问天
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库使用mysqldump导出数据详解
09-10
mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。接下来通过本文给大家介绍MySQL数据库使用mysqldump导出数据详解，需要的朋友一起学习吧
python 提取固定列名数据_python获取数据库列名
weixin_39806288的博客
12-01 6084
{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云数据库专家保驾护航，为用户的数据库应用系统进行性能和风险评估，参与配合进行数据压测演练，提供数据库优化方面专业建议，在业务高峰期与用户共同保障数据库系统平...
mysqldump导出函数、存储过程和视图
最新发布
Sebastien23的博客
01-01 1408
查看函数和存储过程： mysqldump导出函数和存储过程（不导出表数据）： 或者 生成删除函数和存储过程的语句： 修改并确认dump文件后，导入存储过程和函数： 导出视图定义 检查数据库下面所有视图： 获取并拼接所有视图名称： mysqldump单独导出视图/表定义： 生产删除视图的语句： 修改并确认dump文件后导入视图：
Mysqldump时区差问题
qq_19800265的博客
11-01 1215
问题场景     我们要从数据库A中导出test表里create_pub_time在2019-10-30的0点到4点的数据到数据库B中的test表里，其中两个库里的test表表结构完全一致，create_pub_time字段类型为timestamp。     首先，导出sql文件。在服务器上执行以下命令： mysqldump -h主机名 -u用户名 -p密码 -P3306 --default-ch...
mysqldump问题小结
12-14
问题一:SQL 语法错误 mysql> mysqltump -u root -p sa > D:/sa.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right ...
windows mysqldump.zip
07-24
《Windows环境下MySQL数据库备份与恢复：mysqldump详解》 在信息技术领域，数据库管理是至关重要的环节，其中数据的安全性和可恢复性是衡量系统稳定性的重要标准。MySQL作为一款广泛使用的开源关系型数据库管理系统...
mysqldump.exe
05-06
windows版本mysqldump 5.7.24版本 无需安装mysql直接cmd使用 切换到文件存放目录 直接使用命令即可 mysqldump -uroot -p databasename >data.sql
详谈mysqldump数据导出的问题
12-16
1，使用mysqldump时报错(1064)，这个是因为mysqldump版本太低与当前数据库版本不一致导致的。 mysqldump: Couldn’t execute ‘SET OPTION SQL_QUOTE_SHOW_CREATE=1’: You have an error in your SQL syntax; ...
MySQL mysqldump命令使用详解
09-11
MySQL的`mysqldump`命令是数据库管理中不可或缺的一部分，它主要用于从MySQL数据库中导出数据，以实现备份、迁移或数据共享等目的。在本文中，我们将深入探讨`mysqldump`的使用方法及其相关选项。 首先，`mysqldump...
MySQL之导入导出&视图&索引&执行计划
oyang的博客
07-06 1324
数据的导入导出的三种方式1.使用工具，类似sqlyog.Navicat等导入导出delete与truncate 的区别使用mysqldump导入导出（以命令的形式）导出表数据和表结构只导出表结构3.LOAD DATA INFILE,视图1.什么是视图2.视图的作用3.基本语法,三、索引1、什么是索引2、索引的优缺点3、索引分类4、创建索引5.修改索引6.删除索引四、执行计划-Explain,MySql中sql的执行过程图,查询缓存图查询优化图,1.什么是执行计划？2.执行计划的作用：......
mysqldump的tz-utc选项引发的MySQL时区问题
测试0901-1
12-17 1227
背景 在使用mysqldump备份过滤timestamp字段数据时，查看就按导出的文本文件，发现数据与在server端查看的数据不一致 环境 操作系统 centos7.4 MySQL5.7.24 [root@node1 ~]# date Mon Dec 17 11:21:14 CST 2018 [root@node1 ~]# date -R Mon, ...
技术分享 | 改写 mysqldump 解决 DEFINER 问题
ActionTech的博客
09-24 984
作者：xuty 本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 一、背景 项目上 MySQL 还原 SQL 备份 经常会碰到一个错误如下，且通常出现在导入视图、函数、存储过程、事件等对象时，其根本原因就是因为 导入时所用账号 并不具有 SUPER 权限，所以无法创建 其他账号 的所属对象。 ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege.
mysqldump导出数据库视图_mysqldump导出视图数据库
weixin_39773215的博客
12-20 1560
从RDS(兼容MySQL) 导出数据案例分析作者：丁奇背景MySQL全量逻辑备份恢复最基础的方法，就是mysqldump生成文本，再通过source 命令直接导入。一般用于实例迁移或者版本升级。这里说明最近碰到的一个失败例子。描述这个例子可以简要复现如下，在源库上执行如下操作:use mydb;create table...文章阿里云柳璃2014-06-052466浏览量RDS for MySQL...
mysqldump导出数据库视图_mysql中如何用mysqldump批量如何导出视图view啊？
weixin_34186408的博客
01-15 1428
你的位置:问答吧-> MySQL-> 问题详情mysql中如何用mysqldump批量如何导出视图view啊？mysql中如何用mysqldump批量如何导出视图view啊？我有3个库act、fdp、vtd里面有80张左右的视图，我想搬到生产环境中，mysqldump能不能一次批量导出啊？作者: mchdba发...
mysqldump导出数据库视图_mysql导入、导出 ( 带视图）
weixin_39783149的博客
12-20 1077
1创建账号授权grant all privileges on jenkinsddbes.* to 'jenkinsddbes'@'%' identified by '1iN@Da12tA&*ba33gf8';2、导出mysqldump -u root -p jenkinsddbes > 2018-06-07.jenkinsddbes.sql3、导入[root@host-10-1-1-...
在sqlyog进行数据库的备份_狂神说MySQL07：权限及如何设计数据库
weixin_39828783的博客
11-21 147
狂神说MySQL系列连载课程，通俗易懂，基于MySQL5.7.19版本，欢迎各位狂粉转发关注学习。禁止随意转载，转载记住贴出B站视频链接及公众号链接！上课视频同步文档权限及设计数据库用户管理使用SQLyog 创建用户，并授予权限演示基本命令/* 用户和权限管理 */ ------------------用户信息表：mysql.user-- 刷新权限FLUSH PRIVILEGES -- ...
Mysql数据库逻辑备份-mysqldump参数
oradbm的博客
10-10 6990
上面只是展示了对不同引擎来讲加的参数只是为了让数据保持一致性，但在备份中业务并没有停止，时刻可能有新的数据进行写入，为了让我们知道备份时是备份了哪些数据，或者截止到那个指针（二进制日志），我们可以再加入 --master-data参数，备份好的sql文件就会记录从备份截至到哪个指针，指针之后的数据更新我们可以通过二进制日志进行恢复。②、导出单个库：加--lock-tables参数，这会在备份开始的时候锁该库的所有表，其他session可以读但不能更新该库的所有表，该库的数据一致；
mysqldump导出数据库视图_mysqldump的使用--解决不能导出视图的问题
weixin_39550379的博客
12-20 1682
在一个项目的使用过程中，数据库的设计使用了视图；在产品上线的过程中，发现同事恢复的数据库出现了问题，测试人员发现试图中没有数据，而视图基于的表是有数据的，开始以为是关联的字段没有相关匹配出现空数据。后来随着测试的进行，发现以前通过的测试用例不能通过，开发人员说根本就没改过代码；个人觉得问题应该出现在数据库这块，把创建试图的sql语句的select部分通过mysql的客户端执行下，发现有不少数据；然...
mysqldump
05-27
MySQLdump是一个命令行工具，用于将MySQL数据库中的数据转储为SQL格式的文件。这个工具可以用来备份MySQL数据库，也可以用于将数据从一个MySQL数据库转移到另一个MySQL数据库。 使用MySQLdump，你可以选择备份整个...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值