首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 后端 > 正文

mysqldump你可能不知道的参数

作者：奋肀斗小族 | 来源：互联网 | 2022-03-18 16:47
这篇文章主要介绍了mysqldump你可能不知道的参数，帮助大家更好的理解和使用MySQL，感兴趣的朋友可以了解下

在前面文章中，有提到过 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 参数的资料请关注其它相关文章！


推荐阅读
  • mysql

    如何限制php数据库链接数和连接超时时间？

    本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目，以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数，以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]
  • mysql

    Android 新闻App的本地服务器搭建教程

    本文介绍了在开发Android新闻App时，搭建本地服务器的步骤。通过使用XAMPP软件，可以一键式搭建起开发环境，包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表，并设置相应的属性。最后，给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]
  • mysql

    Hibernate配置lazy=false时无法加载数据的问题解决方法

    本文介绍了在Hibernate配置lazy=false时无法加载数据的问题，通过采用OpenSessionInView模式和修改数据库服务器版本解决了该问题。详细描述了问题的出现和解决过程，包括运行环境和数据库的配置信息。 ... [详细]
  • ftp

    Nginx使用AWStats日志分析的步骤及注意事项

    Nginx使用AWStats日志分析的步骤及注意事项
    本文介绍了在Centos7操作系统上使用Nginx和AWStats进行日志分析的步骤和注意事项。通过AWStats可以统计网站的访问量、IP地址、操作系统、浏览器等信息，并提供精确到每月、每日、每小时的数据。在部署AWStats之前需要确认服务器上已经安装了Perl环境，并进行DNS解析。 ... [详细]
  • rust

    基于PgpoolII的PostgreSQL集群安装与配置教程

    基于PgpoolII的PostgreSQL集群安装与配置教程
    本文介绍了基于PgpoolII的PostgreSQL集群的安装与配置教程。Pgpool-II是一个位于PostgreSQL服务器和PostgreSQL数据库客户端之间的中间件，提供了连接池、复制、负载均衡、缓存、看门狗、限制链接等功能，可以用于搭建高可用的PostgreSQL集群。文章详细介绍了通过yum安装Pgpool-II的步骤，并提供了相关的官方参考地址。 ... [详细]
  • rust

    SQL日志收缩及截断方法详解

    本文详细介绍了SQL日志收缩的方法，包括截断日志和删除不需要的旧日志记录。通过备份日志和使用DBCC SHRINKFILE命令可以实现日志的收缩。同时，还介绍了截断日志的原理和注意事项，包括不能截断事务日志的活动部分和MinLSN的确定方法。通过本文的方法，可以有效减小逻辑日志的大小，提高数据库的性能。 ... [详细]
  • sockets

    Python高级之网络编程及TCP/IP协议簇的OSI七层模型介绍

    本文介绍了Python高级网络编程及TCP/IP协议簇的OSI七层模型。首先简单介绍了七层模型的各层及其封装解封装过程。然后讨论了程序开发中涉及到的网络通信内容，主要包括TCP协议、UDP协议和IPV4协议。最后还介绍了socket编程、聊天socket实现、远程执行命令、上传文件、socketserver及其源码分析等相关内容。 ... [详细]
  • mysql

    推荐一个ASP的内容管理框架（ASP Nuke）的优势和适用场景

    推荐一个ASP的内容管理框架（ASP Nuke）的优势和适用场景
    本文推荐了一个ASP的内容管理框架ASP Nuke，并介绍了其主要功能和特点。ASP Nuke支持文章新闻管理、投票、论坛等主要内容，并可以自定义模块。最新版本为0.8，虽然目前仍处于Alpha状态，但作者表示会继续更新完善。文章还分析了使用ASP的原因，包括ASP相对较小、易于部署和较简单等优势，适用于建立门户、网站的组织和小公司等场景。 ... [详细]
  • mysql

    Linux服务器密码过期策略、登录次数限制、私钥登录等配置方法

    Linux服务器密码过期策略、登录次数限制、私钥登录等配置方法
    本文介绍了在Linux服务器上进行密码过期策略、登录次数限制、私钥登录等配置的方法。通过修改配置文件中的参数，可以设置密码的有效期、最小间隔时间、最小长度，并在密码过期前进行提示。同时还介绍了如何进行公钥登录和修改默认账户用户名的操作。详细步骤和注意事项可参考本文内容。 ... [详细]
  • mysql

    rhel5.5搭建网关+LAMP+postfix+dhcp的步骤和配置方法

    本文介绍了在rhel5.5操作系统下搭建网关+LAMP+postfix+dhcp的步骤和配置方法。通过配置dhcp自动分配ip、实现外网访问公司网站、内网收发邮件、内网上网以及SNAT转换等功能。详细介绍了安装dhcp和配置相关文件的步骤，并提供了相关的命令和配置示例。 ... [详细]
  • mysql

    搭建Windows Server 2012 R2 IIS8.5+PHP（FastCGI）+MySQL环境的详细步骤

    搭建Windows Server 2012 R2 IIS8.5+PHP（FastCGI）+MySQL环境的详细步骤
    本文详细介绍了搭建Windows Server 2012 R2 IIS8.5+PHP（FastCGI）+MySQL环境的步骤，包括环境说明、相关软件下载的地址以及所需的插件下载地址。 ... [详细]
  • mysql

    如何在MySQL中将零值替换为先前的非零值？

    本文介绍了如何在MySQL中将零值替换为先前的非零值的方法，包括使用内联查询和更新查询。同时还提供了选择正确值的方法。 ... [详细]
  • mysql

    PHP设置MySQL字符集的方法及使用mysqli_set_charset函数

    PHP设置MySQL字符集的方法及使用mysqli_set_charset函数
    本文介绍了PHP设置MySQL字符集的方法，详细介绍了使用mysqli_set_charset函数来规定与数据库服务器进行数据传送时要使用的字符集。通过示例代码演示了如何设置默认客户端字符集。 ... [详细]
  • mysql

    mysql分组排序_在MySQL中实现分组排序功能

    在数据分析工作中，我们通常会遇到这样的问题，一个业务部门由若干业务组构成，需要筛选出每个业务组里业绩前N名的业务员。这其实是一个分组排序的 ... [详细]
  • mysql

    Centos7.6安装Gitlab教程及注意事项

    Centos7.6安装Gitlab教程及注意事项
    本文介绍了在Centos7.6系统下安装Gitlab的详细教程，并提供了一些注意事项。教程包括查看系统版本、安装必要的软件包、配置防火墙等步骤。同时，还强调了使用阿里云服务器时的特殊配置需求，以及建议至少4GB的可用RAM来运行GitLab。 ... [详细]
author-avatar
奋肀斗小族
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有