共计 9773 个字符，预计需要花费 25 分钟才能阅读完成。
前言：
在后面文章中，有提到过 mysqldump 备份文件中记录的工夫戳数据都是以 UTC 时区为根底的，在筛选复原单库或单表时要留神时区差异。起初再次查看文档，发现 tz-utc、skip-tz-utc 参数与此有关，本篇文章咱们一起来看下此参数的作用吧。
1.tz-utc 与 skip-tz-utc 参数介绍
这两个参数能够作用于 mysqldump 备份过程中，互为相同参数。顾名思义能够看出，一个参数是将工夫戳改为 UTC 时区，另一个是跳过期区变动。
在 mysql 服务器上执行 mysqldump –help 的命令，能够看到上面一段话。
[root@host ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.7.23, for Linux (x86_64)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
... 省略很多内容
--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′，这个时区是格林威治工夫，也就是 0 时区。这样当导出 timestamp 工夫戳字段时，会把在服务器设置的以后时区下显示的 timestamp 工夫值转化为在格林威治工夫下显示的工夫。比方咱们数据库采纳北京工夫东八区，mysqldump 导出的文件当中显示的 timestamp 工夫值绝对于通过数据库查问显示的工夫倒退了 8 个小时。
晓得了 –tz-utc，那么 –skip-tz-utc 的含意就是当 mysqldump 导出数据时，不应用格林威治工夫，而应用以后 mysql 服务器的时区进行导出，这样导出的数据中显示的 timestamp 工夫值也和表中查问进去的工夫值雷同。
2. 试验参数具体作用
为了更分明理解这对参数的作用，上面咱们来具体测试下，咱们晓得 mysqldump 后能够跟 where 条件来备份局部数据，若依据 timestamp 字段来备份局部数据，这对参数是否有影响呢？咱们一并来验证下：
先来看下我的环境设置及测试数据：
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)
# 时区采纳北京工夫东八区
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +08:00 |
+---------------+--------+
1 row in set (0.00 sec)
# 测试表 有 datetime 字段和 timestamp 字段 共 10 条数据 两个工夫显示是雷同的
mysql> show create table test_tb\G
*************************** 1. row ***************************
Table: test_tb
Create Table: CREATE TABLE `test_tb` (`increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`stu_id` int(11) NOT NULL COMMENT '学号',
`stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
`dt_time` datetime NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',
PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='测试表'
1 row in set (0.00 sec)
mysql> select * from test_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | dt_time | create_time |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | fgds | 2020-07-10 09:43:28 | 2020-07-10 09:43:28 |
| 2 | 1002 | fgsw | 2020-10-10 09:43:28 | 2020-10-10 09:43:28 |
| 3 | 1003 | vffg | 2020-10-10 02:00:00 | 2020-10-10 02:00:00 |
| 4 | 1004 | wdsd | 2020-10-31 23:43:28 | 2020-10-31 23:43:28 |
| 5 | 1005 | grdb | 2020-11-01 00:00:00 | 2020-11-01 00:00:00 |
| 6 | 1006 | sdfv | 2020-11-01 02:00:00 | 2020-11-01 02:00:00 |
| 7 | 1007 | fgfg | 2020-11-06 02:00:00 | 2020-11-06 02:00:00 |
| 8 | 1008 | tyth | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 9 | 1009 | ewer | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 10 | 1010 | erre | 2020-11-11 15:17:03 | 2020-11-11 15:17:03 |
+--------------+--------+----------+---------------------+---------------------+mysqldump 默认开启 tz-utc，先来看下默认状况下的备份后果：
# 为更显著看出后果 咱们应用 skip-extended-insert 来一行行展示数据
# 全库备份
[root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --databases testdb > utc_testdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more utc_testdb.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.23-log
... 省略
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
# 先保留老时区 而后将此会话时区改为 0 时区
... 省略
--
-- Dumping data for table `test_tb`
--
LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (1,1001,'fgds','2020-07-10 09:43:28','2020-07-10 01:43:28');
INSERT INTO `test_tb` VALUES (2,1002,'fgsw','2020-10-10 09:43:28','2020-10-10 01:43:28');
INSERT INTO `test_tb` VALUES (3,1003,'vffg','2020-10-10 02:00:00','2020-10-09 18:00:00');
INSERT INTO `test_tb` VALUES (4,1004,'wdsd','2020-10-31 23:43:28','2020-10-31 15:43:28');
INSERT INTO `test_tb` VALUES (5,1005,'grdb','2020-11-01 00:00:00','2020-10-31 16:00:00');
INSERT INTO `test_tb` VALUES (6,1006,'sdfv','2020-11-01 02:00:00','2020-10-31 18:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-05 18:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 07:17:03');
# 能够看出 timestamp 工夫值减去了 8 小时 而 datetime 工夫值不变
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
# 再将时区改为原时区
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- Dump completed on 2020-11-11 15:34:21
# 应用 where 条件备份单表局部数据 备份 11 月份以来的数据
# 数据库中查问
mysql> select * from test_tb where create_time >= '2020-11-01 00:00:00';
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | dt_time | create_time |
+--------------+--------+----------+---------------------+---------------------+
| 5 | 1005 | grdb | 2020-11-01 00:00:00 | 2020-11-01 00:00:00 |
| 6 | 1006 | sdfv | 2020-11-01 02:00:00 | 2020-11-01 02:00:00 |
| 7 | 1007 | fgfg | 2020-11-06 02:00:00 | 2020-11-06 02:00:00 |
| 8 | 1008 | tyth | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 9 | 1009 | ewer | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 10 | 1010 | erre | 2020-11-11 15:17:03 | 2020-11-11 15:17:03 |
+--------------+--------+----------+---------------------+---------------------+
6 rows in set (0.00 sec)
# mysqldump 导出
[root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert testdb test_tb --where "create_time >='2020-11-01 00:00:00' " > utc_testdb2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more utc_testdb2.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.23-log
...
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
... 省略
--
-- Dumping data for table `test_tb`
--
-- WHERE: create_time >= '2020-11-01 00:00:00'
LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-05 18:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 07:17:03');
# 发现只导出 4 条
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2020-11-11 15:58:56倡议各位认真看下下面导出后果，说实话，笔者原来也没做过具体测试，当初看到后果也是略微有点吃惊的。默认状况下，全备进去的数据是没问题的，尽管将 timestamp 工夫值转为 0 时区显示，但当你导入数据库时还会以你的数据库时区来展现 timestamp 工夫。但应用 where 条件导出局部数据时，却呈现了数据库中查问得出的后果与 dump 导出的后果不同的状况，这个时候 mysqldump 只导出了转化成 0 时区后的工夫值合乎 where 条件的数据，与间接查问出的后果有出入，这是我原来没留神到的。
再来看下应用 –skip-tz-utc 参数，看下这个参数是否合乎咱们的预期：
# 应用 skip-tz-utc 全备
[root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --skip-tz-utc --databases testdb > skiputc_testdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more skiputc_testdb.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.23-log
.. 省略 未见时区更改语句
--
-- Dumping data for table `test_tb`
--
LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (1,1001,'fgds','2020-07-10 09:43:28','2020-07-10 09:43:28');
INSERT INTO `test_tb` VALUES (2,1002,'fgsw','2020-10-10 09:43:28','2020-10-10 09:43:28');
INSERT INTO `test_tb` VALUES (3,1003,'vffg','2020-10-10 02:00:00','2020-10-10 02:00:00');
INSERT INTO `test_tb` VALUES (4,1004,'wdsd','2020-10-31 23:43:28','2020-10-31 23:43:28');
INSERT INTO `test_tb` VALUES (5,1005,'grdb','2020-11-01 00:00:00','2020-11-01 00:00:00');
INSERT INTO `test_tb` VALUES (6,1006,'sdfv','2020-11-01 02:00:00','2020-11-01 02:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-06 02:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 15:17:03');
# timestamp 工夫值显示与 datetime 显示一样 未做转换
UNLOCK TABLES;
-- Dump completed on 2020-11-11 16:23:32
# 应用 skip-tz-utc 备份局部数据
[root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --skip-tz-utc testdb test_tb --where "create_time >='2020-11-01 00:00:00' " > skiputc_testdb2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more skiputc_testdb2.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.23-log
.. 省略
--
-- Dumping data for table `test_tb`
--
-- WHERE: create_time >= '2020-11-01 00:00:00'
LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (5,1005,'grdb','2020-11-01 00:00:00','2020-11-01 00:00:00');
INSERT INTO `test_tb` VALUES (6,1006,'sdfv','2020-11-01 02:00:00','2020-11-01 02:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-06 02:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 15:17:03');
# 6 条数据 和数据库中查问统一
UNLOCK TABLES;
-- Dump completed on 2020-11-11 16:28:39从下面后果能够看出，应用 –skip-tz-utc 参数后，timestamp 工夫戳字段值不会转换，导出局部数据也合乎预期。
3. 一些小倡议
那么这个参数的意义何在呢？当你的数据库服务器处于不同时区时。假如一个服务器在北京（东八区），一个服务器在东京（东九区），当初须要将北京服务器里的数据导入至东京服务器。当导入依照默认不加 –skip-tz-utc 参数的 dump 文件，查问的 timestamp 工夫数据绝对于在之前的东八区服务器的工夫值多了一个小时，但因为东八区服务器里的 13 点和东九区服务器里的 14 点代表的是同一时刻，所以，在东九区的服务器里显示的多出的一个小时，这样显示是正确的。而如果减少 –skip-tz-utc 参数，dump 文件导入东九区服务器后，只管显示的工夫值和之前东八区服务器显示的工夫值雷同，但两者代表的时刻却曾经不同。
对于这个参数应该如何应用，咱们首先应该明确，是否加上 –skip-tz-utc 参数，只会影响 timestamp 字段的导入导出，对 datetime 工夫字段不会影响。
这里笔者倡议首先对 timestamp 字段应用作出标准。比方 timestamp 字段只用于创立工夫和更新工夫需要，只代表该行数据的创立及更新工夫，做到与业务弱相干，其余工夫字段尽量应用 datetime。这样即便 mysqldump 采纳不同参数，理论产生影响也不大。
如果你的服务器处于不同时区，那倡议还是依照默认来，这样导入导出的数据都是正确的。如果你的服务器都是处于同一时区，那么是否应用 –skip-tz-utc 参数区别不大，咱们只需晓得默认状况 mysqldump 会将 timestamp 工夫值转为 0 时区存储即可。当备份局部数据且以 timestamp 字段来筛选时，这时候倡议减少 –skip-tz-utc 参数。这里再次揭示下，从全备中筛选单库或单表的备份时，也要留神下 timestamp 字段数据。
参考：
- https://zhuanlan.zhihu.com/p/99395517
正文完
