mysqldump你可能不知道的参数

数据库 2024/11/23 佚名

3 2 1

在前面文章中，有提到过 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 字段数据。

以上就是mysqldump你可能不知道的参数的详细内容，更多关于mysqldump 参数的资料请关注其它相关文章！

mysqldump,参数,mysqldump

标签：

mysqldump,参数,mysqldump

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“mysqldump你可能不知道的参数”

mysqldump你可能不知道的参数

暂无“mysqldump你可能不知道的参数”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

mysqldump你可能不知道的参数

mysqldump,参数,mysqldump

IDEA无法连接mysql数据库的6种解决方法大全

IDEA连接不上MySQL端口号占用的解决

评论“mysqldump你可能不知道的参数”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

mysqldump你可能不知道的参数

mysqldump,参数,mysqldump

IDEA无法连接mysql数据库的6种解决方法大全

IDEA连接不上MySQL端口号占用的解决

评论“mysqldump你可能不知道的参数”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存