百度360必应搜狗淘宝本站头条
分布式事务框架shiro框架java日志框架框架图cad怎么画框架
当前位置：网站首页 > 技术文章 > 正文

关于mysqldump，这个参数你可能还不知道

ccwgpt 2024-12-23 10:34 49 浏览 0 评论

前言：

在前面文章中，有提到过 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

相关推荐

go-admin开源项目，快速搭建一个管理后台系统，直接二次开发上线

#头条创作挑战赛#目录1，关于go-admin2，使用go-admin进行构建3，使用go-admin进行数据库，后端初始化4，下载前端代码，进行node编译5，总结1，关于go-admingithu...

【开源】一款高效优雅的 Vite+Vue3 中后台管理模板——Arco-Admin

今天给大家分享一款开源的基于Vite、TypeScript和Vue3的中后台前端框架，结合了ArcoDesign提供的优雅设计与强大功能。值得一提的是，ArcoDesign是字节跳...

看看这样的Dotnet后台管理，那真是叫一个清新优雅高颜值!!!

MalusAdmin基于Vue3/TypeScript/NaiveUI和NET7&Sqlsugar开发的后台管理框架。采用最原生最简洁的方式来实现,前端清新优雅高颜值，后端结...

NET 7 + Vue.js 的前后端分离的通用后台管理系统框架

DncZeus项目简介DncZeus是一个基于.NET7+Vue.js的前后端分离的通用后台管理系统框架。后端使用.NET7+EntityFrameworkCore构建，UI则...

后台管理系统这么受欢迎吗?又 Go 一个开源项目

大家好，我是欧盆索思（opensource），每天为你带来优秀的开源项目！之前推荐过两款后台管理系统，都是Go语言实现的。十分钟内构建数据可视化和管理后台：还同时支持众多框架基于Go语言Gi...

支持AI + 低代码!一款开箱即用的强大权限管理系统

写在前面大家好，这里是IT学习日记。今日推荐项目：cool-admin，获取方式在文后!1000+优质开源项目推荐进度：51/1000。如需更多类型优质项目推荐，请在文章后留言。项目简介如果你在为设计...

使用 Flask-Admin 快速开发博客后台管理系统:关键要点解析

一、为什么选择Flask-Admin？Flask-Admin是Flask生态中高效的后台管理框架，核心优势在于：-零代码生成CRUD界面：基于数据库模型自动生成增删改查功能-高度可定制...

通用后台管理系统需求及原型设计（后台管理系统需求分析）

编辑导读：后台管理系统，会根据不同公司、不同业务的要求做出改变。那么，有没有通用的功能和和需求设计模版呢？有的。本文作者基于自身工作经验，总结了一套通用的后台管理系统需求及原型设计，与你分享。网上很多...

基于 Vue3 后台管理平台Vue3.x-Admin

今天给大家分享一个Vue3.0框架搭建的后台管理模板Vue3.xAdmin。vue3.x-admin使用vue3开发的后台管理系统。主要包括CSS3特效、可拖拽的div、图表、益智小游戏、vuex4...

火爆全网:后台管理系统源码分享(项目部署+前后端手册+运维)

这是一款基于SpringBoot2.1.0、Jpa、SpringSecurity、redis、Vue的前后端分离的后台管理系统，项目采用分模块开发方式，权限控制采用RBAC，支持数...

好多程序员都在用的通用管理后台—likeadmin

前言作为一个程序员，最害怕的就是每次开新项目时，总是要从用户、角色、菜单、接口等一系列功能从头开始写代码，重复的工作实在是太多了，即耗费时间，又耗费精力，这个时候就需要一些能提高效率的工具，例如一个通...

Axure无限级导航菜单（axure导航栏左右滑动怎么做）

在Axure设计中，树组件是展示层级结构数据的关键UI组件，广泛应用于文件管理、导航菜单和数据分类等场景。本篇文章将详细解析如何在Axure中构建一个实用的无限级导航菜单，帮助你高效管理和...

三星调整HBM团队组织架构 押宝定制化HBM

【三星调整HBM团队组织架构押宝定制化HBM】《科创板日报》27日讯，消息称，三星电子DS（设备解决方案）部门负责人全永铉正在进行内部组织大幅调整。他将三星HBM开发团队细分为标准HBM、定制化HB...

北森组织架构一览（北森总部地址）

北森的职级组织架构和职级名称都很有意思。可以作为企业参考。1）培养路径：森小白（BeisenNewcomer）：新入职员工。森小贤（BeisenSage）：L1层级森大侠（BeisenWarri...

高手正在使用的四个PLC编程思路，吃透让你少走20年弯路

在工业自动化领域，PLC（可编程逻辑控制器）编程是核心技能之一。掌握高效的编程思路不仅能提升工作效率，还能减少调试过程中的错误。以下是四条经过PLC工程师实践检验的编程思路，理解并运用这些思路可以让你...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表